0

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.

0

1 Answer 1

2

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.

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

1 Comment

Ack, I knew it was something stupid. It was a Statement until they wanted a comment field in there also, and I missed that line when I converted it over. Thanks!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.