SQLite Image Upload and Retrieval

Ignition
1

Hi,

Can someone help with uploading image and retrieving it from SQLite?

Table structure:

CREATE TABLE Images (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Image BLOB NOT NULL
);

Insert:

self.view.custom.UploadedFile = event.file.getBytes()

Uploaded image in db like:

<ArrayWrapper>: [-119, 80, 78, 71, 13, 10, 26, 10, 0, 0, 0, 13, 73, 72, 68, 82, 0, 0, 0, -57, 0, 0, 0, 81, 8, 6, 0, 0, 0, 102, 58, -78, -104, 0, 0, 0, 1, 115, 82, 71, 66, 0, -82, -50, 28, -23, 0, 0, 0, 4, 103, 65, 77, 65, 0, 0, -79, -113, 11, -4, 97, 5,....

Retrieved image code:

 import base64
    
    query = 'SELECT Image FROM Images where ID= ?'
    results = system.db.runPrepQuery(query, [currentValue.value])
    
    if results and len(results) > 0:
        imageBlob = results[0][0]
        base64Data = base64.b64encode(bytes(imageBlob)).decode('utf-8')
        self.getChild("root").getChild("Image").props.source = "data:image/jpeg;base64," + base64Data

Image:

data:image/jpeg;base64,PEFycmF5V3JhcHBlcj46IFstMTE5LCA4MCwgNz...
2

Is there any reason you cant just insert the data from file upload component directly and retrieve it using Phil's module?

1 Like
3

This is a lossy conversion.
Perspective properties must be JSON, so they can be safely synchronized back and forth between the gateway and the running session on the user device.

By directly assigning a byte array (the result of getBytes() to a Perspective property, you are implicitly casting it to a string; thus the
<ArrayWrapper>: [-119, ...] you see in the database. Whatever you do at this point is throwing good code after bad.

Why are you attempting to store the bytes into a property, if you're also using a database, to @josborn's point? Just shove the bytes into the database (you probably shouldn't use SQLite for this) directly in the onFileReceived event, via a prep update or named query.

Then retrieve them whenever - you can use scripting for this, but Phil's module is going to be nicer to use. Data URIs are a heavy load on the client device and can't be optimized well. Encoding and decoding binary to base64 unnecessarily also puts extra load on the gateway.

2 Likes
4

I understand. I shall try @pturmel 's module. Thanks for the feedback.