Error While trying to use Functions which return Resultsets - Mailing list pgsql-jdbc

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