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.
2 Answers 2
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.
Another possibility:
- Encode the values with Base64.
- Concatenate the values into a comma-separated string.
- Pass the resulting string.
- Split the string in the stored procedure.
- Decode the strings using Base64.
Base64 encodes no comma in its default character set and either is or appears to be supported by:
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.
INclause, so that might alleviate the cost of the additional round trip(s) - I would at least give this a try and test the performance.