runPrepUpdate puzzle

This query works in MySQL Workbench.

INSERT INTO manf_dashboard.production_log 
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0)

It fails in Designer Script Console

query = """INSERT INTO manf_dashboard.production_log 
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0)"""
system.db.runPrepUpdate(query, [])
Java Traceback
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 63, in <module>
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0), [], , , false, false)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO manf_dashboard.production_log 
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0), [], , , false, false)


	at org.python.core.Py.JavaError(Py.java:547)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:552)

	at org.python.core.PyObject.__call__(PyObject.java:477)

	at org.python.core.PyObject.__call__(PyObject.java:481)

	at org.python.pycode._pyx70.f$0(<input>:63)

	at org.python.pycode._pyx70.call_function(<input>)

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

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

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

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:626)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:614)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

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

	at java.desktop/javax.swing.SwingWorker.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: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO manf_dashboard.production_log 
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0), [], , , false, false)

	... 25 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Cannot run this function when in read-only mode.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:384)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:283)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:288)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 23 more

Traceback (most recent call last):
  File "<input>", line 63, in <module>
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0), [], , , false, false)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO manf_dashboard.production_log 
			(machineId, event, cycleCount, goodCount, sku, runstatus) 
			VALUES ('Delete', 'test', 44, 33, 'Dummy', 0), [], , , false, false)

I've checked the database connection using the select query below and it works.

system.db.runQuery("SELECT * FROM manf_dashboard.production_log LIMIT 10")

Can anyone suggest some further checks for me to narrow down the cause of the problem?

Many thanks.

Set your designer to read/write mode. (Yes, the same setting for tags impacts legacy DB script functions.)

2 Likes