25

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

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Sergio Andreotti
  • 913
  • 2
  • 8
  • 26

5 Answers5

61

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.

Joseph Earl
  • 23,351
  • 11
  • 76
  • 89
11

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

Mark Renouf
  • 30,697
  • 19
  • 94
  • 123
  • 2
    The 1 MB limit is the maximal size of the Cursor window. This is the memory buffer used to store result rows for IPC transfers. The window size grows dynamically up to this limit. – WindRider Oct 14 '13 at 15:43
11

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

Frederick Nyawaya
  • 2,285
  • 1
  • 15
  • 19
1

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?

Jonathan
  • 5,495
  • 4
  • 38
  • 53
  • Better answer: Don't use BLOBs in *any* database. RDBMs are not particularly well suited to storing large pieces of unformatted data- that's what a file system is for. – David Souther May 19 '11 at 05:58
  • @David Souther Thanks for the comment, you are right on. I would like to think BLOBs are useful for something, that's why I was a bit less definitive. However, I can't think of a good use case. Storing BluRay movies? (joking) – Jonathan May 19 '11 at 06:04
  • 1
    That post is from 2007. BLOBs are definitely supported, and have been since at least 1.6 (a.k.a. API level 4, a.k.a. Donut). The problem is with BLOB values over 1MB, as OP noted. – Ted Hopp Jul 18 '12 at 04:25
  • Modern file systems are also databases, so how is a DB not a good place for binary data? – Aleks N. Apr 28 '21 at 13:04
0

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.

Andrew
  • 2,530
  • 16
  • 9
  • I'm not sure what you mean by "loaded all at once". Given that `getBlob()` returns a `byte[]`, the data is already there on the heap... – vaughandroid Sep 04 '12 at 10:03
  • 7
    Assuming your `rs` is a Cursor, that first call to getBlob() will fail on large data due to the cursor window size. – sgibly Mar 27 '15 at 19:18