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.