# Storing Datasets in MySQL Blobs

**URL:** <https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905>\
**Category:** Ignition\
**Created:** [October 1, 2018, 9:00am UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905 "2018-10-01T09:00:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![conlonj25](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@conlonj25](https://forum.inductiveautomation.com/u/conlonj25)\
**Post date:** [October 1, 2018, 9:00am UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/1 "2018-10-01T09:00:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 1, 2018, 12:52pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/2 "2018-10-01T12:52:39Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [October 1, 2018, 3:07pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/3 "2018-10-01T15:07:18Z")

</div>

MySQL also supports the [JSON type](https://dev.mysql.com/doc/refman/8.0/en/json.html) which could be used for something like this aswell, having that the configurations stored can’t be fitted into a regular table.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 1, 2018, 3:14pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/4 "2018-10-01T15:14:45Z")

</div>

> [@Claus\_Nielsen](#):
>
> supports the JSON type

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.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [October 1, 2018, 3:17pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/5 "2018-10-01T15:17:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 1, 2018, 3:26pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/6 "2018-10-01T15:26:56Z")

</div>

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()`](http://files.inductiveautomation.com/sdk/javadoc/ignition79/795/com/inductiveautomation/ignition/common/TypeUtilities.html#datasetToJSON-com.inductiveautomation.ignition.common.Dataset-); would be the easiest way to keep the existing application logic using datasets, but get (some of) the benefit of JSON columns.

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [October 1, 2018, 4:18pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/7 "2018-10-01T16:18:47Z")

</div>

Also, Perspective bindings…

---

<div class="post-metadata">

**Author:** ![conlonj25](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@conlonj25](https://forum.inductiveautomation.com/u/conlonj25)\
**Post date:** [October 2, 2018, 12:45pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/8 "2018-10-02T12:45:38Z")

</div>

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**

```auto
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**

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [October 2, 2018, 1:10pm UTC](https://forum.inductiveautomation.com/t/storing-datasets-in-mysql-blobs/19905/9 "2018-10-02T13:10:16Z")

</div>

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