Re: 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(dot)lister(at)vaisala(dot)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"
>
>