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?
Maximum Database Size 140 tb but it will depends on your device disk size.
SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit of 1 MB for the results.
We can retrieve anything from database using an object of the Cursor class. We will call a method of this class called rawQuery and it will return a resultset with the cursor pointing to the table. We can move the cursor forward and retrieve the data. This method return the total number of columns of the table.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With