Jay Jay - 1 month ago 7
Android Question

SQLite rawquery fails to match expected strings

I'm using this method for a simple db search.

public Cursor queryExpense(String createdDate, String createdTime, String category, String description, String amount) {
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_DATE + " LIKE ? AND " + COLUMN_TIME + " LIKE ? AND " +
COLUMN_CATEGORY + " LIKE ? AND " + COLUMN_DESCRIPTION + " LIKE ? AND " + COLUMN_AMOUNT + " LIKE ? ";
String mDate = createdDate.length() > 0 ? "%" + createdDate + "%" : "%";
String mTime = createdTime.length() > 0 ? "%" + createdTime + "%" : "%";
String mCategory = category.length() > 0 ? "%" + category + "%" : "%";
String mDescription = description.length() > 0 ? "%" + description + "%" : "%";
String mAmount = amount.length() > 0 ? "%" + amount + "%" : "%";

Log.d("Parameters",mDate + "," + mTime + "," + mCategory + "," + mDescription + "," + mAmount);

return db.rawQuery( query, new String[] { mDate, mTime, mCategory, mDescription, mAmount } );
}
}


When no values are passed (All fields are "%"), one of the results is this record:

Date: 30/10/2016
Time: 6:56:41
Category: Food
Description: Lunch at Nosh
Amount: 25


However the same record does NOT appear when specifying the description as "unc". The debug log shows the expected parameters and SQL, so i'm not sure why this is not a result.

SELECT * FROM expenses where createdDate like ? and createdTime like ? and category like ? and description like ? and amount like ?
[%,%,%,%unc%,%]


What am i missing?

EDIT: Problem Solved

Thanks to @laalto i've identified the problem to be due to having both Cursor.moveToFirst and Cursor.moveToNext, as a result of which the search may have been skipping the one result.

Answer

Based on question comments: The query is just fine and matches the expected row.

The problem is reading the returned Cursor. A construct like

if (cursor.moveToFirst()) {
    while (cursor.moveToNext()) {
        //...
    }
}

skips the first result row, which is the only returned row with the more specific selection criteria.

Idiomatic pattern for iterating over cursors would be:

if (cursor.moveToFirst()) {
    do {
        //...
    } while (cursor.moveToNext());
}