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?

@duffymo: Habit. It doesn't hurt to leave it there, either. :) — Bob Stout
– Bob Stout, Commented Jul 17, 2015 at 13:49
@duffymo: Just discovered that while it doesn't matter if the semi-colon is present for MySQL and SQLServer databases, it DOES when connecting to an Oracle database. So plus for you. :) — Bob Stout
– Bob Stout, Commented Jan 13, 2017 at 17:33

M A · Accepted Answer · 2015-07-17 13:44:11Z

9

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.

answered Jul 17, 2015 at 13:44

M A

73.2k14 gold badges149 silver badges182 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bob Stout Over a year ago

Crap. Yep, I see it now. I'll file that under "It's Friday" or "I'm a dork". Thanks.

duffymo Over a year ago

I'll bet he'd prefer that you accept the correct answer to your thanks.

Bob Stout Over a year ago

@duffymo: I will, when the system lets me. Four more minutes.

Dragan Bozanovic · Accepted Answer · 2015-07-17 13:50:25Z

1

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.

answered Jul 17, 2015 at 13:50

Dragan Bozanovic

23.6k5 gold badges50 silver badges117 bronze badges

Collectives™ on Stack Overflow

MySQL query works in MySQL but not in Java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related