• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared SQL Update statement from Java

 
S Garcia
Greenhorn
Posts: 8
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I'm getting a syntax error:

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 '',' firstName = Broade',' lastName = Spade',' gpa = 3.0',' status = Resident',' ' at line 1

from the following code:

@Override public void update(){
Statement stmt = null;
try {
Connect conn = new Connect();
stmt = conn.makeStatement();
// This creates the SQL statement to update an existing employee.
stmt.execute("Update Employee"
+ " Set employeeID = " + employeeID + "','"
+ " firstName = " + firstName + "','"
+ " lastName = " + lastName + "','"
+ " paScore = " + paScore + "','"
+ " status = " + status + "','"
+ " manager = " + manager + "','"
+ " level = " + level + "','"
+ " title = " + null + "','"
+ " hrRep = " + null + "','"
+ " vacation = " + null
+ " WHERE employeeID = " + getEmployeeID() + ";");

stmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
System.err.println("Unable to update employee in the database.");
}
System.out.println("Employee successfully updated to the database.");
}

I'm guessing I've missed something very simple. I'd appreciate any and all help here in pointing out the obvious. Thanks!
 
Gary Fletcher
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Am not a Java JDBC expert but have worked with various DBs and am guessing that the last ";" isn't needed.

Then again I'm sure you've tried that.


+++++EDIT+++++
OR


are you missing a finally??
}
catch (SQLException e) {
e.printStackTrace();
System.err.println("Unable to update employee in the database.");
}
System.out.println("Employee successfully updated to the database.");
}
 
S Garcia
Greenhorn
Posts: 8
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I tried it again just to make sure, but that's not the issue. But thank you! It's probably that simple.
 
Gary Fletcher
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Again, this is just from other Oracle/DB string build ups, And and not too sure about your DB burt does the comma need to be quoted:

stmt.execute("Update Employee"
+ " Set employeeID = " + employeeID + "','"
+ " firstName = " + firstName + "','"
+ " lastName = " + lastName + "','"
+ " paScore = " + paScore + "','"
+ " status = " + status + "','"
+ " manager = " + manager + "','"
+ " level = " + level + "','"
+ " title = " + null + "','"
+ " hrRep = " + null + "','"
+ " vacation = " + null
 
S Garcia
Greenhorn
Posts: 8
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, it does need to be quoted.
I'm using Netbeans for my IDE and MySQL.
 
Gary Fletcher
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
but wouldn't:

stmt.execute("Update Employee"
+ " Set employeeID = " + employeeID + "','"
+ " firstName = " + firstName + "','"
....

equate to

update Employee set employee = <employeeID> ',' firstName = <firstName> ','.....

Should it not be:

update Employee set employee = <employeeID> , firstName = <firstName> ,....

IE. Without the quotes around the commas?
 
Campbell Ritchie
Sheriff
Posts: 51332
86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This thread would fit better in our databases forum: moving.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The quoting is incorrect. You're including the column name as well as the value inside a pair of quotes, ie. your statement contains:
instead of

I'm pretty sure that using PreparedStatement would make the code significantly simpler (no need to bother with proper quoting and formatting).

Edit: the problem might actually be that you're quoting the commas. You really must not quote commas, but you have to quote string literals. Or use a PreparedStatement...
 
S Garcia
Greenhorn
Posts: 8
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, I've corrected the quote problems & now my syntax is correct. Sadly my table is not being updated. Here is my updated code:

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm pretty sure this is not the code you're actually running (it wouldn't compile), so I may be addressing a different problem than you actually have. Nevertheless, let's try.

The problem is that statements likewon't modify anything. This statements updates some_column of the table to the value of some_column again. So, you're updating all of your column to the value they already have (with the exception of manager, which is updated to managerr, given that you don't report any exception it looks like both of these columns exist in your table).

Your code would have to look likewhere employeeID and firstName are both columns in the table and Java variables holding values of these columns.

With PreparedStatement, the code is perhaps a bit longer, but much more maintainable in the long run:

Note that it would be better not to update the primary key of a table, even if it was to the value it already has. So we could just remove the employeeID column from the set clause. But this example illustrates that if you want to use one variable twice with PreparedStatement, you'll simply use two question marks and set it twice.

Another common problem (probably the most common of all) with updates not showing up in the database is a forgotten commit. And finally (pun originally not intended ), see this discussion for why you should close your resources in the finally block, not the try block.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic