0

Consider the below code,

String query1 = "insert into quizquestion (ques, quizId) values "
    + "('"+ques+"', '"+quizId+"')";
s = con.createStatement();
s.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);
rs = s.getGeneratedKeys();
if (rs.next()){
    quesId=rs.getString(1);
}

con.setAutoCommit(false);
String query2 = "insert into quizOption (option, quizQuesId, correct) values (?,?,?)";
ps = con.prepareStatement(query2);
for(int i=0; i<options.length; i++){
    ps.setString(1, options[i]);
    ps.setString(2, quesId);
    if(correctOption.equals((i+1)+"")){
        ps.setString(3, "1");
    }else{
        ps.setString(3, "0");
    }
    ps.addBatch();
}
int x[] = ps.executeBatch();
con.commit();

con.close();
return true;

The problem is my query1 is executed successfully, however I get an exception for query2

One sample error that I'm getting is as follows,

java.sql.BatchUpdateException: 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 'option, quizQuesId, correct) values ('o13','16','1')' at line 1

Any ideas why I'm getting this exception? Thanks in advance.

Saumil
  • 2,521
  • 5
  • 32
  • 54
  • 1
    One idea: "*option*" is a [reserved word][1] in MySQL. [1]: http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql – aro_tech Aug 04 '15 at 23:06
  • @aro_tech you are right, thanks – Saumil Aug 04 '15 at 23:12

1 Answers1

1

Your query2 contains a MySQL reserved keyword, option. This is likely causing the problem. Try enclosing the column name in quotes (") or backticks (`), like this:

String query2 = "insert into quizOption (\"option\", quizQuesId, correct) values (?,?,?)";
Mick Mnemonic
  • 7,808
  • 2
  • 26
  • 30
  • Thanks a ton, enclosing the column name option with quotes didn't helped so I simply changed the name of the column from the database and it works fine now. – Saumil Aug 04 '15 at 23:12
  • Using backticks (`\`option\``) should at least work, but of course the best alternative is to not use reserved words as column names. – Mick Mnemonic Aug 05 '15 at 00:18