Example 31-4. Calling a built in stored function
This example shows how to call a PostgreSQL built in function, upper, which simply converts the supplied string argument to uppercase.
// Turn transactions off. con.setAutoCommit(false); // Procedure call. CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }"); upperProc.registerOutParameter(1, Types.VARCHAR); upperProc.setString(2, "lowercase to uppercase"); upperProc.execute(); String upperCased = upperProc.getString(1); upperProc.close();
PostgreSQL's stored function can return results by means of a refcursor value. A refcursor.
As an extension to JDBC, the PostgreSQL JDBC driver can return refcursor values as ResultSet values.
Example 31-5. Getting refcursor values from a function
When calling a function that returns a refcursor you must cast the return type of getObject to a ResultSet
// Turn transactions off. con.setAutoCommit(false); // Procedure call. CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }"); proc.registerOutParameter(1, Types.Other); proc.setInt(2, -1); proc.execute(); ResultSet results = (ResultSet) proc.getObject(1); while (results.next()) { // do something with the results... } results.close(); proc.close();It is also possible to treat the refcursor return value as a distinct type in itself. The JDBC driver provides the org.postgresql.PGRefCursorResultSet class for this purpose.
Example 31-6. Treating refcursor as a distinct type
con.setAutoCommit(false); CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }"); proc.registerOutParameter(1, Types.Other); proc.setInt(2, 0); org.postgresql.PGRefCursorResultSet refcurs = (PGRefCursorResultSet) con.getObject(1); String cursorName = refcurs.getRefCursor(); proc.close();
Free Sample Chapters