Access Large BLOB in Android Sqlite without Cursor

Question

There seems to be a 1MB limit on Android's Cursor Window size which limits the ability to read BLOBs from SQLite. I know you may say we should not store BLOBs in database but by definition, BLOB is considered a Binary Large Object and if there was no need to store them in database, there was no need to implement such object type in any database engines.

The 1 MB limit on the implementation of Cursor however, seems to be insufficient in almost all cases. I need to store my binary data for valid reasons in SQLite database and they are well over 1 MB. SQLite is capable of handling BLOBs perfectly since the C API is working perfectly fine in Xcode (iPhone platform) to retrieve large objects without any issues.

My questions is if we can possibly access the BLOB data in Android without using cursors. I am thinking of a lower level access to Sqlite in Java. Any suggestions?

Answer 1

There is actually a solution without resorting to a different implementation of SQLite wrapper for Android!

Retrieve large blob from Android sqlite database

Answer 2

As CL mentioned, using NDK is indeed a way to access Sqlite natively via C language in Java language. However I realized it could get really messy if I wanted to write a custom wrapper myself and try to access the functions in Java.

After searching around, I came across a brilliant open source project called Sqlite4java which is a tight wrapper around Sqlite, compiled to use on various platforms including Android. This library allows you to interact with Sqlite without using Android Cursor which removes the limitations.

I am able to retrieve 20 MB of Blob in 480 milliseconds. This is even faster than reading a small record from Sqlite via Cursors. I believe this can be used to enhance any query to Sqlite by skipping the use of Cursor. Here's the link to this great library: http://code.google.com/p/sqlite4java/

Answer 3

Android's Java API always has the 1 MB limit.

You should not store BLOBs of that size in the database; the file system is more efficient at handling them.

If you really want to use BLOBs, you have to go through the NDK to access the C API directly.