# Error when updating SQL DB table using cell edit in a Power Table **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [January 13, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998 "2022-01-13T19:57:24Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Kyle.deKock](https://avatars.discourse-cdn.com/v4/letter/k/ed8c4c/32.png) [@Kyle.deKock](https://forum.inductiveautomation.com/u/Kyle.deKock)\ **Post date:** [January 13, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/1 "2022-01-13T19:57:24Z")
Hi All, I have a power table that gets populated with all of the data from a table in my SQL Server DB that has cell editing enable so that we can update the data in the database. I have the following script in the OnCellEdited extension function to update the values in the database when a cell is edited: ```auto id = self.data.getValueAt(rowIndex,'id') if id != 1: query = "UPDATE tblingredientname SET %s = ? WHERE tblingredientname.id = ?" % (colName) args = [newValue,id] system.db.runPrepUpdate(query,args) ``` The issue that I am running into is that whenever I try to change a value by editing a cell in the power table I get the following error in the Client Console (namely **The conversion from UNKNOWN to UNKNOWN is unsupported** ): > Message: Error invoking extension method. > Time: Thu Jan 13 2:42:11PM > Severity: ERROR > Logger: Vision.Components.AdvancedTable > Stack Trace: org.python.core.PyException: Traceback (most recent call last): > File "\", line 22, in onCellEdited >     at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) >     at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) >     at jdk.internal.reflect.GeneratedMethodAccessor3005.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.runPrepUpdate(UPDATE tblingredientname SET IngredientNumber = ? WHERE tblingredientname.id = ?, [TEST001, 115], , , false, false) > >     at org.python.core.Py.JavaError(Py.java:495) >     at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) >     at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) >     at jdk.internal.reflect.GeneratedMethodAccessor3005.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:186) >     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._pyx1349.onCellEdited$1(\:28) >     at org.python.pycode.pyx1349.call\_function(\) >     at org.python.core.PyTableCode.call(PyTableCode.java:165) >     at org.python.core.PyBaseCode.call(PyBaseCode.java:301) >     at org.python.core.PyFunction.function\_\_\_call_(PyFunction.java:376) >     at org.python.core.PyFunction. **call** (PyFunction.java:371) >     at org.python.core.PyFunction. **call** (PyFunction.java:361) >     at org.python.core.PyFunction. **call** (PyFunction.java:356) >     at com.inductiveautomation.vision.api.client.components.model.ExtensionFunction.invoke(ExtensionFunction.java:152) >     at com.inductiveautomation.factorypmi.application.components.VisionAdvancedTable$Model.setValueAt(VisionAdvancedTable.java:1751) >     at com.jidesoft.grid.TableModelWrapperImpl.setValueAt(Unknown Source) >     at com.jidesoft.grid.DefaultTableModelWrapper.setValueAt(Unknown Source) >     at com.jidesoft.grid.TableModelWrapperImpl.setValueAt(Unknown Source) >     at com.jidesoft.grid.DefaultTableModelWrapper.setValueAt(Unknown Source) >     at java.desktop/javax.swing.JTable.setValueAt(Unknown Source) >     at com.jidesoft.grid.JideTable.editingStopped(Unknown Source) >     at java.desktop/javax.swing.AbstractCellEditor.fireEditingStopped(Unknown Source) >     at java.desktop/javax.swing.DefaultCellEditor$EditorDelegate.stopCellEditing(Unknown Source) >     at java.desktop/javax.swing.DefaultCellEditor.stopCellEditing(Unknown Source) >     at com.inductiveautomation.factorypmi.application.components.table.TextCellEditor$TFFocusListener.stop(TextCellEditor.java:121) >     at com.inductiveautomation.factorypmi.application.components.table.TextCellEditor$TFFocusListener$KeyboardRequest.run(TextCellEditor.java:142) >     at java.desktop/java.awt.event.InvocationEvent.dispatch(Unknown Source) >     at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source) >     at java.desktop/java.awt.EventQueue.access$500(Unknown Source) >     at java.desktop/java.awt.EventQueue$3.run(Unknown Source) >     at java.desktop/java.awt.EventQueue$3.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: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE tblingredientname SET IngredientNumber = ? WHERE tblingredientname.id = ?, [TEST001, 115], , , false, false) >     ... 44 common frames omitted > Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: SQL error for "UPDATE tblingredientname SET IngredientNumber = ? WHERE tblingredientname.id = ?": The conversion from UNKNOWN to UNKNOWN is unsupported. >     at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:353) >     at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:327) >     at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:280) >     at com.inductiveautomation.ignition.client.script.ClientDBUtilities.\_runPrepStmt(ClientDBUtilities.java:215) >     at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256) >     ... 42 common frames omitted > Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayResponse$GatewayThrowable: The conversion from UNKNOWN to UNKNOWN is unsupported. >     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170) >     at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:946) >     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:958) >     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:910) >     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:919) >     at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169) >     at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169) >     at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1138) >     at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:66) >     at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:37) >     at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:77) >     at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405) >     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$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.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) I have found some information about system.db.RunPrepUpdate function having issues in MS SQL with Datetime and Long datatypes but I am only trying to write String or Int values. Some other pertinent information that may be useful is that this query was original written for MySQL and stopped working after migrating over to MS SQL. Any help would be greatly appreciated. ---
**Author:** ![Kyle.deKock](https://avatars.discourse-cdn.com/v4/letter/k/ed8c4c/32.png) [@Kyle.deKock](https://forum.inductiveautomation.com/u/Kyle.deKock)\ **Post date:** [January 13, 2022, 8:42pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/2 "2022-01-13T20:42:46Z")
For those that are curious. I was able to create a workaround using the system.db.runUpdateQuery function: ```auto query = "UPDATE tblingredientname SET %s = '%s' WHERE tblingredientname.id = %d" % (colName, str(newValue), id) system.db.runUpdateQuery(query) ``` ---
**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:** [January 13, 2022, 8:54pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/3 "2022-01-13T20:54:21Z")
Ewww! Please don’t leave it like that. ---
**Author:** ![Kyle.deKock](https://avatars.discourse-cdn.com/v4/letter/k/ed8c4c/32.png) [@Kyle.deKock](https://forum.inductiveautomation.com/u/Kyle.deKock)\ **Post date:** [January 13, 2022, 9:12pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/4 "2022-01-13T21:12:21Z")
I’m open to better solutions. Admittedly I’m not a SQL whizz but after lots of troubleshooting and testing this was the only method I found that would not throw any errors. ---
**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:** [January 13, 2022, 9:20pm UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/5 "2022-01-13T21:20:32Z")
I would drop MS SQL Server in a heartbeat. Migrating **to** it just seems a special kind of crazy. I’ve suffered in various ways with it, but the worst is dealing with multiple time zones. MySQL =\> latest MariaDB would be a reasonable migration. ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [January 14, 2022, 6:44am UTC](https://forum.inductiveautomation.com/t/error-when-updating-sql-db-table-using-cell-edit-in-a-power-table/54998/6 "2022-01-14T06:44:20Z")
That is the solution, it is a bug in 7.9 when using prepUpdate in extension or custom methods. Try running this else where ```auto id = 115 if id != 1: query = "UPDATE tblingredientname SET %s = ? WHERE tblingredientname.id = ?" % (colName) args = ['test001',id] system.db.runPrepUpdate(query,args) ``` In action performed from a button for example, I bet you won’t see the error. If you still see the error, I would take @pturmel recommendation as priority.