Blobs in SQLite

Help
1

I have a SQLite .db file on an edge device.
I can query text, integers and floats, but not blobs.
Is there a special procedure for that?

3

@Lars_Andersson,

Are you seeing any errors when you query blobs or is the query empty?

Would you be able to share with me your SQLite node configuration?

Thank you,
Heath

7

I see no errors

image
image539×664 26.1 KB

9

The below fields selected are not blobs, then it returns the data correct.
As soon as I add a blob field, nothing happens, no errors either. Nothing returned to the debug console.

10

@Lars_Andersson,

Do you have logging enabled? Is there any information in the Edge Agent log?

Thank you,
Heath

11

@Lars_Andersson,

Thanks for sending me some private information in a DM.

We did some digging and it looks like the resulting query returns 5 blobs that are ~180kb each. This would indicate that the payload you are generating is too large to be displayed in the debug panel.

This means that the workflow was likely executing correctly but the data was not being displayed in the debug panel.

Thank you,
Heath

15

So when the returned data is too large, the debug console shows nothing?

17

Debug messages adhere to the same 256kb size limit for all MQTT messages. So in this case, the message was sent, but rejected by the broker.

There’s some improvements we can make here. Ideally some message should still show up indicating the debug node was executed. I submitted a ticket to investigate options.

19

My intention was to report this blob as a device state to one of the attributes that are defined as a blob of content type image/png.
Will that be possible, even though I can’t see anything in the debug console, or will this be rejected all together?

21

The maximum size of a blob attribute is 256kb. As long as what you’re reporting is less than that, when Base64 encoded, it can be reported.

The Debug Panel likely inflates the size quite a bit because of the JSON representation. You can Base64 encode the file locally and check the file size.

22

I realized I was selecting 5 records containing a blob each, so when I limit the result to 1, I can see the data returned in the debug window, but it’s as an array, type “buffer” with 188342 items in it.
Do I need to encode this in the sql statement before it’s executed or can I encode the payload data ?
A little lost here, but it at least returning data.

23

You will likely need to convert the array of bytes into a Base64 string using a Function Node. Something like:

let buf = Buffer.from(payload.working.queryResult.data);
payload.working.encodedImage = buf.toString('base64');

I did not explicitly test this, but it should point you in the right direction.