Using a variable in a SELECT FROM WHERE query

[cplmckenzie]

Hi,
I have a java web app that accesses a MySQL database.

It works well except that I am unable to to retrieve a rowset when I use

the WHERE clause with a variable... as in

SELECT * FROM tablename WHERE tablename.fieldname = variablename

Any help is appreciated

regards,

[fred]

did you get some exception? or any error?

[cplmckenzie]

No. The query executed but it said "No Items Found" as though the variable was null.

I was attempting to change the query string in a SessionBean setcommand.Value

regards,

[leonard]

please paste the code, probably the problem is in it

[daheri]

No. The query executed but it said "No Items Found" as though the variable was null.

Hey man,

"No Items Found" means exactly what it says. Your query has returned 0 rows.
In other words, either the table is empty, or your where clause is eliminating all results. Try running your query directly against the db to check.

[anki1234]

paste the code . May be u are not able to provide proper parameter in query corresponding to which u r making search

[rico16135]

Use PreparedStatement to create your query. leave a '?' in place of your variable to insert. then use the method set() in the PreparedStatement class to insert your variable. There are setInt, setString, etc. for whatever type you are using. You should read up on PreparedStatement as well as all the Statement classes to see how they are used, as well as to determine which one to use.

[DonCash]

Have you constructed the query string correctly?

"SELECT * FROM " + tablename + " WHERE " + tablename.fieldname + " = " + variablename;

[Eranga]

"SELECT * FROM " + tablename + " WHERE " + tablename.fieldname + " = " + variablename;

tablename.fieldname

Here need only the field/column name right? No need to use the table name here. :)

[javarishi]

Hei Eranga, You have pointed out the error.

[Eranga]

It's not Java at all pal. Don may put it by a mistake :)

[DonCash]

I thought he had wrote a Java application to connect to a SQL database and that was his string query to the db being sent within the code. Nice one Eranga.

[Eranga]

Yes Don, I see that on our original post too. I'm just pointing that my friend. Such a little mistake can be make a huge damage at all. :)