Out of Memory Exception Retrieving BLOB from SQLite Database

Targets iOS
1

My app uses a SQLite database. One of the tables has a column called “Image” of type BLOB. I have no data in that column. When I attempt to do anything with the BLOB column I receive an OutOfMemoryException.

So this:

if rs.Field("Image").NativeValue = nil

or this:

myMemoryBlock = rs.Field("Image").NativeValue

or this:

myIOSImage = iOSImage.FromData(rs.Field("Image").NativeValue)

All immediately causes an OutOfMemoryException.

If I wrap any of these lines in a try/catch then I can get past it but the app terminates moments later.

Is there another way to deal with BLOBs that contain no data?

2

Please file a bug report with an example project. For now I suspect you could do an SQL query to see if the column is empty, but I’m not the person to ask about SQL :).

3

Thanks Joe. I’ve been doing some more testing and the error occurs even if there is an image in the column. I’m creating my database in code so I wonder if I’m doing something wrong when I create the BLOB column. I’ll work on a demo project.

4

This might help.

5

Thanks Wayne but I don’t think those methods exist in iOS. The Xojo iOS Eddie’s Electronics demo app just does the equivalent of:

myIOSImage = iOSImage.FromData(rs.Field("Image").NativeValue)

However that app uses a supplied database; one isn’t created in code. I’m just putting together a simple demo app now that will hopefully either demonstrate a bug or show me where my own bug is! :slight_smile:

6

Sorry I didn’t take notice of the target.

7

No problem. :slight_smile:

8

Here’s the case @Joe Ranieri <https://xojo.com/issue/38475>

9

Code looks fine to me
Seems that the column being nil is not handled nor is there a way to tell the column is NULL with the SQliteDatabaseField built in
BUT you could do something like

dim rs as SQLiteRecordSet = db.SQLSelect("SELECT ID, TextColumn, coalesce(BlobColumn, 'NULL') as BlobColumn FROM TestTable")
and IF the memoryblock holds “NULL” then you know the column has no value

10

Thanks Norman. My app code also fails if the BLOB column has data for an iOSImage in it, so it’s not just an issue with a nil column.

With regard to checking for “NULL”, do you mean I should do a ConvertDataToText on the MemoryBlock?

11

or you could check for those specific 4 bytes

as for the image how’d you get the images in ?
that may be relevant

I’m not having trouble with images BUT mine are in postgresql format so they are “blobs” but not raw png’s etc
I still have to decode them from the postgresql hex format (which I can do pretty darned quickly)

12

I get the images in with the code below. Image is a class property of type iOSImage.

dim imageMB as MemoryBlock if Image <> nil then imageMB = Image.ToData("public.jpeg") end

Then it’s added to the database with either an INSERT or an UPDATE.

13

OK
I just did that with a fairly large JPEG here and retrieved it
So the only issue I’m seeing is the NULL column since there’s no isNull on the database field, it doesn’t raise a Nil Object Exception or SqliteException (which maybe it should)

Is your original image a jpeg ? (not that it should matter)
EDIT - doesn’t seem to. I started with a PNG inserted it using the jpeg type & it comes back out
And I can poke around in the database file & see its in there as a JPEG
(yes you CAN access the iOS simulator db file down a VERY long path but its there)
So that conversion seems OK

14

Thanks again Norman. The original image is coming down from a web service so maybe there’s an encoding/decoding issue. I’ll go back and take another look.

So are you saying I could alter my CREATE TABLE command to allow for a null BLOB and this might solve the issue in the sample app? If so, how do I do that?

15

Your table already allows for a NULL
So thats not a problem
The only issue is retrieving that column when it IS null
But the query I posted a while back can make it so you can tell if its null since you will ALWAYS get some value (either the data for the image OR the 4 bytes “NULL”
You’d just have to look at the returned memory block to know and NOT try to convert the memory block to an image IF it starts with “NULL” - otherwise you have something that should be an image

16

OK I just wanted to confirm that the issue only occurs when reading a NULL BLOB, not one with data in it, and that Norman’s workaround for that problem has got my app moving again. Thank you Norman!