Passing column name as a variable to a named query

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.

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

1 Like

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}
1 Like

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

Ensure what @bpreston said regarding param1 being a QueryString
image

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

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.

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?

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

Yes, I can.

I believe you need to change:

{param1} VALUES :param2

to

({param1} VALUES (:param2)

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

({param1}) VALUES (:param2)

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

Try correcting this line:

InductColumn = {Tag_Name}

to:

InductColumn = Tag_Name

Done. Still getting:

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

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.

1 Like

+1

1 Like

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?

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

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:

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’. :wink:

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?

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.