Bug #15141 Should PreparedStatement call EscapeProcessor.escapeSQL
Submitted: 22 Nov 2005 16:48 Modified: 28 Nov 2005 18:26
Reporter: Richard Kirby Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S4 (Feature request)
Version:Connector version 3.1.11 - DB version is mysql5.0.15 OS:
Assigned to: Mark Matthews CPU Architecture:Any
Triage: D5 (Feature request)

[22 Nov 2005 16:48] Richard Kirby
I am using the JDBC escape syntax of {call STORED_PROCEDURE(?,?)} in a PreparedStatement, and get the error "You have an error in your SQL syntax".

How to repeat:
Create a PreparedStatement with the sql using the escape syntax.

Suggested fix:
Looking through the source, the EscapeProcessor.escapeSQL method seems only to be used in Statement and Connection for straight execution of sql. I think it should also be used for the PreparedStatement.
[22 Nov 2005 16:56] Mark Matthews
Not a bug. This is actually disallowed by the JDBC spec (escape processing on Connection.prepareStatement(....)). Also, the JDBC spec states that you should use Connection.prepareCall() to call stored procedures.
[22 Nov 2005 17:41] Richard Kirby
Thanks for the clarification. Not sure why the JDBC spec disallows this though, since my understanding of the escape syntax, is to allow the JDBC driver to convert a standard sql statement into a database specific one, and it seems to me perfectly reasonable to expect even a prepared statement to run through this escape processing before sending on to the actual database server.

Incidentally this behaviour is supported in the Opta2000 driver for sqlserver (which I am moving away from which is why I ran into this problem with our existing code!), 

I will see if I can work out how to raise this issue with the JDBC working group for consideration or further clarification!
[22 Nov 2005 18:13] Mark Matthews
Actually, looking back at the APIDOCs, things are a little confusing:

'Note: Since prepared statements have usually been parsed prior to making this call, disabling escape processing for PreparedStatements objects will have no effect.'.

We'll take a look at what it takes to enable escape processing for prepared statements, however, one should still call stored procedures via prepareCall() as far as I can tell.
[28 Nov 2005 18:26] Mark Matthews
Fixed for 3.1.12 and 5.0.0, see nightly snapshot build after 00:00 GMT Nov 29th at http://downloads.mysql.com/snapshots.php#connector-j or the subversion repository (build from source) at http://svn.mysql.com/svnpublic/connector-j/branches/branch_3_1 to get access to the fix before the official release.

Thanks for the bug report!