CODEWITHSUNDEEP
javamysql
Bob Stout
Bob Stout

Reputation: 1259

MySQL update works, but not when used in PreparedStatement

Can't figure this out. Everything looks good, but I keep getting a MySQLSyntaxErrorException when it's run.

public static void setSupervisorApproval(HttpServletRequest request) throws ClassNotFoundException, SQLException {
    String requestID = request.getParameter("txtRequestId");
    boolean approved = request.getParameter("ckbApprove") != null;
    Connection conn = getConnection();
    Date approveDate = new Date();

    String query = "UPDATE request SET isApproved=?, approverDate=?, approver=?, comments=? where id=?;";
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setBoolean(1, approved);
    ps.setDate(2, new java.sql.Date(approveDate.getTime()));
    ps.setInt(3, Integer.parseInt(request.getParameter("txtUser")));
    ps.setString(4, request.getParameter("taComments"));
    ps.setInt(5, Integer.parseInt(requestID));

    System.out.println(ps);

    ps.executeUpdate(query);

    ps.close();
    conn.close();
}

When the method is run, the query looks fine. For example, one such result is:

UPDATE request SET isApproved=1, approverDate='2014-11-19', approver=80, comments='This is a comment.' where id=1;

which will work as an update query in the MySQL command line. However, I get this error when it runs in Java:

An error has occurred: 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 '?, approverDate=?, approver=?, comments=? where id=?' at line 1.

Anybody else run into this and have a fix?

EDIT: I understand (after proofreading my page) that I use "request" for both the table name and the HttpServletRequest variable name. As it doesn't affect this question, I'll fix that later.

EDIT 2: Updated the real code. Had originally taken it from some test code that I had modified to test which variable was causing it to barf.

Upvotes: 0

Views: 65

Answers (1)

rgettman
rgettman

Reputation: 178263

You have called the executeUpdate(String) method, which is inherited from Statement and does not execute your prepared statement with placeholder variables, which explains why you get the ? is invalid syntax error message.

Call executeUpdate() instead, which is defined in PreparedStatement and does what you intended.

Also, as has been pointed out already, number your parameters 1-5 in order in your calls to setXyz methods.

Upvotes: 2

Related Questions