View Images from Database

[nburt]

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.

[craigb]

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.

[KathyApplebaum]

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

[PGriffith]

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.

[jpark]

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

[nburt]

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

[PGriffith]

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.

[djones]

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?

[PGriffith]

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).

[lwood]

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

[nburt]

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.

@lwood

'varbinary(MAX)'

[tmahaffey]

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/

[pturmel]

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).

[jordan.burnes]

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.

[pturmel]

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

Database and Paintable Canvas Refresh

Where is this code? In the paint method? If so, break it up so your db query runs separately (on some propertyChange) to obtain and set the client property. Say, on your Documents table, have a propertyChange that does most of the work, but only on image change, like so: if event.propertyName == 'id': from javax.imageio import ImageIO from java.io import ByteArrayInputStream blob = system.db.runPrepQuery(“SELECT file FROM magna_silao.documents where id= ?”, [event.newValue]) bytes = blo…

Other related topics:

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

[Ibere.Matias]

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

[ATSFareeth]

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

[nader.chinichian]

@jpark

base64

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?

[Baris.kose]

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'

[pturmel]

A new solution that requires no scripting at all:

Automation Professionals' Blob Server Module

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…

{ /shameless plug }