Title: Java MySQLSyntaxErrorException
Tags: java;sql
Question: Hi i am currently developing an application to maintain a stock in a certain factory and i have trouble inserting the order to the database.. i try to fix this my own but i could not so if any one can help with this i`m glad.
So here is my error
com.mysql.jdbc.exceptions.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 'order (Add_Order_PID,Customer_idCustomer,Items_idItems,User_idUser,Color,Leather' at line floresheather@example.net(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1355)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1270)
at DB.Save_Update_Delete(DB.java:32)
at New_Order1.jButton4ActionPerformed(New_Order1.java:605)
at New_Order1.access$800(New_Order1.java:23)
at New_Order1$9.actionPerformed(New_Order1.java:389)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:289)
at java.awt.Component.processMouseEvent(Component.java:6505)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6270)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4861)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2719)
at java.awt.Component.dispatchEvent(Component.java:4687)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:729)
at java.awt.EventQueue.access$200(EventQueue.java:103)
at java.awt.EventQueue$3.run(EventQueue.java:688)
at java.awt.EventQueue$3.run(EventQueue.java:686)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:702)
at java.awt.EventQueue$4.run(EventQueue.java:700)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:699)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
My code is
for (int i = 0; i < jTable1.getRowCount(); i++) {
String $Name = "", $Color = "", $Leather = "", $s38 = "", $s39 = "", $s40 = "", $s41 = "", $s42 = "", $s43 = "", $s44 = "", $s45 = "", $s46 = "", Total = "", ItemId = "";
$Name += jTable1.getValueAt(i, 0)+"";
$Color += jTable1.getValueAt(i, 1)+"";
$Leather += jTable1.getValueAt(i, 2)+"";
$s38 += jTable1.getValueAt(i, 3)+"";
$s39 += jTable1.getValueAt(i, 4)+"";
$s40 += jTable1.getValueAt(i, 5)+"";
$s41 += jTable1.getValueAt(i, 6)+"";
$s42 += jTable1.getValueAt(i, 7)+"";
$s43 += jTable1.getValueAt(i, 8)+"";
$s44 += jTable1.getValueAt(i, 9)+"";
$s45 += jTable1.getValueAt(i, 10)+"";
$s46 += jTable1.getValueAt(i, 11)+"";
Total += jTable1.getValueAt(i, 12)+"";
ItemId += jTable1.getValueAt(i, 13)+"";
DB.Save_Update_Delete("insert into order (Add_Order_PID,Customer_idCustomer,Items_idItems,User_idUser,Color,Leather,s38,s39,s40,s41,s42,s43,s44,s45,s46,Total) values ('"+pid+"','"+Integer.parseInt(customerID.getText())+"','"+Integer.parseInt(ItemId)+"','"+Integer.parseInt(UserID.getText())+"','"+$Color+"','"+$Leather+"','"+$s38+"','"+$s39+"','"+$s40+"','"+$s41+"','"+$s42+"','"+$s43+"','"+$s44+"','"+$s45+"','"+$s46+"','"+Total+"')");
}
Comment: To elaborate on the "use prepared statement": Prepared Statements are more efficient and are safe from SQL injection attacks, whereas concatenated SQL strings (as you use) are not.

Comment: @GauravGupta I'd suggest you ask a separate question for this; there are certain solutions for this problem (even if the number of conditions varies, e.g. using dummy OR clauses for the bind variables not supplied by the client code), but some of the solutions are RDBMS specific (e.g. using SYS_CONTEXT in Oracle).

Comment: @GauravGupta For SYS_CONTEXT: https://asktom.oracle.com/pls/asktom/f?p=75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c075b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0P11_QUESTION_ID:1669972300346534908 and https://asktom.oracle.com/pls/asktom/f?p=75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c075b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0P11_QUESTION_ID:1288401763279 for some examples (Oracle-specific), for a generic solution you can use something like `WHERE 1=1 AND (col1 = ?1 or ?1 IS NULL) AND (col2 = ?2 or ?2 IS NULL) ...` - you basically add a subclause for each optional parameter and provide NULL if the user didn't set it. Sorry, I couldn't find the link explaining this technique - it should appear somewhere in the AskTom pages I mentioned.

Comment: @GauravGupta One paper summarizing both approaches (PDF): https://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=2369029908548088969

Comment: @FrankSchmitt Is their any way in PreparedStatement to add condition under where clause,or i need to determine all conditions first and then make a PreparedStatement in single go. Sorry this is off topic.

Comment: @FrankSchmitt can you point to some useful resource, i will study that and if i will face any problem, then i will open a new question. I thought this is a stupid question so i didn't open a question. Any help will be appreciated.

Comment: Could you Please add table structure

Comment: Use prepared statement


Here is another answer: insert into order (Add_Order_PID,Customer_idCustomer,Items_idItems,User_idUser,Color,Leather,s38,s‌​39,s40,s41,s42,s43,s44,s45,s46,Total) values ('8','1','2','1','Black','Leather 1','2','0','0','0','0','0','0','0','0','2')
Try your sql statement directly in the mysql console. It is going to give you more information about what the real problem is. Maybe you are violating some foreign key.

Here is another answer: you are inserting int values with in quotes('')
DB.Save_Update_Delete("insert into order (Add_Order_PID,Customer_idCustomer,Items_idItems,User_idUser,Color,Leather,s38,s39,s40,s41,s42,s43,s44,s45,s46,Total) values ('"+pid+"','"+Integer.parseInt(customerID.getText())+"','"+Integer.parseInt(ItemId)+"','"+Integer.parseInt(UserID.getText())+"','"+$Color+"','"+$Leather+"','"+$s38+"','"+$s39+"','"+$s40+"','"+$s41+"','"+$s42+"','"+$s43+"','"+$s44+"','"+$s45+"','"+$s46+"','"+Total+"')"
Better use PreparedStatement and learn from it here
Comment for this answer: @user3615913 edit your question,show your schema and you updated JDBC codes.Also check each fields to know whether it has got any value or its NULL.Some of your fields are not null so if you insert NULL then also it will cause problem.
Comment for this answer: Not sure if that's the problem but normally a SQL server will be smart enough to take in String values (single quotes) for int.
Comment for this answer: @Shashi I see, I tested with Oracle and MSSQL though, wrapping single quotes around an integer has no issue. However for MYSQL I was reading on [this](http://stackoverflow.com/questions/4918512/disadvantages-of-quoting-integers-in-a-mysql-query) and noticed the OP over there is comparing on quoted integers so I guess it works for his MySQL floresheather@example.net.
Comment for this answer: @JqueryLearner  i tried the  Prepared Statement also but it give the same error

Here is another answer: Check whether Add_Order_PID is an integer. If it is integer, don't put into double quotes.
For any numeric data type, don't put inside the double-quotes..
Better to use PreparedStatement for insertion
Comment for this answer: Please provide table structure.. Use pastebin.com and provide the url.. it would be better or update the question with table structure
Comment for this answer: @user3615913 could you please replace DB.Save_Update_Delete to System.out.println and paste the console output...
Comment for this answer: please replace DB.Save_Update_Delete statement to System.out.println and run and  paste the console output...
Comment for this answer: I remove the double quotes, but he i`m getting the same error. beside when i remove double quotes its looks like a string
Comment for this answer: i tried the  Prepared Statement also but it give the same error
Comment for this answer: `idOrder` INT NOT NULL AUTO_INCREMENT ,
`Add_Order_PID` INT NOT NULL ,
`Customer_idCustomer` VARCHAR(45) NOT NULL ,
`Items_idItems` VARCHAR(25) NOT NULL ,
`User_idUser` INT NOT NULL ,
`Color` VARCHAR(45) NULL ,
`Leather` VARCHAR(45) NULL ,
`s38` VARCHAR(45) NULL ,
`s39` VARCHAR(45) NULL ,
`s40` VARCHAR(45) NULL ,
`s41` VARCHAR(45) NULL ,
`s42` VARCHAR(45) NULL ,
`s43` VARCHAR(45) NULL ,
`s44` VARCHAR(45) NULL ,
`s45` VARCHAR(45) NULL ,
`s46` VARCHAR(45) NULL ,
`Total` VARCHAR(45) NULL ,`
Comment for this answer: i tried it but it says void type not allowed here.., i tried the prepared statement also but it give the same error
Comment for this answer: `insert into order (Add_Order_PID,Customer_idCustomer,Items_idItems,User_idUser,Color,Leather,s38,s39,s40,s41,s42,s43,s44,s45,s46,Total) values ('8','1','2','1','Black','Leather 1','2','0','0','0','0','0','0','0','0','2')`

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 8, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:05/08/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><sql>', 'Title': 'Java MySQLSyntaxErrorException', 'ViewCount': 77, 'id': 23538791, 'user_AboutMe': '', 'user_AccountId': 4441451.0, 'user_DisplayName': 'Shansl95', 'user_Reputation': 1.0, 'user_Views': 3.0, 'user_id': 3615913.0}}