Error when updating SQL DB table using cell edit in a Power Table

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:

	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 "<extension-method onCellEdited>", 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(<extension-method onCellEdited>:28)
    at org.python.pycode.pyx1349.call_function(<extension-method onCellEdited>)
    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.

For those that are curious. I was able to create a workaround using the system.db.runUpdateQuery function:

query = "UPDATE tblingredientname SET %s = '%s' WHERE tblingredientname.id = %d" % (colName, str(newValue), id)
system.db.runUpdateQuery(query)

Ewww! Please don’t leave it like that.

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.

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.

That is the solution, it is a bug in 7.9 when using prepUpdate in extension or custom methods.
Try running this else where

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.