Android select statement returns no results via rawquery or query while using query directly works

Question

I've spent the whole day so far trying to get a select query to execute viarawquery or query, but I've had no luck so far. The select statement I want to run is as the following:

 SELECT * FROM h_word WHERE category='GRE' AND DONE=0  ORDER BY RANDOM() LIMIT 1

category is a TEXT type column and DONE is an INTEGER type with the default value of 0.

While the query works fine when executed directly in SQLite, in android,it doesn't return any results. I've tried the below with no luck (the method is located in a class extended from SQLiteAssetHelper which itself is a helper class originally extended from SQLiteOpenHelper originaly taken from here: https://github.com/jgilfelt/android-sqlite-asset-helper:

    public Cursor getRandomWord() {
    Cursor c;
    SQLiteDatabase db = getWritableDatabase();

    c=db.rawQuery(query, null);
    String query = "SELECT * FROM h_word WHERE category='GRE'  AND DONE='0'  
    ORDER BY RANDOM() LIMIT 1 ";
    c=db.rawQuery(query, new String[] {});
    c.moveToFirst();
    db.close(); 
    return c;

    }

I also tested with GRE instead of 'GRE' and 0 instead of '0' but it made no difference. did the following as well:

        public Cursor getRandomWord() {
        Cursor c;
        SQLiteDatabase db = getReadableDatabase();
        c=db.query(true, "h_word", new String[] {
                 "_id",
                 "word",
                 "english_meaning"

                },
                 "category" + "=?" + " AND "  +
                 "DONE" + "=?",
                 new String[]{"GRE" ,"0"},
                 null, null, "RANDOM() LIMIT 1" , null);
        c.moveToFirst();
        db.close(); 
        return c;
    }

but the cursor remains empty.

Any ideas what I might be doing wrong here? Any help would be much appreciated. PS: when running a simple select statement without a where clause it, works fine.

In your first code sample, why are you using quotes for the condition on `DONE` value? If it's an integer, you need to write it as following `...AND DONE=0...` — Joël Salamin, Aug 30 '14 at 18:52
Hi, I removed the db.close(); line to see what happens, but the cursor still remains empty. I also get this in the logcat:"A SQLiteConnection object for database '/data/data/com.example.hangman/databases/hangman_db1.db' was leaked! Please fix your application to end transactions in progress properly and to close the database when it is no longer needed." — Reihaneh R, Aug 30 '14 at 18:53
Hi Joel, I also tried w/o quotes but stills returns an empty cursor. — Reihaneh R, Aug 30 '14 at 18:56

score 0 · Accepted Answer · edited May 23 '17 at 11:49

After another few hours of struggling, I figured it's a bug in android's SQLiteDatabase class.

I managed to solve the problem by changing the name of the "category" column to something else. Seems like "category" is a key word in the android SQLiteDatabase code, and makes a query return nothing when written in where clauses on the android side.

Someone else also had this problem here: Android rawquery with dynamic Where clause

Android select statement returns no results via rawquery or query while using query directly works

1 Answers1