Syntax MySql Insert Error

Question

I got a Sql syntax errorcom.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 '8 , 0' at line 1,(i take attribute by input),my table in mysql is,for example i post an insert:

INSERT INTO itshop.supply (id, idSupplier, dateTime, quantity, totalCost) VALUES ('2', '4', '2009-1-4', '24', '245');

int rs2 = st2.executeUpdate("INSERT INTO supply VALUES ( "
                                    + id + " , " + idSupplier + " , "
                                    + dateTime + " , " + idProduct + " ,"
                                    + Quantity + " , " + price + "");

                    if (rs2 == 1) {
                        JOptionPane.showMessageDialog(Sale,
                                "Product Ordered", "Supply",
                                JOptionPane.DEFAULT_OPTION);
                    }

And what is the actual error? MySQL tells you where it fails in the query. — arkascha
– arkascha, Commented Jan 25, 2014 at 12:08
This is completely unsafe, is opens up the possibility of SQL injection. Sue prepared statements or escape every argument / value. — Patrick Savalle
– Patrick Savalle, Commented Jan 25, 2014 at 12:11

SpringLearner · Accepted Answer · 2014-01-25 12:39:10Z

1

You need to keep the varchar types within '' like this

int rs2 = st2.executeUpdate("INSERT INTO supply VALUES ( '"
                                    + id + " ',' " + idSupplier + "' , '"
                                    + dateTime + " ', '" + idProduct + " ','"
                                    + Quantity + " ', '" + price + "'");

Use PreparedStatement instead of Statement and this removes the confusion of '

Simple example

PreparedStatement pt=connection.prepareStatement(insert into test values(?,?));
pt.setString(1,"hi");
pt.setInt(2,1);
pt.executeUpdate();

For your insert operation PreparedStatemet will be

PreparedStatement pt=connection.prepareStatement("INSERT INTO supply VALUES (?,?,?,?,?,?)")
pt.setString(1,id );
pt.setString(2,idSupplier );
pt.setString(3,dateTime );
pt.setString(4,idProduct );
pt.setString(5,Quantity );
pt.setString(6,price );
int rs2=pt.executeUpdate();

edited Jan 25, 2014 at 12:39

answered Jan 25, 2014 at 12:15

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OiRc Over a year ago

the attribute datetime is a type DateTime in sql ,with java i use this as a string for example string date="2014-03-02", is a good practice?

SpringLearner Over a year ago

@CiMat no this is not a good practice.If it datetime then you should also use in date object in java.

peter.petrov · Accepted Answer · 2014-01-25 12:14:51Z

1

You need to add the apostrophes ' in the String which represents your INSERT statement. Also, you need to add the right bracket ). You don't have them. Even better, use a PreparedStatement as this way of creating INSERT statements (by using direct concatenation of the values) is a bad practice.

edited Jan 25, 2014 at 12:14

answered Jan 25, 2014 at 12:08

peter.petrov

39.6k23 gold badges114 silver badges193 bronze badges

Collectives™ on Stack Overflow

Syntax MySql Insert Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related