Error While trying to use Functions which return Resultsets

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.

*** The Exception:

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

at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:67)
at
org.postgresql.jdbc3.Jdbc3RefCursorResultSet.next(Jdbc3RefCursorResultSet.java:42)
at us.pcsw.billing.data.Entity.lookupClientEntities(Entity.java:238)
<snip>

*** The Function:

CREATE FUNCTION
SelClientEntities
() RETURNS REFCURSOR
AS
'DECLARE
vRef REFCURSOR;
BEGIN
OPEN
vRef
FOR
SELECT DISTINCT
ClientEntityID
FROM
Contract
;
RETURN vRef;
END;'
LANGUAGE 'plpgsql';

*** The Java method:

public static Entity[] lookupClientEntities(Connection con)
throws SQLException
{
Vector entitiesVector = new Vector();
Entity entity = null;

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

// Procedure call.
CallableStatement proc =
con.prepareCall("{ ? = call SelClientEntities ( ) }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
entity = new Entity(con, results.getLong(1));
entitiesVector.add(entity);
}
results.close();
proc.close();

return (Entity[])entitiesVector.toArray(new
Entity[entitiesVector.size()]);
}

--
Philip A. Chapman

Application Development:
Java, Visual Basic (MCP), PostgreSQL, MySQL, MSSQL
Linux, Windows 9x, Windows NT, Windows 2000, Windows XP

You can only do this if you have autocommit turned off. Cursors are
only valid within a transaction, thus as soon as the driver commits the
cursor is no longer valid. Thus the error you are receiving.

--Barry

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.
>
>
>
> *** The Exception:
>
> Tue Feb 03 08:49:50 CST 2004
> org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>"
> does not exist
>
> at
> org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
> at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:67)
> at
> org.postgresql.jdbc3.Jdbc3RefCursorResultSet.next(Jdbc3RefCursorResultSet.java:42)
> at us.pcsw.billing.data.Entity.lookupClientEntities(Entity.java:238)
> <snip>
>
>
>
> *** The Function:
>
> CREATE FUNCTION
> SelClientEntities
> () RETURNS REFCURSOR
> AS
> 'DECLARE
> vRef REFCURSOR;
> BEGIN
> OPEN
> vRef
> FOR
> SELECT DISTINCT
> ClientEntityID
> FROM
> Contract
> ;
> RETURN vRef;
> END;'
> LANGUAGE 'plpgsql';
>
>
>
> *** The Java method:
>
> public static Entity[] lookupClientEntities(Connection con)
> throws SQLException
> {
> Vector entitiesVector = new Vector();
> Entity entity = null;
>
> // Turn transactions off.
> con.setAutoCommit(true);
>
> // Procedure call.
> CallableStatement proc =
> con.prepareCall("{ ? = call SelClientEntities ( ) }");
> proc.registerOutParameter(1, Types.OTHER);
> proc.execute();
> ResultSet results = (ResultSet) proc.getObject(1);
> while (results.next()) {
> entity = new Entity(con, results.getLong(1));
> entitiesVector.add(entity);
> }
> results.close();
> proc.close();
>
> return (Entity[])entitiesVector.toArray(new
> Entity[entitiesVector.size()]);
> }
>

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

On Tue, 2004-02-03 at 16:09, Oliver Jowett wrote:

> Probably the simplest solution is to turn off autocommit unless you have
> good reasons for turning it on.
>

Oliver,

You are most correct. I finally figured it out. I came back to the
mailing list with the intent to reply to my own message for the benefit
of others... However, you beat me to it.

Thanks,
--
Philip A. Chapman

Application Development:
Java, Visual Basic (MCP), PostgreSQL, MySQL, MSSQL
Linux, Windows 9x, Windows NT, Windows 2000, Windows XP