Java JDBC/MySQL appropriate Syntax

[thelinuxguy]

Hi

I am having a problem trying to get the correct syntax for a java sql query of a mysql database.

Currently I have:-
result = statement.executeQuery("select account.account_number" +
"from account where account_number =" +accountNumberIn);

I keep getting a syntax error message:-

com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: 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 'where account_number =11111111' at line 1

where 11111111 is the value of accountNumberIn.

I would appreciate help from anyone who might know the correct syntax for such a query.

Thanks

[wolfcro]

try this code:

result = statement.executeQuery("select account_number" +
"from account where account_number = '" +accountNumberIn+"'");

[thelinuxguy]

Hi wolfcro
Thanks for your quick reply, I have tried the line you gave me but it did'nt work still got the syntax error as follows:-
com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: 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 'where account_number = '11111111'' at line 1

[wolfcro]

let's try with:

result = statement.executeQuery("select account_number" +
"from account where account_number = "+accountNumberIn+";");

if it doesn't work here you can see the syntax yourself and try:
dev.mysql.com/doc/refman/5.1/en/select.html

[thelinuxguy]

Hi Wolfcro
Thanks again for the quick reply, it didn't work, thanks for the link Ill check the syntax there.

Regards

theLinuxGuy

[Steve11235]

You will hate the answer to this one. Look at the end of your first literal and the beginning of the second one. There is no blank! Your query string looks like

select account_numberfrom account where account_number = ...

The parser things account_numberfrom is a column name and account is its alias. Then, it looks for the "from" clause and doesn't find one.

I saw this instantly, having done it to myself so many times. What I do is always *start* a new literal with a blank, unless no blank should be used. I found putting a blank at the beginning is easier to spot if it leave it out.

[thelinuxguy]

Hello Steve11235
Thank you for your reply. You were absoultely right. Problem solved, Thanks again.

Sincerely

theLinuxGuy

[masijade]

As a note, the semicolon at the end is not needed, and, when providing "arguments" (i.e. that accountnumber variable) you are much better using PreparedStatement and it's varying set... methods, as these will properly escape anything that needs to be escaped, as well as being able to redo the query, with different values, without having to recreate the query string. (And the first part of that will also help to prevent SQL Injection attacks.)