Bug #22252 PreparedStatement with parameters results in MySQLSyntaxErrorException
Submitted: 11 Sep 2006 23:26 Modified: 12 Sep 2006 0:41
Reporter: e2e4e7e5f2f4 e2e4e7e5f2f4 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0.3 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[11 Sep 2006 23:26] e2e4e7e5f2f4 e2e4e7e5f2f4 
Description:
  String sql4 = "INSERT INTO " + DB_TABLENAME + " (col1,col2) VALUES(?,?)";
  PreparedStatement stmt4 = getConnection().prepareStatement(sql4);
  stmt4.setString(1, "B");
  stmt4.setString(2, "C");
  
  Executing this statement results in a
  com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 
     You have an error in your SQL syntax; check the manual that corresponds
     to your MySQL server version for the right syntax to use near '?,?)' 
     at line 1

  I suspect that the parameter marks (?) are not correctly identified,
  either in the JDBC driver or on the database server. 
  The charset is utf8. See BugPreparedStatement.java for more details.  

How to repeat:
see BugPreparedStatement.java
[11 Sep 2006 23:27] e2e4e7e5f2f4 e2e4e7e5f2f4 
BugPreparedStatement.java

Attachment: BugPreparedStatement.java (text/x-java), 3.74 KiB.
[12 Sep 2006 0:41] Mark Matthews 
Your testcase shows that you're calling Statement.execute(String) (not PreparedStatement.execute()).

In actuality, you're executing stmt.execute("INSERT INTO mytable (col1,col2) VALUES(?,?)"), which of course is not what you want, because it is definitely a syntax error. One executes _prepared_ statements via the execute() methods that don't take a String-typed query.