select statement giving error for string variable

Question

I am writing the following SQL query in my Java program

PreparedStatement pre = conn.prepareStatement("select ID,FirstName,LastName,Dept from "                 
    + "student where ID =" + ID + " or FirstName=" + firstName + ";");

However, I am getting the following error:

use the right syntax for FirstName="+Parker

How is this caused and how can I solve it?

Please give the exact error, as well as telling us what the database is, and whether you're getting the error at compile-time or execution time. — Jon Skeet
– Jon Skeet, Commented Nov 9, 2012 at 23:32

MasterV · Accepted Answer · 2012-11-09 23:44:46Z

1

You should take advantage of prepared statements by making use of prepared statements parameters. This way, you can set your parameters pragmatically using setters.

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html.

Here is a snippet from the Oracle docs:

PreparedStatement updateSales = null;

String updateString = "update " + dbName + ".COFFEES " + "set SALES = ? where COF_NAME = ?";

updateSales = con.prepareStatement(updateString);

updateSales.**setInt**(1, e.getValue().intValue());

updateSales.**setString**(2, e.getKey());

Just make sure you set the statements *in order a*s the sql query.

answered Nov 9, 2012 at 23:44

MasterV

1,1821 gold badge13 silver badges18 bronze badges

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

1 Comment

Stranger Over a year ago

Let me ask, the way my prepared statement is written right now, is there any syntax error in that statement? When I debug my program, the variable firstName contains a value(which is Parker) but I wonder why it tells me that there is syntax error. Its hard to get the actual error because I can see it when I hover over my PrintStackTrace object.But it basically says " check the manual that corresponds with it, sql error near FirstName="+Parker

jlordo · Accepted Answer · 2012-11-09 23:36:47Z

1

Use a PreparedStatement like this:

PreparedStatement pre = conn.prepareStatement("select ID,FirstName,LastName,Dept from student where ID = ? or FirstName = ?");
pre.setInt(1, ID);
pre.setString(2, firstName);

answered Nov 9, 2012 at 23:36

jlordo

37.9k7 gold badges63 silver badges84 bronze badges

Comments

alanmanderson · Accepted Answer · 2012-11-09 23:33:30Z

0

I haven't used sql in Java, but my guess is it is because you don't have single quotes around first name. You want:

PreparedStatement pre = conn.prepareStatement("select ID,FirstName,LastName,Dept from " + "student where ID =" + ID + " or FirstName='" + firstName + "';");

emphasis:

... FirstName='" + firstName + "';");

answered Nov 9, 2012 at 23:33

alanmanderson

8,31012 gold badges56 silver badges89 bronze badges

Collectives™ on Stack Overflow

select statement giving error for string variable

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related