CODEWITHSUNDEEP
javamysqlsqlprepared-statement
blah
blah

Reputation: 141

java mysql database insert using prepared statement

I've seen a lot of questions regarding this topic but none seems to be the correct solution. I've trying to insert a new row into my database using preparedstatements but I'm getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1, 2, 3, 4, 5, 6, 7, 8, 9, guess, answer, value) VALUES (17, 187, 224, 276, 19, ' at line 1

This is my code:

String query = "INSERT INTO block (1, 2, 3, 4, 5, 6, 7, 8, 9, guess, answer, value)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pst = con.prepareStatement(query);
        pst.setInt(1, array[0]);
        pst.setInt(2, array[1]);
        pst.setInt(3, array[2]);
        pst.setInt(4, array[3]);
        pst.setInt(5, array[4]);
        pst.setInt(6, array[5]);
        pst.setInt(7, array[6]);
        pst.setInt(8, array[7]);
        pst.setInt(9, array[8]);
        pst.setString(10, String.valueOf(letter[guess]));
        pst.setString(11, String.valueOf(answer));
        pst.setInt(12, value);

        pst.executeUpdate();

This is my db: sql

I feel like I'm missing something really simple. . .

Any help would be highly appreciated! Thank you!

Upvotes: 0

Views: 735

Answers (1)

ernest_k
ernest_k

Reputation: 45309

You have a syntax problem. Here's what MySql documentation says:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So you probably need to quote your field names:

String query = "INSERT INTO block (\"1\", \"2\", \"3\", \"4\", \"5\"
 , \"6\", \"7\", \"8\", \"9\", \"guess\", \"answer\", \"value\")" +
    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

You need to be sure whether the guess, answer, and value fields' case-sensitivity applies.

Upvotes: 4

Related Questions