Re: Stored Procedure returns a ResultSet

jonathan(dot)lister(at)vaisala(dot)com writes:

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

You need the latest JDBC driver. It will allow you to get the
refcursor via the second method.

We should support the first version but don't yet.

--
Nic Ferrier
http://www.tapsellferrier.co.uk