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

Question

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 makes parameter encoding using comma-separated strings tricky. (Generally speaking, any separator can appear as a character somewhere in the values, which includes escaped separators as well, such as \,.)

This seems like the most database-agnostic solution, but implementing a CSV decoding routine across multiple databases in PL/SQL is neither trivial nor efficient.

Idea #3

Use Hibernate as an abstraction layer, then implement a JPQL routine that passes in the array of name/value pairs. For example, calling query.setParameterList, which might only work for IN clauses, rather than stored procedure parameters.

Answer 1

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.

Answer 2

Another possibility:

1. Encode the values with Base64.
2. Concatenate the values into a comma-separated string.
3. Pass the resulting string.
4. Split the string in the stored procedure.
5. Decode the strings using Base64.

Base64 encodes no comma in its default character set and either is or appears to be supported by:

• Oracle
• SQL Server
• Sysbase
• PostgreSQL
• MySQL
• Teradata?
• DB2?
• Informix?

Note that for SQL Server, UTF-8 encoded text is not supported, so UTF-16 must be used. This relies on basic string splitting functionality, which is reasonably ubiquitous.