Error in SQL syntax using Java

Question

public boolean setgame(int botid, int gameid, String name, String ip, int spoofed, int reserved, int loadingtime, int left, String leftreason, int team, int colour, String spoofedrealm) {
 try {
 Connection connection = connection();
 PreparedStatement statement = connection.prepareStatement("INSERT INTO gameplayers (id, botid, gameid, name, ip, spoofed, reserved, loadingtime, left, leftreason, team, colour, spoofedrealm) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
 statement.setInt(1, botid);
 statement.setInt(2, gameid);
 statement.setString(3, name);
 statement.setString(4, ip);
 statement.setInt(5, spoofed);
 statement.setInt(6, reserved);
 statement.setInt(7, loadingtime);
 statement.setInt(8, left);
 statement.setString(9, leftreason);
 statement.setInt(10, team);
 statement.setInt(11, colour);
 statement.setString(12, spoofedrealm);
 statement.execute();
 connectionReady(connection);
 return true;
 } catch (SQLException e) {
 if (Main.DEBUG) {
 }
 Main.println("[SQLThread] Unable to add bot ban to MySQL database: " + e.getLocalizedMessage());
 }
 return false;
}

---

I created a main method to add all data and I can't see any error in the insert statement.

I am getting this error:

[SQLThread] Fail: 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 'left, leftreason, team, colour, spoofedrealm) VALUES (NULL, 2, 146, 'Teste', '120.32' at line 1

• user2113404: I don't think this is the problem i have many other public class and everyone has NULL in id field
• Jesse: Have you tested the SQL code directly on the SQL Server? Additionally, can you specific which SQL Engine you are using? SQL Server, MySQL, Postgre, etc. - there are certain subtleties between each in the language.

Answer 1

LEFT is reserved word in MySql (as in LEFT OUTER JOIN...). You need to use quotes around it to use it as an identifier.

Better yet, consider renaming the field to not use a reserved word as an identifier.

Answer 2

LEFT is a reserved word in MySQL; you have to escape it:

INSERT INTO gameplayers (
 id, botid, gameid, name, ip, spoofed, reserved, loadingtime, `left`, ...
 ----------^----^