# Passing column name as a variable to a named query

**URL:** <https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692>\
**Category:** Ignition\
**Created:** [September 15, 2020, 1:47pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692 "2020-09-15T13:47:20Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 1:47pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/1 "2020-09-15T13:47:20Z")

</div>

My goal is to store the DateTime when an event occurs. I have a conveyor system that has several Incoming conveyor stubs where product may be loaded. I have a MYSQL database that consists of one table and several columns. Each column name corresponds to one of the incoming sensors, DD121, DD122, and DD123 are the column names and also the sensor names. So, when sensor DD122 goes high, it triggers a Gateway Tag Change event. The Tag Change Event script is:  
‘’’  
if newValue.value:

# Get the name of the tag that called this script

```
Tag_Name = event.getTagPath().getItemName()

```

# Set the Parameters to pass to the Named Query

```
InductColumn = {Tag_Name}
t1 = system.date.now()
   currentDateTime = system.date.format(t1,'yyyy-MM-dd HH:mm:ss')

```

# Put the Parameters in a python dictonary

```
params = {"param1" : "InductColumn","param2" : "currentDateTime"}

```

# Call the query

```
system.db.runNamedQuery("dockDoorUpdate", params)

```

‘’’  
the Named Query is:  
‘’’  
INSERT INTO amazon\_stats.dock\_door\_induct\_counts :param1 VALUES :param2;  
‘’’  
When trying to test the named query, param1 is set to (DD122). param2 is set to 2020-09-14 09:24:59.  
When I execute the query, I get the following error:

GatewayException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’(DD122)’ VALUES ‘2020-09-14 09:24:59.0’’ at line 1

If I test the script in the script console with static values that are ( I think) represented with param1 and param2

I am using MySQL. Please be gentle, big-time newbie with Ignition, Python, and SQL in general.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [September 15, 2020, 1:53pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/2 "2020-09-15T13:53:21Z")

</div>

For your param1 Type in your named query, is it set to value or QueryString?

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 1:53pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/3 "2020-09-15T13:53:55Z")

</div>

> [@twstevic](#):
>
> params = {"param1" : "InductColumn","param2" : "currentDateTime"}

You are passing literal strings here for **"InductColumn"** should be **InductColumn** (no quotes) and same with currentDateTime.

Try adjusting those in your scripts and test again.  
Adjusted code:

```auto
params = {"param1" : InductColumn, "param2" : currentDateTime}

```

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 2:14pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/4 "2020-09-15T14:14:18Z")

</div>

More info here based on your question regarding dynamic column names.

Ensure what @bpreston said regarding param1 being a **QueryString**  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/7/0737c3926b4803ee4c48a8715d9009067988426c.png)

Then update your final query like this:

```auto
INSERT INTO amazon_stats.dock_door_induct_counts ({param1}) VALUES (:param2)

```

Manual regarding Named Query parameters:  
[https://docs.inductiveautomation.com/display/DOC79/Named+Query+Parameters#NamedQueryParameters-QueryString](https://docs.inductiveautomation.com/display/DOC79/Named+Query+Parameters#NamedQueryParameters-QueryString)

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 2:14pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/5 "2020-09-15T14:14:27Z")

</div>

param1 is supposed to be the name of the column in the ‘dock\_door\_induct\_counts’ table.

params = {“param1” : InductColumn, “param2” : currentDateTime} give me a ‘param1 is not defined’ error.

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 2:23pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/6 "2020-09-15T14:23:15Z")

</div>

code\_skin: I did that, but the script still does not complete giving me a ‘param1 not defined’ error.  
‘’’  
'if newValue.value:  
#Get the name of the tag that called this script  
Tag\_Name = event.getTagPath().getItemName()

'# Set the Parameters to pass to the Named Query  
InductColumn = {Tag\_Name}  
t1 = system.date.now()  
currentDateTime = system.date.format(t1,‘yyyy-MM-dd HH:mm:ss’)

'# Put the Parameters in a python dictonary  
params = {“param1”:InductColumn, “param2”:currentDateTime}

'# Set the #transactions to be serialized  
'# TxId = s#ystem.db.beginTransaction("",8)

'# Debug  
logger = system.util.getLogger(‘myLogger’)  
logger.info (str(param1))  
'# print str(params)

‘# Call the query  
system.db.runNamedQuery(“dockDoorUpdate”, params)  
‘’’

I thought the params = {“param1”:InductColumn, “param2”:currentDateTime} statment defined them on the fly?

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 2:25pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/7 "2020-09-15T14:25:05Z")

</div>

Can you provide a screenshot of your Named Query Authoring tab please?

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 2:27pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/8 "2020-09-15T14:27:53Z")

</div>

Yes, I can.

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

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [September 15, 2020, 2:31pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/9 "2020-09-15T14:31:16Z")

</div>

I believe you need to change:

```auto
{param1} VALUES :param2

```

to

```auto
({param1} VALUES (:param2)

```

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 2:33pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/10 "2020-09-15T14:33:45Z")

</div>

