Re: Stored Procedure returns a ResultSet - Mailing list pgsql-jdbc
From | Barry Lind |
---|---|
Subject | Re: Stored Procedure returns a ResultSet |
Date | |
Msg-id | 3F5F51BB.5090809@xythos.com Whole thread Raw |
In response to | Stored Procedure returns a ResultSet (jonathan.lister@vaisala.com) |
List | pgsql-jdbc |
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" > >
pgsql-jdbc by date: