2013-05-23

Common MySQL / JDBC programming error that causes unexpected syntax errors

What is wrong with this code?

...
    String sql = "INSERT INTO PRODUCT (CODE, DESCRIPTION) VALUES(?,?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
        
    pstmt.setString(1, "A1");
    pstmt.setString(2, "Soccer ball");
        
    pstmt.executeUpdate(sql);
...

On execution, the execute method returns a com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException.

Why is that? The syntax looks OK...

It's tricky, but since the statement is prepared in advance, the method to call is pstmt.executeUpdate() without parameters.
If the SQL query is provided, the compiler accepts the code, but the '?' produce syntax errors.

PreparedStatement is preferred over Statement because it can give better performance and prevent many SQL Injection attacks.

The correct code is:

...
    String sql = "INSERT INTO PRODUCT (CODE, DESCRIPTION) VALUES(?,?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
        
    pstmt.setString(1, "A1");
    pstmt.setString(2, "Soccer ball");
        
    pstmt.executeUpdate();
...


Credits: Mark Matthews

No comments: