Re: JDBC function call: PS vs CS

John,

You should re-post this to the jdbc list.

Dave
On Wed, 2003-12-10 at 06:48, John Sidney-Woollett wrote:
> 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!
>
> ...
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>