Prepared statement parameter bug for "JAVA parameter index out of range (1 > number of parameters, which is 0)"

Question

I'm trying to insert a variable into the database.

Heres my code :

public static void main(String[] args) throws SQLException {
    Connection connection =null;
    DBhelper helper = new DBhelper();
    PreparedStatement statement = null;
    ResultSet resultSet;
    try{
        connection= helper.getConnection();

        System.out.println("baglantı olustu");

        String sql = "INSERT INTO pandemi(toplamvirus) VALUES ('?') ";
        statement = connection.prepareStatement(sql);
        statement.setInt(1,2);
        statement.executeUpdate(sql);
        int result = statement.executeUpdate();
    }
    catch (SQLException exception){
        helper.showErrorMessage(exception);
    }
    finally {
        statement.close();
        connection.close();
    }



    //String sql = "UPDATE pandemi SET toplamvirus='ff' ";





}

And the error is:

baglantı olustu
Error: Parameter index out of range (1 > number of parameters, which is 0).
Error code : 0

The database is: https://prnt.sc/rile7q

pretty sure the offsets are zero-based, i.e. the correct call is statement.setInt(0,2); — Sean Patrick Floyd
– Sean Patrick Floyd, Commented Mar 18, 2020 at 19:47
Use String sql = "INSERT INTO pandemi(toplamvirus) VALUES (?) "; without the apostrophes. It thinks your inserting the ? character — ControlAltDel
– ControlAltDel, Commented Mar 18, 2020 at 19:51
I did what you suggest. But now , here is another error : Error : 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 '? )' at line 1 — Emre
– Emre, Commented Mar 18, 2020 at 19:58
@ggb667 i guess i done it.. The error is gone!! But the variable didn't insert into database. Whatever , thank you so much — Emre
– Emre, Commented Mar 18, 2020 at 20:18
No for SQL the offsets are 1 based. Also, you are calling update twice. statement.executeUpdate(sql);//Do not do this, you already set SQL as the sql the statement is to process in the constructor. int result = statement.executeUpdate(); You should eliminate the ' characters surrounding the ? — Gregory Bishop
– Gregory Bishop, Commented Mar 18, 2020 at 20:42

Basil Bourque · Accepted Answer · 2020-03-18 20:06:49Z

2

Pass argument ordinal and a value to `setInt`

You are incorrectly using the PreparedStatement::setInt method. The first argument is an ordinal number (incorrectly documented in the Javadoc as an index). So first placeholder is # 1, second placeholder is # 2, and so on. You have only a single ? placeholder. Or were you trying to pass a value of two to be inserted into the database? Your question is not clear.

Use naked `?`

Also, you need to remove the single-quote marks from around the ? placeholder in your prepared statement. Using '?' means you want a single-character string consisting of a question mark inserted into the database. With the single-quotes in place, you have no placeholder in your SQL statement. So your setInt method will fail. If using a naked ?, the question mark will be recognized as a placeholder.

By the way, I suggest making a habit of using the SQL statement terminator, a semicolon.

String sql = "INSERT INTO pandemi ( toplamvirus ) VALUES ( ? ) ; ";

For more info, see another Answer of mine with a complete example app using the H2 database engine demonstrating INSERT with a placeholder in a prepared statement.

edited Mar 18, 2020 at 20:06

answered Mar 18, 2020 at 19:51

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

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

6 Comments

Emre Over a year ago

I did what you suggest. But not , i get this error :Error : 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 '? )' at line 1

Basil Bourque Over a year ago

@Emre Print the text of your SQL to the console, and compare with other examples. See Tutorial by Oracle.

Basil Bourque Over a year ago

And copy that SQL text from the console to an admin app, and run manually to eliminate Java from the question and focus on understanding proper SQL syntax.

Abra Over a year ago

Try the following: String sql = "INSERT INTO pandemi ( toplamvirus ) VALUES ( ? ) "; In other words, drop the semicolon in the string literal. (Maybe I should down-vote for an incorrect answer ;-)

Basil Bourque Over a year ago

@Abra You are claiming semicolon is not the statement terminator in SQL?

|

Collectives™ on Stack Overflow

Prepared statement parameter bug for "JAVA parameter index out of range (1 > number of parameters, which is 0)"

1 Answer 1

Pass argument ordinal and a value to `setInt`

Use naked `?`

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pass argument ordinal and a value to setInt

Use naked ?

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Pass argument ordinal and a value to `setInt`

Use naked `?`