QSqlQuery & parameters

[HoMa]

Hi all!
I had some difficulties, until I got queries against sqlite database with parameters to run. Now I think I know how everything works. But I think I found a bug: The "bindValue" function has a remark, that placeholder start with a placeholder mark. From the sqlite docu I guess, this could be :, @ and $. But with qt I only get colon (:) to run. Is this a bug - or do I still have some misconzeptions?

My sample code:

{
    QSqlDatabase::addDatabase("QSQLITE");
    QSqlDatabase::database().setDatabaseName(qsl(":memory:"));
    QSqlDatabase::database(/*qsl("qt_sql_default_connection"), true*/);
    QString sqlCreateTable =QStringLiteral("CREATE TABLE testt (s TEXT, i INTEGER)");
    QSqlQuery qCreate{sqlCreateTable};
}
// positional binding works
{
    QString sqlInsertData =QStringLiteral("INSERT INTO testt (s, i) VALUES (?, ?)");
    QSqlQuery q;
    q.prepare (sqlInsertData);
    q.addBindValue (QVariant("text1"));
    q.addBindValue (QVariant(13));
    if( not q.exec ())
        qDebug() << sqlInsertData << q.boundValues () << q.lastError () << q.lastQuery ();
    q.addBindValue (QVariant("text2"));
    q.addBindValue (QVariant(14));
    if( not q.exec ())
        qDebug() << sqlInsertData << q.boundValues () << q.lastError () << q.lastQuery ();
}
// How about named binding?
{
    QString sqlSelect =QStringLiteral("SELECT * FROM testt WHERE s=:val1 AND i=:val2");
    QSqlQuery qSelect;
    qSelect.prepare (sqlSelect);
    qSelect.bindValue (":val1", QVariant("text2"));
    qSelect.bindValue (":val2", QVariant(14));
    qDebug() << qSelect.boundValues ();
    if( qSelect.exec ()) {
        qDebug() << qSelect.lastQuery ();
        qSelect.first();
        qDebug() << qSelect.record ().value (0);
    } else
        qDebug() << sqlSelect << qSelect.boundValues () << qSelect.lastError () << qSelect.lastQuery ();
}
// change order: OK!
{
    QString sqlSelect =QStringLiteral("SELECT * FROM testt WHERE s=:val1 AND i=:val2");
    QSqlQuery qSelect;
    qSelect.prepare (sqlSelect);
    qSelect.bindValue (":val2", QVariant(14));
    qSelect.bindValue (":val1", QVariant("text2"));
    qDebug() << qSelect.boundValues ();
    if( qSelect.exec ()) {
        qSelect.first();
        qDebug() << qSelect.record ().value (0);
    } else
        qDebug() << sqlSelect << qSelect.boundValues () << qSelect.lastError () << qSelect.lastQuery ();
}
// change placeholder mark to @ -> will not work
{
    QString sqlSelect =QStringLiteral("SELECT * FROM testt WHERE s=@val1 AND i=@val2");
    QSqlQuery qSelect;
    qSelect.prepare (sqlSelect);
    qSelect.bindValue ("@val1", QVariant("text2"));
    qSelect.bindValue ("@val2", QVariant(14));
    qDebug() << qSelect.boundValues ();
    if( qSelect.exec ()) {
        qSelect.first();
        qDebug() << qSelect.record ().value (0);
    } else
        qDebug() << sqlSelect << qSelect.boundValues () << qSelect.lastError () << qSelect.lastQuery ();
}
// change placeholder mark to $ -> will not work
{
    QString sqlSelect =QStringLiteral("SELECT * FROM testt WHERE s=$val1 AND i=$val2");
    QSqlQuery qSelect;
    qSelect.prepare (sqlSelect);
    qSelect.bindValue ("$val1", QVariant("text2"));
    qSelect.bindValue ("$val2", QVariant(14));
    qDebug() << qSelect.boundValues ();
    if( qSelect.exec ()) {
        qSelect.first();
        qDebug() << qSelect.record ().value (0);
    } else
        qDebug() << sqlSelect << qSelect.boundValues () << qSelect.lastError () << qSelect.lastQuery ();
}

[Christian Ehrlicher]

@HoMa said in QSqlQuery & parameters:

If this is not a bug, at least the documentation could be improved ... anyway ...
Could anyone tell me, where in the source code of Qt this could be found? I was not able to find a check against the colon.

