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.

Kommentare

  1. this a good example here :
    http://j2eeandroid.blogspot.com/2014/11/androidsqlite.html

  2. I encountered a similar crash with Android 2.1, a SQL query containing DISTINCT and my joins. Android 2.2+ worked perfect fine when executing this statement but Android 2.1 would FC complaining it could not find column "_id":
    int nutrientId = cursor.getInt(cursor.getColumnIndexOrThrow(NUTRIENTS_ID));
    Adding an "as" to my SQL query resolved my issue:
    "SELECT distinct n._id as _id ..."

  3. thanks