Passing column name as a variable to a named query

twstevic · September 15, 2020, 1:47pm

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.

bpreston · September 15, 2020, 1:53pm

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

code_skin · September 15, 2020, 1:53pm

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:

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

code_skin · September 15, 2020, 2:14pm

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

Ensure what @bpreston said regarding param1 being a QueryString

Then update your final query like this:

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

twstevic · September 15, 2020, 2:14pm

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.

twstevic · September 15, 2020, 2:23pm

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?

code_skin · September 15, 2020, 2:25pm

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

twstevic · September 15, 2020, 2:27pm

Yes, I can.

bpreston · September 15, 2020, 2:31pm

I believe you need to change:

{param1} VALUES :param2

to

({param1} VALUES (:param2)

code_skin · September 15, 2020, 2:33pm

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

({param1}) VALUES (:param2)

twstevic · September 15, 2020, 2:36pm

‘’’
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

code_skin · September 15, 2020, 2:43pm

Try correcting this line:

InductColumn = {Tag_Name}

to:

InductColumn = Tag_Name

twstevic · September 15, 2020, 2:52pm

Done. Still getting:

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

code_skin · September 15, 2020, 2:56pm

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

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

JordanCClark · September 15, 2020, 3:24pm

+1

twstevic · September 15, 2020, 6:07pm

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?

code_skin · September 15, 2020, 6:15pm

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

# like this

twstevic · September 15, 2020, 6:21pm

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:

JordanCClark · September 15, 2020, 6:30pm

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?

twstevic · September 15, 2020, 8:03pm

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.