Re: jdbc3, pg 8.1.4 , and stored procedures

Found the problem here.  A lot of calls had a semicolon after the SQL, for example '? = call getDispatchList ( ? ); '  I'm guessing this worked with 7.4 since they were treated just like SELECT statements.  After fixing that, most worked, except that all of the code was using PreparedStatements instead of CallableStatements which seems to break setting results scrollable or not.

Dave Cramer wrote:

On 30-Aug-06, at 8:43 PM, Matt Chambers wrote:

Thanks for response.

Do you happen to know what 'Malformed function or procedure escape  syntax at offset 4' means?  That might get me going in the right  direction.
I will see what I can do about a code example..  There are a couple  layers of abstraction and some casting I would want to put into the  example.  One thing I noticed was that when using the 7.4 driver,  while it did work, would say "idle in transaction" next to the  process.

Yeah, that was a bug in the 7.4 driver. As far as the Malformed  function goes. It's a syntax error in the call, but it looks pretty  normal which is why I wanted a self contained test that failed.

Also, what I said about every stored procedure, turns out that is  false.  It seems the ones that don't work are the ones that return  cursors.  That code looks something like this.  Looks like its  abstracted to handle normal queries and stored procedure calls.   ob_is_call is set to true if they are calling a stored procedure,  false if its a normal query.  The SQLCleaner handles close() stuff.

   public Tresult query ( PreparedStatement x_stmt, Tparam  x_param ) throws SQLException {
       ResultSet x_rset = null;
       if ( ob_is_call ) {
           ((CallableStatement) x_stmt).registerOutParameter( 1,  Types.OTHER );
           x_stmt.setObject ( 2, x_param );
           x_stmt.execute ();
           x_rset = (ResultSet) ((CallableStatement)  x_stmt).getObject(1);
       } else {
           x_stmt.setObject ( 1, x_param );
           x_rset = x_stmt.executeQuery ();
       }
       try {
           return handle ( x_rset );
       } finally {
           SqlCleaner.cleanUpSqlSession ( x_rset );
       }
   }

-Matt

Dave Cramer wrote:

Matt,

That's pretty strange, the test suite in the driver uses exactly  the same syntax.
Can you send a self contained test that fails ?

Dave
On 30-Aug-06, at 7:11 PM, Matt Chambers wrote:

Greetings.  I've inherited this Tomcat / Postgres application and  I'm having a problem, not sure what is.  Basically, its a  postgres 8.1.4 server and a tomcat application.  All of the SQL  is done in plpgsql, which is called with prepareCall statements,  example:

 "{ ? = call getDispatchList ( ? ) }"

With the 7.4 driver it works, with the 8.1 driver every query  fails with:

WARNING: Caught unexpected: org.postgresql.util.PSQLException:  Malformed function or procedure escape syntax at offset 4.,  org.postgresql.util.PSQLException: Malformed function or  procedure escape syntax at offset 4.

I've been able to move the other applications over to the 8.1  driver (none use stored procedures) no problem.  What is the secret?