Android Question Check Device memory to avoid SQLITE Errors.

[Pedro Caldeira]

Hello All,
I have a problem that have no idea how to solve, for now.
I have a APP the reads some images from a SQLite database in B64 strings.
Some devices read and decode the image with no problems whatsoever, but others crash the App with a SQLITE error 'cursor to big to fit in row'.
Even with a Try..Catch doesn't "catch" the error. If i place a lenght condition in the query, it works, but I only want to do that if the device hasn't enough memory., and to set a boolean (LowMemoryDevice ) var to control this.
How can I solve this problem ? Any ideas ?

Below the query. imageb64 is the image coded in a B64 string.

If ShareCode.LowMemoryDevice Then
      ImageLimit = $"and length(imageb64) < 170000"$
 End If

Dim sSQL As String = $"select distinct a.id, ifnull(a.image,'') as imageb64, ifnull(a.filename,'') as imagename, 0 as base_image, 0 as on_report
            from dta_tasks_items as a
            inner join dta_requests_values as b on (b.task_tagcode=b.task_tagcode
            and b.item_tagcode=a.item_tagcode and b.unique_key=a.unique_key)
            where b.request_tagcode='${this.Request.Trim}'
            and b.inner_request_tagcode='${this.Action.Trim}'
            and b.task_tagcode='${this.Task.Trim}'
            and b.item_tagcode='${this.Item.Trim}'
            and b.unique_key='${this.UniqueKey.Trim}'
            and b.repeatcounter=${this.RepeatCounter}
            and b.repeatitemcounter=${this.RepeatItemCounter}
            and b.repeatfieldcounter=${this.repeatfieldcounter}
            ${ImageLimit}"$

 

[Quandalle]

On Android the size of a cursor is limited depending on the version to 1MB or 2MB (to be checked).
So with your request you overflow the size of the cursor and it causes an error.
The reason for the overflow is probably related to the size of the images.

A first (partial) solution would be not to encode the images in base64. Sqlite can store images directly in binary without base64 encoding. The weight of a base64 encoded image is about 4/3 the weight of the binary image. So you can save 1/3 of the size. (Search on forum ImageToByte() for storing in SQLITE Blob)

This will improve the situation but even so you may still have overflows. The solution in this case is not to retrieve the image directly in the SQL query, but by later by an other independent query.

For example in your query instead of returning the image field, you return the rowid line of the record.

Dim sSQL As String = $"select distinct a.id, rowid , ifnull(a.filename,'') as imagename, 0 as base_image, 0 as on_report
            from dta_tasks_items as a
            inner join dta_requests_values as b on (b.task_tagcode=b.task_tagcode
            and b.item_tagcode=a.item_tagcode and b.unique_key=a.unique_key)
            where b.request_tagcode='${this.Request.Trim}'
            and b.inner_request_tagcode='${this.Action.Trim}'
            and b.task_tagcode='${this.Task.Trim}'
            and b.item_tagcode='${this.Item.Trim}'
            and b.unique_key='${this.UniqueKey.Trim}'
            and b.repeatcounter=${this.RepeatCounter}
            and b.repeatitemcounter=${this.RepeatItemCounter}
            and b.repeatfieldcounter=${this.repeatfieldcounter

This way the cursor will stay at a reasonable size.
And when you want to use the image you query by another SQL query. For example :

myImage = sql.selectExecQuerySingleResult ($"SELECT image   FROM dta_tasks_items  WHERE rowid =${rowidFromCurrentCursorline}$ "$)

Otherwise it is not enough to cut the search for the image into slices
see for example : https://stackoverflow.com/questions...rom-android-sqlite-database/12721734#12721734


An other approach is to reduce the image size by compression (JPEG/WEBP) and/or resizing. This allows you to strongly reduce the space occupied.

A last approach is not to store the images in the database but as files in a directory