Error executing system.db.runUpdateQuery - spot the obvious error?

This query works in MySQL Workbench.

UPDATE db_os_manf_dashboard.production_log 
SET machineId = 'AC03', event = 'live', cycleCount = 111, goodCount = 102, sku = '123 45' 
WHERE (machineId = 'AC03') AND (event = 'live')  AND id > 0;-- 

(The id > 0 is used to force MySQL safe update mode.)

Converting it to a runUpdateQuery results in the following error (text wrapped for legibility):

java.lang.Exception: java.lang.Exception: Error executing
 system.db.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log 
   SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? 
   WHERE (machineId = ?) AND (event = 'live') AND (id > 0), 
   ['AC03', 'live', 111, 102, u'123 45', 'AC03'], , false)
Full script console error log
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:productionLogging.eventLog>", line 108, in liveCycleCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 24797782, 19926827, u'981 34', 'AC03'], , false)
 
	at org.python.core.Py.JavaError(Py.java:547)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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:541)
 	at org.python.core.PyObject.__call__(PyObject.java:477)
 	at org.python.core.PyObject.__call__(PyObject.java:481)
 	at org.python.pycode._pyx12.liveCycleCount$4(<module:productionLogging.eventLog>:114)
 	at org.python.pycode._pyx12.call_function(<module:productionLogging.eventLog>)
 	at org.python.core.PyTableCode.call(PyTableCode.java:173)
 	at org.python.core.PyBaseCode.call(PyBaseCode.java:150)
 	at org.python.core.PyFunction.__call__(PyFunction.java:426)
 	at org.python.pycode._pyx11.f$0(<input>:1)
 	at org.python.pycode._pyx11.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:611)
 	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)
 	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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 24797782, 19926827, u'981 34', 'AC03'], , false)
 	... 31 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._runUpdateQuery(ClientDBUtilities.java:257)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)
 	... 29 more
 Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:productionLogging.eventLog>", line 108, in liveCycleCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 24797782, 19926827, u'981 34', 'AC03'], , false)
>>> Keyboard Interrupt
>>> 

Can anyone spot what I’m doing wrong?

What is that “u”?

Unicode identifier. It’s automatically added by Ignition where required, apparently.

Ah, i thought that might be the case, but is that valid in the query?

My query is shown below:

	query = ("UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? "
				+ "WHERE (machineId = ?) AND (event = 'live') AND (id > 0)")
	args = [machineID,		# MachineId
		'live',				# There will be only one 'live' record per machine
		values[0].value,	# cycleCount
		values[1].value,	# goodCount
		values[2].value,	# sku e.g. '123 45'
		machineID			# MachineId again
	]
	system.db.runUpdateQuery(query, args)

I didn’t specify Unicode. Ignition added it!

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

Ha! I’m glad I asked rather than struggle on!

I’ve turned on Full Read / Write in the developer and call the function from the Script Console again. Now I’m getting,

Caused by: java.sql.SQLException: 
    Datasource "['AC03', 'live', 111, 102, u'123 45', 'AC03']" 
    does not exist in this Gateway.
Full error log
>>> 
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:productionLogging.eventLog>", line 108, in liveCycleCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 24798239, 19927282, u'981 34', 'AC03'], , false)
 
	at org.python.core.Py.JavaError(Py.java:547)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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:541)
 	at org.python.core.PyObject.__call__(PyObject.java:477)
 	at org.python.core.PyObject.__call__(PyObject.java:481)
 	at org.python.pycode._pyx12.liveCycleCount$4(<module:productionLogging.eventLog>:114)
 	at org.python.pycode._pyx12.call_function(<module:productionLogging.eventLog>)
 	at org.python.core.PyTableCode.call(PyTableCode.java:173)
 	at org.python.core.PyBaseCode.call(PyBaseCode.java:150)
 	at org.python.core.PyFunction.__call__(PyFunction.java:426)
 	at org.python.pycode._pyx14.f$0(<input>:1)
 	at org.python.pycode._pyx14.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:611)
 	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)
 	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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 24798239, 19927282, u'981 34', 'AC03'], , false)
 	... 31 more
 Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Datasource "['AC03', 'live', 24798239, 19927282, u'981 34', 'AC03']" does not exist in this Gateway.
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
 	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runUpdateQuery(ClientDBUtilities.java:257)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)
 	... 29 more
 Caused by: java.sql.SQLException: Datasource "['AC03', 'live', 24798239, 19927282, u'981 34', 'AC03']" does not exist in this Gateway.
 	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:149)
 	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:100)
 	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:51)
 	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)
 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
 	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
 	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
 	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
 	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1626)
 	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
 	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:602)
 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
 	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
 	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
 	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1434)
 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
 	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
 	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
 	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
 	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1349)
 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
 	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
 	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
 	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
 	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
 	at org.eclipse.jetty.server.Server.handle(Server.java:516)
 	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388)
 	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633)
 	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380)
 	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
 	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
 	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
 	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
 	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
 	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:386)
 	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
 	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
 	at java.lang.Thread.run(null)
 Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:productionLogging.eventLog>", line 108, in liveCycleCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.runUpdateQuery(UPDATE db_os_manf_dashboard.production_log SET machineId = ?, event = ?, cycleCount = ?, goodCount = ?, sku = ? WHERE (machineId = ?) AND (event = 'live') AND (id > 0), ['AC03', 'live', 123, 102, u'123 45', 'AC03'], , false)
>>> 

What does that mean?

Problem solved by changing from
system.db.runUpdateQuery(query, args)
to
system.db.runPrepUpdate(query, args)

It would be nice to know why the runUpdateQuery doesn’t work.

Thanks all.

Because an update query does not take parameters in the way that a prep update query does. The update query expects the second parameter to be the datasource.

Your query is in the form of a prepared statement (the question marks are the giveaway), so you need to use prepUpdate. (prep being short for prepared statement)

4 Likes