Re: jdbc3, pg 8.1.4 , and stored procedures - Mailing list pgsql-jdbc

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