# SQLite Image Upload and Retrieval

**URL:** <https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [August 19, 2024, 4:51am UTC](https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792 "2024-08-19T04:51:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [August 19, 2024, 4:51am UTC](https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792/1 "2024-08-19T04:51:20Z")

</div>

Hi,

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

Table structure:

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

```

Insert:

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

```

Uploaded image in db like:

```python
<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:

```python
 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:

```python
data:image/jpeg;base64,PEFycmF5V3JhcHBlcj46IFstMTE5LCA4MCwgNz...

```

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [August 19, 2024, 6:40pm UTC](https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792/2 "2024-08-19T18:40:18Z")

</div>

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

> [@Automation Professionals' Blob Server Module](https://forum.inductiveautomation.com/t/automation-professionals-blob-server-module/57970):
>
> Automation Professionals is pleased to announce an alpha release of its new Blob Server Module. Available only for Ignition v8.1, here. The Blob Server Module provides a simple way to deliver images, PDF files, or other binary content from a database to your Perspective clients or other web applications. Without needing the WebDev module. All you need is a Named Query that has appropriate security settings and returns a single row with two columns: Content, and ContentType. Modest documentati…

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [August 19, 2024, 6:58pm UTC](https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792/3 "2024-08-19T18:58:44Z")

</div>

> [@Dharani\_T](#):
>
> ```python
> self.view.custom.UploadedFile = event.file.getBytes()
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [August 20, 2024, 4:17am UTC](https://forum.inductiveautomation.com/t/sqlite-image-upload-and-retrieval/94792/4 "2024-08-20T04:17:54Z")

</div>

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