Good day all. I am trying to retrieve data from table using:
namedQuery = system.db.runNamedQuery('WasteBarrelDesc')
This table contains a TID column and a BarrelNum column. I want the know the BarrelNum based on the TID.
How can I achieve this?
Show us your Named Query. Post it as text.
On re-reading it appears that you are trying to create a named query in a script. That's not how it works. You create the named query in the Project Browser section labelled Named Query. You then use it in your script in the manner of,
results = system.db.runNamedQuery('WasteBarrelDesc')
https://docs.inductiveautomation.com/display/DOC81/Named+Queries
https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery
2 Likes
my named query is this:
Select *
From WasteBarrelDesc
right now the contents are just the one row. It will be several more after this trail.
Im wanting grab the BarrelNum, in reference to the TID.
SELECT BarrelType
FROM WasteBarrelDesc
WHERE TID = :tidNum
:tidNum will be a parameter of the named query.
It's all explained in the Named Queries document link above and there's probably a video on Inductive University.
The manual's runNamedQuery page shows how to pass the parameter in the system.db.runNamedQuery() function.
1 Like
If you're binding a perspective table to a query, you probably don't need to use runNamedQuery. Just use a query binding.
Don't use scripting unless necessary. Ignition has a lot a built-in ways of doing things efficiently, use them before resorting to custom methods.
2 Likes
Sorry for the late reply, Ive been out of town.
I am using the namedQuery in python. I have come up with something like this:
# START Getting Barrel Number based on TID
barrelNum_namedQuery = system.db.runNamedQuery('WasteBarrelDesc')
dataset = barrelNum_namedQuery
for i in range(dataset.getRowCount()):
if dataset.getValueAt(i, 'TID') == TID:
barrelNum = dataset.getValueAt(i, 'BarrelNum')
break
else:
print("No matching row found.")
# END Getting Barrel Number based on TID
I am making it execute via an "on change" script of a tag. Is this an OK way of doing it?
That is a dangerous place to run database operations. See this and its linked discussions:
If you must, you will need to change the call syntax to include the NQ project name to comply with that function's gateway scope requirement.
Can you show me an example, Im not sure what you mean on this. Thank you
Every system function has documentation describing the differences in usage in various calling scopes, if any.
Let me repeat an earlier link:
https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery
That's really not how you want to be doing this.
Where do you want to display that barrel num ?
I'm going to suppose it's on a label, in a view.
Create a new named query.
Set it to be a SCALAR query.
Add a parameter of type string, call it TID.
Write the query so it looks like this
SELECT BarrelNum
FROM your_barrel_table
WHERE TID = :TID
In the view where you want to display it, put a label.
Put a query binding on that label, and use your new named query.
Pass the TID to look for as the parameter to the query.
If you REALLY want to use your already existing named query (but don't), add a custom property on the label, put a query binding on that property, use your named query.
Now put a structure binding on the label, and add the custom property and the TID source.
Add an expression transform and use lookup to retrieve your barrel num.
No scripting needed anywhere, no change scripts, no touching tags.
1 Like