MySQL query works in MySQL but not Java
I get the error "You have an error in your SQL syntax" when running a mysql insert via Java, which works fine in MySQL. Not really sure what's going on.
Table:
mysql> desc fauteam;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| officer1 | tinytext | YES | | NULL | |
| officer2 | tinytext | YES | | NULL | |
| callsign | tinytext | NO | | NULL | |
| sector | tinytext | NO | | NULL | |
| teamDate | date | NO | | NULL | |
| vehicle | tinytext | NO | | NULL | |
+----------+----------+------+-----+---------+----------------+
Function:
public static int addTeam(String officer1, String officer2, String callSign,
String sector, String vehicle, String date, Connection conn)
throws SQLException, ParseException {
int id = -1;
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
String query = "INSERT INTO fauteam (officer1,"
+ "officer2,"
+ "callsign,"
+ "sector,"
+ "teamDate,"
+ "vehicle) VALUES (?,?,?,?,?,?);";
PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, officer1);
ps.setString(2, officer2);
ps.setString(3, callSign);
ps.setString(4, sector);
Date d = sdf.parse(date);
java.sql.Date jsd = new java.sql.Date(d.getTime());
ps.setDate(5, jsd);
ps.setString(6, vehicle);
System.out.println(ps.toString());
ps.executeUpdate(query);//, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
id = rs.getInt(1);
}
rs.close();
ps.close();
return id;
}
Arguments sent:
officer1, value = c
officer2, value = d
callSign, value = 5211
vehicle, value = 1
sector, value = 1
date, value = 06/17/2015
The generated query is output as:
INSERT INTO fauteam (officer1,officer2,callsign,sector,teamDate,vehicle) VALUES ('c','d','5211','1','2015-06-17','1');
which works great in MySQL. However, when run in Java, I get:
com.mysql.jdbc.exceptions.jdbc4.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 don't understand what is causing the error. Does anyone see something that I have missed?
source to share
First, call executeUpdate
with no parameters; you have already submitted a request to conn.prepareStatement(query)
.
Second, remove ;
the String from the statement, it is not needed in jdbc; it's just a (custom) separator when you are communicating with the database through the interactive SQL client.
source to share