Syntax error with SQL in Java

Question

Am I missing something silly here?

String update = "UPDATE Patients SET fullName = '" + patient.getName() + "', 
                 houseNum = '" + patient.getHouseNum() + "', 
                 address = '" + patient.getAddress() +"', 
                 postCode = '" + patient.getPostCode() + "', 
                 condition = '" + patient.getCondition() +  "', 
                 who = '" + patient.getWho() + "', 
                 time = '" + patient.getTime() + "', 
                 location = '" + patient.getLocation() + "', 
                 actionTaken = '" + patient.getActionTaken() + "', 
                 duration = '" + patient.getDuration() + "' 
                 WHERE regNo = '" +patient.getNHSnum()+"'";

For the sake of it, I returned on each new line for formatting here. Within my file it's on a single line. All database fields are of type text.

The error I get is:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. -3503

EDIT:

For info, the following works ok:

String update = "UPDATE Patients SET fullName = '" + patient.getName() + "', 
                houseNum = '" + patient.getHouseNum() + "', 
                address = '" + patient.getAddress() +"', 
                postCode = '" + patient.getPostCode() + "', 
                condition = '" + patient.getCondition() +  "' 
                WHERE regNo = '" +patient.getNHSnum()+"'";

EDIT2:

Here is the update string in full:

UPDATE Patients SET fullName = 'Dave', houseNum = '5', address = 'Bla', postCode = 'PQ1 RS2', condition = 'Unknown', who = 'Test', time = 'Test1', location = 'Test2', actionTaken = 'Test3', duration = 'Test4' WHERE regNo = '1'

As I said, in this example, every field in the database is of type text

You're missing the fact that you're opening yourself to a SQL injection attack by not using parameters with PreparedStatement... — Jon Skeet
– Jon Skeet, Commented Nov 21, 2012 at 21:57
What is the return types of each of the methods in the patient object? — Code-Apprentice
– Code-Apprentice, Commented Nov 21, 2012 at 21:57
in your date properties, have you confirmed that the string format is YYYY-MM-DD HH:MM:SS or otherwise right for your backend sql implementation? are all your feilds strings? if not drop the '' around each var that isn't. — Frank Thomas
– Frank Thomas, Commented Nov 21, 2012 at 21:59
try to dump the final query, with all parameters. Maybe is one of the parameters that breaks the statement. Also, you can try the dichotomic search: remove the second half of the query and try. If it works, re-insert the first half of the second half in the string and so on — Antonio
– Antonio, Commented Nov 21, 2012 at 22:00

Joop Eggen · Accepted Answer · 2012-11-21 22:17:23Z

3

time is an SQL reserved word. Best is to rename it, otherwise it is often a vendant dependant quoting, deviating from standard SQL.

Fully agree with the PreparedStatement remarks.

answered Nov 21, 2012 at 22:17

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

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

1 Comment

AreYouSure Over a year ago

You my friend, are a legend.

PermGenError · Accepted Answer · 2012-11-21 22:08:31Z

3

i would strongly recommend you to use PreparedStatements, rather than simple Statement (which would lead to SQl injection).

Below is an example of executing an Update using PreparedStatement.

String query = "UPDATE TABLENAME SET COL1=?, COL2=? WHERE somecondistrue";
Statement st = connection.preparedStatement(query);
st.setString(1, col1value);
st.setString(2, col2Value);
st.executeUpdate();

as you can see, this is more neater approach of executing SQL Queries using JDBC.

edited Nov 21, 2012 at 22:08

answered Nov 21, 2012 at 21:57

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

Comments

axelrod · Accepted Answer · 2012-11-21 21:59:23Z

2

You don't need the commas in number fields like num and duration

answered Nov 21, 2012 at 21:59

axelrod

3,9981 gold badge23 silver badges26 bronze badges

1 Comment

AreYouSure Over a year ago

The database fields are all of type text. All methods of Patient return String.

dreamcrash · Accepted Answer · 2021-03-19 20:52:27Z

1

You should use PreparedStatements, since all fields are string type the only problem I could see here is you be passing strings with the character ', that would generate an error

edited Mar 19, 2021 at 20:52

dreamcrash

52.6k26 gold badges114 silver badges138 bronze badges

answered Nov 21, 2012 at 22:03

fxe

5751 gold badge5 silver badges15 bronze badges

Comments

sshannin · Accepted Answer · 2012-11-21 22:03:44Z

0

As other people said, you should use prepared statements. In fact, consider if one of your patient names had an apostrophe (like O'Brien). Prepared statements would solve that issue.

Hard to say otherwise without seeing the final query.

answered Nov 21, 2012 at 22:03

sshannin

2,8251 gold badge23 silver badges25 bronze badges

Comments

dan · Accepted Answer · 2012-11-21 22:07:47Z

0

The issue probably is that some values that you are trying to update contains an apostrophe ' or other special characters. You can print the update string before executing the statement, and see if this is the case. A simple:

System.out.println("update: "+update);

will help you easily see if you are hitting this issue. Also, like other have mentioned, if you will use PreparedStatement you will not have to worry about SQL-injection issues and will not hit this issue.

answered Nov 21, 2012 at 22:07

dan

13.3k3 gold badges41 silver badges51 bronze badges

2 Comments

AreYouSure Over a year ago

I posted the update string in an edit to the question. Can't see anything noticeably wrong.

dan Over a year ago

@Meekel And you are getting that error for the update string? I used SQLFiddle and confirmed that your update is working.

Collectives™ on Stack Overflow

Syntax error with SQL in Java

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related