CODEWITHSUNDEEP
javaandroidmysqldatabasesqlite
anup
anup

Reputation: 605

Sqlite IN clause not working using values from an array in android?

I want to execute a sqlite query: 

c =  db.rawQuery("SELECT " + KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN + " , " +
                 KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " + KEY_TITLE +
                 " , " + KEY_PRICE + " , " + KEY_LANG + " , " + KEY_STATUS + " FROM " +
                 BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";", null);

The values in the in clause need to be taken from an array of strings:

String values[] = {"Singles","5 in 1 series","Childrens Book"};

Following is my error log.

Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: SELECT book_id , book_code , ISBN , vol_no , brand , sku , title , price , lang , status FROM books_table WHERE sku IN (Singles, 5 in 1 series, Childrens Book); at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1112) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:689) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1433) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1372)

This is the method that I call to get the Json Object.

public JSONObject searchForBooks( )
{
    String values[] = {"Singles","5 in 1 series","Childrens Book"};
    String inClause = Arrays.toString(values);

    //replace the brackets with parentheses
    inClause = inClause.replace("[","(");
    inClause = inClause.replace("]",")");

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c ;
    c =  db.rawQuery("SELECT " +KEY_BOOK_ID+ " , " + KEY_BOOK_CODE  + " , " + KEY_ISBN +
                " , " + KEY_VOL_NO  + " , " + KEY_BRAND  + " , " + KEY_SKU + " , " +
                KEY_TITLE + " , " + KEY_PRICE+ " , " + KEY_LANG+ " , " + KEY_STATUS
                + " FROM " + BOOKS_TABLE + " WHERE " + KEY_SKU + " IN " + inClause + ";",
                null);

    if (c.moveToFirst()) {
        do {
            JSONObject jobj = new JSONObject();
            try {
                jobj.put("book_id", c.getString(0));
                jobj.put("book_code", c.getString(1));
                jobj.put("ISBN", c.getString(2));
                jobj.put("vol_no", c.getString(3));
                jobj.put("brand", c.getString(4));
                jobj.put("sku", c.getString(5));
                jobj.put("title", c.getString(6));
                jobj.put("price", c.getString(7));
                jobj.put("lang", c.getString(8));
                jobj.put("status", c.getString(9));

                jarr1.put(0, jobj);
                jm1.put("books_info", jarr1);

                Log.d("Selected BOOKS info", jm1.toString());

            } catch (JSONException e) {
                e.printStackTrace();
            }
        } while (c.moveToNext());
    }
    c.close();
    db.close();

    return jm1;
}

Any help or suggestion is appreciated.Thank you.

Upvotes: 1

Views: 1574

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5916

You're missing the quotes around those strings.

I would build the in clause in a slightly different way though

String values[] = {"Singles","5 in 1 series","Childrens Book"};
boolean first = true;
String inClause = "(";

for(String v : values){
    if(first){
        first = false;
    } else {
        inClause += ","
    }
    inClause += "'" + v + "'";
}
inClause += ")";

Edit

To avoid what CL. describes in his comment, you can replace

inClause += "'" + v.replaceAll("'", "''") + "'";

with

inClause += "'" + v + "'";

This way if you have single quotes inside your strings they will be escaped and won't mess the final query up.

Upvotes: 2

Related Questions