MySQL query works in MySQL but not in Java

Question

I'm getting a "You have an error in your SQL syntax" error when I run a mysql insert through Java that works fine in MySQL. Not quite sure what's going on.

The 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    |                |
+----------+----------+------+-----+---------+----------------+

The 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;
}

The arguments being sent:

officer1, value = c
officer2, value = d
callSign, value = 5211
vehicle, value = 1
sector, value = 1
date, value = 06/17/2015

The query generated comes out as:

INSERT INTO fauteam (officer1,officer2,callsign,sector,teamDate,vehicle) VALUES ('c','d','5211','1','2015-06-17','1');

which works fine in MySQL. When run in Java, however, 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'm not understanding what's causing the error. Anybody see anything that I missed?

Answer 1

You're executing the SQL query "as-is" when calling executeUpdate(query). You need to call the method without the argument in order to execute the query with the bound parameters:

ps.executeUpdate();

Note that executeUpdate(String query) is inherited from the standard Statement type.

Answer 2

Firstly, call executeUpdate without the parameters; you've already provided the query in conn.prepareStatement(query).

Secondly, remove ; in the statement String, it is not needed in jdbc; it is just a (configurable) separator when you communicate with the database through an interactive SQL client.