# runUpdateQuery works in Console but doesn't in Gateway

**URL:** <https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686>\
**Category:** Ignition\
**Tags:** database\
**Created:** [March 21, 2024, 1:10pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686 "2024-03-21T13:10:12Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joshua\_Graham](https://avatars.discourse-cdn.com/v4/letter/j/73ab20/32.png) [@Joshua\_Graham](https://forum.inductiveautomation.com/u/Joshua_Graham)\
**Post date:** [March 21, 2024, 1:10pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/1 "2024-03-21T13:10:12Z")

</div>

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.

```auto
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'")

```

 ![jljlkljkl](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/4/642b10ab7742aa2090df0a3c9b829761d970b8a7.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 21, 2024, 1:12pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/2 "2024-03-21T13:12:23Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![Joshua\_Graham](https://avatars.discourse-cdn.com/v4/letter/j/73ab20/32.png) [@Joshua\_Graham](https://forum.inductiveautomation.com/u/Joshua_Graham)\
**Post date:** [March 21, 2024, 1:32pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/3 "2024-03-21T13:32:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 21, 2024, 1:35pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/4 "2024-03-21T13:35:09Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![Joshua\_Graham](https://avatars.discourse-cdn.com/v4/letter/j/73ab20/32.png) [@Joshua\_Graham](https://forum.inductiveautomation.com/u/Joshua_Graham)\
**Post date:** [March 21, 2024, 2:22pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/5 "2024-03-21T14:22:25Z")

</div>

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.

```auto
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, )

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [March 21, 2024, 2:24pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/6 "2024-03-21T14:24:36Z")

</div>

> [@Joshua\_Graham](#):
>
> `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, )`

Wrong function.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 21, 2024, 9:03pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/7 "2024-03-21T21:03:28Z")

</div>

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.

```auto
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'])

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [March 21, 2024, 9:43pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/8 "2024-03-21T21:43:17Z")

</div>

> [@lrose](#):
>
> `'%.3f'.format(timeDiffSeconds)`

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 21, 2024, 10:11pm UTC](https://forum.inductiveautomation.com/t/runupdatequery-works-in-console-but-doesnt-in-gateway/87686/9 "2024-03-21T22:11:10Z")

</div>

I did, corrected