[SOLVED] Parse a table returned from a named query

Ignition
1

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?

2

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

3

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.

4

system.db.runPrepQuery returns a PyDataset

5

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)
1 Like
6

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
7

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

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

8

I want to convert a RunNamedQuery to a dataset and send data to export too CSV

9

Just configure the Named Query to return a dataset

10

How? Is the part I don’t understand

11

When you configure a query binding there is an option to set the return format. By default this is set to auto.

If you wanting to do this in script then the type returned by system.db.runNamedQuery() is determined by the Query Type of the Named Query. If the Named Queries query type is set to query then the function will return a dataset.

12

I have this and this save a document with two sheets for the both tables, I want to add another sheet for the runNamedQuery :


namedQuery = system.db.runNamedQuery("EstadisticasPedidos/FiltroLineasPedido3",{})

table =event.source.parent.getComponent("tblHistorialPedidos")
table2=event.source.parent.getComponent("tblHistorialPedidosDetalles")

from com.inductiveautomation.ignition.common import BasicDataset
dataset= BasicDataset(table.data)
dataset2= BasicDataset(table2.data)

dataset.setColumnNames(["F.Pedido","Confirm G","No. Pedido","No. Hoja","No. Ruta","F.Arranque","F. Activo","F. Finalizado","ID Ruta","T. LĂ­neas","Cod. Caja","Estado Pedido"])
dataset2.setColumnNames(["No. Pedido","Nombre Producto","No. Sector","No. Canal","CĂłdigo Producto","Cantidad","Surtido","Detalle Surtido"])
sp= system.dataset.dataSetToExcel(1,[dataset,dataset2])
filePath= system.file.saveFile(u'Archive.xls')

if filePath != None:
	system.file.writeFile(filePath,sp)
	system.net.openURL("file:///"+filePath.replace('\\','/'))
13

This code won’t work yet but should get you pretty close. The named query returns a basic dataset so no need to convert it. Then add the column names for that dataset. Then pass it to the variable sp.

table =event.source.parent.getComponent("tblHistorialPedidos")
table2=event.source.parent.getComponent("tblHistorialPedidosDetalles")

from com.inductiveautomation.ignition.common import BasicDataset
dataset= BasicDataset(table.data)
dataset2= BasicDataset(table2.data)

#Named query returns a basic dataset
dataset3 = system.db.runNamedQuery("EstadisticasPedidos/FiltroLineasPedido3",{})

dataset.setColumnNames(["F.Pedido","Confirm G","No. Pedido","No. Hoja","No. Ruta","F.Arranque","F. Activo","F. Finalizado","ID Ruta","T. LĂ­neas","Cod. Caja","Estado Pedido"])
dataset2.setColumnNames(["No. Pedido","Nombre Producto","No. Sector","No. Canal","CĂłdigo Producto","Cantidad","Surtido","Detalle Surtido"])

#Insert correct columns returned from dataset3
dataset3.setColumnNames(["Column1", "Column2", "Column3", etc])

sp= system.dataset.dataSetToExcel(1,[dataset,dataset2, dataset3])
filePath= system.file.saveFile(u'Archive.xls')

if filePath != None:
	system.file.writeFile(filePath,sp)
	system.net.openURL("file:///"+filePath.replace('\\','/'))
1 Like