SEO

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.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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.


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc