Retrieving Base64 Images from SQL Databases

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:

import base64

logger = system.utilities.getLogger("logger")
image = <my-image-data>

#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:

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:

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:

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:

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:

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.

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 to supply src to image components. Those data URLs of yours are horribly inefficient.

Not an accident. There's was no clear information because it is not a good approach.

1 Like

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.