jsonEncode dataset tags with Query as Value Source

Ignition
1

One of my projects have a nested dictionary that contains report configuration and datasets.

I ran into this after upgrading to Ignition 8.1.15 from (I think) 8.1.7

When trying to encode a dictionary of datasets I get a max recursion error. I traced it down to datasets with a query value source. All other datasets work. As a temporary fix, I modified the dataset before encoding it. This happens when using a dataset from the runQuery function or from reading a tag derived from a query.

To replicate:

dsTest1 = system.db.runQuery('SELECT * FROM someTable')
print system.util.jsonEncode({'test' : dsTest1}, 4)

Temporary Fix:

dsTest2 = system.dataset.addColumn(dsTest1, [None for row in system.dataset.toPyDataSet(dsTest1)], 'value', str)
print system.util.jsonEncode({'test' : dsTest2}, 4)

Is an unintended result of some other code fix in a previous release?

2

Hello, are you able to set up and use a Named Query with system.db.runNamedQuery? From a quick test, there doesn’t seem to be an issue with Named Queries.
https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery

System.db.runQuery() appears to return a
<type ‘com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet’>
type object, whereas system.db.runNamedQuery() returns a
<type ‘com.inductiveautomation.ignition.common.BasicDataset’>.
The two different dataset return types may be what is causing the max recursion error.

If you wanted investigate further with system.db.runQuery(), please open a Support Ticket so that we may investigate further. Support Home Page | Inductive Automation

3

Confirmed. I set up a query tag, and a dataset tag with the just the data hand-typed in. From what I’m seeing:

  • All datasets will encode when used singly, but not necessarily when used as in a key/value pair.
  • I could see this happening after the change to PyDataSet to make it more efficient. In the meantime, you can get the underlying dataset from a PyDataSet and it will encode ok, either by using the undelyingDataset property or system.dataset.toDataSet().
  • The dataset from a query tag, even though it’s a BasicDataset, will not encode. addColumn() creates a new dataset.
def encode(dataIn):
	try:
		return system.util.jsonEncode(dataIn)
	except:
		return 'Cannot encode'

query = 'SELECT 1 as x, 2 as y'

queryResult = system.db.runQuery(query, 'Production')
print '*** PyDataSet ***'
print '-print queryResult, type(queryResult)'
print queryResult, type(queryResult)
print '\n-encode(queryResult)'
encode(queryResult)

print "\n-print {'test' : queryResult}"
print {'test' : queryResult}
print "-encode({'test' : queryResult})"
encode({'test' : queryResult})

print '\n-type(queryResult.underlyingDataset)'
print type(queryResult.underlyingDataset)
print "\n-print {'test' : queryResult.underlyingDataset}"
print {'test' : queryResult.underlyingDataset}
print "\n- encode({'test' : queryResult.underlyingDataset})
encode({'test' : queryResult.underlyingDataset})

print '\n-print type(system.dataset.toDataSet(queryResult))'
print type(system.dataset.toDataSet(queryResult))
print "\n-print {'test': system.dataset.toDataSet(queryResult)}"
print {'test': system.dataset.toDataSet(queryResult)}
print "\n-encode({'test': system.dataset.toDataSet(queryResult)})"
encode({'test': system.dataset.toDataSet(queryResult)})


tagList = ['[Test]xyDatasetTag', '[Test]xyQueryTag']
xyDatasetTag, xyQueryTag = [tag.value for tag in system.tag.readBlocking(tagList)]
print '\n*** Dataset tag (no query) ***'
print 'print xyDatasetTag, type(xyDatasetTag)'
print xyDatasetTag, type(xyDatasetTag)
print '\n-encode(xyDatasetTag)'
encode(xyDatasetTag)
print "\n-{'test' : xyDatasetTag}"
print {'test' : xyDatasetTag}
print "\n-encode({'test' : xyDatasetTag})"
encode({'test' : xyDatasetTag})

