• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

A strange MySQLSyntaxErrorException

 
Greenhorn
Posts: 10
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)


 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic