MySQL Bugs: #13289: SQLException throws in using PreparedStatement for insert record

Bug #13289	SQLException throws in using PreparedStatement for insert record
Submitted:	17 Sep 2005 3:28	Modified:	17 Sep 2005 14:03
Reporter:	Susan Cai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	mysql-connector-java-3.1.7	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Error encountered as following:
init:
deps-jar:
compile-single:
run-single:
Success to register JDBC driver
Connected.
Exception in thread "main" java.sql.SQLException: 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
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2370)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1289)
        at test.Main.main(Main.java:50)
Java Result: 1

How to repeat:
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class Main {
    public static void main(String[] args)
        throws Exception
    {
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/dbCurrency_Control?autoReconnect=true";
    
        try{
            //Register the JDBC driver for MySQL.
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Success to register JDBC driver");
        } catch (Exception e) {
            System.out.println("Failed to load MySQL JDBC driver.");
            return;
        }
        try{
            conn = DriverManager.getConnection(url, "USER", "PD");
            System.out.println("Connected.");
        } catch (SQLException e) {
            System.out.println("fail to connect to DB");
            e.printStackTrace();
        }

        String query = "INSERT INTO dbcurrency_control.tbcurrency(currency_code, currency_description, active) values (?, ?, ?) ";
        PreparedStatement  prep = conn.prepareStatement(query);
            prep.setString(1, "USD");
            prep.setString(2, "TEEEs");
            prep.setBoolean(3, true);
            int ct = prep.executeUpdate(query);
    }
}

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Thanks for your reply.

I change my version to mysql-connector-java-3.1.10, but I still be able to reproduce the bug. Errors that slightly different are shown as following:

init:
deps-jar:
compile-single:
run-single:
Success to register JDBC driver
Connected.
INSERT INTO dbcurrency_control.tbcurrency(currency_code, currency_description, active) values (?, ?, ?) 
Exception in thread "main" java.sql.SQLException: 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
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:929)
        at test.Main.main(Main.java:51)
Java Result: 1

If I changed query statement to as the following statement, and then insert the record into database sucessfully. It really puzzles me that why there is problem in using holder-place with PreparedStatement?

        String query = "INSERT INTO dbcurrency_control.tbcurrency(currency_code, currency_description, active) values ('AAA','testtt ', true) ";

Yours help will be great appreciated. Thanks in advance.

I should have noticed in your first report, you're making a common mistake.

You're calling Statement.executeUpdate(String) with your prepared statement placeholder query, which is incorrect. You should be calling PreparedStatement.executeUpdate(). Remove the reference to the query from your call to executeUpdate() and things should work.

Hi, Thanks a lot.  I solved my problem. Your help are great appreciated.

Many thanks again.

Just had the same probleme, 

You should not call the executequery function with the query as a parameter :

bad:
PreparedStatement  prep = conn.prepareStatement(query);

correct:
PreparedStatement  prep = conn.prepareStatement();