CODEWITHSUNDEEP
javasqljdbc
JLA
JLA

Reputation: 329

JDBC Insert Query Error

I'm new to Java and JDBC.. Currently I'm working on a school project for a Java course. I'm trying to pass some java variables to an Insert SQL query using JDBC, but I'm getting the follow exception:

SQLException: ORA-00917: missing comma

This is the instruction:

stmt.executeUpdate("INSERT INTO question (idnum, question, option1, option2, option3, option4, answer) VALUES("+4+", "+question+", "+option1+", "+option2+", "+option3+", "+option4+", "+answer+")");

Any ideas how to fix the query?

Upvotes: 0

Views: 1454

Answers (4)

Brian Roach
Brian Roach

Reputation: 76918

First, the reason you are receiving an error is that you have a syntax error in your SQL. Simply printing the String you're creating would show you the problem.

That said, you should be using prepared statements. THere is simply no reason to construct the SQL manually. Not only does it avoid the problem you're having, it all but eliminates the chance your code is susceptible to SQL injection.

String query = "INSERT INTO question (idnum, question, option1, option2, option3, option4, answer) VALUES (?,?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setInt(1,4);
pstmt.setString(2,question);
// ... set all your params
pstmt.execute();

Upvotes: 3

Reimeus
Reimeus

Reputation: 159874

Try this

String sql = 
   "INSERT INTO question (idnum, question, option1, option2, option3, option4, answer)       VALUES(?, ?, ?, ?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.setInt(1, 4);
preparedStatement.setString(2, question);
preparedStatement.setString(3, option1);
preparedStatement.setString(4, option2);
preparedStatement.setString(5, option3);
preparedStatement.setString(6, option4);
preparedStatement.setString(7, answer);

int rowsAffected = preparedStatement.executeUpdate();

Upvotes: 2

user1279522
user1279522

Reputation:

Try this:

 stmt.executeUpdate("INSERT INTO question (idnum, question, option1, option2, option3, option4, answer) VALUES("+4+", '"+question+"', '"+option1+"', '"+option2+"', '"+option3+"', '"+option4+"', '"+answer+"')"

Upvotes: 0

Azad
Azad

Reputation: 5055

Change the double quotation" to to a single quote ' like this

String query = "INSERT INTO question (idnum,question) values 
 (4 , "'"+question+"'")";

Note that only string needs ', you can pass integers directly without using ', however, I prefer to use PreparedStatement, as it's much easier and very strong with SQL injections.

    PreparedStatement pr = conection.prepareStatement("insert into question(...) values(?,?,?,?...)");
    pr.setInt(1,4);
    pr.setString(2,"Some string");
//.....
 pr.executeUpdate();

Upvotes: 1

Related Questions