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

Ignition
1

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?

2

What is that "u"?

3

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

4

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

5

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!

6

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

7

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?

8

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.

9

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