Posted on by Tobias Knell | Posted in Mobile Blog, Tutorial | Tagged , , , ,

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.



2 Responses to Android 2.1 SQLite: problem with QueryBuilder and Distinct

  1. ganesh says:
  2. Bill Mote says:

    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 …”


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>