Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A strange MySQLSyntaxErrorException

 
Paulius Kukulskis
Greenhorn
Posts: 10
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to update my jtable(change value and hit enter) and I get the error. The error is not full due to the size. I think the rest of it is just irrelevant information produced from c3p0 tool for pooling connections.

Presumptions At first, I thought it might be the problem of c3p0, cause in order to use it for prepared statements I would have to edit configuration file/change settings. But I used the traditional get connection from DriverManager way to find out it has nothing to do with it.

Another presumption - is some data type problems. Because I type 3.00 and the error says ''Flat' = '3.0' where ID = '4'' at line 1 Text test line within ImportData method is not executed. So it means it crashes while executing SQL statement. In my database table columns except ID and date are of type float(9,2), meaning total digits = 9; digits "after dot" = 2. And I do have DecimalFormatRenderer class wich might have something to do with this.
com.mysql.jdbc.exceptions.jdbc4.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 ''Flat' = '3.0' where ID = '4'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at components.TableWithBottomLine$ImportData.<init>(TableWithBottomLine.java:174)
at components.TableWithBottomLine.tableChanged(TableWithBottomLine.java:147)
at javax.swing.table.AbstractTableModel.fireTableChanged(Unknown Source)
at javax.swing.table.AbstractTableModel.fireTableCellUpdated(Unknown Source)
at components.TableWithBottomLine$MyTableModel.setValueAt(TableWithBottomLine.java:251)
at javax.swing.JTable.setValueAt(Unknown Source)
at javax.swing.JTable.editingStopped(Unknown Source)
at javax.swing.AbstractCellEditor.fireEditingStopped(Unknown Source)
at javax.swing.DefaultCellEditor$EditorDelegate.stopCellEditing(Unknown Source)
at javax.swing.DefaultCellEditor.stopCellEditing(Unknown Source)
at javax.swing.JTable$GenericEditor.stopCellEditing(Unknown Source)
at javax.swing.DefaultCellEditor$EditorDelegate.actionPerformed(Unknown Source)
at javax.swing.JTextField.fireActionPerformed(Unknown Source)
at javax.swing.JTextField.postActionEvent(Unknown Source)
at javax.swing.JTextField$NotifyAction.actionPerformed(Unknown Source)
at javax.swing.SwingUtilities.notifyAction(Unknown Source)
at javax.swing.JComponent.processKeyBinding(Unknown Source)
at javax.swing.JComponent.processKeyBindings(Unknown Source)
at javax.swing.JComponent.processKeyEvent(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.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(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$400(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$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.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$1.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)


 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To be honest I see 3 issues with your code. First and most important: I don't think you can do something like this in your prepared statementI think column names can't be replaced with placeholders. Try using and see if it works. If it does, you know what's causing the issues.

The other two are you are setting the parameters as String while they are not strings, but probably a double and an int. So change your codetoand see if this works. You should of course make sure that your variables have the appropriate type.

Hope it helps!
Kind regards,
Roel

PS. Using underscores in variable names is not recommended, according to the naming conventions it should be camelCase.
 
Paulius Kukulskis
Greenhorn
Posts: 10
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thank you for your help. Much appreciated. I followed on every advise you gave me. Now, I have a question. When "extracting" float value from object of type Object is it better to use cast or .toString method? Working code:
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paulius Kukulskis wrote:I thank you for your help. Much appreciated. I followed on every advise you gave me. Now, I have a question. When "extracting" float value from object of type Object is it better to use cast or .toString method?

Invoking the toString() method will not give you a float, but it gives you a String. So using instanceof operator and a cast will be the only way to go.

But maybe you could use this method signature and make sure you pass the appropriate arguments to this methodAnd you should definitely use meaningful variable names, because a, b, and c make it very hard to read (and maintain) your code; I think we can agree columnName, newValue, and id are 1000x more meaningful.

And another tip I like to share: if you are using Java 7 or beyond, you should use try-with-resources statement. Using this Java 7 feature you don't have to care about closing your resources anymore, it will be done automatically for you.

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic