Re: Calling stored functions in executeBatch

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