Postgres Pro
Downloads
Home   >   mailing lists

Thread: Error While trying to use Functions which return Resultsets

Error While trying to use Functions which return Resultsets

From
"Philip A. Chapman"
Date:
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
Attachment

Re: Error While trying to use Functions which return Resultsets

From
Oliver Jowett
Date:
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

Re: Error While trying to use Functions which return

From
"Philip A. Chapman"
Date:
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
Attachment

Re: Error While trying to use Functions which return Resultsets

From
Barry Lind
Date:
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()]);
> }
>