Re: Error While trying to use Functions which return Resultsets

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()]);
> }
>