> [@bpreston](#):
>
> ({param1} VALUES (:param2)

You'll need to add parenthesis around both columns and values.

```auto
({param1}) VALUES (:param2)

```

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 2:36pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/11 "2020-09-15T14:36:36Z")

</div>

‘’’  
INSERT INTO dock\_door\_induct\_counts ({param1}) VALUES (:param2);  
‘’’  
Still gives me the not defined error:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 18, in NameError: name ‘param1’ is not defined

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 2:43pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/12 "2020-09-15T14:43:29Z")

</div>

Try correcting this line:

```auto
InductColumn = {Tag_Name}

```

to:

```auto
InductColumn = Tag_Name

```

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 2:52pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/13 "2020-09-15T14:52:13Z")

</div>

Done. Still getting:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 19, in NameError: name ‘param1’ is not defined

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 2:56pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/14 "2020-09-15T14:56:41Z")

</div>

I think what’s happening is you’re calling runNamedQuery from the gateway scope. Therefore you need to include the project name.

```auto
# Call the query
system.db.runNamedQuery(“dockDoorUpdate”, params)

# should be
projectName = system.util.getProjectName()
system.db.runNamedQuery(projectName , “dockDoorUpdate”, params)

```

If this fails I suggest contacting support and having them look deeper into your setup to assist you further.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 15, 2020, 3:24pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/15 "2020-09-15T15:24:46Z")

</div>

> [@twstevic](#):
>
> ...it triggers a Gateway Tag Change event.

> [@code\_skin](#):
>
> Therefore you need to include the project name.

+1

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 6:07pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/16 "2020-09-15T18:07:41Z")

</div>

Okay, I changed the Gateway Event Script to:

system.db.runNamedQuery(A\_Stats, “dockDoorUpdate”, params)|

I still get:  
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 19, in NameError: name ‘param1’ is not defined

Does  
params = {“param1”:InductColumn, “param2”:currentDateTime}

Not define param1?

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 15, 2020, 6:15pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/17 "2020-09-15T18:15:23Z")

</div>

> [@twstevic](#):
>
> Not define param1?

It's definitely defined so the issue is elsewhere. Post your entire script and provide any screenshots of configurations (named query, tag change setup, etc).

Wrap your script with ```

```auto
# like this

```

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 6:21pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/18 "2020-09-15T18:21:13Z")

</div>

Gateway tag change script:

‘’’  
if newValue.value:  
#Get the name of the tag that called this script  
Tag\_Name = event.getTagPath().getItemName()  
'# Tag\_Name = tag.name

'# Set the Parameters to pass to the Named Query  
InductColumn = Tag\_Name  
t1 = system.date.now()  
currentDateTime = system.date.format(t1,‘yyyy-MM-dd HH:mm:ss’)

'# Put the Parameters in a python dictonary  
params = {“param1”:InductColumn, “param2”:currentDateTime}

'# Set the #transactions to be serialized  
'# TxId = s#ystem.db.beginTransaction("",8)

'# Debug  
logger = system.util.getLogger(‘myLogger’)  
logger.info (str(param1))  
'# print str(params)

‘# Call the query  
system.db.runNamedQuery(A\_Stats, “dockDoorUpdate”, params)  
‘’’’  
Named Query:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/f/efbcc6b9ca36c3f8fa3a68227c690ebfaa9a1747.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 15, 2020, 6:30pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/19 "2020-09-15T18:30:38Z")

</div>

Try using backticks instead of apostrophes to enclose your code. On US keyboards, it’s the key in the upper left corner by the ‘1’. 😉

In the meantime:

- what does the logger say?
- in the named query, ensure there are no leading or trailing spaces for the parameter names.
- does the named query work when you use the ‘Testing’ tab?

---

<div class="post-metadata">

**Author:** ![twstevic](https://avatars.discourse-cdn.com/v4/letter/t/a183cd/32.png) [@twstevic](https://forum.inductiveautomation.com/u/twstevic)\
**Post date:** [September 15, 2020, 8:03pm UTC](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692/20 "2020-09-15T20:03:22Z")

</div>

The logger

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 19, in NameError: name ‘param1’ is not defined

at org.python.core.Py.NameError(Py.java:290)

at org.python.core.PyFrame.getname(PyFrame.java:257)

at org.python.pycode.\_pyx13.f$0(:23)

at org.python.pycode.\_pyx13.call\_function()

at org.python.core.PyTableCode.call(PyTableCode.java:171)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1614)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:779)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:678)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:727)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:659)

at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:241)

at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:193)

at com.inductiveautomation.ignition.common.util.SerialExecutionQueue$PollAndExecute.run(SerialExecutionQueue.java:102)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

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: Traceback (most recent call last): File “”, line 19, in NameError: name ‘param1’ is not defined

… 19 common frames omitted

The testing works if I put static values in place of the variables. It does not work using the variables though. using the variables, I get the same ‘not defined’ error.

[Next page](https://forum.inductiveautomation.com/t/passing-column-name-as-a-variable-to-a-named-query/38692.md?page=2)