# Retrieving Base64 Images from SQL Databases **URL:** \ **Category:** Ignition\ **Tags:** perspective, ignition81\ **Created:** [October 5, 2023, 12:58am UTC](https://forum.inductiveautomation.com/t/retrieving-base64-images-from-sql-databases/80046 "2023-10-05T00:58:28Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [October 5, 2023, 12:58am UTC](https://forum.inductiveautomation.com/t/retrieving-base64-images-from-sql-databases/80046/1 "2023-10-05T00:58:28Z")
I have been struggling through making Base64 images work properly in various areas of a job, so seeing as there is no clear information on how to do this, I thought I would make a post to preserve the info I have accumulated. **Encoding an image to save in a Database** I am using a device that sends me Image data on OPC-UA, so my data will come from a different system than most people will use. When I get an image, I have a python script in my gateway that does the following: ```auto import base64 logger = system.utilities.getLogger("logger") image = #Convert file to base64 string imageBytes = ''.join(map(lambda x: chr(x % 256), image)) b64image = base64.b64encode(imageBytes) #Send to DB try: system.db.runPrepUpdate("INSERT INTO images (image) VALUES (?)", [b64image],database=Database) except: logger.info("Barcode Reader Database Insert Failed") ``` My database table is simply two columns, an index 'id' which is an AUTO\_INCREMENT PRIMARY KEY, and 'image' which is a MEDIUMTEXT Depending how your image data comes into python, you may have either an array of Java.lang.Short, and you need this code above. If you have an array of bytes, you can skip to: ```auto b64image = base64.b64encode(imageBytes) ``` and just set imageBytes to your image data. If you look in your database, you should have soem nice large simple strings in the 'image' column. **Using the image in Perspective** In Perspective, the image data is relatively easy to use. Make a named query with the following SQL: ```auto SELECT image from images where id = :image ``` Add a parameter of type Int4 to the query with the name "image" Test the query works and returns the string from the database when given a correct ID as a parameter. Make a page and drag into the page an "Image" component from the "Perspective Components" Name the image component "Image" Bind PROPS-\>source to a "Query" and select the named query you just made. Add a valid ID to the parameters Add the following script transform to the binding: ```auto def transform(self, value, quality, timestamp): b64Image = "data:image/jpeg;base64,%s"%value return b64Image ``` The image should now work correctly and display the image you selected. You may need to change the "data:image/jpeg" to your image type. **Making a Download Button** Drag a button component from the "Perspective Components" onto the page. Open up the Event Configuration and add a Script to "onClick" Enter the method below: ```auto def runAction(self, event): from com.inductiveautomation.ignition.common import Base64 image64 = self.getSibling("Image").props.source image = Base64.decode(image64) data = image system.perspective.download("image.jpg", data) ``` When you click on the button, the image should download to your client and be usable as a normal image file. **Using the image in Reports** Create a Report and add a query datasource using the named query you used earlier. Set the Data Key to "queryDataKey" Add a Script Datasource _under_ it with the below code: ```auto def updateData(data, sample): from com.inductiveautomation.ignition.common import Base64 image64 = data['queryDataKey'].getCoreResults().getValueAt(0, 0) image = Base64.decode(image64) data['image']=image ``` Drag an image into the report in the Design tab, and drag the 'image' key from Data Sources into the image ImageShape-\>Key property. Open the Preview tab, and the image should now be displayed. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 5, 2023, 1:22am UTC](https://forum.inductiveautomation.com/t/retrieving-base64-images-from-sql-databases/80046/2 "2023-10-05T01:22:37Z")
Consider just storing the bytes directly in your DB ("blob" aka "binary large object" or "byte array" column types). Most databases support this, your database will consume 25% less space, and you won't burn CPU cycles encoding and decoding base64 at every turn. You can also dramatically improve Perspective performance in tables or repeating views containing images by **not** including data URLs in your tables/queries, but defer to a WebDev or a my free [Blob Server module](https://forum.inductiveautomation.com/t/automation-professionals-blob-server-module/57970/48) to supply `src` to image components. Those data URLs of yours are horribly inefficient. > [@David\_Stone](#): > > struggling through making Base64 images work .... there is no clear information on how to do this Not an accident. There ~~'s~~ _ **was** _ no clear information because it is not a good approach. ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [October 5, 2023, 1:51am UTC](https://forum.inductiveautomation.com/t/retrieving-base64-images-from-sql-databases/80046/3 "2023-10-05T01:51:20Z")
> Consider just storing the bytes directly in your DB ("blob" aka "binary large object" or "byte array" column types). Unfortunately the path to store the bytes from the data types that I get from OPC-UA do not work when retrieving them from a database. None of the published methods of storing the data into SQL as a BLOB worked with the data I have, but this method does work, and as a result, this is the method that is being used. In my application, these images are of very small size, limited to being used one image per page and do not refresh or poll, so the performance impact is negligible. If you need to scale this to larger or more intensive systems, then you will have to re-evaluate your choices of system design. This is not my recommendation of a best solution, but a record of one option if you are not able to get it working with other methods.