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