22.05.2012

Android 2.1 SQLite: problem with QueryBuilder and Distinct

In a recent project I encountered a problem with SQLite on android 2.1. On later versions, my code worked perfectly, but on 2.1 it crashed every time when trying to get a column from a cursor.

Here’s the simplified code:

//member, a SQLiteOpenHelper
BackendOpenHelper helper;
//...
public List <Example> getExamples(String arg){
SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables("example e JOIN
        secondtable s ON e.id = s.example_id");
        Map<String, String> projectionMap =
        new HashMap<String, String>();
        projectionMap.put("id", "e.id");
        //... put in some more values ...
        builder.setProjectionMap(projectionMap);
        builder.setDistinct(true);
        builder.appendWhere(" e.someRow = ? ");
        //... some more wheres ...
        SQLiteDatabase db = helper.getReadableDatabase();
        String[] selectionArgs = new String[] {
            arg
        };
        Cursor cursor = builder.query(db, null,
        null, selectionArgs, null, null, null);
        if (cursor.moveToFirst()) {
            while (cursor.isAfterLast() == false) {
                int index = cursor.getColumnIndex("id");
                //on android 2.1, index is returned as -1
                //on newer versions as 1
                int id = cursor.getInt(index);
                //crashes if index is -1
                //...
                cursor.moveToNext();
            }
        }
        cursor.close();
        //...
}

After some research I found out that this apparently happens, when using distinct with the QueryBuilder on android 2.1.

So a quick fix for this problem is to simply don’t use the getColumnIndex() method from the cursor, but instead just access it by its id (Though you have to remember to change this part of the code if you make changes to your table rows).

    int id = cursor.getInt(1);

I hope this will help someone who encounters the same problem, so he doesn’t have to search for a solution as long as I had to.

Tobias Knell

Android 2.1 SQLite: problem with QueryBuilder and Distinct