SQL query syntax is wrong

Question

        Class.forName("com.mysql.jdbc.Driver");
        Connection con=DriverManager.getConnection("jdbc:mysql://localhost/exam","root","password");
        String q="insert into group"
         +"(gname,des)" 
        +"values(?,?)";

        PreparedStatement p=con.prepareStatement(q);
        p.setString(1,gname);
        p.setString(2,des); 
        p.executeUpdate();
        con.close();

This is my code for adding a group. But the error says that my query syntax is wrong. I have tried single cores with the feilds in my table but still getting that error. Firstly i was using create statement that was also giving the same error. Please tell what us wrong with this query?

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 'group(gname,des)values('Science','')' at line 1
    sun.reflect.GeneratedConstructorAccessor16.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    com.mysql.jdbc.Util.getInstance(Util.java:408)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
    com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
    com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
    org.apache.jsp.groupreg_jsp._jspService(groupreg_j

Are both gname and des varchars in the table?

yaswanth
– yaswanth

2017-08-06 05:17:56 +00:00
Commented Aug 6, 2017 at 5:17 — yaswanth
– yaswanth, Commented Aug 6, 2017 at 5:17
Yes, both of them are varchar in group table.

rango
– rango

2017-08-06 05:34:32 +00:00
Commented Aug 6, 2017 at 5:34 — rango
– rango, Commented Aug 6, 2017 at 5:34

elixenide · Accepted Answer · 2017-08-06 11:55:37Z

4

Your query, after stripping out the line breaks in your code, is this:

insert into group(gname,des)values(?,?)

In other words, you are trying to insert into a table named group. This is not allowed because GROUP is a reserved word in MySQL.

From the manual:

Most of the reserved words in the table are forbidden by standard SQL as column or table names (for example, GROUP).

You need to pick a different table name (recommended) or surround the word group with backticks, like this:

INSERT INTO `group` (gname, des) VALUES (?, ?)

See the manual entry on Schema Object Names.

Also, make sure you have appropriate spaces; the lack of spacing you have now will cause additional problems in many contexts.

edited Aug 6, 2017 at 11:55

answered Aug 6, 2017 at 5:30

elixenide

45k16 gold badges79 silver badges103 bronze badges

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

1 Comment

Michael - sqlbot Over a year ago

N.B. "forbidden by standard SQL." In MySQL, you can still backtick this... INSERT INTO `GROUP` .... Higher up on the same page, Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Schema Object Names.”

N00b Pr0grammer · Accepted Answer · 2017-08-06 10:15:31Z

0

You could have written the complete query in a single line with proper spacing in Java, instead of the SQL way of writing queries. You should be able to identify that the first mistake is with the spacing. This can be corrected from this (what you've used):

String q = "insert into group"
          +"(gname,des)" 
          +"values(?,?)";

to this (the modified query):

String q = "insert into group (gname, des) values(?, ?)";

The above change once made, makes it readable. The next thing as rightly pointed out by @EdCottrell, refrain from using restricted keywords as table names. Depending on the DB type, there could be issues related to this.

Hope this helps!

answered Aug 6, 2017 at 10:15

N00b Pr0grammer

4,6776 gold badges36 silver badges49 bronze badges

3 Comments

Mark Rotteveel Over a year ago

The missing spaces are not a problem, because the parser can use the ( and ) as token separators.

N00b Pr0grammer Over a year ago

@MarkRotteveel, separators help with the table_name(column_name1,column_name2) only but like this table_name(column_name1,column_name2)values(column_value1,column_value2) it won't. Hence the error.

Mark Rotteveel Over a year ago

If that were the case, the syntax error would only contain the values list, and not the table name and columns list.

Collectives™ on Stack Overflow

SQL query syntax is wrong

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related