Re: Getting a ResultSet for a refcursor element.

From: Barry Lind <barry(at)xythos(dot)com>
To: Nic Ferrier <nferrier(at)tapsellferrier(dot)co(dot)uk>
Cc: Dave Cramer <Dave(at)micro-automation(dot)net>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Getting a ResultSet for a refcursor element.
Date: 2002-10-10 15:33:46
Message-ID: 3DA59DDA.1070107@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Nic,

What do you mean by "the CallableStatement handling is not setup yet"?
Current code should support CallableStatements such that the example you
give should work (except of course for returning a ResultSet object :-)

thanks,
--Barry

Nic Ferrier wrote:
> how Oracle does it:
>
>>Ordinarily one register's the out parameter of the proc you are calling
>>with the Oracle ResultSet implementation class.
>
>
> Here's my earlier example re-written for the more conventional
> style. Unfortunately this doesn't work out of the box on postgresql
> jdbc because the CallableStatement handling is not setup yet.
>
> However, here's what the code should look like:
>
>
>
> import java.sql.*;
>
>
> public class proctest
> {
> public static void main (String[] argv) throws Exception
> {
> Class driver = Class.forName("org.postgresql.Driver");
> Connection con
> = DriverManager.getConnection("jdbc:postgresql:test",
> "someuser",
> "somepassword");
> con.setAutoCommit(false);
> CallableStatement st = con.prepareCall("{ ? = call f() }");
> // With Oracle at this point you'd do:
> // st.registerOutParameter(1,
> // oracle.jdbc.driver.OracleTypes.CURSOR);
> // see my comment below.
> st.registerOutParameter(1, Types.JAVA_OBJECT);
> st.execute();
> ResultSet rs = (ResultSet) st.getObject(1);
> while (rs.next()) {
> System.out.println(rs.getString(1));
> }
> con.commit();
> st.close();
> con.close();
> }
> }
>
>
> The use of "OracleTypes" by oracle is interesting. Obviously, I
> haven't looked at the code, but I imagine it would have to be based
> on java.sql.Types. That could be done I guess, something like:
>
>
> java/sql/Types.java:
>
> final static int INTEGER = 0;
> final static int LONG = INTEGER + 1;
> .
> .
> .
> final static int STRING = ... + 1;
>
> org/postgresql/PGTypes.java:
>
> final static int REFCURSOR = java.sql.Types.STRING + 1;
>
>
> But of course then you guys would either have to distribute your own
> java.sql or at least be confident that it always worked in the same
> way (maybe, via the build process?).
>
>
> That's why I plumped for using getObject() and the cast. It seemed to
> work quite well.
>
>
> Nic
>
>

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2002-10-10 15:35:48 Re: Out of memory error on huge resultset
Previous Message Dave Cramer 2002-10-10 15:33:07 Re: Getting a ResultSet for a refcursor element.