3

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:

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.

  • 1
    Why not put the parameters into a temporary table and then join to that table in the stored procedure? That should be fairly DBMS independent. – a_horse_with_no_name Feb 9 '15 at 8:55
  • It would also be terribly inefficient, because you will add at least one extra round trip to the system (plus add load to the temp database) – Thomas Kejser Feb 9 '15 at 14:14
  • @ThomasKejser: not sure what you mean with "temp database". I was talking about a temp table not a temp *database. As I see it: it's only one additional roundtrip (either using on insert with many rows or in case of Oracle a batched insert). Most DBMS will be more efficient when using a JOIN rather than a large IN clause, so that might alleviate the cost of the additional round trip(s) - I would at least give this a try and test the performance. – a_horse_with_no_name Feb 9 '15 at 14:25
  • @a_horse_with_no_name: If you create a temp table in the database, you are putting stress on the allocation structures, either in tempdb (in the case of SQL Server, mySQL and Postgres) or in the Oracle caches. There are only so many temp tables you can create/sec before you hit scale trouble. – Thomas Kejser Feb 9 '15 at 14:38
3

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.

1

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.