Thread: plpgsql function with RETURNS SETOF refcursor AS. How to get it work via JDBC
plpgsql function with RETURNS SETOF refcursor AS. How to get it work via JDBC
From
David Gagnon
Date:
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';
On Thu, 24 Mar 2005, David Gagnon wrote: > 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. > > > rs = new ResultSet[j]; > System.arraycopy(tempArray, 0, rs, 0, j); > rsTmp.close(); System.arraycopy does not make a deep copy, so the rsTmp.close() closes the ResultSet. You really can't copy resources around like that. Consider how you would copy a Connection object. Does that establish a new connection? The underlying tcp/ip connection can't be copied. Kris Jurka
Hi Kris,
I don't get error with the rsTmp.close() statement but with "
(rsTmp.next()) ". The arraycopy is because I want to shrink the
original array (size 50) to it real size. It's not intended to be a
deep copy.
Plpgsql function can return multiple refcursor .. so the question is how
I can get them via JDBC? Below I included the function that doen't work
(throw exception in the while condition). I also included a "WORKING"
function that actually return only one refcursor.
Thanks for your help .. if you see something wrong I'll be happy to know
it :-)
/David
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;
}
public ResultSet executePreparedStatementQueryCursor() throws
SQLException {
ResultSet rsTmp = ps.executeQuery();
rs = new ResultSet[1];
rs[0] = (ResultSet) rsTmp.getObject(1);
rsTmp.close();
return rs[0];
}
Kris Jurka wrote:
>On Thu, 24 Mar 2005, David Gagnon wrote:
>
>
>
>> 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.
>>
>>
>> rs = new ResultSet[j];
>> System.arraycopy(tempArray, 0, rs, 0, j);
>> rsTmp.close();
>>
>>
>
>System.arraycopy does not make a deep copy, so the rsTmp.close() closes
>the ResultSet. You really can't copy resources around like that.
>Consider how you would copy a Connection object. Does that establish a
>new connection? The underlying tcp/ip connection can't be copied.
>
>Kris Jurka
>
>
>
>
On Thu, 24 Mar 2005, David Gagnon wrote: > Hi Kris, > > I don't get error with the rsTmp.close() statement but with " > (rsTmp.next()) ". The arraycopy is because I want to shrink the > original array (size 50) to it real size. It's not intended to be a > deep copy. Right, my bad. I see nothing wrong with your code, but you haven't included a complete example. There aren't any thread safety problems in your code where the executePreparedStatementQueryMultipleCursor function is called simultaneously? I've attached the test code I've used to verify that this is not a driver problem. Kris Jurka
Attachment
Hi Kris, I use this the code found here. http://www.postgresql.org/docs/7.4/interactive/jdbc-callproc.html But I don't think there is a way to make it work with SETOF RefCursor. I will try your code wich seem to work. SQL STRING: ? = call usp_Comptabilite_JournalVentes (?, ?, ?, ?, ? ) Java Code. CallableStatement cs = (CallableStatement) dbCon.getPreparedStatement(); cs.registerOutParameter(1, Types.OTHER); cs.setString(2, (String) parameters.get("companyId")); ..... After I call this function and I can get the refcursor with the : rsTmp.getObject(1). That works .. If the function returns only a refcursor. I will try your way ( select usp_Comptabilite_JournalVentes (?, ?, ?, ?, ? ) ) to get my SETOF refcursor. public ResultSet executePreparedStatementQueryCursor() throws SQLException { ResultSet rsTmp = ps.executeQuery(); rs = new ResultSet[1]; rs[0] = (ResultSet) rsTmp.getObject(1); rsTmp.close(); return rs[0]; } Thanks for your help! /David P.S.: Buy the way I think it should be possible to get my SETOF refcursor using Callable Statement. Regarding how the jdbc driver handle refcursor returning by CallableStatement .. I'm not sure correctly written to handle my problem. Kris Jurka wrote: >On Thu, 24 Mar 2005, David Gagnon wrote: > > > >>Hi Kris, >> >> I don't get error with the rsTmp.close() statement but with " >>(rsTmp.next()) ". The arraycopy is because I want to shrink the >>original array (size 50) to it real size. It's not intended to be a >>deep copy. >> >> > >Right, my bad. I see nothing wrong with your code, but you haven't >included a complete example. There aren't any thread safety problems in >your code where the executePreparedStatementQueryMultipleCursor function >is called simultaneously? I've attached the test code I've used to verify >that this is not a driver problem. > >Kris Jurka > > >------------------------------------------------------------------------ > >import java.sql.*; > >public class MultRefCursor { > > public static void main(String args[]) throws Exception { > Class.forName("org.postgresql.Driver"); > Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka",""); > > Statement stmt = conn.createStatement(); > stmt.execute("CREATE OR REPLACE FUNCTION multcurfunc() RETURNS SETOF refcursor AS 'DECLARE ref1 refcursor; ref2refcursor; BEGIN OPEN ref1 FOR SELECT 1; RETURN NEXT ref1; OPEN ref2 FOR SELECT 2; RETURN next ref2; RETURN; END;' LANGUAGEplpgsql"); > stmt.close(); > > conn.setAutoCommit(false); > > PreparedStatement ps = conn.prepareStatement("SELECT * FROM multcurfunc()"); > ResultSet rs = ps.executeQuery(); > > while (rs.next()) { > System.out.println(rs.getString(1)); > ResultSet rs2 = (ResultSet)rs.getObject(1); > while (rs2.next()) { > System.out.println(rs2.getInt(1)); > } > rs2.close(); > } > > rs.close(); > ps.close(); > conn.close(); > } >} > > > > > >------------------------------------------------------------------------ > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >