runUpdateQuery works in Console but doesn't in Gateway

Joshua_Graham · March 21, 2024, 1:10pm

I'm trying to create a counter in an SQL database. This all works except for the system.db.runUpdateQuery, I've tested it in the script console and it does work there with no errors. Default database is selected and Read/write is turned on so I'm not sure what the problem is.

qv = system.tag.read("[default]Line6").value
Current_datetime_STR = system.tag.read("[default]Current Time").value
Current_datetime = system.tag.read("[default]Current Time as DT").value
Previous_datetime = system.db.runQuery("SELECT Top 1 DateTime FROM Line6 WHERE Input = 'test' ORDER BY ID DESC")[0][0]

if qv:
	timeDiff = Current_datetime.getTime() - Previous_datetime.getTime() 
	timeDiffSeconds = timeDiff / 1000.0 
	system.db.runQuery("INSERT INTO Line6 VALUES ('%s', 'Line6', %.3f)" % (Current_datetime_STR, timeDiffSeconds))
	system.db.runUpdateQuery("UPDATE Counts SET Counter = Counter + 1 WHERE Machine = 'Line6'")

pturmel · March 21, 2024, 1:12pm

In gateway scope you often have to explicitly provide the name of the database connection in the system.db.* call. See the per-scope documentation. (The designer's script console is, fundamentally, Vision scope.)

Joshua_Graham · March 21, 2024, 1:32pm

So I tried this instead

system.db.runUpdateQuery("UPDATE Counts SET Counter = Counter + 1 WHERE Machine = 'Line6'","NewConnection")

Still doesn't work in the gateway script, but does work in script console.

pturmel · March 21, 2024, 1:35pm

Have you looked at your logs?

Consider extracting the code into a function in a library script module. Call the function from the gateway event, and call the function from the script console.

(Events should be one-line calls to project library functions in any case.)

Joshua_Graham · March 21, 2024, 2:22pm

ok, I am getting an error for the INSERT query, even though it still works in the gateway script. The UPDATE query still doesn't work in the gateway script, even though it doesn't create an error in the logs.

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:NewScript>", line 13, in updateCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:359)
	at jdk.internal.reflect.GeneratedMethodAccessor62.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.runQuery(INSERT INTO Line6 VALUES ('2024-03-21 10:19:21.585', 'Line6', 4.002), NewConnection, )
	at org.python.core.Py.JavaError(Py.java:545)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:359)
	at jdk.internal.reflect.GeneratedMethodAccessor62.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:553)
	at org.python.core.PyObject.__call__(PyObject.java:477)
	at org.python.core.PyObject.__call__(PyObject.java:481)
	at org.python.pycode._pyx747.updateCount$1(<module:NewScript>:13)
	at org.python.pycode._pyx747.call_function(<module:NewScript>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:134)
	at org.python.core.PyFunction.__call__(PyFunction.java:416)
	at org.python.pycode._pyx748.f$0(<input>:1)
	at org.python.pycode._pyx748.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:1703)
	at org.python.core.Py.exec(Py.java:1747)
	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:628)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)
	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.runQuery(INSERT INTO Line6 VALUES ('2024-03-21 10:19:21.585', 'Line6', 4.002), NewConnection, )
	... 30 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The statement did not return a result set.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:893)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:859)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runQuery(ClientDBUtilities.java:336)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:350)
	... 28 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:904)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:793)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:715)
	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
	at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeQuery(DelegatingStatement.java:64)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeQuery(SRConnectionWrapper.java:793)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:80)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:66)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.Server.handle(Server.java:563)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<module:NewScript>", line 13, in updateCount
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:359)
	at jdk.internal.reflect.GeneratedMethodAccessor62.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.runQuery(INSERT INTO Line6 VALUES ('2024-03-21 10:19:21.585', 'Line6', 4.002), NewConnection, )

pascal.fragnoud · March 21, 2024, 2:24pm

Wrong function.

lrose · March 21, 2024, 9:03pm

As @pascal.fragnoud says, an INSERT query is considered an update query.

There are other things about the script that should be corrected.

system.tag.read() has been deprecated you should be using system.tag.readBlocking()
You should avoid making multiple calls to system.tag.read*() or system.tag.write*() functions
If a query is only returning a single row and column then you should use a scalar query.
I'm not sure what exactly qv is, I presume that it is intended to be a Boolean value. In the context of Ignition qv would typically mean qualified value. The way you are using it accomplishes nothing, because if qv: will always evaluate as true.
I would strongly recommend that you always use a "prep" query (unless you're using a Named Query).
I would also strongly recommend that you not convert dates into strings for storage. Store them as dates.

Here is how I would recommend that you write this script.

Line6, Current_datetime = [qv.value for qv in system.tag.readBlocking(["[default]Line6", "[default]Current Time as DT"])]
Previous_datetime = system.db.runScalarPrepQuery("SELECT Top 1 DateTime FROM Line6 Where Input = ? ORDER BY ID DESC", ['test'])

if Line6:
    timeDiffSeconds = systme.date.secondsBetween(Previous_datetime, Current_datetime)
    system.db.runPrepUpdate("INSERT INTO Line6 VALUES (?,?,?)",[Current_datetime,'Line6','{:3f}'.format(timeDiffSeconds)])
    system.db.runPrepUpdate("UPDATE Counts SET Counter = Counter + 1 WHERE Machine = ?", ['Line 6'])

pascal.fragnoud · March 21, 2024, 9:43pm

I believe you meant "{:3f}".format(...) or "%.3f" % (...).

lrose · March 21, 2024, 10:11pm

I did, corrected