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

Nic,

I don't think this is the correct approach.  I think a better approach
would be to return a pg specific object (lets call it PGrefcursor).  The
object would have at least the following two methods: getRefCursorName()
and getResultSet().  The reason I think this is a better approach is
then you can turn around and use the PGrefcursor object on a setObject()
call to bind the refcursor to a different function call.  So you can
have a function that returns a refcursor and another that takes a
refcursor and you can get the refcursor object from one call and pass it
onto the other.

Now it is true that you could do this today using
getString()/setString() but that isn't very intuitive.

The other reason I don't like returning a result set directly from
getObject is that it doesn't seem to follow the same pattern as all the
other objects that are being returned.  You are losing the distinction
that the refcursor is a pointer to a result set, not the actual result
set itself.

Finally, does anyone know how other databases' jdbc drivers deal with
this type of functionality?  I would rather try to follow an existing
example of how someone else has done this then to go it alone and build
our own mechanism.  Since I know Oracle has refcursors, how does oracle
expose them through jdbc?

thanks,
--Barry


Nic Ferrier wrote:
> Here's my context diff for getting ResultSet's whole from another
> ResultSet (via a proc returning a refcursor).
>
> Here's some example code:
>
> 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",
>                                         "someone",
>                                         "something");
>     Statement st = con.createStatement();
>     con.setAutoCommit(false);
>     // f() is a function that returns a refcursor.
>     ResultSet rs = st.executeQuery("select f();");
>     if (! rs.next())
>       throw new SQLException("whoops! there were no rows.");
>     try
>       {
>     Object v = rs.getObject(1);
>     if (v instanceof ResultSet) {
>       ResultSet rs2 = (ResultSet) v;
>       while (rs2.next()) {
>         System.out.println(rs2.getString(1));
>       }
>     }
>       }
>     catch (Exception e) {
>       System.out.println(e.getMessage());
>     }
>     con.commit();
>     st.close();
>     con.close();
>   }
> }
>
>
> Do I need to do a documentation patch? Does anybody else have a good
> idea for how this should be described in the doc?
>
>
> Nic
>
>
> Here's the diff:
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
> retrieving revision 1.8
> diff -c -r1.8 AbstractJdbc2ResultSet.java
> *** src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2002/09/11 05:38:45    1.8
> --- src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2002/10/09 01:21:13
> ***************
> *** 142,147 ****
> --- 142,158 ----
>                   {
>                       return getString(columnIndex);
>                   }
> +                 else if (type.equals("refcursor"))
> +                 {
> +                         // We must return a ResultSet with the results packaged.
> +                         // We should probably check that auto commit is turned off.
> +                         String cursorName = getString(columnIndex);
> +                     Statement st
> +                       = new Jdbc2Statement((Jdbc2Connection)this.connection);
> +                     return st.executeQuery("FETCH ALL IN \""
> +                                    + cursorName
> +                                    + "\";");
> +                 }
>                   else
>                   {
>                       return connection.getObject(field.getPGType(), getString(columnIndex));
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>