querying sqlite using string as where condition

Question

why does when i use a string as a where condition in my database, my program just force close. though it is working fine if i use number as a query condition. please help me, thanks

public ArrayList<Contact> getAvailableList() 
{
    // TODO Auto-generated method stub
    ArrayList<Contact> results = new ArrayList<Contact>();
    String[] columns = new String[]{KEY_NAME, KEY_NUMBER, KEY_STATUS};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_STATUS +"=available" , null, null, null, KEY_NAME);
    String sName = "";
    String sNum = "";
    String status = "";
    int iName = c.getColumnIndex(KEY_NAME);
    int iNumber = c.getColumnIndex(KEY_NUMBER);
    int iStatus = c.getColumnIndex(KEY_STATUS);
    Contact contact;
    for(c.moveToFirst(); ! c.isAfterLast(); c.moveToNext())
    {
        contact = new Contact();
        sName += c.getString(iName);
        sNum += c.getString(iNumber);
        status += c.getString(iStatus);
        contact.setName(sName);
        //contact.setPhoneNumber(sNum);
        contact.setPhoneNumber("0".concat(sNum));
        contact.setStatus(status);
        results.add(contact);
        sName = "";
        sNum = "";
        status = "";
    }
    return results;
}

Sam · Accepted Answer · 2012-12-06 01:54:19Z

2

You need to wrap strings in SQL with quotes, like this:

//                                                       Add these  v         v
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_STATUS +"='available'", 
        null, null, null, KEY_NAME);

Or if you want to use dynamic data, you should use the selectionArgs parameter:

String status = "available";
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_STATUS +"=?", 
        new String[] {status}, null, null, KEY_NAME);

This approach simplifies keeping track of matching quotes in complex Java/SQL Strings, more importantly it protect you from SQL injections.

edited Dec 6, 2012 at 1:54

answered Dec 6, 2012 at 1:45

Sam

87k21 gold badges185 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lazy Ninja · Accepted Answer · 2012-12-06 01:46:03Z

1

You forgot ' ' around available

  Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_STATUS +"='available'" , null, null, null, KEY_NAME);

answered Dec 6, 2012 at 1:46

Lazy Ninja

22.6k9 gold badges86 silver badges104 bronze badges

Collectives™ on Stack Overflow

querying sqlite using string as where condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related