Database insert fails

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: actionPerformed
on component: 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

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

3 Likes

Welcome!!

Link showing what Phil is referring to…
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:

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

runQuery() and runPrepQuery() don’t do insert or update funcitons. Use runPrepUpdate() instead. :slight_smile:

1 Like

I also notice your first item in values is ‘newid()’. I’m not sure if that was intended or not. :man_shrugging:

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

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

1 Like