I am having a hard time building a query that only runs at certain times to get the closest value at 06:00 and 18:00. Any ideas?
Use a Gateway Scheduled event to run the query at the times that you want, and then store the value in a tag. This will insure that the query is run even if no client sessions are accessing the view.
Then you can bind your gauge to that tag.
Using a named query correct?
Named queries are best used in most cases. Note, if your query returns a single value set it as returning a scalar value for easier use.
When I do plant schedule stuff, I like to have a schedule start tag that holds a specific time when the current shift starts. I use that datetime value in a query tag for shift total numbers. This way I don't have scripts to change when I need to alter shift starting times, just change the shift start datetime and the query adjusts accordingly.
Unfortunately, I am still fairly new to ignition. I have written the named query and have it working as expected. I have 2 issues
system.db.runNamedQuery( "folderName/myNamedQuery" )
First, bookmark this page, it will help you in the future.
System Functions - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)
You run the named query which accepts a python dictionary as a list of supplied parameters (not required). Unless it is a scalar query, which returns a singular value, it returns an Ignition dataset.
system.db.runNamedQuery("queryNameHere",{"someParam":someValue})
Ignition datasets cannot be iterated over unless you convert it to a python dataset like so
# get a value from Ignition dataset
results = system.db.runNamedQuery( "folderName/myNamedQuery" )
someValue = results.getValueAt(0,1)
# Or convert to pydataset and iterate
for row in system.dataset.toPyDataSet(results):
someValue = row['someValue']
for value in row:
print value
Once you have the value you want to write to the tag you supply the tag paths and values as lists.
someValue = 2
tagPaths = ['[tagProvider]Folder/tagName']
values = [someValue]
# Takes lists of paths and values so you can write to multiple tags in one call
system.tag.writeBlocking(tagPaths,values)
In the case that it is a Scalar Query, which it is, how would I create a tag based off of that?
Very simple, the scalar query returns just a value not a dataset, so
# Query returns a scalar value so no indexing is needed
someValue = system.db.runNamedQuery("queryNameHere",{"someParam":someValue})
tagPaths = ['[tagProvider]Folder/tagName']
values = [someValue]
# Takes lists of paths and values so you can write to multiple tags in one call
system.tag.writeBlocking(tagPaths,values)
Can you explane what goes in someParam? I don't have any current parameters, since I am using a specific "blender id" (103),
select top 1 usage_1 from v_Blender_Dta where blender_id= '103' order by time desc
Your Named query, currently has a parameter. If you want to parameterize this query, so that it could be used for multiple blender id's then you need to change the query syntax to use the paramter. In a Named Query, you would use syntax like this :parmaterName to use the parameter. Most of the time paramters will be used in a where clause. For your use case your query would look like this:
SELECT TOP 1 usage_1 FROM v_Blender_Dta WHERE blender_id = :103 ORDER BY time desc
Then in the script you would supply the parameter in the parameters dictionary as @dkhayes117 showed.
someValue = system.db.runNamedQuery("103target",{"103":103})
I would probably recommend changing the name of your parameter so it's a bit more readable, something like blenderId would be better than 103.
Also, I would recommend that you name your queries in a way that tells you what they do. So instead of "103target", perhaps "getBlenderTargetById" is a better name.
Definitely this. Change the parameter name to blenderID and now you have one query that can be used for any blender id you want
result = system.db.runNamedQuery("getBlenderTargetById",{"blenderID":103})
Firstly, thanks to both of you for all of the patience and help!
Here is my updated script, I have changed the NamedQuery to blenderTargetUpdate and the parameter to blenderID (103 is the blender that I am checking here)
test123 is my tag that I am testing the query with.
def onScheduledEvent():
"""
A scheduled script that will execute periodically on the gateway.
"""
# Query returns a scalar value so no indexing is needed
someValue = system.db.runNamedQuery("103target",{blenderID:103})
tagpaths = '[default]BlenderTracing/test123'
values = [someValue]
# Takes lists of paths and values so you can write to multiple tags in one call
system.tag.writeBlocking(tagPaths,values)
I am getting an error:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 2, in onScheduledEvent UnboundLocalError: local variable 'someValue' referenced before assignment
at org.python.core.Py.UnboundLocalError(Py.java:266)
at org.python.core.PyFrame.getlocal(PyFrame.java:240)
at org.python.pycode._pyx67115.onScheduledEvent$1(:6)
at org.python.pycode._pyx67115.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function___call__(PyFunction.java:474)
at org.python.core.PyFunction.__call__(PyFunction.java:469)
at org.python.core.PyFunction.__call__(PyFunction.java:464)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:831)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:813)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:806)
at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:994)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:871)
at com.inductiveautomation.ignition.common.script.ScheduledScriptManager$ScheduledScriptTask.run(ScheduledScriptManager.java:173)
at com.inductiveautomation.ignition.common.util.ExecutionQueue$PollAndExecute.run(ExecutionQueue.java:239)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: UnboundLocalError: local variable 'someValue' referenced before assignment
... 19 common frames omitted
A few things.
When calling system.db.runNamedQuery() from the Gateway Scope (Which you are here), you need to provide the project name that the Named Query resides in. Also, you didn't update the path to the named query to use the new name.
someValue = system.db.runNamedQuery("blenderTargetUpdate", {"blenderID":103})
Also, you may wan't to be careful using the word Update in a query name, as that could be seen as meaning it was an UPDATE query, rather than used to update the value of a tag.
It looks like your problem is in the named query dictionary. Curly braces in python means the collection is a dictionary, in other words, key-value pairs.
# below is wrong as blenderID is not in quotes
system.db.runNamedQuery("103target",{blenderID:103})
#do this instead, "blenderID" is the key, 103 is the value
system.db.runNamedQuery("103target",{"blenderID":103})
Note you do not have to call it someValue. Name your variable so you understand what it is when you come back 6 months from now to fix a problem 
Thank you again,
So close! Now I'm getting that "tagPaths" on line 7 is not defined:
Code:
||# Query returns a scalar value so no indexing is needed|
|---|---|
||someValue = system.db.runNamedQuery(blenderTargetRefresh,{blenderID:103})|
||tagPaths = '[default]BlenderTracing/test123'|
||values = [someValue]|
||# Takes lists of paths and values so you can write to multiple tags in one call|
||system.tag.writeBlocking(tagPaths,values)|
Error:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 7, in onScheduledEvent NameError: global name 'tagPaths' is not defined
at org.python.core.Py.NameError(Py.java:261)
at org.python.core.PyFrame.getglobal(PyFrame.java:265)
at org.python.pycode._pyx67383.onScheduledEvent$1(:7)
at org.python.pycode._pyx67383.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function___call__(PyFunction.java:474)
at org.python.core.PyFunction.__call__(PyFunction.java:469)
at org.python.core.PyFunction.__call__(PyFunction.java:464)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:831)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:813)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:806)
at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:994)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:871)
at com.inductiveautomation.ignition.common.script.ScheduledScriptManager$ScheduledScriptTask.run(ScheduledScriptManager.java:173)
at com.inductiveautomation.ignition.common.util.ExecutionQueue$PollAndExecute.run(ExecutionQueue.java:239)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: NameError: global name 'tagPaths' is not defined
... 19 common frames omitted
Make sure that you're using the same indentations throughout, and also you're missing some corrections.
Your code should be something like this:
# Query returns a scalar value so no indexing is needed|
someValue = system.db.runNamedQuery('yourProjectName',blenderTargetRefresh,{'blenderID':103})
tagPaths = ['[default]BlenderTracing/test123']
values = [someValue]
# Takes lists of paths and values so you can write to multiple tags in one call
system.tag.writeBlocking(tagPaths,values)
See previous posts for the reasons why.
It's working now, thanks so much to the both of you!