I have been fighting this issue for about a week now, and I figured I might be able to get an assist here. So I am trying to set up a button that updates a DB Table with the current date and time when pressed, as well as a "Are you sure? Confirm" popup. I believe I have my script mostly right. Here is that script.
if system.gui.confirm(u'Are you sure?', 'Confirm'):
time = system.date.now();
row = event.source.parent.getComponent('Power Table').selectedRow;
system.db.runNamedQuery("Complete", time, row)
This is scripted to a button on the actionPerformed event handler. When I click the button I get this error.
Traceback (most recent call last):
File "<event:actionPerformed>", line 4, in <module>
java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value 'Tue Feb 07 07:59:49 CST 2023' into type: interface java.util.Map
caused by ClassCastException: Cannot coerce value 'Tue Feb 07 07:59:49 CST 2023' into type: interface java.util.Map
Ignition v8.1.23 (b2022121308)
Java: Azul Systems, Inc. 11.0.16.1
and here is the NamedQuery
UPDATE tracking SET completed = :time WHERE :row
Now I am almost positive that my issue is its trying to update a DateTime value with a String value, but when I use the dateFormat script, I am not even able to apply the script.
You are passing a timestamp (a java.util.Date objecte) where a the named query call expects its parameters (a dictionary). You probably aren't getting what you think for row, either, as that is the zero-based index of the row in the table's dataset, not a value within the row. You probaby want something like this:
if system.gui.confirm(u'Are you sure?', 'Confirm'):
MyTable = event.source.parent.getComponent('Power Table')
time = system.date.now()
row = MyTable.selectedRow
rowID = MyTable.data.getValueAt(row, 'RowIDColumnName')
system.db.runNamedQuery("Complete", {'time': time, 'row': rowID})
So I put in that script, changed 'RowIDColumnName' to the proper column name, and now I am getting this error.
Traceback (most recent call last):
File "<event:actionPerformed>", line 6, in <module>
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
caused by GatewayException: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
caused by Exception: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
Ignition v8.1.23 (b2022121308)
Java: Azul Systems, Inc. 11.0.16.1
Do I need to make any changes to the NamedQuery params? Here is what I have now.
Here is all the updated info, as far as structure of the table, do you mean the DB table itself or the Power Table component? "row" datatype is Int4 right now.
Script on Button:
if system.gui.confirm(u'Are you sure?', 'Confirm'):
MyTable = event.source.parent.getComponent('Power Table')
time = system.date.now()
row = MyTable.selectedRow
rowID = MyTable.data.getValueAt(row, 'completed')
system.db.runNamedQuery("Complete", {'time': time, 'row': rowID})
NamedQuery:
UPDATE tracking SET completed = :time WHERE completed = :row
Query Params: time = DateTime, row = Int4
Full Error message:
Traceback (most recent call last):
File "<event:actionPerformed>", line 6, in <module>
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.invoke(GatewayInterface.java:954)
at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:73)
at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:46)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:117)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
at org.python.core.Py.JavaError(Py.java:547)
at org.python.core.Py.JavaError(Py.java:538)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:552)
at org.python.core.PyObject.__call__(PyObject.java:477)
at org.python.core.PyObject.__call__(PyObject.java:481)
at org.python.pycode._pyx56.f$0(<event:actionPerformed>:6)
at org.python.pycode._pyx56.call_function(<event:actionPerformed>)
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:1687)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:803)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:299)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy61.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 com.inductiveautomation.snap.swing.RibsEventQueue.dispatchEvent(RibsEventQueue.java:99)
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: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
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.invoke(GatewayInterface.java:954)
at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:73)
at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:46)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:117)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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)
... 49 more
Caused by: java.lang.Exception: java.lang.ClassCastException: Cannot coerce value 'Wed Dec 31 18:00:00 CST 1969' into type: class java.lang.Integer
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:196)
at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:121)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:67)
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(null)
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
at java.lang.reflect.Method.invoke(null)
at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:228)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)
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$NotAsync.service(ServletHolder.java:1450)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
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:1624)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
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:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
at java.lang.Thread.run(null)
Ignition v8.1.23 (b2022121308)
Java: Azul Systems, Inc. 11.0.16.1
This should be the column in the table that is the primary key of the DB table, presuming there's just one. If a multi-column PK, you will need to extract all of them from the table, pass them as params, and check them all in the WHERE clause.
There is only one column labeled 'Complete', There is also 6 other columns and soon It will be set up so they add and remove information as needed from ignition. for the WHERE clause I supposed I could just do "WHERE tracking_id = :row" ? tracking_id being the first column of the table. I figured the WHERE clause could point at any column as long as the :row param is set up correctly in the script.
The WHERE clause must point at the column that uniquely identifies the row to be updated. There's no magic that connects the data displayed in a table back to the DB it came from. That's up to you and your scripts and your SQL.
Such a column is typically identified as the DB Table's primary key. If you've never heard that term, you should spend some time learning a bit about databases.