Getting an error in update statement when accessing mysql from java

String sql = "update `library`.`memebers` set 'STATUS'='" + "1" +"' where mem_id = '"+str+"'";

int i = st.executeUpdate(sql);

Error in 2nd line above:

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 ''status' = '1' where mem_id = '656597'' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        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:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
        at Frames.Addmembers.jButton1ActionPerformed(Addmembers.java:181)
        at Frames.Addmembers.access$000(Addmembers.java:21)
        at Frames.Addmembers$1.actionPerformed(Addmembers.java:83)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
        at java.awt.Component.processMouseEvent(Component.java:6263)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at java.awt.Component.processEvent(Component.java:6028)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4574)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

2012-04-03 20:44
by Manish

This is a SQL issue. I am 99.9 percent sure this has nothing to do with Java. You should do a print out with the actual query that you are sending to mysq - ControlAltDel 2012-04-03 20:48

Are you sure with: library.memebers instead of library.member - lkdg 2012-04-04 09:14

Field name in single quotes. It should be either without quotes, or inside back single quotes: ```:

String sql = "update library.memebers set `STATUS`='1' where mem_id = '"+str+"'";

2012-04-03 20:47
by Eugene Retunsky

Even though with the above corrections,I am not getting any error. But still database is not getting updated - Manish 2012-04-03 22:00

status is the name of a column, and as such, it should be either status or `status`.

Entities are either written plain, or they're quoted with ``. (Backticks, not apostrophes.)

Unless you're using reserved field names for column names (which you shouldn't do), you do not have to quote fields.

String sql = "update library.memebers set STATUS = 1 where mem_id = " + Integer.valueOf(str);

Note that I removed the quotes around the status and the mem_id. I'm assuming that both are integer fields, and if they are, you should not be quoting their values. Integer fields should have integer values, not strings that evaluate to integer values. (Note though that you would never want to just blindly assume a string is an integer and concatenate it into a query -- always make certain it's a proper integer first)

2012-04-03 20:45
by Corbin

Even though with the above corrections,I am not getting any error. But still database is not getting updated - Manish 2012-04-03 21:53

@Manish I would make sure the mem_id matches something in the table then. As someone suggested in a comment, you may want to echo out the SQL and try to run it by hand - Corbin 2012-04-03 22:00

Can you help me ot with this String sql = "insert into library.books values("+bid+","+bname+","+publisher+","+author+","+Integer.parseInt(price)+","+rackno+","+Date.valueOf(dop)+","+username; try { int i = st.executeUpdate(sql); if(i == 1) JOptionPane.showMessageDialog(null,"Book Added" ,"SUCESS",JOptionPane.PLAINMESSAGE); else JOptionPane.showMessageDialog(null,"Some problem occured in adding book","ERROR",JOptionPane.ERROR_MESSAGE); } catch(Exception ex) { ex.printStackTrace(); - Manish 2012-04-03 22:21

@Manish If you print out the value of sql, what does it say - Corbin 2012-04-03 22:32

Mr.Corbin thanx a lot for the help and concern.I have sorted it out yesterday by myself.If you permit, can I seek ur help in future as well - Manish 2012-04-04 19:19