# Gauge minValue & maxValue SQL binding

**URL:** <https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294>\
**Category:** Ignition\
**Created:** [December 6, 2022, 3:05pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294 "2022-12-06T15:05:28Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 3:05pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/1 "2022-12-06T15:05:28Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/7/371291bd366756b089fbe253aaaabe262c2d8b3e.png)  
Good morning, I am working on a live dashboard that shows shift progress in our production facility. I want to set a SQL query value from a specified time (06:00:00 and another for 18:00:00) for minValue and then that same original query value + shift goal. The live query will be set as the value for the employees to track during the shift.

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?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 6, 2022, 3:51pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/2 "2022-12-06T15:51:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 6:29pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/3 "2022-12-06T18:29:48Z")

</div>

Using a named query correct?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 6, 2022, 6:37pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/4 "2022-12-06T18:37:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 6:53pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/5 "2022-12-06T18:53:05Z")

</div>

Unfortunately, I am still fairly new to ignition. I have written the named query and have it working as expected. I have 2 issues

1. I am not sure what the gateway script should be to run the query- Something along the lines of `system.db.runNamedQuery(` `"folderName/myNamedQuery"` `)`
2. I do not know how to create a tag based on this value.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 6, 2022, 7:09pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/6 "2022-12-06T19:09:58Z")

</div>

First, bookmark this page, it will help you in the future.  
[System Functions - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/System+Functions)

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.

```python
system.db.runNamedQuery("queryNameHere",{"someParam":someValue})

```

Ignition datasets cannot be iterated over unless you convert it to a python dataset like so

```python
# 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.

```python
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)

```

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 7:14pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/7 "2022-12-06T19:14:33Z")

</div>

In the case that it is a Scalar Query, which it is, how would I create a tag based off of that?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 6, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/8 "2022-12-06T19:32:43Z")

</div>

Very simple, the scalar query returns just a value not a dataset, so

```python
# 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)

```

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 7:37pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/9 "2022-12-06T19:37:35Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/1/c1e6deead0f416f47171c909e5f4e1cf1dc90356.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 6, 2022, 7:50pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/10 "2022-12-06T19:50:04Z")

</div>

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:

```auto
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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 6, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/11 "2022-12-06T19:57:54Z")

</div>

> [@lrose](#):
>
> 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

```python
result = system.db.runNamedQuery("getBlenderTargetById",{"blenderID":103})

```

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 8:11pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/12 "2022-12-06T20:11:15Z")

</div>

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.

```auto

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:

```auto

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

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 6, 2022, 8:16pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/13 "2022-12-06T20:16:08Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 6, 2022, 8:16pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/14 "2022-12-06T20:16:09Z")

</div>

> [@Tyler\_Esters1](#):
>
> someValue = system.db.runNamedQuery("103target",{blenderID:103})  
> tagpaths = '[default]BlenderTracing/test123'  
> values = [someValue]

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.

```python
# 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 🙂

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 9:18pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/15 "2022-12-06T21:18:47Z")

</div>

Thank you again,

So close! Now I'm getting that "tagPaths" on line 7 is not defined:

Code:

```auto
||# 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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 6, 2022, 9:23pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/16 "2022-12-06T21:23:04Z")

</div>

Make sure that you're using the same indentations throughout, and also you're missing some corrections.

Your code should be something like this:

```auto
# 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.

---

<div class="post-metadata">

**Author:** ![Tyler\_Esters1](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@Tyler\_Esters1](https://forum.inductiveautomation.com/u/Tyler_Esters1)\
**Post date:** [December 6, 2022, 9:25pm UTC](https://forum.inductiveautomation.com/t/gauge-minvalue-maxvalue-sql-binding/67294/17 "2022-12-06T21:25:55Z")

</div>

It's working now, thanks so much to the both of you!