0

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

ggb667
  • 1,881
  • 2
  • 20
  • 44
Emre
  • 53
  • 6
  • pretty sure the offsets are zero-based, i.e. the correct call is `statement.setInt(0,2);` – Sean Patrick Floyd Mar 18 '20 at 19:47
  • Use String sql = "INSERT INTO pandemi(toplamvirus) VALUES (?) "; without the apostrophes. It thinks your inserting the ? character – ControlAltDel Mar 18 '20 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 Mar 18 '20 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 Mar 18 '20 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 ? – ggb667 Mar 18 '20 at 20:42

1 Answers1

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 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 – Emre Mar 18 '20 at 19:57
  • @Emre Print the text of your SQL to the console, and compare with other examples. See [Tutorial by Oracle](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). – Basil Bourque Mar 18 '20 at 19:59
  • 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. – Basil Bourque Mar 18 '20 at 20:06
  • 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 ;-) – Abra Mar 19 '20 at 01:03
  • @Abra You are claiming semicolon is not the statement terminator in SQL? – Basil Bourque Mar 19 '20 at 05:07
  • The SQL string you pass to a `PreparedStatement` object should not be terminated by a semicolon. – Abra Mar 19 '20 at 06:20