Skip site navigation (1) Skip section navigation (2)

Error While trying to use Functions which return Resultsets

From: "Philip A(dot) Chapman" <pchapman(at)pcsw(dot)us>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Error While trying to use Functions which return Resultsets
Date: 2004-02-03 15:18:40
Message-ID: 1075821519.30361.8.camel@dragon.acoeis.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-jdbc by date

Next:From: Barry LindDate: 2004-02-03 16:54:31
Subject: Re: Error While trying to use Functions which return Resultsets
Previous:From: Kris JurkaDate: 2004-02-03 08:57:53
Subject: Comments on adding more connection URL parameters.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group