Is it wise to use dataset tags for storing file 'blob' data vs storing in SQL?

I am hoping someone can share their expertise on the recommended or best practice in terms of saving data. I currently utilize a dataset type memory tag to store user uploadable files…one is an image of the instrument (temperature sensor/flow meter/motor…etc.) and one pdf file of the technical spec or manual. I have previously saved binary data into an SQL database using a blob data type. My question is in terms of reliability and efficiency if it is worth making a table in MSSQL to map filename, binary data, and associated tag (either by saving the ID or tag path or udt instance path…etc.) vs. just keeping all the binary data in the real-time tag provider as memory tags. I might be answering my own question, but one of my colleagues uploaded about 20 files and a week later we noticed that the tag was “NULL” and Ignition indicated some error about the data not being the right datatype and defaulting to NULL value. Sorry I don’t have the actual error as I thought no one had uploaded any images yet. My concern about going the SQL route is the delay/speed to perform the query and retrieve the data and show on a popup window when the user clicks on a given instrument. The memory tags right now work fast with no delay. However, because of that hiccup I don’t know how permanent the data in a real-time tag provider is and if an improper shutdown of the server would cause data loss or corruption.

If you made it this far in reading…thanks.

Memory tags are written to the internal database whenever changed. Dataset memory tags containing large file data is a really bad idea, I would think.

I’d agree with @pturmel - not the best idea.

Thanks guys. I’ll change the code to write to SQL Is it still safe to use a dataset Custom Property on a template to temporarily store the file data so that an Image component can reference it and display the image if/when the property updates? Or do I bypass all of this and directly run a select query, grab the binary data, and use setIcon to display the image immediately…no temporary storage?

I would run the select query, grab the binary data, use ImageIO to make the final image object, and cache that. Presumably on your target component using .putClientProperty(), but could also be cached in a script module variable.

I suggest a search here for “putClientProperty” … (-:

Thanks Phil, this is definitely pointing me in the right direction. I used to do something similar with the paintable canvas and caching, but it never worked out the way I wanted since it wouldn’t update with new images (or invalidate the old cache). Plus the paintable canvas would only work and show something on the client window and not in the designer which slowed down my development/testing. So I ended up just sticking with the Image component and the setIcon() function. I’ll give this a shot.

The paintable canvas repaints when any custom property is change, plus whenever Swing needs to fix any screen area. If you don’t have some other reason to have a custom property, just make a dummy boolean that you can toggle whenever a new image is cached.

Do you not use preview mode in the designer? The paintable canvas displays just fine in designer preview mode.

I do use the preview mode…but perhaps its been too long since I messed with paintable canvas that I moved on. Your probably going to hate me for this, but I’ve been using this code for years now and I don’t have to worry about ImageIO, BAOS, or redrawing/caching…even when resizing. I use this on an Image control propertyChange event and so far no complaints.

if (event.propertyName == "ImageID"):
	id = event.newValue
	imgctrl = event.source
	if (id > 0):
		sqlquery = "SELECT [fileBlob] FROM [TagFiles] WHERE ID = ?"
		fileData = system.db.runPrepQuery(sqlquery,[id])
		if (fileData.rowCount > 0):
			from javax.swing import ImageIcon
			image = ImageIcon(fileData.getValueAt(0,0))
			image = image.getImage().getScaledInstance(imgctrl.getWidth(),imgctrl.getHeight(),2)
			image = ImageIcon(image)
			imgctrl.setIcon(image)
	else:#clear image
		imgctrl.setIcon(None)

No, that’s a decent piece of code. It’s all in the foreground, though, so it could cause interactivity problems. Consider passing the ImageID and the component dimensions to a background thread (invokeAsynchronous), finishing with .setIcon() in the foreground with invokeLater(). While it may sound like a lot of trouble, it should run noticeably smoother.

Well, here’s my first attempt to using invokeAsynchronous and invokeLater. From what I read, you can’t “pass” parameters with invokeAsync so I get the ImageID and the dimensions from within the getImage() function. Then, I use the invokeLater to perform the .setIcon() function…which I presume is happening in the foreground…somehow…despite the fact that it is inside the async function.
If this is correct, I’ve got a lot of other code that needs to be put into background threads.

def getImage():
	id = event.newValue
	imgctrl = event.source
	width = imgctrl.getWidth()
	height = imgctrl.getHeight()
	image = None
	if (id > 0):
		sqlquery = "SELECT [fileBlob] FROM [TagFiles] WHERE ID = ?"
		fileData = system.db.runPrepQuery(sqlquery,[id])
		if (fileData.rowCount > 0):
			from javax.swing import ImageIcon
			image = ImageIcon(fileData.getValueAt(0,0))
			image = image.getImage().getScaledInstance(width,height,2)
			image = ImageIcon(image)
		#end if
	#end if
	def loadImage():
		event.source.setIcon(image)
	#end def
	system.util.invokeLater(loadImage)
#end def

if (event.propertyName == "ImageID"):
	system.util.invokeAsynchronous(getImage)
1 Like

Put the def getImage() and following inside your if block. No need to waste time defining that function for nothing (when other properties are changing). Otherwise, yes, that’s about right.

You might find the helper functions in this script module useful.

1 Like

Thanks for the tips and the script module!