Database-agnostic stored procedure call passing array of name/value pairs

Background

Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows:

CREATE TYPE array_parameters AS (
  v_name VARCHAR2(255),
  v_value CLOB
);

This structure, which can have equivalent definitions in most modern relational databases, is being proposed as a way to pass an arbitrary number of name/value pairs into a stored procedure. The stored procedure call resembles:

SELECT rxm( '...map...', array_parameters );

Where the ...map... can include any number of variable references, taking the following form:

account.id = $id &&
person.last_name = $surname && ...

The array_parameters, in theory, could be populated as:

array_parameters[0].v_name = "$id";
array_parameters[0].v_value = "123456789";
array_parameters[1].v_name = "$surname";
array_parameters[1].v_value = "O'Malley, The \"Great\"";

Problem

JDBC4 defines a method called createArrayOf, which is the New South China Mall of APIs:

• Unsupported by Oracle

• Unsupported by MySQL

• Unsupported by Microsoft SQL Server

• Unsupported by Apache Derby

• Unsupported by Sybase

Without the ability to create the name/value pair array, I can see no obvious way to pass in the values without resorting to database-specific implementations (such as using Oracle's ARRAY, or obtuse contortions to support MySQL).

Question

How would you define and then call a stored procedure that can take an arbitrary number of name/value pairs in a database-agnostic fashion?

Idea #1

One idea would be to define two string arrays, rather than an object array structure, and call the stored procedure as follows:

SELECT rxm( '...map...', array_names, array_values );

The two arrays would be index-linked, but this likely depends on createArrayOf(), as well.

Idea #2

It might be possible to pass the pairings as comma-separated strings. However, the values could contain commas themselves, which make

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language:

• Postgres

• MySQL

• SQL Server

• Oracle

• DB2 UDM

Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.

StackExchange Database Administrators Q#91450, answer score: 3