Qt only supports : as placeholder and it's properly described in the documentation: https://doc.qt.io/qt-6/qsqlquery.html#approaches-to-binding-values

Also "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." is very clear - Qt supports both versions - either :foo or ?

If you want the source code for it - qsqlresult.cpp

[SGaist]

Hi,

Which version of Qt is that ?

[HoMa]

I work with the latest 5.x version !

[SGaist]

Latest Open Source release is 5.15.7, is that the one ? Because commercial users have an even more recent version.

[Christian Ehrlicher]

@HoMa said in QSqlQuery & parameters:

From the sqlite docu

You don't use SQlite directly but Qt so you should follow the Qt rules: https://doc.qt.io/qt-6/qsqlquery.html#approaches-to-binding-values

[HoMa]

@SGaist It is the open source version!

[HoMa]

@Christian-Ehrlicher I think I followed all the suggestions. I would not use bindValue for positional binding, but only addBindValue.
On the placeholder mark the bindValue docu metiones:

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. In this case, sufficient space must be pre-allocated to store the result into.

So colon should not be the only one to work.

[JonB]

@HoMa
https://doc.qt.io/qt-6/qsqlquery.html#details

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.

My bold. I read it as just these 2 syntaxes (:name, ?) are supported.

[Christian Ehrlicher]

@HoMa said in QSqlQuery & parameters:

I would not use bindValue for positional binding, but only addBindValue.

Why not? Simply use :name and you can use bindValue() as @JonB already suggested.

[HoMa]

@JonB Sorry, I don't get your point here. But I have to admit, that I don't understand the term "my bold".
And I am not talking oracle here.

About the "syntaxes": the "two syntaxes" are: positional - with ? as placeholder - and "named" - with some placeholder mark as signal. I stated before: the docu of bindValue tells us, that colon, as a placeholder mark, should be only one example:
Note that the placeholder mark (e.g :) must be included when specifying the placeholder name.

[HoMa]

@Christian-Ehrlicher
Why would I not use bindValue for positional binding: I was talking about the version with the QString and QVariant arguments. Indeed there is the version with int and QVariant - to use this with positional binding is - of course - OK.
Anyways: You said
Simply use :name and you can use bindValue()
This I don't get. Using :name is definitly named binding - so how does this relate back to positional binding?

[Christian Ehrlicher]

@HoMa said in QSqlQuery & parameters:

qSelect.bindValue ("$val1", QVariant("text2"));
qSelect.bindValue ("$val2", QVariant(14));

You said that this does not work with QSqlQuery, we told you that this can not work because the documentation tells you that named binding only works with :. Now you talk about positional binding? What's the problem there?

[HoMa]

@Christian-Ehrlicher said in QSqlQuery & parameters:

You said that this does not work with QSqlQuery, we told you that this can not work because the documentation tells you that named binding only works with :. Now you talk about positional binding? What's the problem there?

I don't share your oppinion on the documentation - maybe that is the difference (I would not call it a problem).
The part of the docu, that was quoted by JonB , talkes of oracle databases and from the two systems, that oracle supports - Qt supports both. I would not draw any conclusion from that on how placeholders should look in general.
And the other place in the docu says about placeholder marker "e.g. ':'" - so colon is an example of a placeholder marker, not the only possibility.
If this is not a bug, at least the documentation could be improved ... anyway ...
Could anyone tell me, where in the source code of Qt this could be found? I was not able to find a check against the colon.

[Christian Ehrlicher]

@HoMa said in QSqlQuery & parameters:

If this is not a bug, at least the documentation could be improved ... anyway ...
Could anyone tell me, where in the source code of Qt this could be found? I was not able to find a check against the colon.

Qt only supports : as placeholder and it's properly described in the documentation: https://doc.qt.io/qt-6/qsqlquery.html#approaches-to-binding-values

Also "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." is very clear - Qt supports both versions - either :foo or ?

If you want the source code for it - qsqlresult.cpp

[HoMa]

@Christian-Ehrlicher said in QSqlQuery & parameters:

If you want the source code for it - qsqlresult.cpp

OK - thank you. This is it. line 161 and around that - is quite clear.
I still not completly agree on the docu - but the code shows, that Qt does not use the named placeholders of the drivers at all - but only simulates it, kind of. Not sure, if I think this is good, but - it is what it is. So - who said it: this is Qt and not Sqlite.

Thx for all contributions! I will mark this solved