I have about 10 different buttons that reflect a department for each one. I am wanting to be able to select a button and it fire a sql query to retrieve information from a table that have the rows that equate to that department. So if I select the safety department, my designated numeric text field updates with the average safety number of all the rows that have the safety area in the column. here is what I have so far:
//event.source.parent.getComponent(‘Saverage’).intValue = system.db.runQuery("SELECT AVG(CAST(Safety AS int)) AS SafetyNumber FROM SQDCME.dbo.SQDCME where Area = ‘Stacker1’ ")//
The problem is that I get an error back saying:
File “event:actionPerformed”, line 23, in
AttributeError: ‘NoneType’ object has no attribute ‘intValue’
Basically I am trying to get the average safety number from the Safety column that has all the rows with the Stacker1 area in it.
This lookup is failing - getComponent('Saverage')
is returning None, Python's stand-in for null
, which doesn't have an intValue
property, and thus is throwing the error you're seeing. Double check the component name, whether it's inside any groups/containers, etc. Use the property selector panel on the right hand side of the script editor to confirm the path to the component you want.
Yeah, so I went back and checked the scrip editor and it was in a container:
event.source.parent.getComponent(‘Container’).getComponent(‘Saverage’).intValue
and after I ran that I got:
File “event:actionPerformed”, line 23, in
TypeError: can’t convert Dataset [1R ? 1C] to int
Okay, so now the problem is the other half of that line
system.db.runQuery
returns a dataset. If you know your query will only return one result, you can just switch your call from runQuery
to runScalarQuery
- it'll automatically pull out the first row/column of the result.
Because the Query is returning a dataset which contains a column “SafetyNumber” and a row (the value). You can either directly assign a value form the dataset, or you can change the query to return a scalar value.
I think you could do
event.source.parent.getComponent(‘Container’).getComponent(‘Saverage’).intValue = system.db.runQuery("SELECT AVG(CAST(Safety AS int)) AS SafetyNumber FROM SQDCME.dbo.SQDCME where Area = ‘Stacker1’ ").getValueAt(0,0)
Well I am trying to get the safety number average of all the rows that have the Stacker1 value in the Area Column.
We understand that, but the container you’re placing the value into only cares about the value - not the name. Switching to runScalarQuery will return just the simple value of the query, instead of the result set, which is structured as - in your case - a dataset with a named column and a value row.
1 Like
Right, but your query is doing that aggregation (on the DB side). When it comes into Ignition, it will always be one value. But, the runQuery
function returns a dataset in all cases - whether you’re returning 100 rows x 5 columns or 1 row x 1 column.
1 Like