Android Question SQLite - BLOB field

artsoft · Mar 23, 2021

Hi all!

I am currently working with big BLOBs in a SQLite DB.

(==> android:targetSdkVersion="30")

_________________________________________________________________________
I know that a SQLite DB can be very big:

_________________________________________________________________________
And BLOB fields can have the following size:

I can save big BLOBs (max. 5 MBytes) in my SQLite DB without any problems.

But the reading (selecting) of the BLOBs creates the following exception:

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1

The cause of this exception is the big size of the BLOB in combination with the used CURSOR.

The size of the CURSOR is too small to get the Bytes() data from DB.

My question:
So, how I can increase the limit of the CURSOR in order to read my bytes arrays from DB without any exceptions?

Would be nice if somebody could help here

Thanks in advance and best regards
ARTsoft

emexes · Mar 24, 2021

I'm sure you're already onto this (given that your Google Search result mentions it also) but just in case ? : if the BLOBs are images, then there is a lot to be said for storing them as normal image files, with just a reference to them in the database (eg, the image filename or serial number).

I agree that sometimes this is outweighed by the convenience of having everything in a single file (but is a database ever truly just one file?!) or the extra robustness of having everything protected by database preimaging and transactions and rollbacks and backups.

OliverA · Mar 24, 2021

1) Live with the Android limit (1mb cursor limit. Note: not row limit, cursor limit). Chunk your large data (split it into multiple pieces)
2) Try newest SQLCipher
Source: https://github.com/sqlcipher/android-database-sqlcipher/issues/47
3) Compile your and install own version of SQLite

artsoft · Mar 24, 2021

emexes said:
I'm sure you're already onto this (given that your Google Search result mentions it also) but just in case ? : if the BLOBs are images, then there is a lot to be said for storing them as normal image files, with just a reference to them in the database (eg, the image filename or serial number).

I agree that sometimes this is outweighed by the convenience of having everything in a single file (but is a database ever truly just one file?!) or the extra robustness of having everything protected by database preimaging and transactions and rollbacks and backups.

Yes, I am absolutely with you, emexes! Thanks for your quick answer!!

In this case, my objects are images indeed - BUT : all of them are crypted with AES/SHA256. Therefore my wish, to have all such objects only in one DB. The crypted images may only be visible for someone who knows the correct password in order to decrypt them accordingly.

I suspect that will be the only solution.
But I'm not giving up hope yet.

Thanks again and best regards
ARTsoft

emexes · Mar 24, 2021

OliverA said:
Chunk your large data (split it into multiple pieces)

Lol my whole body clenched at the thought of having to do this.

But a derivative approach might be to store the images in a different table of the same database file, and link your metadata table to them, so that the metadata query results aren't clogged up with MB of image data (if that is what's happening - I thought that the result set returned is simply pointers to the records, rather than the actual data).

emexes · Mar 24, 2021

artsoft said:
I can save big BLOBs (max. 5 MBytes) in my SQLite DB without any problems.
...
The size of the CURSOR is too small to get the Bytes() data from DB.

So you can write data, but not read it back? It seems improbable that a trap like this would have lasted long before being fixed.

Then again, it is coming up to April 1st, so perhaps it is the modern database equivalent of WOM (datasheet):

artsoft · Mar 24, 2021

emexes said:
Lol my whole body clenched at the thought of having to do this.

But a derivative approach might be to store the images in a different table of the same database file, and link your metadata table to them, so that the metadata query results aren't clogged up with MB of image data (if that is what's happening - I thought that the result set returned is simply pointers to the records, rather than the actual data).

Before adding a new table, I will better create files in app private storage. And then only the links to these files have to be saved in DB.

But nevertheless, your idea to create a new table in order to store the encrypted images will result again in such an exception... or I am wrong?

Regards
ARTsoft

artsoft · Mar 24, 2021

emexes said:
So you can write data, but not read it back? It seems improbable that a trap like this would have lasted long before being fixed.

Then again, it is coming up to April 1st, so perhaps it is the modern database equivalent of WOM (datasheet):

Hahahaha, 1st April ... Thats good joke! ?

But hey, If the sizes are smaller than 1 MB I can write and read.
If the sizes are bigger, then there is no exception while writing the data, but an exception (too big exception) if I try to read it.
So I guess that the writing works ok. My Insert doesnt work with DB cursor. Only the selecting of the data.
I also tried to work only with "Resultset"s .... but this is only a Wrapper for Cursors ... and I get the same exception.

:-(

Regards
ARTsoft

artsoft · Mar 24, 2021

OliverA said:
1) Live with the Android limit (1mb cursor limit. Note: not row limit, cursor limit). Chunk your large data (split it into multiple pieces)
2) Try newest SQLCipher
Source: https://github.com/sqlcipher/android-database-sqlcipher/issues/47
3) Compile your and install own version of SQLite

Thx for your answer .... But I am not able to adapt and compile any SQLite sources or SQLite extensions.
Perhaps you can explain how to do this and then I can try.

Thx in advance.
Regards
ARTsoft

emexes · Mar 24, 2021

artsoft said:
But nevertheless, your idea to create a new table in order to store the encrypted images will result again in such an exception... or I am wrong?

You're probably right. I misread your description of the problem and naively assumed that it would be possible to read back at least a single record from the database.

emexes · Mar 24, 2021

