Re: jdbc3, pg 8.1.4 , and stored procedures

[Matt Chambers <chambers(at)imageworks(dot)com>]

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.

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?