CODEWITHSUNDEEP
javasql
AreYouSure
AreYouSure

Reputation: 732

Syntax error with SQL in Java

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

Upvotes: 3

Views: 229

Answers (6)

fxe
fxe

Reputation: 575

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

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109547

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.

Upvotes: 3

PermGenError
PermGenError

Reputation: 46398

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.

Upvotes: 3

dan
dan

Reputation: 13262

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.

Upvotes: 0

sshannin
sshannin

Reputation: 2825

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.

Upvotes: 0

axelrod
axelrod

Reputation: 3978

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

Upvotes: 2

Related Questions