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

• yaswanth: Are both gname and des varchars in the table?
• rango: Yes, both of them are varchar in group table.

Answer 1

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.

• Michael - sqlbot: 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."

Answer 2

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!

• Mark Rotteveel: The missing spaces are not a problem, because the parser can use the ( and ) as token separators.
• N00b Pr0grammer: @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: If that were the case, the syntax error would only contain the values list, and not the table name and columns list.