Обсуждение: jdbc3, pg 8.1.4 , and stored procedures
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?
"{ ? = 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@imageworks.com>
Sony Pictures Imageworks
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221
Sony Pictures Imageworks
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221
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@imageworks.com>
Sony Pictures Imageworks
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221
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? >
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? >> > > >
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:
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@imageworks.com>
Sony Pictures Imageworks
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221
Sony Pictures Imageworks
Software Engineer
Phone: (310) 840 9072
Cell: (703) 624 0221