JDBC function call: PS vs CS - Mailing list pgsql-general

Is it possible to call a Postgres stored function (as if it were a
procedure), using a PreparedStatement which will receive no return value,
instead of having to use a CallableStatement and ignore the return result?

The reason...

Our java middleware was written against Oracle (where we use stored
procedures extensively) and is now being ported to Postgres. I would
prefer not to have to rewrite all the java code replacing stored
PreparedStatement calls by CallableStatement calls.

We might have to move back to Oracle one day...  Just kidding!   ;)

Is there any way around the java code rewrite?

Thanks.

John Sidney-Woollett


Error message:
==============

org.postgresql.util.PSQLException: ERROR: syntax error at or near "{"

    at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
    at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154) at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101) at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
    at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:230)
    at
com.wardbrook.webdav.store.JDBCDavStore.updateDiskUsage(JDBCDavStore.java:1842)
    at
com.wardbrook.webdav.store.JDBCDavStore.putResource(JDBCDavStore.java:449)
    at
com.wardbrook.webdav.servlet.WebdavServlet.doPut(WebdavServlet.java:674)
    at
com.wardbrook.webdav.servlet.WebdavServlet.service(WebdavServlet.java:243)


Code as it now stands:
======================

//now delete the resource entry from the database
String sql = "{call UpdateDiskUsed(?,?)}";

Connection conn = null;
PreparedStatement ps = null;

try
{
    //get the connection
    conn = mDS.getConnection();

    //create the statement
    ps = conn.prepareStatement(sql.toString());

    //set up the parameters
    ps.setInt(1, resourceID);
    ps.setInt(2, newContentLength);

    //execute the procedure
    ps.execute();
 ...

Code rewritten to work: (with Postgres)
=======================

//now delete the resource entry from the database
String sql = "{? = call UpdateDiskUsed(?,?)}";

Connection conn = null;
CallableStatement cs = null;

try
{
    //get the connection
    conn = mDS.getConnection();

    //create the statement
    cs = conn.prepareStatement(sql.toString());

    //register the out parameter
    cs.registerOutParameter(1,  java.sql.Types.INTEGER);

    //set up the parameters
    cs.setInt(2, resourceID);
    cs.setInt(3, newContentLength);

    //execute the function
    cs.execute();

    //and ignore the function result!

 ...