java mysql database insert using prepared statement

Question

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!

Are 1, 2, 3, 4, 5, 6, 7, 8, 9 actual columns in your block table? — ernest_k
– ernest_k, Commented Mar 24, 2018 at 8:15
is Parameter index starts from 1 ? maybe you should start with pst.setInt(0, array[0]); — mBogaz
– mBogaz, Commented Mar 24, 2018 at 8:16
@mBogaz Yes, JDBC parameter indexes start from 1 ... yes, it's a pain — MadProgrammer
– MadProgrammer, Commented Mar 24, 2018 at 8:16
that was a false alarm, it's still not working, but i'll keep trying hahahaha — blah
– blah, Commented Mar 24, 2018 at 8:33

ernest_k · Accepted Answer · 2018-03-24 08:22:49Z

4

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.

answered Mar 24, 2018 at 8:22

ernest_k

45.5k5 gold badges58 silver badges107 bronze badges

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

Collectives™ on Stack Overflow

java mysql database insert using prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related