| 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 |
[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!
Description: 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.