CODEWITHSUNDEEP
javamysqlsqlconnectiondatabase-connection
user3868051
user3868051

Reputation: 1249

Unknown column error in where clause, using MySQL and Java

Following is my code line :

ResultSet rs3 = stmt6.executeQuery("SELECT * FROM ShopSystem.Order where s_id="+s_id+" AND status="+Pending);

I am getting the following error :

Unknown column 'Pending' in 'where clause'

What could be the reason... I cant get through it..

Upvotes: 0

Views: 2691

Answers (4)

mugiwaradz
mugiwaradz

Reputation: 393

you must use the PreparedStatement in this case

// use the ? for the 2 entries values
    String selectSQL = new String("SELECT * FROM ShopSystem.Order where s_id=? AND status=?")
    preparedStatement = dbConnection.prepareStatement(selectSQL);
// in order you must incialise them here                
                 preparedStatement.setString(1, "s_id");
                preparedStatement.setString(2, "Pending");
//execute your resultset    `enter code here`
ResultSet rs = preparedStatement.executeQuery();

Upvotes: 0

slavik
slavik

Reputation: 1303

Don't make concatenation ! Use prepared statements

PreparedStatement stm = conn.prepareStatement("SELECT * FROM ShopSystem.Order where s_id = ? AND status = ?");
stm.setInt(1, s_id);
stm.setString(2, Pending.name());
ResultSet rs = stm.executeQuery();

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

No doubt, status is a string, so it needs to be compared to a string. Use delimiters:

SELECT * FROM ShopSystem.Order where s_id="+s_id+" AND status='"+Pending+"'"

Or better yet, learn how to write code that uses parameter substitution for putting parameter values into SQL strings.

Upvotes: 3

juergen d
juergen d

Reputation: 204766

Change it to

AND status = '" + Pending + "'"

You need to put the string in quotes. Otherwise the DB thinks you mean a column name.

But actually you should use Prepared Statements. Then you don't need to patch the queries together like this and you don't worry about parameters and escaping them...

Upvotes: 1

Related Questions