JDBC function call: PS vs CS

[John Sidney-Woollett]

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(QueryE xecutor.java:154) at
org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:101) at
org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:43) at
org.postgresql.jdbc1.AbstractJdbc1Statement.execut e(AbstractJdbc1Statement.java:515)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:50)
at
org.apache.commons.dbcp.DelegatingPreparedStatemen t.execute(DelegatingPreparedStatement.java:230)
at
com.wardbrook.webdav.store.JDBCDavStore.updateDisk Usage(JDBCDavStore.java:1842)
at
com.wardbrook.webdav.store.JDBCDavStore.putResourc e(JDBCDavStore.java:449)
at
com.wardbrook.webdav.servlet.WebdavServlet.doPut(W ebdavServlet.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

[Dave Cramer]

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(QueryE xecutor.java:154) at
org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:101) at
org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:43) at
org.postgresql.jdbc1.AbstractJdbc1Statement.execut e(AbstractJdbc1Statement.java:515)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:50)
at
org.apache.commons.dbcp.DelegatingPreparedStatemen t.execute(DelegatingPreparedStatement.java:230)
at
com.wardbrook.webdav.store.JDBCDavStore.updateDisk Usage(JDBCDavStore.java:1842)
at
com.wardbrook.webdav.store.JDBCDavStore.putResourc e(JDBCDavStore.java:449)
at
com.wardbrook.webdav.servlet.WebdavServlet.doPut(W ebdavServlet.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend