# Database insert fails

**URL:** <https://forum.inductiveautomation.com/t/database-insert-fails/39121>\
**Category:** General Discussion\
**Tags:** scripting, database, ignition80\
**Created:** [September 28, 2020, 4:21pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121 "2020-09-28T16:21:12Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![frank.foerster](https://avatars.discourse-cdn.com/v4/letter/f/e95f7d/32.png) [@frank.foerster](https://forum.inductiveautomation.com/u/frank.foerster)\
**Post date:** [September 28, 2020, 4:21pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/1 "2020-09-28T16:21:12Z")

</div>

Hi folks, trying this for some time time, but can’t find a solution:

(’\_MDA\_Active ‘, “‘active’”)  
(’\_MDA\_Version ‘, “‘1111’”)  
(’\_MDA\_Name ‘, “‘twertt’”)  
(’\_MDA\_Description ‘, “‘wert’”)  
(’\_MDA\_Status ‘, “‘active’”)  
(’\_MDA\_Version\_Desc ‘, “‘wert’”)  
(’\_MDA\_DataType ‘, “‘0’”)  
(’\_MDA\_Object\_Type ', “‘1’”)  
18:17:11.049 [AWT-EventQueue-0] INFO mylogger - INSERT INTO [MDA\_ItemHeader] ([UUID],[MDA\_Active],[MDA\_Version\_Name],[MDA\_Name],[MDA\_Description],[MDA\_Status],[MDA\_Version\_Desc],[MDA\_DataType],[MDA\_Object\_Type]) VALUES (newid(),‘active’,‘1111’,‘twertt’,‘wert’,‘active’,‘wert’,‘0’,‘1’)  
18:17:11.050 [AWT-EventQueue-0] INFO mylogger - setDatasource  
18:17:11.051 [AWT-EventQueue-0] INFO mylogger - runQuery  
18:17:11.086 [AWT-EventQueue-0] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - Error executing script for event:&nbsp;`actionPerformed`  
on component:&nbsp;`OKButton`.  
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):  
File “event:actionPerformed”, line 45, in   
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)  
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)  
at jdk.internal.reflect.GeneratedMethodAccessor402.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(sglPackages, INSERT INTO [MDA\_ItemHeader] ([UUID],[MDA\_Active],[MDA\_Version\_Name],[MDA\_Name],[MDA\_Description],[MDA\_Status],[MDA\_Version\_Desc],[MDA\_DataType],[MDA\_Object\_Type]) VALUES (newid(),‘active’,‘1111’,‘twertt’,‘wert’,‘active’,‘wert’,‘0’,‘1’), SGLMESDB)

```
at org.python.core.Py.JavaError(Py.java:552)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)
at jdk.internal.reflect.GeneratedMethodAccessor402.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:188)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:524)
at org.python.core.PyObject. __call__ (PyObject.java:515)
at org.python.core.PyObject. __call__ (PyObject.java:519)
at org.python.pycode._pyx48.f$0(<event:actionPerformed>:45)
at org.python.pycode._pyx48.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java:171)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1614)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:782)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:730)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:208)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:297)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy67.actionPerformed(Unknown Source)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.desktop/java.awt.Component.processEvent(Unknown Source)
at java.desktop/java.awt.Container.processEvent(Unknown Source)
at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)

```

Caused by: org.python.core.PyException: Traceback (most recent call last):  
File “event:actionPerformed”, line 45, in   
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)  
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)  
at jdk.internal.reflect.GeneratedMethodAccessor402.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(sglPackages, INSERT INTO [MDA\_ItemHeader] ([UUID],[MDA\_Active],[MDA\_Version\_Name],[MDA\_Name],[MDA\_Description],[MDA\_Status],[MDA\_Version\_Desc],[MDA\_DataType],[MDA\_Object\_Type]) VALUES (newid(),‘active’,‘1111’,‘twertt’,‘wert’,‘active’,‘wert’,‘0’,‘1’), SGLMESDB)

```
... 56 common frames omitted

```