print '\n*** Query tag'
print '\n-print xyQueryTag, type(xyQueryTag)'
print xyQueryTag, type(xyQueryTag)
encode(xyQueryTag)
print "\n-print {'test' : xyQueryTag}"
print {'test' : xyQueryTag}
print "\n-encode({'test' : xyQueryTag})"
encode({'test' : xyQueryTag})

print '\n *** Write query tag to new dataset() ***'
newDataSet = system.dataset.toDataSet(list(xyQueryTag.getColumnNames()), 
                                      [[xyQueryTag.getValueAt(row, col) 
                                        for col in xrange(xyQueryTag.getColumnCount())] 
                                        for row in xrange(xyQueryTag.getRowCount())
                                      ]
                                     )
print "\n-print newDataSet"
print newDataSet
print "\n-print {'test' : newDataSet}"
print {'test' : newDataSet}
print "\n-encode({'test' : newDataSet})
encode({'test' : newDataSet})

Output

*** PyDataSet ***
-print queryResult, type(queryResult)
<PyDataset rows:1 cols:2> <type 'com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet'>

-encode(queryResult)
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-print {'test' : queryResult}
{'test': [[1, 2]]}
-encode({'test' : queryResult})
'Cannot encode'

-type(queryResult.underlyingDataset)
<type 'com.inductiveautomation.ignition.common.BasicDataset'>

-print {'test' : queryResult.underlyingDataset}
{'test': Dataset [1R ⅹ 2C]}
{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}

-print type(system.dataset.toDataSet(queryResult))
<type 'com.inductiveautomation.ignition.common.BasicDataset'>

-print {'test': system.dataset.toDataSet(queryResult)}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test': system.dataset.toDataSet(queryResult)})
u'{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}'

*** Dataset tag (no query) ***
print xyDatasetTag, type(xyDatasetTag)
Dataset [1R ⅹ 2C] <type 'com.inductiveautomation.ignition.common.BasicDataset'>

-encode(xyDatasetTag)
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-{'test' : xyDatasetTag}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test' : xyDatasetTag})
u'{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}'

*** Query tag

-print xyQueryTag, type(xyQueryTag)
Dataset [1R ⅹ 2C] <type 'com.inductiveautomation.ignition.common.BasicDataset'>
u'{"columns":[{"name":"x","type":"java.lang.Integer"},{"name":"y","type":"java.lang.Integer"}],"rows":[[1,2]]}'

-print {'test' : xyQueryTag}
{'test': Dataset [1R ⅹ 2C]}

-encode({'test' : xyQueryTag})
'Cannot encode'

 *** Write query tag to new dataset() ***

-print newDataSet
Dataset [1R ⅹ 2C]

-print {'test' : newDataSet}
{'test': Dataset [1R ⅹ 2C]}
{"test":{"data":[[1],[2]],"columnNames":["x","y"],"bulkQualityCodes":null,"columnTypes":["class java.lang.Integer","class java.lang.Integer"],"asXML":"H4sIAAAAAAAAALOxr8jNUShLLSrOzM+zVTLUM1BSSM1Lzk/JzEu3VQoNcdO1ULK34+WySSm2s0nO\nz7GzSbEztNFPsbPRB/OgQkZIQvpApQAhNKbjVwAAAA==","rowCount":1,"columnCount":2}}
>>>
4

So, for your first workaround:

dsTest1 = system.db.runQuery('SELECT * FROM someTable')
print system.util.jsonEncode({'test' : dsTest1.underlyingDataset}, 4)

Second workaround (since addColumn iterates though the dataset already, we may as well do it without the extra data):

tag = system.tag.readBlocking(['[provider]path/to/tag'])[0].value
newDataset = system.dataset.toDataSet(list(tag.getColumnNames()), 
                                      [[tag.getValueAt(row, col) 
                                        for col in xrange(tag.getColumnCount())] 
                                        for row in xrange(tag.getRowCount())
                                      ]
                                     )
2 Likes