Re: Getting a ResultSet for a refcursor element.

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