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

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
>
>