How to use string field in where clause in android sqlite -

i have on sqlite table tb_expense string user_id field primary key. inserting table using contentvalues this

    contentvalues values = new contentvalues();      values.put(key_user_id, databaseutils.sqlescapestring(expense.getuser_id()));      values.put(key_date , databaseutils.sqlescapestring(date));      values.put(key_amount , expense.getamount());      values.put(key_particulars , databaseutils.sqlescapestring(expense.getparticulars()));      values.put(key_place, databaseutils.sqlescapestring(expense.getplace()));      long expense_id = db.insert(table_expense , null ,values); 

     

insertion perfect , able print expense_id using log.e problem while retrieving table ;

when retrieve whole data tbl_expense without condition , works fine .

    string query = "select * "+table_expense;      cursor c =  db.rawquery(query,null); 

above query works fine , able whole data;

but when try retrieve expense of 1 single user id in clause , retrieves nothing. here code

     

 string query = "select * "+table_expense +" "+ key_user_id+" = '"+ user.getid() +"'";      cursor c =  db.rawquery(query,null); 

and log.e prints query this

   

select * tbl_expense user_id = 'usr107672' 

i think query correct , , usr107672 exists in db , because without clause record getting. error here?

i tried using db.query this

     

cursor c = db.query              (                      table_expense,                      new string[] {  key_user_id, key_particulars , key_amount , key_date , key_place },                      key_user_id + " = ? " ,                      new string[] { user.getid()},                      null, null, null              ); 

 but didn't work. 

i ran same problem yesterday.

reason

databaseutils.sqlescapestring surrounds string single quotes. so, string 'usr107672' instead of usr107672. quotes part of string , stored in database such. since query database without quotes in where clause, didn't match.

solution 1

this query retrieve entry want:

cursor c = db.query (table_expense,            new string[] {  key_user_id, key_particulars , key_amount , key_date , key_place },            key_user_id + " ? " ,            new string[] { "'%" + user.getid() + "%'"},            null, null, null); 

the % allows 0 or more characters, covers single quotes.

since cause unwanted matches, rather suggest following.

solution 2

use replaceall method escape single quotes:

user.replaceall("'", "''"); 

see sqlite faq

finally

i wouldn't use databaseutils.sqlescapestring anymore. called method databaseutils.appendescapedsqlstring marked deprecated (except single use case special crafted clause needed -- see source code reference).