Android Sqlite query returning Empty set when there is Data

Question

I am having a Database Adapter that extends SQLiteOpenHelper.

public class DatabaseHelper extends SQLiteOpenHelper {
public SQLiteDatabase database;
public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    database = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
    // Creating Image table
    db.execSQL("CREATE TABLE "+IMAGE_TABLE+"("
            +IMAGE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
            +IMAGE_DATA+" BLOB NOT NULL);");
    db.execSQL("CREATE TABLE "+CREDIT_CATEGORY_TABLE+"("
            +CREDIT_CATEGORY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
            +CREDIT_CATEGORY_NAME+" VARCHAR(256) UNIQUE NOT NULL,"
            +CREDIT_CATEGORY_IMAGE+" INTEGER DEFAULT NULL REFERENCES "+IMAGE_TABLE+"("+IMAGE_ID+") ON DELETE RESTRICT ON UPDATE CASCADE);");
}
}

This is the Database Structure that is relevent to this question. And with such Structure, I am querying a row for populating the RecyclerView Adapter as shown below....

Cursor c = database.query(CREDIT_CATEGORY_TABLE,null,null,null,null,null,null,position+",1");

With the CREDIT_CATEGORY_ID returned from this query, I am allowing the data to be deleted in the application through a method(this method is within DatabaseAdapter).

public boolean deleteCreditCategory(int creditCategoryId) {
    if(isCreditCategoryDeletable(creditCategoryId)) {
        // TODO DEBUG this: The query function is always returning an Empty set
        Cursor c = database.query(CREDIT_CATEGORY_TABLE,new String[] {CREDIT_CATEGORY_IMAGE,CREDIT_CATEGORY_ID},CREDIT_CATEGORY_ID+"=?",new String[] {creditCategoryId+""},null,null,null);
        database.beginTransaction();
        if( database.delete(CREDIT_CATEGORY_TABLE,CREDIT_CATEGORY_ID+" = ?",new String[] {creditCategoryId+""}) == 1 )
        {
            // TODO BUG_INFO: since the query method returning empty set, we can't moveToFirst()...
            if(!c.moveToFirst()) {
                database.endTransaction();
                return false;
            }
            if(c.isNull(c.getColumnIndex(CREDIT_CATEGORY_IMAGE))) {
                database.setTransactionSuccessful();
                database.endTransaction();
                return true;
            }
            else {
                int imageId = c.getInt(c.getColumnIndex(CREDIT_CATEGORY_IMAGE));
                if( this.deleteImage(imageId) ) {
                    database.setTransactionSuccessful();
                    database.endTransaction();
                    return true;
                }
                else {
                    database.endTransaction();
                    return false;
                }
            }
        }
        else {
            database.endTransaction();
            return false;
        }
    }
    else
        return false;
}

Since this query c.query() is always returning an Empty set, The method c.moveToFirst() is returning false and the DeleteCreditCategory method is returning false indicating a Failure.

I am pretty sure of the results is Empty as I checked that through Debug and Log.d().There is sure data available in the Database.

What have I done wrong here?

Answer 1

I think the issue may be that you delete the row and this is then reflected in the cursor which you then look at and hence is empty, as beleive that a cursor is only actually obtained when you move within it (which includes using the getCount method, so getCount could be used instead of moveToFirst).

I suggest you try to get the row information before the delete and then delete after getting the information from the row.

No use checking for null cursor, it will not be null if the query works.

So perhaps you want something like :-

public boolean deleteCreditCategory(int creditCategoryId) {
    if(isCreditCategoryDeletable(creditCategoryId)) {
        // TODO DEBUG this: The query function is always returning an Empty set
        Cursor c = database.query(CREDIT_CATEGORY_TABLE,new String[] {CREDIT_CATEGORY_IMAGE,CREDIT_CATEGORY_ID},CREDIT_CATEGORY_ID+"=?",new String[] {creditCategoryId+""},null,null,null);
        if (Cursor.moveTofirst) {
            int imageid = c.getInt(c.getColumnIndex(CREDIT_CATEGORY_IMAGE));
            database.beginTransaction();
            if (database.delete(CREDIT_CATEGORY_TABLE,CREDIT_CATEGORY_ID+" = ?",new String[] {creditCategoryId+""}) == 1) {
                if( this.deleteImage(imageId) ) {
                    database.setTransactionSuccessful();
                    database.endTransaction();
                    return true;
                }
            }
            database.endTransaction();
        }
    }
    return false;
}

Note! I haven't tested this so there may be the odd typo or error.