JDBC support for CALL / PERFORM

Moved from the INTERFACES list.

Kris Jurka wrote:
> On Mon, 13 Feb 2006, Guy Rouillier wrote:
>
>> I have some Java code that I'm trying to convert from Oracle to PG.
>> This code uses the JDBC batch functionality to submit batches of
>> stored procedures invocations using the "call" syntax. I implemented
>> the same stored functions in PG, having them return void. I
>> converted the batch statements to use "select" with these stored
>> functions. Even though the stored functions return void, the select
>> is still producing a result set, and JDBC does not allow results
>> with batches.
>>
>> I'd like to take a crack at adding CALL (for Oracle and general JDBC
>> compatibility) and/or PERFORM (for PL/SQL compatibility) to the JDBC
>> driver. My approach would be to simply substitute SELECT, then
>> discard the result set upon completion.
>
> You shouldn't need to do anything other than discard the results
> instead of erroring for CallableStatement batches. You shouldn't do
> any messing with the SQL and CALL or PERFORM. This should be handled
> by the standard {call } syntax.
>
> Also JDBC messages should go to the jdbc list,
> pgsql-jdbc(at)postgresql(dot)org(dot)
>
> Kris Jurka

Ok, thanks. I haven't begun to look at the code yet, but the general
approach will be

(1) Execute each statement in the batch one at a time.
(2) If a particular statement encounters this one error (result not
allowed in batch), and if the statement is a callable statement, then
absorb the error, discard the result set and continue.
(3) Otherwise (not this particular error or not a callable statement),
then allow the error to propagate as currently implemented.

The reason I started out with the idea of introducing CALL and/or
PERFORM is that a non-trivial amount of Java code working with Oracle
uses SQL strings that begin "call ...", i.e., not an escaped call but an
actual Oracle-proprietary SQL call. While doing as I suggested make
converting such code easier, I understand that introducing one kludge to
accommodate another is probably not a great idea. Better to have the
programmer go back and fix the original kludge.

--
Guy Rouillier