Android sqlite unable to read after updating a blob

I want to store an image (size approx. 10MB) in the SQLite database. For that I created a DB helper, a Dao. Everything works fine, I can create several records and read them without a problem, I can even update the blob in the latest record without a problem.
But if I go back to an older record and update the blob, I cannot load this record with the blob any longer.
I have a list view where I show all the records, and for that I use a select that doesn't return the blob. This list works fine, but when I click on an item in the list, I try to load the record with the blob, the cursor returns 0 rows.

    public void save(Bill aBill) {
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.BILL_NAME_COLUMN, aBill.getName());
        values.put(DatabaseHelper.BILL_DUE_DATE_COLUMN, getContentValue(aBill.getDueDate()));
        values.put(DatabaseHelper.BILL_IMAGE_COLUMN, aBill.getImage());
        if (!aBill.isPersistent()) {
            aBill.setId(database.insert(DatabaseHelper.BILL_TABLE, null, values));
            aBill.setPersistent(true);
        } else {

            database.update(DatabaseHelper.BILL_TABLE, values, DatabaseHelper.BILL_ID_COLUMN + "=?", new String[]{String.valueOf(aBill.getId())});
        }
    }

    // fails after updating the blob
    public Bill get(long id) {
        Cursor cursor = database.query(DatabaseHelper.BILL_TABLE,
                new String[]{DatabaseHelper.BILL_ID_COLUMN, DatabaseHelper.BILL_NAME_COLUMN, DatabaseHelper.BILL_DUE_DATE_COLUMN, DatabaseHelper.BILL_IMAGE_COLUMN}, "id = ?", new String[] {String.valueOf(id)}, null,
                null, DatabaseHelper.BILL_DUE_DATE_COLUMN);
        Bill bill = null;

        while (cursor.moveToNext()) {
            bill = new Bill();
            bill.setPersistent(true);
            bill.setId(cursor.getLong(cursor.getColumnIndex(DatabaseHelper.BILL_ID_COLUMN)));
            bill.setName(cursor.getString(cursor.getColumnIndex(DatabaseHelper.BILL_NAME_COLUMN)));
            bill.setDueDate(getDate(cursor.getString(cursor.getColumnIndex(DatabaseHelper.BILL_DUE_DATE_COLUMN))));
            bill.setImage(cursor.getBlob(cursor.getColumnIndex(DatabaseHelper.BILL_IMAGE_COLUMN)));

        }
        cursor.close();

        return bill;
    }

    //works fine after updating the blob
    public List findAll() {
        List bills = new ArrayList();
        Cursor cursor = database.query(DatabaseHelper.BILL_TABLE,
                new String[]{DatabaseHelper.BILL_ID_COLUMN, DatabaseHelper.BILL_NAME_COLUMN, DatabaseHelper.BILL_DUE_DATE_COLUMN}, null, null, null,
                null, DatabaseHelper.BILL_DUE_DATE_COLUMN);


        while (cursor.moveToNext()) {
            Bill bill = new Bill();
            bill.setPersistent(true);
            bill.setId(cursor.getLong(cursor.getColumnIndex(DatabaseHelper.BILL_ID_COLUMN)));
            bill.setName(cursor.getString(cursor.getColumnIndex(DatabaseHelper.BILL_NAME_COLUMN)));
            bill.setDueDate(getDate(cursor.getString(cursor.getColumnIndex(DatabaseHelper.BILL_DUE_DATE_COLUMN))));

            bills.add(bill);
        }
        cursor.close();

        return bills;
    }

Here is the exception:

java.lang.IllegalStateException: Couldn't read row 0, col 0 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
at android.database.CursorWindow.nativeGetLong(Native Method)
at android.database.CursorWindow.getLong(CursorWindow.java:511)

at android.database.AbstractWindowedCursor.getLong(AbstractWindowedCursor.java:75)
at net.rka.android.billreminder.BillDao.get(BillDao.java:106)

I suspect that updating a blob in a row corrupts the database somehow.
Did anybody run into a similar problem? If so how did you solve it?

Answer

Your issue is very likely due to the size of the image(s) and a quirk, for want of a better term, that you can store large BLOB's without issue, but due to the size limitations of an Android's Cursor Window of 2m, that you may not be able to retrieve the BLOB. This sometimes compounded by some of the SQLiteDatabase/Cursor (The Cursor getBlob() or it's underlying methods in this case) methods that basically hide underlying failures, in order to provide what is often a simpler development experience.

If you used the SQLiteDatabase DatabaseUtils.dumpCursor this may highlight the issue(s) that may have been hidden by the SQLiteDatabase query convenience method. So adding :-

DatabaseUtils.dumpCursor(cursor); //<<<< ADDED
while (cursor.moveToNext()) { ........

May provide clues.

I can think of 3 options :-

1. Rather than store the files as BLOBS, store files as files on disk and store the path in the Database.



2. Significantly reduce the size of the images.


3. Look into using C++ and the native SQLIte3 libraries to retrieve the BLOBS into a suitably sized container.

Perhaps the may be some libraries that do this. However, I don't recall any being mentioned.