I’m trying to run a Named Query to Insert Row at MS SQL, and return the ID column.
I created a named query with:
INSERT INTO STOP_GO_CARD (BEHAVIOR_GO, CARD_TYPE, DATE_CREATED, EMPLOYEE_NUMBER_1, EMPLOYEE_NUMBER_2, NAME_1, NAME_2, RACF_1, RACF_2)
OUTPUT Inserted.ID
VALUES ( :BEHAVIOR_GO, :CARD_TYPE, GETDATE(), :EMPLOYEE_1, :EMPLOYEE_2, :NAME_1, :NAME_2, :RACF_1, :RACF_2)
And at test, it return the ID column of insertion.
But when try to use at Script, has a error:
Script:
BEHAVIOR_GO = system.tag.getTagValue('[Client]GO_1')
CARD_TYPE = 1
EMPLOYEE_1 = system.tag.getTagValue('[Client]Employee_Number_1')
EMPLOYEE_2 = system.tag.getTagValue('[Client]Employee_Number_2')
NAME_1 = system.tag.getTagValue('[Client]Name_Select_1')
NAME_2 = system.tag.getTagValue('[Client]Name_Select_2')
RACF_1 = system.tag.getTagValue('[Client]RACF_1')
RACF_2 = system.tag.getTagValue('[Client]RACF_2')
params = {"BEHAVIOR_GO":BEHAVIOR_GO, "CARD_TYPE":CARD_TYPE, "EMPLOYEE_1":EMPLOYEE_1,"EMPLOYEE_2":EMPLOYEE_2,"NAME_1":NAME_1,"NAME_2":NAME_2,"RACF_1":RACF_1,"RACF_2":RACF_2}
card_number = system.db.runNamedQuery("INSERT CARD GO", params)
and occur this error:
Traceback (most recent call last):
File "<event:internalFrameActivated>", line 16, in <module>
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:894)
at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:56)
at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:40)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:90)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at org.python.core.Py.JavaError(Py.java:495)
at org.python.core.Py.JavaError(Py.java:488)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:431)
at org.python.core.PyObject.__call__(PyObject.java:404)
at org.python.core.PyObject.__call__(PyObject.java:408)
at org.python.pycode._pyx108.f$0(<event:internalFrameActivated>:39)
at org.python.pycode._pyx108.call_function(<event:internalFrameActivated>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:636)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:180)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:271)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy30.internalFrameActivated(Unknown Source)
at javax.swing.JInternalFrame.fireInternalFrameEvent(Unknown Source)
at javax.swing.JInternalFrame.setSelected(Unknown Source)
at javax.swing.plaf.basic.BasicLookAndFeel$AWTEventHelper.eventDispatched(Unknown Source)
at java.awt.Toolkit$SelectiveAWTEventListener.eventDispatched(Unknown Source)
at java.awt.Toolkit$ToolkitEventMulticaster.eventDispatched(Unknown Source)
at java.awt.Toolkit$ToolkitEventMulticaster.eventDispatched(Unknown Source)
at java.awt.Toolkit$ToolkitEventMulticaster.eventDispatched(Unknown Source)
at java.awt.Toolkit$ToolkitEventMulticaster.eventDispatched(Unknown Source)
at java.awt.Toolkit$ToolkitEventMulticaster.eventDispatched(Unknown Source)
at java.awt.Toolkit.notifyAWTEventListeners(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:894)
at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:56)
at com.inductiveautomation.ignition.designer.db.namedquery.NamedQueryDesignerRpc.execute(NamedQueryDesignerRpc.java:40)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:90)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
... 50 more
Caused by: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.inductiveautomation.ignition.gateway.project.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:310)
at com.inductiveautomation.ignition.gateway.project.NamedQueryExecutor.execute(NamedQueryExecutor.java:116)
at com.inductiveautomation.ignition.gateway.project.ProjectManagerImpl.executeNamedQuery(ProjectManagerImpl.java:2165)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryGatewayRpc.execute(NamedQueryGatewayRpc.java:30)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:70)
at sun.reflect.GeneratedMethodAccessor208.invoke(null)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:208)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:404)
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:85)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(Thread.java:748)
Ignition v7.9.9 (b2018081621)
Java: Oracle Corporation 1.8.0_181
Anyone has any idea how can resolve this? I don’t have so many experience with SQL and start to use Ignition.
Thanks!