Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What is wrong with this Query?

 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can someone please clue me in to what in the world is wrong with this search query? I tried it directly into database it it works fine yet it continues to throw an exception with I test it in my java code




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 'USERNAME = 'user1'' at line 1


EDIT:

So apparnlty it only likes it on one line and not concatenated on several lines. This works fine

 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not the multiple lines that's the problem. Here's your code:



And here's what the same code would look like on a single line:



You'll notice that your correct version of the code has whitespace which the failing code doesn't have.

By the way I guess that you haven't had any users named O'Brien yet. That would cause your SQL to fail. So I suggest you should switch to a PreparedStatement, which takes care of all those annoying details.


 
Matthew Brown
Bartender
Posts: 4568
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's fine split over several lines - but you need to remember to add the spaces at the end of each line. Your first example is the equivalent to this:
See the problem?

By the way, you ought to look into using parameterised queries - dynamically building up the query like that is poor practice. What happens if usr contains a quote, for instance?

Edit: snap!
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good catch on that guys. It was making me crazy!

What would happen if a user has a quote in his or her name? Isn't it still stored as a string? It there no way to escape the quote as there are in PHP?
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65340
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, use a PreparedStatement. There's no reasonable excuse not to. It will also guard against injection exploits.
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:Yes, use a PreparedStatement. There's no reasonable excuse not to. It will also guard against injection exploits.


I will look at this more closely. Of course what I am working on is just for a learning experience but if I am going to learn it, I need to learn how to do it the right way.

Thanks!
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found the below example and I am not sure if I understand what is going on.

Is this saying USER_ID since 1 if the first column in the table and the second parameter is what is being searched for and this is what the "?" stands for?




 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, the 1 which is the first parameter of the setInt method refers to the first ? parameter in the SQL string.
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would someone be kind enough to explain how this works then? I have been reading through the documentation and it doesn't really make sense.

I don't understand how the search parameter is padded to the search query.
 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's not to understand? Your query:



has one question mark, which corresponds to having one parameter. So to set that parameter you do something like this:



which says "Set the value of parameter number 1 to 1001."

If you had two question marks, i.e. two parameters, then you would need another statement using "setInt(2..." or "setString(2..." to set its value. And so on.
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK. I got it, I think.

So if I have a query and I need to match a name and an ID would it be something like this?

 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, exactly. You got it.
 
Raymond Gillespie
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Yes, exactly. You got it.


Awesome. Thanks for the help.


Just as a follow up to anyone that may stumble on this later on. I found a pretty good example of using a prepared statement.

http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic