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

--
*Matt Chambers <chambers(at)imageworks(dot)com>*
*Sony Pictures Imageworks*
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221