The random ramblings of a techie

Oracle C++ API non-sense

Parameterized statement throws invalid number SQLExceptions

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);
Facebook Twitter Digg Reddit Linkedin Email

, , ,

Leave a Reply

Connect with:

Your email address will not be published. Required fields are marked *

Please type the characters of this captcha image in the input box

Please type the characters of this captcha image in the input box

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>