Cryptic error trying to run system.db.runPrepQuery in a Gateway Tag Change Script

Hello all,

I'm trying to push tag values to a MySQL database using a gateway tag change script. The script looks like this.

Date = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Date")
Shift = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Shift")
system.db.runPrepUpdate("INSERT INTO meltshopwatercheck (Date, Shift) VALUES (?, ?)", [Date, Shift])

The tag which changes to trigger the script is being toggled through a button on an Edge Client, and the tags that I want to push to the database are from the same Edge client but the tag change script is being run on a gateway with a Non-Edge instance of Ignition. When I try to trigger the script and look at the error on the ignition gateway, this is what is returned.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 5, in at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO meltshopwatercheck (Date, Shift) VALUES (?, ?), [[Wed May 23 15:30:18 EDT 2018, Good, Tue May 22 10:19:43 EDT 2018], [First Shift, Good, Tue May 22 10:19:43 EDT 2018]], , , false, false)
at org.python.core.Py.JavaError(Py.java:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:429)
at org.python.core.PyObject.__call__(PyObject.java:404)
at org.python.core.PyObject.__call__(PyObject.java:408)
at org.python.pycode._pyx11.f$0(:11)
at org.python.pycode._pyx11.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:634)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:601)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:192)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:135)
at com.inductiveautomation.ignition.common.util.SerialExecutionQueue$PollAndExecute.run(SerialExecutionQueue.java:96)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 5, in at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO meltshopwatercheck (Date, Shift) VALUES (?, ?), [[Wed May 23 15:30:18 EDT 2018, Good, Tue May 22 10:19:43 EDT 2018], [First Shift, Good, Tue May 22 10:19:43 EDT 2018]], , , false, false)
... 26 common frames omitted
Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO meltshopwatercheck (Date, Shift) VALUES (?, ?), [[Wed May 23 15:30:18 EDT 2018, Good, Tue May 22 10:19:43 EDT 2018], [First Shift, Good, Tue May 22 10:19:43 EDT 2018]], , , false, false)
... 25 common frames omitted
Caused by: java.lang.IllegalArgumentException: Cannot find database connection - name cannot be null.
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.findForName(DatasourceManagerImpl.java:525)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:137)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:88)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:73)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:127)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)
... 23 common frames omitted

Can anyone with more experience deciphering these error messages than me, point me in the right direction to solve this issue? I'm aware that Edge does not have the ability to connect to databases, but running through a standard Ignition Gateway should solve that right?

Tag change scripts run on the gateway - meaning they don’t know about “default” databases, and therefore don’t know which database connection to use (even if you only have one!). Add the database name as a string in the third parameter to the system.db.runPrepUpdate() call and the code should work fine.

2 Likes

It also looks like you are grabbing the tag objects, not the tag values.

Try:

Date = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Date").value
Shift = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Shift").value
2 Likes

I revised the code to:

Date = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Date").value
Shift = system.tag.read("[Ignition-JLV_Rockwell_edge]Form/Shift").value
system.db.runPrepUpdate("INSERT INTO meltshopwatercheck (Date, Shift) VALUES (?, ?)", [Date, Shift],"inspections")

and it worked perfectly! Thanks!