artsoft said:
how I can increase the limit of the CURSOR in order to read my bytes arrays from DB without any exceptions?

I got as far as this post: Can't select images from SQLite database (Android) which (to look on the bright side) confirms your independent discovery and OliverA's advice:

Issue 2 - Image too large.

Images are not ideal candidates for storing in databases, rather it is better to store something that indicates the image to be used (e.g. a path to the image). The image itself is comprised of bytes, often a very large number of bytes. Although SQLite can hold pretty large images.

The android SDK implementation, that extracts data into a Cursor is more restrictive.

The Cursor is effectively a buffer that holds a partial snapshot of the whole data. The underlying snapshot, a Cursor Window, is restricted in the Android SDK to 2M and that at a minimum must be able to hold a single row. You are guaranteed a failure if you have an image that is 2M or more. You are very likely to get a failure or issues if an image is say greater than 0.5M.

An example of an error where an image is too large for the Cursor Window, even though it has successfully been stored in the database is :-

Fix for Issue 2

There is no simple fix unless you can reduce the size of the images to an acceptable/manageable size.

The recommended approach is to store the images elsewhere and to then store the path or part thereof in the database.

artsoft · Mar 24, 2021

I think I have a solution - probably a bad solution.

After taking a picture or after select a picture from Intent, I have access to the bitmap byte array.

With "WriteStream" command to output the bitmap data with a given quality level (format: JPEG / because PNG is lossless), I can reduce the byte data step for step (10% steps) until the size is lower than 1MB. The performance is really good because all manipulations of the bitmap is executed in the RAM. I imported a picture with 4000 x 3000 pixels (~ 3 MB file size) and I rewrite the bitmap data with 40% instead of 100% and the size was at one at about 730 KB. Also possible is a data reducing with percent values like a binary search ( <= 50% versus > 50%).

Tomorrow I will try this procedure - because it is not needed to have a super-quality picutre for printing or something like this. Only to see the content of the picture on android screen.

Regards
ARTsoft

emexes · Mar 24, 2021

emexes said:
So you can write data, but not read it back? It seems improbable that a trap like this would have lasted long before being fixed.

I believe this is a situation for which the word "gobsmacked" was invented.

OliverA · Mar 24, 2021

artsoft said:
because it is not needed to have a super-quality picutre for printing or something like this. Only to see the content of the picture on android screen.

If no zooming/panning is required, you could also resize the image. I don’t think there are any 4000x3000 phone screens. That’s how iOS does it with their iCloud picture management. The full size is stored on iCloud and a reduced size (appropriate for the phone’s screen) is stored on the phone.

emexes · Mar 24, 2021

artsoft said:
With "WriteStream" command to output the bitmap data with a given quality level (format: JPEG / because PNG is lossless), I can reduce the byte data step for step (10% steps) until the size is lower than 1MB. The performance is really good because all manipulations of the bitmap is executed in the RAM. I imported a picture with 4000 x 3000 pixels (~ 3 MB file size) and I rewrite the bitmap data with 40% instead of 100% and the size was at one at about 730 KB. Also possible is a data reducing with percent values like a binary search ( <= 50% versus > 50%).

Reduce the dimensions also, otherwise you will get some really strange artifacting going on (which is probably hidden at the moment when the image is downsampled to fit on the phone screen).

In fact, if the images are only for display on phones, why not reduce the dimensions to something approaching a typical phone resolution eg 800 x 600? (typing that gave me a SVGA spine tingle )

emexes · Mar 24, 2021

OliverA said:
If no zooming/panning is required, you could also resize the image.

Lol while I was off making a test image, OliverA beat me to the same idea. Great minds think alike.

This is a 480k pixel (~800x600) JPEG at quality 80 with regular subsampling = 110237 bytes. Looks good to me. Still spewin about that Android SQLite cursor limit, tho.

emexes · Mar 24, 2021

artsoft said:
I think I have a solution - probably a bad solution.

it is not needed to have a super-quality picutre for printing or something like this. Only to see the content of the picture on android screen.

This time you are wrong. It is a brilliant solution. Not only to skirt around the Android cursor problem, but also to keep your program from unnecessarily chewing up bandwidth and storage. ?

artsoft · Mar 24, 2021

emexes said:
I believe this is a situation for which the word "gobsmacked" was invented.

???

artsoft · Mar 24, 2021

emexes said:
This time you are wrong. It is a brilliant solution. Not only to skirt around the Android cursor problem, but also to keep your program from unnecessarily chewing up bandwidth and storage. ?

Well, then I'm really glad that you agree with me for once and support me - instead of making fun of me.

artsoft · Mar 24, 2021

emexes said:
Lol while I was off making a test image, OliverA beat me to the same idea. Great minds think alike.

This is a 480k pixel (~800x600) JPEG at quality 80 with regular subsampling = 110237 bytes. Looks good to me. Still spewin about that Android SQLite cursor limit, tho.

[IMAGE]

Thank you both for the great help. I am very annoyed about the cursor limit. Nevertheless, I think it's a combination between image quality and image resolution that leads to the goal here.

Best regards
ARTsoft

Android Question SQLite - BLOB field

Active Member

Expert

Expert

Active Member

Expert

Expert

Active Member

Active Member

Active Member

Expert

Expert

Active Member

Expert

Expert

Expert

Expert

Expert

Active Member

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency