Java - You have an error in your SQL syntax;

Question

I just want to make a simple insert into a table in my sql data base. Nonetheless I get this error eventhough I tried it in several ways. I got a table named "values" in my database "test" with a simple column for id "idValues" and a column for int values called "value". I just want to insert a simple record with 1 for ID and 1 for value (1/1). Is there maybe some problem with certain versions?

package jdbcdemo;

import java.sql.*;

public class Driver {

public static void main(String[] args) {
    // TODO Auto-generated method stub

    try {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false", "root", "rmi1337");

        Statement statement = myConn.createStatement();

        String sql = "INSERT INTO values " + "(idValues, value)" + "VALUES (1, 1)";
        statement.executeUpdate(sql);


    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }




}


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 'values(idValues, value)VALUES (1, 1)' 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:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at jdbcdemo.Driver.main(Driver.java:22)

As you can see, values is a keyword in SQL. If you're going to call your table "values" (and I suggest you don't), you would have to escape it; e.g. `values` — khelwood
– khelwood, Commented May 26, 2017 at 9:39
thank you, should have thought of that! well they could have also tolde me this when creating the table though — Julian Herbold
– Julian Herbold, Commented May 26, 2017 at 9:52
You also don't need to do the string concatenation at all for your sql variable. It be cleaner looking at least if you removed the concatenation and made it one big string. — Timothy G.
– Timothy G., Commented May 26, 2017 at 11:21

Rahul · Accepted Answer · 2017-05-26 09:42:59Z

4

Both Values and value is reserve word and thus needs escaping like

String sql = "INSERT INTO `values` " + "(idValues, `value`)" + " VALUES (1, 1)";

answered May 26, 2017 at 9:42

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

davidxxx · Accepted Answer · 2017-05-26 09:43:21Z

0

It misses a white space.

'values(idValues, value)VALUES (1, 1)'

should be :

'values(idValues, value) VALUES (1, 1)'

So you should write it :

String sql = "INSERT INTO values " + "(idValues, value) " + " VALUES (1, 1)";

But anyway, you should not use keyword or reserved word for your metadata.
A "values" table is misleading and besides it may work according to the DBMS used and also the OS used.

In this link presenting how MySQL handle the case sensitivity, you can read :

This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.

In this other link you can get information about MySQL reserved keys.

answered May 26, 2017 at 9:43

davidxxx

132k23 gold badges231 silver badges228 bronze badges

Collectives™ on Stack Overflow

Java - You have an error in your SQL syntax;

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related