Error while inserting data to the mySQL DB, with Java

Question

Good night everyone. I have these codes. I only copied here the codes which have the problem.

//This is my main method. I called my Database.class, made the connection and called the insertnewcustomer method.

public static void main(String[] args) {
    Database db = new Database();
    db.connectDB();
    db.insertNewCustomer(86754312, "arda", "zenci", 55418, 400);
.................//

//and here is my insertNewCustomer method which is inside the Database.class

public void insertNewCustomer(int num, String name, String surname, int phone, int debt){
    try {
        statement.executeUpdate("INSERT INTO Customer(customer_cardno, customer_name, customer_sirname, customer_phone, debt) VALUES(" + num + ", " + name  + ", " + surname + ", " + phone + ", " + debt + ")");
    } catch (SQLException e) {
        e.printStackTrace();
    }}

I cannot see any problems but I have a MySQLSyntaxErrorException

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'arda' in 'field list'
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:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1811)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725)
at Database.insertNewCustomer(Database.java:39)
at mainFrame.main(mainFrame.java:50)

Fabio · Accepted Answer · 2013-05-27 23:05:59Z

1

This is happening because you need to surround variables which are not integers with quotes, otherwise database would understand them as column, simply add quotes in not integers column, such as

statement.executeUpdate("INSERT INTO Customer(customer_cardno, customer_name, customer_sirname, customer_phone, debt) VALUES(" + num + ", '" + name  + "', '" + surname + "', " + phone + ", " + debt + ")");

I assumed customer_cardno, customer_phone, debt being i tegers columns, if they are nit just surround variabkes with quotes

answered May 27, 2013 at 23:05

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

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

Comments

Barranka · Accepted Answer · 2013-05-27 23:06:43Z

1

Two things:

First, SQL Injection risk. I suggest you take a look on Prepared Statements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Now, if you insist in building the query string like that, you should enclose the String values you are inserting in quotes:

statement.executeUpdate("INSERT INTO Customer " + 
    (customer_cardno, customer_name, customer_sirname, customer_phone, debt) " + 
    VALUES(" + num + ", '" + name  + "', '" + surname + "', '" + phone + "', " + debt + ")");
/*
 * Notice the single quotes arround "name", "surname" and "phone"
 */

answered May 27, 2013 at 23:06

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

2 Comments

Arda Oğul Üçpınar Over a year ago

Thanks, before you answer, I had found. =) Thanks again.

Barranka Over a year ago

@ArdaOğulÜçpınar Anyway, please read the link I provided to you. Your code is vulnerable to SQL injection. Prepared statements can save you a lot of pain (and they are quite easy to use)

Collectives™ on Stack Overflow

Error while inserting data to the mySQL DB, with Java

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related