Re: Getting a ResultSet for a refcursor element.

how Oracle does it:
> Ordinarily one register's the out parameter of the proc you are calling
> with the Oracle ResultSet implementation class.

Here's my earlier example re-written for the more conventional
style. Unfortunately this doesn't work out of the box on postgresql
jdbc because the CallableStatement handling is not setup yet.

However, here's what the code should look like:

import java.sql.*;

public class proctest
{
public static void main (String[] argv) throws Exception
{
Class driver = Class.forName("org.postgresql.Driver");
Connection con
= DriverManager.getConnection("jdbc:postgresql:test",
"someuser",
"somepassword");
con.setAutoCommit(false);
CallableStatement st = con.prepareCall("{ ? = call f() }");
// With Oracle at this point you'd do:
// st.registerOutParameter(1,
// oracle.jdbc.driver.OracleTypes.CURSOR);
// see my comment below.
st.registerOutParameter(1, Types.JAVA_OBJECT);
st.execute();
ResultSet rs = (ResultSet) st.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(1));
}
con.commit();
st.close();
con.close();
}
}

The use of "OracleTypes" by oracle is interesting. Obviously, I
haven't looked at the code, but I imagine it would have to be based
on java.sql.Types. That could be done I guess, something like:

java/sql/Types.java:

final static int INTEGER = 0;
final static int LONG = INTEGER + 1;
.
.
.
final static int STRING = ... + 1;

org/postgresql/PGTypes.java:

final static int REFCURSOR = java.sql.Types.STRING + 1;

But of course then you guys would either have to distribute your own
java.sql or at least be confident that it always worked in the same
way (maybe, via the build process?).

That's why I plumped for using getObject() and the cast. It seemed to
work quite well.

Nic