Storing Datasets in MySQL Blobs

Ignition
1

Is there any way to store entire datasets as blob objects in MySQL?

I’m trying to create a mechanism where a user can store any number of trend configurations and retrieve them later on. It allows me to insert my dataset into the blob no problem. But, when I try to select it back out of the blob, it returns garbage text. Maybe I’m misunderstanding how blobs works?

2

You’ll want to serialize them into byte arrays, and store those. Use something like these functions:

from java.io import ByteArrayInputStream, ByteArrayOutputStream, ObjectInputStream, ObjectOutputStream

# Serialize one or more objects into a byte array
def objectsToBytes(*objects):
	baos = ByteArrayOutputStream()
	oos = ObjectOutputStream(baos)
	for o in objects:
		oos.writeObject(o)
	oos.flush()
	return baos.toByteArray()

# Reconstruct one or more serialized objects from a byte array
# Always returned as a list
def bytesToObjects(ba):
	bais = ByteArrayInputStream(ba):
	ois = ObjectInputStream(bais)
	result = []
	while True:
		try:
			result.append(ois.readObject())
		except:
			break
	return result
3 Likes
3

MySQL also supports the JSON type which could be used for something like this aswell, having that the configurations stored can’t be fitted into a regular table.

2 Likes
4

Using the native JSON type has huge advantages, if your process can be adjusted to switch away from datasets. In particular, you can search within them efficiently on the server side.

1 Like
5

The JSON type is very good for this exact case, the data isn’t too sensitive and there isn’t any joins, grouping etc.

6

Native JSON support in columns is definitely something we’re looking at taking more advantage of.

Also, for the OP: might want to check out TypeUtilities.datasetToJSON(); would be the easiest way to keep the existing application logic using datasets, but get (some of) the benefit of JSON columns.

2 Likes
7

Also, Perspective bindings…

1 Like
8

Thanks for all the help lads. I went with the JSON solution. Changed my column datatype to JSON. Works pretty well!

Only hiccup is that it won’t allow me to insert/select JSONObjects directly. On the store side I have to convert the JSONObject to a string before insertion. On the retrieve side, I select out a string which I can then remake into a JSONObject.

I though maybe you guys might have some input. It’s not really a problem, my code works fine, but it could always be better!

Store

from com.inductiveautomation.ignition.common import TypeUtilities

data = event.source.parent.getComponent('Easy Chart').tagPens
jData = TypeUtilities.datasetToJSON(data)

query = 'INSERT INTO trend_config (trend_name,trend_pens) VALUES (?,?)'
args = [event.source.parent.getComponent('Text Field').text, jData.toString()]
system.db.runPrepUpdate(query,args)

Retrieve

from com.inductiveautomation.ignition.common import TypeUtilities
from org.json import JSONObject

query = 'SELECT trend_pens FROM trend_config WHERE trend_name = ?'
args = [event.source.parent.getComponent('Dropdown').selectedStringValue]
qResult = system.db.runPrepQuery(query,args)[0][0]

jData = JSONObject(qResult)
data = TypeUtilities.datasetFromJSON(jData)

event.source.parent.getComponent('Easy Chart').tagPens = data
9

I don’t think there’s anything to do about that, TypeUtilities.datasetToJSON returns a JSONObject and MySQL wouldn’t know what to do with it.

The only thing I see that could be changed is that instead of using system.db.runPrepQuery in your retrieve part, I would choose system.db.runScalarPrepQuery.