Update from Power Table cell SQL Error

Stuart · April 25, 2018, 5:04pm

I have a Power Table with editable cells that are then written to my database. This works great and all my changes are reflected in the database. The problem comes when I try to refresh the table to show the changes.

The onCellEdited function of the table has two lines of code commented out:

      import system
      self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue)

If I uncomment them the values in the table do change after I edit them, but I also get this error:

GatewayException: SQL error for "UPDATE tablename
SET columnname = value
WHERE condition": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3
	caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3

Ignition v7.9.7 (b2018032914)
Java: Oracle Corporation 1.8.0_172

My guess is that the onCellEdited function is being fired twice, and the second time it’s running, it’s missing most of the values.

A work around came up with was to run: system.db.refresh(self, "data") which works to show the changes, but it also re-orders the rows etc. which I’d rather not have happen. Leaving the self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue) commented out means no errors, but that my table shows old data.

Does anyone have any idea why this is happening, and a solution?

Thanks,

Stuart · April 25, 2018, 8:19pm

Actually, I’m getting the same error on a different table when trying to drag/drop a row. This table has no cell edit functions, but it is populated from my database.

pturmel · April 25, 2018, 8:28pm

Not enough information. Please share the actual SQL from the latter problem.

Stuart · April 25, 2018, 8:32pm

The second table has this SQL: SELECT pmkPart, partNumber, partPrice FROM part. I don’t think the SQL here is the problem as I am still getting the same error, but no where on this table is there a SQL update.

GatewayException: SQL error for "UPDATE tablename
SET columnname = value
WHERE condition": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3
	caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3

Ignition v7.9.7 (b2018032914)
Java: Oracle Corporation 1.8.0_172

pturmel · April 25, 2018, 8:34pm

Look at the error message window for the name of the component that is generating the error. It probably isn’t either of these tables.

Stuart · April 25, 2018, 8:42pm

Here is the full error. Part table is the template name, and Power Table the component name.

12:37:45.156 [UpdateExecuter 0] ERROR com.inductiveautomation.factorypmi.application.gateway.QueryManager - Error running update query:
"UPDATE tablename
SET columnname = value
WHERE condition"@-1ms
On: Part Table.Part Table.Power Table.data
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "UPDATE tablename
SET columnname = value
WHERE condition": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runUpdateQuery(GatewayInterface.java:576)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runUpdateQuery(GatewayInterface.java:560)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$UpdateQuery.execute(QueryManager.java:624)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:657)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:630)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$UpdateQueryExecutor.run(QueryManager.java:425)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition' at line 3
	at sun.reflect.GeneratedConstructorAccessor72.newInstance(null)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(null)
	at java.lang.reflect.Constructor.newInstance(null)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1816)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1730)
	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
	at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunUpdateQuery.runUpdateQuery(RunUpdateQuery.java:35)
	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.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)

pturmel · April 25, 2018, 9:18pm

Consider taking the table out of the template and dropping on an empty window and playing with it there. Something on the data binding is executing what appears to be a default (dummy) update query.

pturmel · April 25, 2018, 9:20pm

It looks like you somehow got an update query turned on for bi-directional binding on a dataset property?

Stuart · April 26, 2018, 12:04am

That is sort of what it looks like. I’ll try some more things and report back with any findings.

I noticed this problem shortly after upgrading to 7.9.7, but I also added other things at that time so it may be unrelated.

Mr.K001 · April 26, 2018, 6:12pm

@Stuart
I have the same issue one couple of my windows and I also did start noticing it after 7.9.7, The one window I’m using I only have two components that have a SQL binding, one a power table and the other a custom property dataset on the root container and neither of those you can enable the update option under SQL binding because it doesn’t exist so it’s very puzzling…
wondering if more people have notices this…
My error points to the dataset custom property

pturmel · April 26, 2018, 6:15pm

Is there any chance you originally placed that binding by copying it from a scalar property where the update option was turned on?

ikenna.nwaogu · June 6, 2018, 4:50pm

Am having the same issue. My project worked fine on 7.9.2, 7.9.6 but failed to work on 7.9.7. Could it be a bug? because at first it was automatically switching to the bi-directional update after I have turned it off.

