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

plpgsql function with RETURNS SETOF refcursor AS. How to get it work via JDBC

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: plpgsql function with RETURNS SETOF refcursor AS. How to get it work via JDBC
Date: 2005-03-24 23:30:17
Message-ID: 42434D89.5080104@siunik.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-jdbc
Hi all,

  I'm already able to get Refcursor from a stored procedure.  But now I 
need to get a SETOF refcursor and I can't make it work... Is that 
possible to do this via JDBC?

He is the code I did.   The rsTmp.next() throws a  Connection is 
closed.  Operation is not permitted. Exception. 

    public ResultSet[] executePreparedStatementQueryMultipleCursor() 
throws SQLException {
        ResultSet rsTmp = ps.executeQuery();
        ResultSet[] tempArray = new ResultSet[50];  // Should be enough
        int j = 0;
        while (rsTmp.next()) {
            tempArray[j] = (ResultSet) rsTmp.getObject(1);
            j++;
        }

        rs = new ResultSet[j];
        System.arraycopy(tempArray, 0, rs, 0, j);

        rsTmp.close();
        return rs;
    }


Here is a part of my function (see below) wich seems to work correctly.

If it's not supported is there a workaround?  Is this supposed to be 
supported sooner?

Thanks for your help it's really appreciated!

/David




CREATE OR REPLACE FUNCTION usp_Comptabilite_JournalVentes(VARCHAR, DATE, 
DATE, VARCHAR,VARCHAR) RETURNS SETOF refcursor  AS '
DECLARE
     companyId ALIAS FOR $1;
     startDate ALIAS FOR $2;
     endDate ALIAS FOR $3;
     periodIdFrom ALIAS FOR $4;
     periodIdTo ALIAS FOR $5;

     ref1 refcursor;
     ref2 refcursor;
     statement varchar(4000);
     appliedStr varchar(10);
     printedStr varchar(10);

BEGIN


....

  OPEN ref1 FOR EXECUTE statement;
  RETURN NEXT ref1;

 ...
  OPEN ref2 FOR EXECUTE statement;
  RETURN NEXT ref2;

  RETURN;


END;
' LANGUAGE 'plpgsql';



Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2005-03-24 23:40:33
Subject: Re: Unable to make a connention through jdbc
Previous:From: VernonDate: 2005-03-24 23:06:59
Subject: Re: Unable to make a connention through jdbc

pgsql-general by date

Next:From: Kris JurkaDate: 2005-03-24 23:44:05
Subject: Re: [JDBC] plpgsql function with RETURNS SETOF refcursor AS. How
Previous:From: VernonDate: 2005-03-24 22:58:14
Subject: Any easy ways to change configuration

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