User: code_simian

Re:I recommend Mysql users to take a look at PG

On: PostgreSQL 8.0 Enters Beta

I use PGSQL and return reference cursors. The PostgreSQL JDBC driver is able to cast the return value into a standard ResultSet. Here is how I do it. The PGSQL function:

CREATE OR REPLACE FUNCTION
SelServiceTypes
() RETURNS REFCURSOR
AS
'DECLARE
ref REFCURSOR;
BEGIN
OPEN
ref
FOR
SELECT
ServiceType,
Description,
SortOrder
FROM
Service
ORDER BY
SortOrder;
RETURN ref;
END;'
LANGUAGE 'plpgsql';

The JDBC code, ignoring try - catch and assuming Connection con is already defined and holds an open connection, for simplicity:

Vector vector = new Vector();

CallableStatement proc = null;
ResultSet results = null;

// It is essential that we are inside a transaction, though in real
// code, you would either a) know you are not already in a transaction
// or b) check the status of con.getAutoCommit() first.
con.setAutoCommit(false);

// Creation the callable statment and set parameters
proc = con.prepareCall
("{ ? = call SelServiceTypes () }");
// Types.OTHER is important
proc.registerOutParameter(1, Types.OTHER);
// We could set up input parameters if we needed them

// Execute the procedure
proc.execute();
// Casting result to a standard jdbc.ResultSet
results = (ResultSet) proc.getObject(1);
while (results.next()) {
pst = new Service
(results.getInt(1), results.getString(2));
vector.add(pst);
}
results.close();
proc.close();

con.commit();
con.setAutoCommit(true); // Optional, see notes above