Re: jdbc3, pg 8.1.4 , and stored procedures

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