Re: Getting a ResultSet for a refcursor element. - Mailing list pgsql-jdbc

Dave,

True, but that has nothing to do with refcursor's.  refcursors and SRFs
(set returning functions) are two different features.  I thought this
thread was all about refcursors, so I am still unsure what Nic meant by
the comment below.

thanks,
--Barry

Dave Cramer wrote:
> Alledgedly 7.3 now supports returning result sets from a function
>
> Dave
> On Thu, 2002-10-10 at 11:33, Barry Lind wrote:
>
>>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
>> >
>> >
>>
>>
>>
>>
>
>
>