Re: [JDBC] plpgsql function with RETURNS SETOF refcursor

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
>
>