I spent 2 hours trying to figure out this nonsense. Here is the problem:
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection("username", "password",
connectionString);
string sqlQueryText = "UPDATE myTable SET field4 =:p4, field3 =:p3
WHERE field2 :=p2 AND field1 :=p1";
Statement* updateStatement = conn->createStatement(sqlQueryText);
updateStatement.setInt(1, field1Var);
updateStatement.setString(2, field2Var);
updateStatement.setInt(3, field3Var);
updateStatement.setString(4, field4Var);
updateStatement.executeUpdate(conn);
What’s wrong with the code above? Not a thing in my opinion. Yet, if you run it, you’ll get a pretty cryptic invalid number exception.
Turns out that the way the query is put together is order specific and when the values are converted at run time, an exception is thrown.
The following is how I made it works:
Environment* env = Environment::createEnvironment();
Connection* conn = env->createConnection("username", "password",
connectionString);
string sqlQueryText = "UPDATE myTable SET field4 =:p1, field3 =:p2
WHERE field2 :=p3 AND field1 :=p4";
Statement* updateStatement = conn->createStatement(sqlQueryText);
updateStatement.setString(1, field4Var);
updateStatement.setInt(2, field3Var);
updateStatement.setString(3, field2Var);
updateStatement.setInt(4, field1Var);
updateStatement.executeUpdate(conn);
It seems they just start parsing the query left to right, and the order in which the parameters appear is supposed to match the order of the values. That would mean that if you want to use the same parameter twice, you have to add it twice in the list of values.
string sqlQueryText = "UPDATE myTable SET field4 =:p1, field3 =:p2 WHERE field4 :=p1"; Statement* updateStatement = conn->createStatement(sqlQueryText); updateStatement.setString(1, field4Var); updateStatement.setInt(2, field3Var); updateStatement.setString(1, field4Var);
Why they implemented it like that is beyond me. They could have easily done what the string format function in C# does:
String query = String.Format(
"Update field1={0}, field3={1} where field1={0}", intVal, stringVal);