QSqlQuery and ORACLE style value binding

Algirdasss

Hy,

I encountered one ugly thing while working with SQL in QT 4.6. As you can find in QT docs two types of value binding in SQL statements are supported: ORACLE style and ODBC style. Documentation says:

bq. Oracle databases identify placeholders by using a colon-name syntax, e.g :name. ODBC simply uses ? characters. Qt supports both syntaxes, with the restriction that you can't mix them in the same query.

Lets say we are using ORACLE style of binding. Because in this case values are given names, one can think that their order is not important. Thats what is said in documentation abount method "QSqlQuery::bindValue(...)".

bq. void QSqlQuery::bindValue ( const QString & placeholder, const QVariant & val, QSql::ParamType paramType = QSql::In )
Set the placeholder placeholder to be bound to value val in the prepared statement. Note that the placeholder mark (e.g :) must be included when specifying the placeholder name. If paramType is QSql::Out or QSql::InOut, the placeholder will be overwritten with data from the database after the exec() call.

I dont see here anything mentioned about parameter order. Simple test:

@// Works fine because be bind values in the same order as placeholders appear in query
QString queryStr = QString("UPDATE Table SET Field1 = :value WHERE Field2 = :id");
QSqlQuery query(queryStr, db);
query.bindValue(":value", "6666");
query.bindValue(":id", "someID");

// Query fails. There is no SQL error but ":value" is bound to "someID", not to "6666"
QString queryStr = QString("UPDATE Table SET Field1 = :value WHERE Field2 = :id");
QSqlQuery query(queryStr, db);
query.bindValue(":id", "someID");
query.bindValue(":value", "6666");@

So damn question is ... WHY THESE PLACEHOLDERS HAVE NAMES IF THESE NAMES ARE IGNORED ANYWAY. I spent half of the day with pain in my head asking question why my queries fail ... It's QT BUG or its how it should work :)

dalaing

Unless the comment about the second block of code is wrong I don't see a problem.

From what I can see

bq. but "value" is bound to "6666"

is the expected behaviour of

@query.bindValue(":value","6666")@

I don't see where the order comes into it (unless there's a typo in you comment, in which case it might be a bug).

Algirdasss

Yep. Sorry. I was frustrated a little :) yesterday. It's mistake in my comment. In secod block of code placeholder ":value" is binded not to "6666" but to "someID". So if you say it is a bug, I'll report it.

IrQX

It is simple:

@query.bindValue(":key", value);
query.exec();
@

Algirdasss

What do you mean simple? I personally understand how to use value binding. The problem is in other place if you carefully have read posts above.

Today I made a sample app for re-checking this issue. I found some new things. The following code works (BUT IT SHOULDN'T DO SO):

@ QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "DB");
db.setDatabaseName("..//Server//log.s3db");
db.open();

QSqlQuery query("SELECT * FROM DeviceStates WHERE DeviceId = :id", db);
query.bindValue(":idsssss", "c2380b27731f091c0aaa1b85be354894");
query.exec&#40;&#41;;

while(query.next(&#41;){
    qDebug() << "DeviceId" << query.record().value("DeviceId").toString();
}@

You can mention that placeholder name in statement bindValue(...) is different than in query text, but this code works as if these names were the same. One row is selected. BUT IT SHOULDN'T BE SO!!! I GET NO ERRORS!!!

Algirdasss

I think I have found source of the problem. I am using SQLite database driver. Today I checked it's futures, and it reports that named place holders are not supported by it. As I know SQLite API itself supports named placeholders but for some reason QT driver does not use them ... Maybe user should get some error message about this if one tries to use future that is not supported?

IrQX

sure, ORACLE style value appliable for ORACLE and PostreSQL. I can use this name-style only for this databases.