| PostgreSQL 7.4.30 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 31. JDBC Interface | Fast Forward | Next |
PostgreSQL's JDBC driver fully supports calling PostgreSQL stored functions.
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();
CallableStatement
InterfaceAll the considerations that apply for Statement and PreparedStatement apply for CallableStatement but in addition you must
also consider one extra restriction:
You can only call a stored function from within a transaction.
ResultSet from a
stored functionPostgreSQL'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();
Pay close attention to the syntax here. The "call" keyword MUST be lowercase, or you will get an error. The spacing doesn't appear to be vital.
This JDBC functionality does not allow for "void" types for your stored functions. It will return an exception of "unsupported return type".