Cursor size limit in Android SQLiteDatabase

Question

I download a db from internet. I save it in my datases folder and I open it. Inside the db there is a table "Ads" with 6 fields. 2 of these fields are BLOB. When I want to read from this table... I have some problem... I noticed that when I read a row with a blob field more bigger than 1 mega byte, this causes an exception... "get field slot from row 0 col 0 failed". if it's a little blob, all is ok... thanks in advance :)

Answer 1

Reading a BLOB that is less than 100KB from a SQLite database is faster than reading the same from the file system. However, anything greater than that is best kept on the disk, with a reference in the db. More at: http://www.sqlite.org/intern-v-extern-blob.html

Answer 2

There's a limit of 1MB on internal assets due to dynamic decompression; the 1MB limit also seems to apply to Cursor blobs but this doesn't seem to be documented anywhere.

Generally you should avoid blobs in SQLite as they perform poorly; instead save the blob data as a file and store the location of the file in your DB.

Answer 3

Is the query you're using 'selecting' said blob? Can you omit it? If you can't, are you using:

 blob = rs.getBlob(column) ;
 InputStream in = blob.getBinaryStream();

or

 blob.getBytes(1, lengthOblob) ;

The first method will let you read the blob in chunks. The second is going to have the blob loaded all at once.

Answer 4

There is a 1MB limit per operation. (I am unsure if this is per row, or per column in the case of SQLite queries). The limit is due to the SQLite API interacting with sqlite out-of-process over the Binder/Parcel IPC system. The same limit applies for values within a Bundle (Intent extras, for instance).

The Binder transaction buffer has a limited fixed size, currently 1Mb, which is shared by all transactions in progress for the process.

See: http://developer.android.com/reference/android/os/TransactionTooLargeException.html

Answer 5

Looks like BLOB support in Android isn't implemented yet...

http://www.basic4ppc.com/forum/basic4android-updates-questions/6648-support-sqlite-blob.html

What data is stored in the BLOB fields? I would guess pictures.

I would recommend NOT using BLOBs. The file system is a much better choice for binary data.

Can you explain a bit more about what you want to accomplish with the database?