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 PreparedStateme nt which will receive no return value,
instead of having to use a CallableStateme nt 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
PreparedStateme nt calls by CallableStateme nt 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.PSQLExcept ion: ERROR: syntax error at or near "{"

at
org.postgresql. util.PSQLExcept ion.parseServer Error(PSQLExcep tion.java:139)
at org.postgresql. core.QueryExecu tor.executeV3(Q ueryExecutor.ja va:154) at
org.postgresql. core.QueryExecu tor.execute(Que ryExecutor.java :101) at
org.postgresql. core.QueryExecu tor.execute(Que ryExecutor.java :43) at
org.postgresql. jdbc1.AbstractJ dbc1Statement.e xecute(Abstract Jdbc1Statement. java:515)
at
org.postgresql. jdbc2.AbstractJ dbc2Statement.e xecute(Abstract Jdbc2Statement. java:50)
at
org.apache.comm ons.dbcp.Delega tingPreparedSta tement.execute( DelegatingPrepa redStatement.ja va:230)
at
com.wardbrook.w ebdav.store.JDB CDavStore.updat eDiskUsage(JDBC DavStore.java:1 842)
at
com.wardbrook.w ebdav.store.JDB CDavStore.putRe source(JDBCDavS tore.java:449)
at
com.wardbrook.w ebdav.servlet.W ebdavServlet.do Put(WebdavServl et.java:674)
at
com.wardbrook.w ebdav.servlet.W ebdavServlet.se rvice(WebdavSer vlet.java:243)
Code as it now stands:
=============== =======

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

Connection conn = null;
PreparedStateme nt ps = null;

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

//create the statement
ps = conn.prepareSta tement(sql.toSt ring());

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

//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;
CallableStateme nt cs = null;

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

//create the statement
cs = conn.prepareSta tement(sql.toSt ring());

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

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

//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 PreparedStateme nt which will receive no return value,
instead of having to use a CallableStateme nt 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
PreparedStateme nt calls by CallableStateme nt 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.PSQLExcept ion: ERROR: syntax error at or near "{"

at
org.postgresql. util.PSQLExcept ion.parseServer Error(PSQLExcep tion.java:139)
at org.postgresql. core.QueryExecu tor.executeV3(Q ueryExecutor.ja va:154) at
org.postgresql. core.QueryExecu tor.execute(Que ryExecutor.java :101) at
org.postgresql. core.QueryExecu tor.execute(Que ryExecutor.java :43) at
org.postgresql. jdbc1.AbstractJ dbc1Statement.e xecute(Abstract Jdbc1Statement. java:515)
at
org.postgresql. jdbc2.AbstractJ dbc2Statement.e xecute(Abstract Jdbc2Statement. java:50)
at
org.apache.comm ons.dbcp.Delega tingPreparedSta tement.execute( DelegatingPrepa redStatement.ja va:230)
at
com.wardbrook.w ebdav.store.JDB CDavStore.updat eDiskUsage(JDBC DavStore.java:1 842)
at
com.wardbrook.w ebdav.store.JDB CDavStore.putRe source(JDBCDavS tore.java:449)
at
com.wardbrook.w ebdav.servlet.W ebdavServlet.do Put(WebdavServl et.java:674)
at
com.wardbrook.w ebdav.servlet.W ebdavServlet.se rvice(WebdavSer vlet.java:243)
Code as it now stands:
=============== =======

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

Connection conn = null;
PreparedStateme nt ps = null;

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

//create the statement
ps = conn.prepareSta tement(sql.toSt ring());

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

//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;
CallableStateme nt cs = null;

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

//create the statement
cs = conn.prepareSta tement(sql.toSt ring());

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

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

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