CODEWITHSUNDEEP
javasqlsyntaxworkbench
Julian Herbold
Julian Herbold

Reputation: 537

Java - You have an error in your SQL syntax;

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)

Upvotes: 1

Views: 7318

Answers (2)

davidxxx
davidxxx

Reputation: 131546

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.

Upvotes: 0

Rahul
Rahul

Reputation: 77936

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

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

Upvotes: 4

Related Questions