Re: Error While trying to use Functions which return Resultsets

Philip A. Chapman wrote:
> Everyone,
>
> I am receiving an error when I attempt the ResultSet.next() method on a
> ResultSet returned from a function. I am using PostgreSQL server and
> JDBC drivers compiled from the 7.4.1 source. I have attempted to
> emulate the first example given in the documentation (31.5.2 Obtaining
> ResultSet from a stored function). I've googled and cannot seem to find
> any mention of this error.
>
> I would appreciate any help that you may be able to provide.

While I'm not sure exactly how it applies to refcursor-returning
functions, the problem will be that all cursors are closed at the end of
a transaction unless you specify HOLD. So with autocommit on, by the
time the returned cursor is used to fetch the results, it's already been
closed.

I'm not sure how you'd specify use of HOLD in that function though.
Probably the simplest solution is to turn off autocommit unless you have
good reasons for turning it on.

> *** The Exception:
>
> Tue Feb 03 08:49:50 CST 2004
> org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>"
> does not exist

[...]

> // Turn transactions off.
> con.setAutoCommit(true);

[...]

-O