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