org.h2.jdbc.JdbcSQLException: Invalid value 2 for parameter parameterIndex [90008-76]

[Marcel]

Hi!

I get an exception from h2 concerning a prepared statement, but I
can't figure out why.
Following scenario:
I've a table in which I save lines of a matrix. The table has three
columns. The first column is the rowId. The second column is the
matrix line of that rowId and the third column is the matrix line for
that rowId after I did some calculations. Now on the first run when
the table is empty and I insert the rowIds and the normal matrix lines
via prepared statements everything works fine. But in the 2nd run,
when I want to store the changed matrix lines in the 3rd column I get
the following error:

org.h2.jdbc.JdbcSQLException: Invalid value 2 for parameter
parameterIndex [90008-76]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getInvalidValueException(Message.java:169)
at
org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:
1224)
at
org.h2.jdbc.JdbcPreparedStatement.setInt(JdbcPreparedStatement.java:
293)
at somePath.db.DB_Dao.updateMatrixLine(DB_Dao.java:222)
at somePath.calc.Calculator.calculateMatrix(Calculator.java:149)
at somePath.Starter.begin(Starter.java:239)

I know what this error means, but as I said already, I can't figure
out why. My source looks the following:

Source parts out of the calculateMatrix() method
------------------------------------------------------------------------
// Create Prepared Statement
if (insert) {
this.db_dao.createInsertMatrixLinePS(matrixTable, targetColumn);
}
else {
this.db_dao.createUpdateMatrixLinePS(matrixTable, targetColumn);
}
...
if (insert) {
retVal = this.db_dao.insertMatrixLine(i, sb.toString()); <-- i is
the int of the for loop in which this code is in!
}
else {
retVal = this.db_dao.updateMatrixLine(i, sb.toString()); <--
this is line 149 from the error message!
}



Some methods from the DB_Dao class
------------------------------------------------------------
/**
* Creates the insertMatrixLineStmt Prepared Statement.
* @param table
* @param column
*/
public void createInsertMatrixLinePS(String table, String column)
{
try {
this.insertMatrixLineStmt = this.dbConnection.prepareStatement(
"INSERT INTO " + table + " (seqId," + column +
") VALUES (?,?)");
} catch (SQLException e) {
logr.fatal("Couldn't create Prepared Statement " +
"'insertMatrixLineStmt'! SQLException: " + e);
System.exit(0);
}
}


/**
* Stores (INSERT) a matrix line via a Prepared Statement in the
database.
* @param seqId
* @param line
* @return
*/
public int insertMatrixLine(int seqId, String line) {
int retVal = 0;

try {
this.insertMatrixLineStmt.setInt(1, seqId);
this.insertMatrixLineStmt.setString(2, line);
retVal = this.insertMatrixLineStmt.executeUpdate();
} catch (SQLException e) {
logr.fatal("Couldn't store matrix line '" + line + "'.
SQLException: " + e);
System.exit(0);
}

return retVal;
}


/**
* Creates the updateMatrixLineStmt Prepared Statement.
* @param table
* @param column
*/
public void createUpdateMatrixLinePS(String table, String column)
{
try {
this.updateMatrixLineStmt = this.dbConnection.prepareStatement(
"UPDATE " + table + " SET " + column + "='?' WHERE seqId=?");
} catch (SQLException e) {
logr.fatal("Couldn't create Prepared Statement " +
"'insertMatrixLineStmt'! SQLException: " + e);
System.exit(0);
}
}


/**
* Stores (UPDATE) a matrix line via a Prepared Statement in the
database.
* @param line
* @return
*/
public int updateMatrixLine(int seqId, String line) {
int retVal = 0;

try {
this.updateMatrixLineStmt.setString(1, line);
this.updateMatrixLineStmt.setInt(2, seqId); <-- this is line 222
from the error message!
retVal = this.updateMatrixLineStmt.executeUpdate();
} catch (SQLException e) {
logr.fatal("Couldn't store matrix line '" + line + "'.
SQLException: " + e);
e.printStackTrace();
System.exit(0);
}

return retVal;
}



The setParameter() method from org.h2.jdbc.JdbcPreparedStatement
------------------------------------------------------------------------------------------------------
private void setParameter(int parameterIndex, Value value) throws
SQLException {
checkClosed();
parameterIndex--;
ObjectArray parameters = command.getParameters();
if (parameterIndex < 0 || parameterIndex >= parameters.size())
{
throw Message.getInvalidValueException("" +
(parameterIndex + 1), "parameterIndex");
}
ParameterInterface param = (ParameterInterface)
parameters.get(parameterIndex);
// can only delete old temp files if they are not in the batch
param.setValue(value, batchParameters == null);
}


So Message.getInvalidValueException is thrown if (parameterIndex < 0
|| parameterIndex >= parameters.size()).
But I don't know why, since I definitely have two ? in my
createUpdateMatrixLinePS() method. But maybe I just don't see the wood
for the trees. Can anyone help? And also: what does [90008-76] (I
guess it's an error code) exactly mean?


Thanks,
Marcel

[Thomas Mueller]

Hi,

You have only one parameter because the first ? is in quotes, meaning
it's a literal text:

this.updateMatrixLineStmt = this.dbConnection.prepareStatement(
"UPDATE " + table + " SET " + column +
"='?' WHERE seqId=?");

What you probably want to do is:

this.updateMatrixLineStmt = this.dbConnection.prepareStatement(
"UPDATE " + table + " SET " + column +

"=? WHERE seqId=?");

Regards,
Thomas

[Marcel]

Hi Thomas!

Thomas Mueller wrote:
> Hi,
>
> You have only one parameter because the first ? is in quotes, meaning
> it's a literal text:
>
> this.updateMatrixLineStmt = this.dbConnection.prepareStatement(
> "UPDATE " + table + " SET " + column +
> "='?' WHERE seqId=?");
>
> What you probably want to do is:
>
> this.updateMatrixLineStmt = this.dbConnection.prepareStatement(
> "UPDATE " + table + " SET " + column +
> "=? WHERE seqId=?");

Yep, that was it. I thought, that I've to use the quotes, when I put a
String in a query. I didn't know, that in this case I don't need them.
Thanks heaps for the incredibly fast reply and your help.

Keep up the excellent work,
Marcel