Android sqlite unable to read after updating a blob

Question

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?

You need to include the relevant code, considering How do I ask a good question? — MikeT
Do you have a stack-trace in the log, if so add that. How big is the image? As a restriction that applies only when reading into a cursor, not when inserting/updating a column, is the size of data that a CursorWindow can hold 1 or 2m if memory serves (so generally only see with BLOBs) — MikeT
Added the stacktrace. There is no previous error in the log. As I said it's working fine, I can add new records with images the same size without a problem. I can open them up. I can even update the last record over and over again and also load them. But when I update one of the previous records, those records cannot be opened. The cursor doesn't contain the record. — Rico Kahnert
try adding DatabaseUtils.dumpCursor(cursor); before while (cursor.moveToNext()) { ..... Output will be in the log. Also try omitting the image from the query see if that works. — MikeT
10Mb image size may well result in issues. Up to around and average of about 100k then ok to store image in DB. Larger then you should really store image as a file and store the path in the DB. — MikeT

MikeT MikeT · Accepted Answer · 2018-06-01T04:45:06

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 :-

Rather than store the files as BLOBS, store files as files on disk and store the path in the Database.
Significantly reduce the size of the images.
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.

Android sqlite unable to read after updating a blob

1 Answers