Thread: Calling stored functions in executeBatch

I've browsed the archives and understand that I am not supposed to
submit SELECT statements in batch.  I'm converting some code from Oracle
that invokes stored procedures via batch (for high volume inserts.)
I've got the stored proc converted, but invoking in batch using "select
my_proc(x,y,z)" produces the predictable "result not expected", even
though my stored proc returns VOID.

Is there any way I can absorb the return value from the select to
prevent this error?  I'd like to avoid rewriting this code as I want it
to continue to run with Oracle until I successfully sell the conversion
idea.  I'm able to do this so far by changing the SQL statements to be

INVOKE + "my_proc(x,y,z)"

and then just setting INVOKE to either "call" or "select" for Oracle and
PostgreSQL, respectively.  Will I run into the same problem if I change
these SQL statements to use the call escape format?

Thanks.

--
Guy Rouillier

On Fri, 11 Mar 2005 19:21:34 -0500
Guy Rouillier <guy-rouillier@speakeasy.net> wrote:

> I've browsed the archives and understand that I am not supposed to
> submit SELECT statements in batch.  I'm converting some code from
> Oracle that invokes stored procedures via batch (for high volume
> inserts.) I've got the stored proc converted, but invoking in batch
> using "select my_proc(x,y,z)" produces the predictable "result not
> expected", even though my stored proc returns VOID.
>
> Is there any way I can absorb the return value from the select to
> prevent this error?  I'd like to avoid rewriting this code as I want
> it to continue to run with Oracle until I successfully sell the
> conversion idea.  I'm able to do this so far by changing the SQL
> statements to be
>
> INVOKE + "my_proc(x,y,z)"
>
> and then just setting INVOKE to either "call" or "select" for Oracle
> and PostgreSQL, respectively.  Will I run into the same problem if I
> change these SQL statements to use the call escape format?

Answering the last part of my own question.  I wrote a simple program to
execute my stored procedure using the call escape mechanism:

    CallableStatement s = conn.prepareCall("{ call insert_t1(?)}");
    s.setInt(1, 1);
    s.executeUpdate();

When I ran this, it invoked the stored procedure successfully (i.e., a
new row appears in the target table).  But then it throws the following
exception (the same one I'm seeing when using "select insert_t1(1)"
with batch):

Exception caught: org.postgresql.util.PSQLException: A result was
returned when none was expected.

So my conclusion is that you simply can't use PostgreSQL stored
functions with batches.

Something seems odd though both with this example and with my batch
test.  In both cases, the update is committed to the database, then the
exception is thrown.  I understand the reason is that everything in the
database went ok; the problem is in the JDBC driver not expecting a
return value.  It seems that if the driver is going to throw an
exception, it shouldn't commit the changes to the database.  But I
understand autocommmit is on, so by the time the driver encounters its
problem, the change has already been committed.

At the very least, warn people that it is possible to get committed
changes followed by an exception.

--
Guy Rouillier

Guy Rouillier wrote:

> Answering the last part of my own question.  I wrote a simple program to
> execute my stored procedure using the call escape mechanism:
>
>     CallableStatement s = conn.prepareCall("{ call insert_t1(?)}");

> Exception caught: org.postgresql.util.PSQLException: A result was
> returned when none was expected.
>
> So my conclusion is that you simply can't use PostgreSQL stored
> functions with batches.

At the moment, no. The { call } escape is currently translated to a
SELECT. I suppose we could do a special case to handle batching of that
(ignoring the "unexpected" resultset because we know there's no real
result there), but it seems pretty hairy, and I think the JDBC spec only
requires INSERT/UPDATE/DELETE to be supported.

> At the very least, warn people that it is possible to get committed
> changes followed by an exception.

Well, that's generally true when using autocommit anyway. Consider a
network or server failure just after the implicit commit but before the
client has seen the response.

-O