[SOLVED] Parse a table returned from a named query

What is the syntax to call a named query and parse the results in Ignition scripting?

tbl = system.db.runNamedQuery("projectName", "somePath/selectStuff", {})

if tbl.getRowCount() > 0:
    keyID = tbl[0][0]
    someText= tbl[0][1]

My named query succeeds in testing but when I implement the above in a script I get the following error:

Error type: <type 'exceptions.TypeError'> Error message : 
'com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset' object is 
unsubscriptable

Any thoughts on how to fix this issue?

If you want to access individual elements like it’s a nested list, you need to change the Dataset returned by runNamedQuery into a PyDataset, via the appropriately named system.dataset.toPyDataset function. See the Datasets page for more background on the difference between the two:
https://docs.inductiveautomation.com/display/DOC80/Datasets

So a named query returns a regular dataset? Am I understanding that correctly?

What does an in-line query return?

	sql = 'SELECT TOP(1) keyID, someText FROM someTable ORDER BY someText ASC'
	args = []
	tbl = system.db.runPrepQuery(sql, args, db)
	return tbl

I ask because this is the code I was converting from a script definition to a named query.

system.db.runPrepQuery returns a PyDataset

So the named query executes the IN-LINE code and returns a pydataset but then the named query is converting it to a regular dataset? That’s mildly annoying to contemplate but thanks for the info.

Anyways I was able to fix my problem by converting the regular dataset back into a pydataset using the line:

pyDataSet = system.dataset.toPyDataSet(tbl)

I’ve had good success with:

for row in range( tbl.rowCount ):
	keyId = tbl.getValueAt( row, 0 )  				# better to use column names
	someText = tbl.getValueAt( row, 1 )  			# 2nd column
	serialNum = tbl.getValueAt( row, 'SERIAL_NBR' )	# sample column name
	# do your workload / logic here ...

That works with the dataset returned by the Named Query without duplicating all the data into another datatype.
You can use system.dataset.getColumnHeaders( tbl ) to get a List with the exact spelling and capitalization of the columns returned by the Named Query.

1 Like

the toPyDataSet function solved my problem reading from a dataset returned from a named Query. Thank you very much for the post.
#----------------------------------------------------------------------------------------------------------------
Preformatted text
parameters = {“LOT_NUMBER”:(self.session.custom.strLotNumber)}
ds = system.db.runNamedQuery(“nq_spGetBrand”, parameters)
pyDataSet = system.dataset.toPyDataSet(ds)
if pyDataSet.rowCount > 0:
strBrand = str(pyDataSet[0][‘BRAND’])
Preformatted text

#----------------------------------------------------------------------------------------------------------------