Re: Getting a ResultSet for a refcursor element.

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