Thread: CallableStatement and getUpdateCount

Hi,

I have a query about using CallableStatement with functions that return
a SETOF. I know the "Calling Stored Functions" documentation says use a
Statement or a PreparedStatement - I'm in the process of porting over a
database and would like to leave my calling code the same - that means
CallableStatement (more in a moment).

First, an example. A simple function (I know the SETOF is redundant in
this example, normally it wouldn't be) -

    CREATE OR REPLACE FUNCTION cstest()
    RETURNS SETOF integer AS
    $$
        SELECT 1;
    $$
    LANGUAGE 'sql' VOLATILE;

The calling code -

    Class.forName("org.postgresql.Driver");
    Connection con = DriverManager.getConnection("...");

    CallableStatement cs = con.prepareCall("{call cstest()}");
    cs.execute();

    System.out.println("update count - should be -1? " +
cs.getUpdateCount());

    ResultSet rs = cs.getResultSet();
    while(rs.next())
    {
        System.out.println(rs.getInt(1));
    }
    rs.close();
    cs.close();
    con.close();

The code works fine and returns the record correctly. My problem is the
getUpdateCount after the execute - from the java.sql.Statement
documentation "if the result is a ResultSet object or there are no more
results, -1 is returned". It actually comes back as 1. Is this a bug or
have I missed something?

I found this problem via Spring's
org.springframework.jdbc.object.StoredProcedure class - it doesn't find
any ResultSets because it relies on the value of getUpdateCount.

I'm running PostgreSQL 8.3.0 with postgresql-8.3-603.jdbc4.

Thanks in advance.

Sam.

Sam Lawrence wrote:
> I have a query about using CallableStatement with functions that return
> a SETOF. I know the "Calling Stored Functions" documentation says use a
> Statement or a PreparedStatement - I'm in the process of porting over a
> database and would like to leave my calling code the same - that means
> CallableStatement (more in a moment).
>
> First, an example. A simple function (I know the SETOF is redundant in
> this example, normally it wouldn't be) -
>
>     CREATE OR REPLACE FUNCTION cstest()
>     RETURNS SETOF integer AS
>     $$
>         SELECT 1;
>     $$
>     LANGUAGE 'sql' VOLATILE;
>
> The calling code -
>
>     Class.forName("org.postgresql.Driver");
>     Connection con = DriverManager.getConnection("...");
>
>     CallableStatement cs = con.prepareCall("{call cstest()}");
>     cs.execute();
>
>     System.out.println("update count - should be -1? " + cs.getUpdateCount());
>
>     ResultSet rs = cs.getResultSet();
>     while(rs.next())
>     {
>         System.out.println(rs.getInt(1));
>     }
>     rs.close();
>     cs.close();
>     con.close();
>
> The code works fine and returns the record correctly. My problem is the
> getUpdateCount after the execute - from the java.sql.Statement
> documentation "if the result is a ResultSet object or there are no more
> results, -1 is returned". It actually comes back as 1. Is this a bug or
> have I missed something?
>
> I'm running PostgreSQL 8.3.0 with postgresql-8.3-603.jdbc4.

Hmmm. getUpdateCount() is defined in
org/postgresql/jdbc2/AbstractJdbc2Statement.java as

    public int getUpdateCount() throws SQLException
    {
[...]
        if (isFunction)
            return 1;
[...]
    }

So it will always return 1 for a callable statement.

There is no comment in the source. Does anybody know why that is?

Yours,
Laurenz Albe

On Tue, 1 Apr 2008, Albe Laurenz wrote:

> Hmmm. getUpdateCount() is defined in
> org/postgresql/jdbc2/AbstractJdbc2Statement.java as
>
>        if (isFunction)
>            return 1;
>

I would guess that this code was conceived without regard to returning
sets.  For code that does {? = call f()} you expect the caller to fetch
the result using CallableStatement.getXXX() so that's why the code isn't
indicating that a ResultSet is returned even though there is one under the
hood.  The JDBC driver has no idea whether the function it's calling is
returning a SETOF or not, so it can't use that to determine what to return
for getUpdateCount.

Perhaps we can differentiate between calls of the form {call f()} and {? =
call f()} ?

Kris Jurka

Kris Jurka wrote:
> > Hmmm. getUpdateCount() is defined in
> > org/postgresql/jdbc2/AbstractJdbc2Statement.java as
> >
> >        if (isFunction)
> >            return 1;
>
> I would guess that this code was conceived without regard to returning
> sets.  For code that does {? = call f()} you expect the caller to fetch
> the result using CallableStatement.getXXX() so that's why the code isn't
> indicating that a ResultSet is returned even though there is one under the
> hood.  The JDBC driver has no idea whether the function it's calling is
> returning a SETOF or not, so it can't use that to determine what to return
> for getUpdateCount.
>
> Perhaps we can differentiate between calls of the form {call f()} and {? =
> call f()} ?

If I understood correctly then there *is* a result set in the case mentioned.

Would it work as desired if the two checks in getUpdateCount were reversed?

        if (result.getResultSet() != null)
            return -1;

        if (isFunction)
            return 1;

Or is there a problem I do not see?

Yours,
Laurenz Albe

On Wed, 2 Apr 2008, Albe Laurenz wrote:

> If I understood correctly then there *is* a result set in the case mentioned.
>
> Would it work as desired if the two checks in getUpdateCount were reversed?
>
>        if (result.getResultSet() != null)
>            return -1;
>
>        if (isFunction)
>            return 1;
>

Reading some more, the whole "if (isFunction)" line is bogus.  I was
thinking that the current situation was that all function calls returned
an update count of 1, but that's not true.  If you look at the code in
AbstractJdbc2Statement.executeWithFlags(int) you can see that if someone
has registered an output parameter (returnTypeSet), then it extracts the
results out of the underlying ResultSet and discards it (sets it to null).
So when getUpdateCount is called, the first result == null check is hit so
we return -1.

So right now the only case we're returning 1 is when we actually do have a
ResultSet which is exactly the opposite of what we want.  I've applied the
attached patch to CVS and backpatched to 8.0.

Kris Jurka

Kris Jurka wrote:
> So right now the only case we're returning 1 is when we actually do
> have a ResultSet which is exactly the opposite of what we want.  I've
> applied the attached patch to CVS and backpatched to 8.0.
>
> Kris Jurka
Many thanks Kris - that fixes it. I can also confirm that Spring's
org.springframework.jdbc.object.StoredProcedure now behaves as expected.

Sam.

Kris Jurka wrote:
> So right now the only case we're returning 1 is when we actually do have a
> ResultSet which is exactly the opposite of what we want. I've applied the
> attached patch to CVS and backpatched to 8.0.

Cool, thanks!

Laurenz Albe