Re: Stored Procedure returns a ResultSet - Mailing list pgsql-jdbc

Nic, Barry - many thanks for your help.
My project requires the use of stable release code and after the explanation from Barry I now have both RefCursors and SetReturning Functions working well with the production release.

The SRF seems like the more natural way to go - it's a nice feature.

thanks again,
Jonathan
-----Original Message-----
From: Barry Lind [mailto:blind@xythos.com]
Sent: 10 September 2003 17:31
To: jonathan.lister@vaisala.com
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Stored Procedure returns a ResultSet

I just wanted to add a couple of comments to this thread.

The database supports two different ways of returning sets of
information from a stored function (as of 7.3).  RefCursors and Set
Returning Functions.  These two different mechanisms interact with
client interfaces like jdbc in different ways.

RefCursors - A query returning a refcursor is only returning a pointer
to the client.  So 'select aa_test(1)' is only returning the pointer
(actually the string name of the cursor) to the client.  So in jdbc you
can call getString() to get the cursor name, and then issue a 'fetch ...
from <name or cursor here>...' sql statement to get the results of the
cursor.  This is what the driver is doing in 7.4 when you call
getObject() on a refcursor.

Set Returning Functions - A query calling a set returning function
actually gets the data back from the server (instead of a pointer as in
refcursors).  You generally call set returning functions with a
different syntax.  You would generally use the following form to get the
results of a set returning function:  select * from aa_test(1).  With a
set returning function, the client doesn't need to do anything special
to support them since the server is sending the data back to the client
in a normal fashion like any select statement.

thanks,
--Barry

jonathan.lister@vaisala.com wrote:
> I have searched the archives and tried two different approaches, both are
> giving me errors.
> (Calling a stored function that returns e.g. an Integer works fine).
>
> Could this be because I'm running PG 7.3.2 ? Would an upgrade to 7.3.4 help?
>
> aa_test is a stored function that takes an integer and returns a refcursor.
> aa_test works as expected when run from pgsql command line.
>
> Approach #1:
>       PreparedStatement ps = dbConn.prepareStatement("select aa_test(1)");
>       ResultSet rs = ps.executeQuery();
>       while (rs.next())
>       {
>         System.out.println("Got : " + rs.getString(2));
>         // or  System.out.println(rs.getString("rpu_name"));
>       }
>       rs.close();
>       ps.close();
> 
> Gives the run-time error:
> "The column index is out of range"
>
> Approach #2:
>       CallableStatement cs = dbConn.prepareCall("{? = call aa_test(?) }");
>       cs.registerOutParameter(1,Types.OTHER);
>       cs.setInt(2,27);
>       try
>       {
>         cs.execute(); 
>         ResultSet rs = (ResultSet) cs.getObject(1);
>         while (rs.next())
>         {
>           System.out.println(rs.getString("rpu_name"));
>         }
>       }
>       catch (java.sql.SQLException ex)
>       {
>         System.out.println("test function exception :" + ex);
>       }
>       rs.close();
>       cs.close();
> Gives the run-time error "No class found for refcursor"
>
>