Agfdhyk

There are plenty of similar code blocks in the project I've been working on.

    DBConnector db = new DBConnector();

    Connection conn = db.connect();

    String insertQuery = "INSERT INTO track_item_result (tirTrackItemId, tirNodeId, tirDetailId, tirResultString, tirResultValue, tirStatus) " + "VALUES (?,?,?,?,?,?)";

    PreparedStatement pstmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);

    pstmt.setInt(1, this.trackItem.getId());

    pstmt.setInt(2, this.node.getId());

    pstmt.setInt(3, this.tirDetailId);

    pstmt.setString(4, this.tirResultString);

    pstmt.setString(5, this.tirResultValue);

    pstmt.setString(6, this.tirStatus);

    System.out.println(pstmt.toString());

    pstmt.executeUpdate(insertQuery);



    ResultSet generatedKeys = pstmt.getGeneratedKeys();

I printed the query in 3rd-last line in the above code which is a well formed SQL statement.

com.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO track_item_result (tirTrackItemId, tirNodeId, tirDetailId, tirResultString, tirResultValue, tirStatus) VALUES (3,15,0,'<EOL><EOL><EOL><EOL>','-1','F')

And if I copy-paste this generated query directly on MySQL client, it seems to work fine. But in the project it throws the dreaded error in SQL syntax

java.sql.SQLSyntaxErrorException: 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.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)

    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)

    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1393)

    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2353)

    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1303)

    at com.example.myproject.models.TrackItemResult.insertInDb(TrackItemResult.java:98)

    at com.example.myproject.healthcheck.TrackItemRunnable.run(TrackItemRunnable.java:86)

    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)

Here line TrackItemResult.insertInDb(TrackItemResult.java:98) is the pstmt.executeUpdate() line.

I'm unable to understand the behavior. The query looks pretty sane and actually works. The values are correctly formatted according to the schema. What else might cause this?

try pstmt.executeUpdate(); instead of pstmt.executeUpdate(insertQuery);

pstmt.executeUpdate(); No need for parameters

or you should Check parameter types for you sql