Wednesday, April 21, 2010

SQLite gotchas

We will soon share some video of our final game, a simple platformer, but in the meantime I've run into a strange gotcha that might prove handy for others.

It's well documented that a viable way to use SQLite on Android is as follows:

  • Subclass SQLiteOpenHelper and override onCreate and onUpgrade to create your databases.

  • Call that subclass to get a SQLiteDatabase object

  • Call your SQL commands (usually queries) on the instance of SQLiteDatabase, and use a Cursor to navigate the results.

But I found a curious bug: Every time I queried my cursor, it returned 0 results. I was sure my queries were well formed, and I'd tried using both rawQuery and query. Given that I was getting 0 rows as a result, I looked at my INSERT code.

None of the many fixes I attempted did any good, and Stack Overflow was baffled with me. But the Stack Overflow snippet I posted had the culpable code, which was the method I used to retrieve the cursor:

private Cursor fetchLevelDynamics(int id)
  SQLiteDatabase db = this.leveldata.getReadableDatabase();
  try {
    String fetchQuery = "SELECT row_num, col_num, type_dyn FROM " + DYNAMICS_TABLE + " WHERE level_id = ?";
    String[] queryArgs = {Integer.toString(id)};
    Cursor cursor = db.rawQuery(fetchQuery, queryArgs);

    Activity activity = (Activity) this.context;
    return cursor;
  finally {

Presented on its own, the error is very obvious: I close the database in the finally block, meaning the method returns a cursor pointing to a closed database.

But strangely, calling accessors on that cursor doesn't throw an exception. The Cursor instead acts as if pointing to an empty data set, going out of bounds when you call specific fields, and returning 0 on getCount.

This lame database-open/database-closed oversight was completely my fault. Still, the behavior indicated a very different problem than the actual cause, and be on the lookout if your Cursors don't return data that you suspect is present.

No comments:

Post a Comment