I'm another one of those refugees from oracle who like to return
cursors from stored procs.
I was getting annoyed at PostgreSQL's solution, which seems to be
less elegant than Oracle's, so I've added code to the JDBC driver to
implement the same solution as is possible with Oracle.
In brief, getting a returned cursor can now be done like this:
Statement st = con.createStatement();
// Has to be done within a single transaction.
con.setAutoCommit(false);
ResultSet rs
= st.executeQuery("select some_func_returning_refcursor();");
if (! rs.next())
throw new SQLException("whoops! there were no rows.");
try
{
ResultSet v = (ResultSet) rs.getObject(1);
while (rs2.next())
System.out.println(rs2.getString(1));
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
// This causes the refcursor to be closed.
con.commit();
st.close();
The change wasn't difficult to make, it's a few changes but mainly
it's not much more than this:
else if (type.equals("refcursor"))
{
String cursorName = getString(columnIndex);
Statement st = new Statement(this.statement.connection);
return st.executeQuery("FETCH ALL IN \""
+ cursorName
+ "\";");
}
in the default handling for the JDBC type in the jdbc2/ResultSet
class.
Would a diff for the patch be appreciated here? If so, what sort of
diff?
Nic Ferrier
