# DB update from Power Table and Button, cannot coerce value **URL:** \ **Category:** Ignition\ **Tags:** ignition81, database, vision, scripting\ **Created:** [February 7, 2023, 2:10pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526 "2023-02-07T14:10:02Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![jmason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jmason/32/47301_2.png) [@jmason](https://forum.inductiveautomation.com/u/jmason)\ **Post date:** [February 7, 2023, 2:10pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/1 "2023-02-07T14:10:02Z")
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. ```auto 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. ```auto Traceback (most recent call last): File "", line 4, in 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 ```auto 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 ---
**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:** [February 7, 2023, 2:34pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/2 "2023-02-07T14:34:11Z")
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: ```auto 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}) ``` ---
**Author:** ![jmason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jmason/32/47301_2.png) [@jmason](https://forum.inductiveautomation.com/u/jmason)\ **Post date:** [February 7, 2023, 3:16pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/3 "2023-02-07T15:16:08Z")
So I put in that script, changed 'RowIDColumnName' to the proper column name, and now I am getting this error. ```auto Traceback (most recent call last): File "", line 6, in 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. ![DB Params](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/2/8243afe148db43a087cbf7b76f4241e68e3749a8.jpeg) ---
**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:** [February 7, 2023, 3:41pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/4 "2023-02-07T15:41:03Z")
Show your whole script. (Those funky dates are the derived from UTC milliseconds == zero.) ---
**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:** [February 7, 2023, 3:42pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/5 "2023-02-07T15:42:20Z")
Also, what is the structure of the table? What datatype is "row" ? Actually, your WHERE clause is incomplete. It should be `WHERE "someColumn" = :row`. ---
**Author:** ![jmason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jmason/32/47301_2.png) [@jmason](https://forum.inductiveautomation.com/u/jmason)\ **Post date:** [February 7, 2023, 3:52pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/6 "2023-02-07T15:52:39Z")
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: ```auto 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: ```auto UPDATE tracking SET completed = :time WHERE completed = :row ``` Query Params: time = DateTime, row = Int4 Full Error message: ```auto Traceback (most recent call last): File "", line 6, in 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(:6) at org.python.pycode._pyx56.call_function() 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 ``` ---
**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:** [February 7, 2023, 3:55pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/7 "2023-02-07T15:55:47Z")
> [@jmason](#): > > do you mean the DB table itself Yes. > [@jmason](#): > > `UPDATE tracking SET completed = :time WHERE completed = :row` 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. ---
**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:** [February 7, 2023, 4:00pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/8 "2023-02-07T16:00:52Z")
> [@jmason](#): > > `rowID = MyTable.data.getValueAt(row, 'completed')` 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. ---
**Author:** ![jmason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jmason/32/47301_2.png) [@jmason](https://forum.inductiveautomation.com/u/jmason)\ **Post date:** [February 7, 2023, 4:06pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/9 "2023-02-07T16:06:27Z")
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. ---
**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:** [February 7, 2023, 4:08pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/10 "2023-02-07T16:08:11Z")
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. ---
**Author:** ![jmason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jmason/32/47301_2.png) [@jmason](https://forum.inductiveautomation.com/u/jmason)\ **Post date:** [February 7, 2023, 5:06pm UTC](https://forum.inductiveautomation.com/t/db-update-from-power-table-and-button-cannot-coerce-value/69526/11 "2023-02-07T17:06:03Z")
I got this to work EDIT: Polling was turned off