Retrieve large blob from Android sqlite database

Question

I stored chunks of binary data (protobufs) in the sqlite database of an Android app without realizing that Android's Cursor can only hold a maximum of 1MB of data. I now know that I should have stored these binary blobs in files and only referenced the files in the sqlite database entries.

I need to upgrade the database (the app has been in use for a while) in order to move these binary chunks to files. The problem is that some user's data may have already exceeded the 1MB limit and I'm not able to retrieve it from the database (accessing the resulting Cursor for a single row that contains a large blob throws an IllegalStateException: Couldn't read row 0, col 0 from CursorWindow. Make sure the Cursor is initialize before accessing data from it).

How do I retrieve the binary blobs that are greater than the 1MB Cursor limit that have been stored in the sqlite database?

score 38 · Accepted Answer · answered Oct 04 '12 at 06:47

38

You can read large blobs in pieces. First find out which ones need this treatment:

SELECT id, length(blobcolumn) FROM mytable WHERE length(blobcolumn) > 1000000

and then read chunks with substr:

SELECT substr(blobcolumn,       1, 1000000) FROM mytable WHERE id = 123
SELECT substr(blobcolumn, 1000001, 1000000) FROM mytable WHERE id = 123
...

You could also compile your own copy of SQLite and access either the BLOB stream I/O functions or the normal query functions of the C API with the NDK, but that would be too complex in this case.

answered Oct 04 '12 at 06:47

CL.

173,858
17
217
259

Thank you so much! This really helped. I had no idea about the SQLite core functions. Worked perfectly! – ashughes Oct 04 '12 at 16:54
Do you have any efficient way of adding those chunks to make up a whole `byte[]`? – Kamran Ahmed Aug 24 '14 at 07:32
@KamranAhmed You already know the entire length, so allocate a big array and copy the chunks. – CL. Aug 24 '14 at 07:41
I was wondering if there was a better way than iterating over each byte. Thanks for the quick response. :) – Kamran Ahmed Aug 24 '14 at 12:44
2

can you provide an example with steps please, it will be really helpful – Jitesh Upadhyay Oct 13 '16 at 11:15

score 0 · Answer 2 · answered Jul 17 '16 at 08:46

CL. answer will only with blobs<5MB. If you tried to use it with blobs bigger than 5 megabytes you will still get the exception. To fetch large blobs you need to use a library called sqlite4java that uses native calls to the database without using cursors. Here is an example of using this library to fetch a large blob:

SQLiteConnection sqLiteConnection=null;
SQLiteStatement sqLiteStatement=null;
try
{
    File databaseFile = context.getDatabasePath("database.db");
    sqLiteConnection=new SQLiteConnection(databaseFile);
    sqLiteConnection.open();
    sqLiteStatement=sqLiteConnection.prepare("SELECT blob FROM table WHERE id=?");
    sqLiteStatement.bind(1, id);
    sqLiteStatement.step();
    byte[] blob=sqLiteStatement.columnBlob(0);
}
finally
{
    if(sqLiteStatement!=null)
        sqLiteStatement.dispose();
    if(sqLiteConnection!=null)
        sqLiteConnection.dispose();
}

Note that this code will store the whole blob in memory. You might not have enough memory for it. Instead of `columnBlob`, you might consider `columnStream`, which allows you to pull data byte by byte: https://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/SQLiteStatement.html#columnStream(int) — sereda, Apr 21 '19 at 15:29

Retrieve large blob from Android sqlite database

2 Answers2

Linked

Related