Then I left the update part blank and switched it off. Now, I cant update the sql bind property. when I open the binding and press OK button whether I update it or not I get the below error

===========================================================================
Exception in thread "AWT-EventQueue-2" java.lang.NullPointerException
	at com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter.getBindingConfiguration(SQLPropertyAdapter.java:117)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ConfiguratorMultiplexor$EditorParent.getBindingConfiguration(ConfiguratorMultiplexor.java:284)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ConfiguratorMultiplexor$EditorParent.tryCommit(ConfiguratorMultiplexor.java:266)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ConfiguratorMultiplexor.tryCommit(ConfiguratorMultiplexor.java:468)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog.doOK(DynamicOptsDialog.java:95)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog.access$000(DynamicOptsDialog.java:43)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog$1.actionPerformed(DynamicOptsDialog.java:64)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$500(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue$4.run(Unknown Source)
	at java.awt.EventQueue$4.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.WaitDispatchSupport$2.run(Unknown Source)
	at java.awt.WaitDispatchSupport$4.run(Unknown Source)
	at java.awt.WaitDispatchSupport$4.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.awt.WaitDispatchSupport.enter(Unknown Source)
	at java.awt.Dialog.show(Unknown Source)
	at java.awt.Component.show(Unknown Source)
	at java.awt.Component.setVisible(Unknown Source)
	at java.awt.Window.setVisible(Unknown Source)
	at java.awt.Dialog.setVisible(Unknown Source)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog.showDialog(DynamicOptsDialog.java:163)
	at com.inductiveautomation.factorypmi.designer.model.VisionDesignerImpl.openBindingDialog(VisionDesignerImpl.java:1125)
	at com.inductiveautomation.factorypmi.designer.property.editors.bb.DynamicOptionsButton.actionPerformed(DynamicOptionsButton.java:37)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at com.jidesoft.plaf.basic.BasicJideButtonListener.mouseReleased(Unknown Source)
	at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at javax.swing.plaf.basic.BasicTableUI$Handler.repostEvent(Unknown Source)
	at javax.swing.plaf.basic.BasicTableUI$Handler.mouseReleased(Unknown Source)
	at com.jidesoft.swing.DelegateMouseInputListener.mouseReleased(Unknown Source)
	at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$500(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue$4.run(Unknown Source)
	at java.awt.EventQueue$4.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)
===========================================================================================

this binding isn’t on the table itself but on a dropdown that references the table and its a power table. Thanks.

PGriffith · June 6, 2018, 4:59pm

It’s a bug, fixed in 7.9.8.

ikenna.nwaogu · June 6, 2018, 6:23pm

Thank you @PGriffith.

Myllka · June 8, 2018, 1:20pm

We’re in 7.9.8 since yesterday and we just run on the exact same bug @ikenna.nwaogu

jimmillereads · June 9, 2018, 4:04pm

I get the same error in 7.9.8 in two different apps. Really bothers the customer.
The UPDATE succeeds. It’s just that an error is reported, seemingly erroneously.

It’s a simple SELECT for a powertable. I’ve tried using runSFPrepQuery (and without SF). Error does not occur for MSSQL, but only for MySQL. Looks like the error is occurring in the java execution as the error reports:

GatewayException: SQL error for "UPDATE tablename
SET columnname = value
WHERE condition": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘condition’ at line 3
caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check[details=Summary]This text will be hidden[/details] the manual that corresponds to your MySQL server version for the right syntax to use near ‘condition’ at line 3

Not sure why it’s reporting generic “columnname” and generic “condition” in the error message. Line 3 is also not the correct line in my script. That’s what makes me think it’s referring to a line # in the underlying java code.

Any work around found yet?

pturmel · June 9, 2018, 8:44pm

Just upgrading to 7.9.8 won’t fix the corrupted bindings that were saved in 7.9.7. You have to delete and re-create the binding one more time.

Mr.K001 · June 11, 2018, 4:24pm

@pturmel
Yeah I noticed those errors still stuck around but thankfully deleting and redoing those is much easier than recreating a table that has all sorts of bells and whistles lol.
Glad to see that bug was fixed!!