DB update from Power Table and Button, cannot coerce value

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.

Any help would be appreciated

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})
1 Like

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.

Show your whole script. (Those funky dates are the derived from UTC milliseconds == zero.)

Also, what is the structure of the table? What datatype is "row" ?

Actually, your WHERE clause is incomplete. It should be WHERE "someColumn" = :row.

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

Yes.

This can't possibly be correct. You have the same column being set to a timestamp and also being used to pick which row to update.

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.

1 Like

I got this to work

EDIT: Polling was turned off