There's a Blob in my Android

It turns out that storing large blobs in Android’s sqlite3 database is not quite straightforward.

The blobs (say, a few megabytes in size) get stored all right, however, what’s the point in storing things you never retrieve?

So, we attempt to retrieve this little blob of ours; the usual, straightforward Java-bound sqlite3 way (inside an SQLiteOpenHelper-derived class):

SQLiteDatabase db = getWritableDatabase();
Cursor crs = db.query(DB_TABLE_NAME,
                      new String[] { "blob" }, "id = " + id,
                      null, null, null, null);
Bitmap bm = null;
byte[] rv = null;

if(crs != null && crs.moveToFirst()) {
  rv = crs.getBlob(0);
}

Simple, right? Simple indeed, but it just doesn’t work. Welcome to the wonderful world of IllegalStateExceptions with no meaningful reason given.

Turns out that something in the database stack, someplace between sqlite3 and its Java bindings on Android, really doesn’t want to give you large pieces of data in one single chunk.

Well, fyvm, we’ll get it out in small pieces then. :P

SQLiteDatabase db = getReadableDatabase();
// here's the trick: we have some fairly large blobs in our db, and
// android SQLite bindings *will* fail when retrieveing too
// large blobs. therefore ...

// ... we first fetch the blob size ...
Cursor crs = db.rawQuery("SELECT LENGTH(blob) AS len FROM " +
                         DB_TABLE_NAME + " WHERE id = " + id,
                         null);
if(crs == null || !crs.moveToFirst()) {
  // bail out
}
int size = crs.getInt(cursor.getColumnIndex("len"));
crs.close();

// ... allocate space for the complete blob ...
byte[] rv = new byte[size];
// ... and finally fetch the blob in parts of
// MAX_BLOB_PART_SIZE size
int off = 0;
while(size > 0) {
  int partSize = MAX_BLOB_PART_SIZE;
  if(partSize > size)
    partSize = size;
  crs = db.rawQuery("SELECT SUBSTR(blob, " + off + "," +
                    partSize + ") AS part FROM " +
                    DB_TABLE_NAME + " WHERE id = " + id,
                    null);
  if (crs == null || !cursor.moveToFirst()) {
    // bail out.  again.
  }
  byte[] part = crs.getBlob(cursor.getColumnIndex("part"));
  crs.close();
  System.arraycopy(part, 0, rv, off, part.length);
  off += part.length;
  size -= part.length;
}
// there. you have the complete blob in rv now.

So, we have our blob now. Not sure whether it was a smart decision to fetch it …