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

Re: [JDBC] plpgsql function with RETURNS SETOF refcursor

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [JDBC] plpgsql function with RETURNS SETOF refcursor
Date: 2005-03-25 02:52:18
Message-ID: 42437CE2.8020603@siunik.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-jdbc
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; ref2 refcursor; BEGIN OPEN ref1 FOR SELECT 1; RETURN NEXT ref1; OPEN ref2 FOR SELECT 2; RETURN next ref2; RETURN; END;' LANGUAGE plpgsql");
>		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(at)postgresql(dot)org so that your
>      message can get through to the mailing list cleanly
>  
>


In response to

pgsql-jdbc by date

Next:From: Michael BarkerDate: 2005-03-25 07:42:19
Subject: Re: [Patch] JDBC3 Blob support
Previous:From: Kris JurkaDate: 2005-03-25 01:14:41
Subject: Re: [JDBC] plpgsql function with RETURNS SETOF refcursor AS. How

pgsql-general by date

Next:From: BenDate: 2005-03-25 03:13:30
Subject: building 8.0.1 on OS X
Previous:From: Edson Vilhena de CarvalhoDate: 2005-03-25 02:01:23
Subject: relid and relname

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