Stored Procedure returns a ResultSet

From: jonathan(dot)lister(at)vaisala(dot)com
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Stored Procedure returns a ResultSet
Date: 2003-09-10 11:55:47
Message-ID: 0077BA604D38D311918B00508B444258022D3D16@birsrv01.vaisala.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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"

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Nic 2003-09-10 12:01:31 Re: Stored Procedure returns a ResultSet
Previous Message Andreas Prohaska 2003-09-10 08:23:04 Streaming binary data into db, difference between Blob and LargeO bject