Android sqlite unable to read after updating a blob

itemprop="text">

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.