View Images from Database

Is there a way/will there be a way to directly display an image that is retrieved from a database? Right now the only way to display an image is by storing it in the gateway and specifying its path in a component.

It would also be helpful to know how images will be stored when the future camera functionality is implemented.

1 Like

You can load a blob from a database into a paintable canvas. I don’t have the code to hand but can post it later, do a search and I’m sure it will come up as that’s how I found it.

Here’s an example from the forum. As craigb said, searching the forum can lead to lots of examples: Scale image in canvas

If you’re asking for how to do this in Perspective:
Currently, you would probably have to do it using a webdev script, that could either return the image directly or a correctly formatted base64 string, which would then be able to be used by Perspective’s image component (I’m pretty sure). There are some ways that we could improve this experience, but the hard problem to deal with is the fact that the browser can’t directly access the database - so we need to deliver the images to it from the gateway in a format it can understand.

You can create a named query that brings back the image bytes. If you are using MySQL, convert it to base64 in the query with TO_BASE64(ImageData).

Bind the source property of the perspective image component to the named query.

Do the following script transform:

b64Image = "data:imagef;base64,%s"%value
	
return b64Image

20 Likes

Thanks @jpark this did the trick! I had to find a different way to convert to base64 as I’m using sql server but your reply had a lot of detail which really helped me figure it out

2 Likes

Note that there is one heavy caveat to Jae’s method - significantly large images will dramatically slow down page loads, and can lead to issues in the designer, as well. Image components with true URLs will be automatically asynchronously loaded by modern web browsers, in a way that image;data encoded images cannot be. For some small images, it should be fine, but be aware of that if you’re building your entire project on it.

8 Likes

Would you have an example of having the webdev script to to create an image data from the database and return an image.

I am fine with the SQL part of this. I have vision projects that are returning image data. I am new to the WebDev module and unsure how to return the data.

I assume it is a doGet request?

The webdev script is just return {'bytes': <your byte array>}, where your byte array is populated via whatever mechanism you prefer to query the database (ie, system.db.runNamedQuery, system.db.runPrepQuery, etc).

How did you convert to 64 with sql server? I’m trying:

SELECT CAST('' as xml).value('xs:base64Binary(sql:column("lotphotos.photo"))', 'varbinary(MAX)') 
FROM lotPhotos
WHERE id = :photoID

then the script transform:

b64Image = "data:imagef;base64,%s"%value
	
return b64Image

And only having luck locking up my designer

Try changing varbinary(max) to varchar(MAX). I think that will convert that result to varchar type which is what you want for when you convert it to the base64 string.

Just found this thread and I m wondering why using python to encode the bytes into base 64 wasn’t discussed. Maybe somewhere else on the forum.

https://stackabuse.com/encoding-and-decoding-base64-strings-in-python/

Probably because it wasn’t needed, if the DB could do it for you. The crux of the discussion was the potential performance problems with base64 encode images, which would be avoided by serving the images via the WebDev module (direct byte stream).

Do you know how this would be accomplished in Vision? I am able to save my images using the file uploader in perspective, but then I want to display those images in the vision portion of the application, which I can’t seem to find a way to do.

The Paintable Canvas component is the most flexible tool to display arbitrarily-encoded images. Start here:

Other related topics:

http://forum.inductiveautomation.com/search?q=paintable%20canvas%20imageio

Hi @nburt.

I did the conversion using NamedQuery:
select cast(’’ as xml).value(‘xs:base64Binary(sql:column(“Table.ColVarBinaryMax”))’, ‘varchar(max)’) as img from Pergunta where idTable = :idT

  • [ColVarBinaryMax] VARBINARY(max) NULL, on SQL Server.

Then I execute the function on a button >>
image3 = system.db.runNamedQuery(“QueryImg”,{“idP”: 14})
system.perspective.print(image3)
self.getSibling(“Image”).props.source = “data:imagef;base64,%s”%image3

(“Image”) is my component in the view on perspective

Did not work.
Can you help me.

Thanks

This needs to be part of the image component section in the documentation.

Hi Jpark
I'm using SQLite for this so I wonder how to convert it to TO_BASE64. Do you know how to do this?

Hi Nader,

it is useful for converting it to TO_BASE64.

>>> import base64
>>> encoded = base64.b64encode(b'data to be encoded')
>>> encoded
b'ZGF0YSB0byBiZSBlbmNvZGVk'
>>> data = base64.b64decode(encoded)
>>> data
b'data to be encoded'
1 Like

A new solution that requires no scripting at all:

{ /shameless plug }

4 Likes