Getting ArrayOutOfBoundsException for Parameters in Preparedstatement

[kkuez]

`try (PreparedStatement statement = getConnection().prepareStatement( "update Gamedata set timestamp='?', mines=?, money=? where user=?")) {

        for(GameData gameData: gamedataList) {
            statement.setString(1, gameData.getDateTime().toString());
            statement.setInt(2, gameData.getMines());
            statement.setFloat(3, gameData.getMoney());
            statement.setLong(4, gameData.getUserId());
            statement.addBatch();
        }
        statement.executeUpdate();

    } catch (SQLException e) {
       e.printStackTrace();
    }`

...resulting in Exception in thread "Thread-0" java.lang.ArrayIndexOutOfBoundsException: Index 3 out of bounds for length 3 at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:106) at org.sqlite.jdbc3.JDBC3PreparedStatement.setLong(JDBC3PreparedStatement.java:330) at tapir.DBService.updateGamedata(DBService.java:289) at tapir.ares.AresModule.lambda$startUpdateGamedataThread$0(AresModule.java:40) at java.base/java.lang.Thread.run(Thread.java:829)

Schema is CREATE TABLE "Gamedata" (minesINTEGER,moneyREAL,userNUMERIC,timestampTEXT, PRIMARY KEY(user) )

When debugging, I can see that the parameters size is 3 instead of four.

[wassinki]

I think the problem is in your query. The first questionmark is quoted. So it is just a string.

[kkuez]

Oh my... Guess what, you were right. Sorry for wasting your time here, you may close this.

[xerial]

No worries! :)

[noloader]

@xerial,

Thanks for pointing out the problem with quoting around the question mark.

In my case, I need the single quote because of the LIKE operator:

String query = "SELECT FirstName,LastName FROM Customer WHERE LastName LIKE '?%'";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, lastName);

The query will match an exact last name like "Brown", if lastName = "Brown". It will also stem, and match "Brooks" and "Brown" if lastName = "Br".

Trying to run the code results in the problem/fix you discussed: java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0.

If I change the code to escape the single quotes:

String query = "SELECT FirstName,LastName FROM Customer WHERE LastName LIKE ''?%''";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, lastName);

Then I receive org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "?": syntax error).

I can't tell if this is from the JDBC layer or the SQLite engine. (Databases are not my area of expertise).

Would you happen to know how to work around it?