Consider accessing XMLSERVICE with UDFs rather than stored procedures

[alanseiden]

Stored procedures with in/out parameters have served us fairly well, with a few drawbacks:

• each database extension (ibm_db2, pdo) has its own way to bind in/out params
• odbc procedural extension does not handle output parameters, so a separate resultset technique had to be used
• a "plugSize" has to be set so that the toolkit can choose the right stored procedure having the correct size output parameter. This size can only be guessed at by the programmer, resulting in errors and confusion
• multiple stored procedures must be shipped for various plugSize values as well as for odbc (resultset style) and all other extensions (output parameter style)

A user-defined function (UDF) would be well-suited for XMLSERVICE's parameters: multiple in, a single value out. Other advantages:

• test XMLSERVICE calls easily in an SQL statement, such as: SELECT xmlservice(internalKey, controlKey, inputXml) as outputXml
• would need only a single database object (the UDF) rather than multiple stored procedures
• simpler toolkit code
• avoid the difficulties of ibm_db2 and PDO bound variables
• reduced learning curve

[kadler]

You may still want multiple "plug" sizes, since that constrains the size of the buffer allocated when binding the parameter from the result set. eg. if you have just one procedure that returns a 2GiB LOB, ibm_db2 will try to allocate 2GiB of data for it since that's the max size.

I believe the current code is constrained by the max size of a space pointer (16MiB - 1024) so we could probably just set it to that. You could still end up allocating a much larger buffer than you need, but in a 64-bit process, it shouldn't affect things that much.

[alanseiden]

Thanks, @kadler. My suggestion/request would be to start with a single-plug-size-UDF as you described above and see if it's "efficient enough." Doing it this way would reduce the learning curve for users and virtually eliminate the user error of selecting a too-small plug size and receiving accidentally truncated output XML. If peformance is acceptable then this technique would simplify calling XMLSERVICE, even to test program calls via straight SQL without a toolkit.

[kadler]

Agreed. Start with the common case and specialize as-needed.

[alanseiden]

A review of this issue still finds it a worthy endeavor, both to eliminate the impact of driver differences and to make XMLSERVICE easier to call from within SQL queries.