Caused by: java.lang.Exception: Error executing system.db.runQuery(sglPackages, INSERT INTO [MDA\_ItemHeader] ([UUID],[MDA\_Active],[MDA\_Version\_Name],[MDA\_Name],[MDA\_Description],[MDA\_Status],[MDA\_Version\_Desc],[MDA\_DataType],[MDA\_Object\_Type]) VALUES (newid(),‘active’,‘1111’,‘twertt’,‘wert’,‘active’,‘wert’,‘0’,‘1’), SGLMESDB)  
… 55 common frames omitted  
Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Connection Error: Transaction “SGLMESDB” is closed.  
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.gateway\_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:860)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runQuery(GatewayInterface.java:826)  
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.\_runQuery(ClientDBUtilities.java:325)  
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:329)  
… 53 common frames omitted  
Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayResponse$GatewayThrowable: Transaction “SGLMESDB” is closed.  
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTx(DatasourceManagerImpl.java:284)  
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getTransactionConnection(DatasourceManagerImpl.java:291)  
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:95)  
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:51)  
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:414)  
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$NotAsyncServlet.service(ServletHolder.java:1391)  
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)  
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)  
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)  
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)  
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:1607)  
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)  
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)  
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)  
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)  
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)  
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)  
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)  
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)  
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)  
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:500)  
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)  
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)  
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)  
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)  
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)  
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)  
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)  
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)  
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)  
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)  
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)  
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)  
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)  
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)  
at java.lang.Thread.run(null)  
18:17:12.390 [Browser Events Thread] INFO Perspective.SessionProps - WebSocket connection to ‘ws://eefis558.logon.sglcarbon.net:8088/system/pws/sglPackages/session-props?token=pyqoKUi\_rfAYr1\_giuHcLLalyljrQjCs0ohuNxoB\_NQ’ failed: Error during WebSocket handshake: net::ERR\_CONNECTION\_RESET

If I execute the query inside the database query browser, it works, but as soon I doo it in scriptin, I get the error above.

My Script:

query = “INSERT INTO [MDA\_ItemHeader] ([UUID],[MDA\_Active],[MDA\_Version\_Name],[MDA\_Name],[MDA\_Description],[MDA\_Status]”  
query = query+",[MDA\_Version\_Desc],[MDA\_DataType],[MDA\_Object\_Type]) VALUES ("  
query = query+\_MDA\_UUID+separator+\_MDA\_Active+separator+\_MDA\_Version\_Name+separator+\_MDA\_Name+separator+\_MDA\_Description+separator  
query = query+\_MDA\_Status+separator+\_MDA\_Version\_Desc+separator+\_MDA\_DataType+separator+\_MDA\_Object\_Type+ “)”

```
logger.info(query)
logger.info("setDatasource")
#system.db.setDatasourceEnabled("SGLMESDB",1)
logger.info("runQuery")
system.db.runQuery("sglPackages",query,"SGLMESDB")

```

Any help highly appreciated!!

FF

---

<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:** [September 28, 2020, 4:31pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/2 "2020-09-28T16:31:40Z")

</div>

First **edit** your post to include code formatting marks, so we can read it. For each section of pasted code or log output, highlight it then press the \</\> button. That’ll put three back-ticks above and below the multi-line sections.

But I suspect you are misusing `runQuery()`: the SQL should be the first argument. (And you should consider using `runPrepQuery()` instead, to avoid string assembly of your SQL.)

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [September 28, 2020, 5:13pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/3 "2020-09-28T17:13:00Z")

</div>

Welcome!!

Link showing what Phil is referring to…  
[https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate)

It says your database is closed, which is probably not accurate since you can browse.

Follow the help and get your code to follow the proper syntax:

```auto
system.db.runPrepUpdate( query, args, [database], [tx], [getKey], [skipAudit] )

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 28, 2020, 5:20pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/4 "2020-09-28T17:20:05Z")

</div>

runQuery() and runPrepQuery() don’t do insert or update funcitons. Use [runPrepUpdate()](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate) instead. 🙂

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 28, 2020, 5:30pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/5 "2020-09-28T17:30:34Z")

</div>

I also notice your first item in values is ‘newid()’. I’m not sure if that was intended or not. 🤷‍♂️

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [September 28, 2020, 6:02pm UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/6 "2020-09-28T18:02:13Z")

</div>

Oops, I should have looked closer, I took what Phil posted and ran with it.

---

<div class="post-metadata">

**Author:** ![frank.foerster](https://avatars.discourse-cdn.com/v4/letter/f/e95f7d/32.png) [@frank.foerster](https://forum.inductiveautomation.com/u/frank.foerster)\
**Post date:** [September 29, 2020, 7:42am UTC](https://forum.inductiveautomation.com/t/database-insert-fails/39121/7 "2020-09-29T07:42:44Z")

</div>

Using runprepQuery wasn’t the only thing which helped, i also had to restart the MS-SQL server service