QSqlQuery & parameters
-
@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
Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
Latest Open Source release is 5.15.7, is that the one ? Because commercial users have an even more recent version.
-
@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
@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.
-
@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.
@HoMa
https://doc.qt.io/qt-6/qsqlquery.html#detailsOracle 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 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.
@HoMa said in QSqlQuery & parameters:
I would not use bindValue for positional binding, but only addBindValue.
Why not? Simply use
:nameand you can use bindValue() as @JonB already suggested.Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@HoMa
https://doc.qt.io/qt-6/qsqlquery.html#detailsOracle 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.@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 said in QSqlQuery & parameters:
I would not use bindValue for positional binding, but only addBindValue.
Why not? Simply use
:nameand you can use bindValue() as @JonB already suggested.@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? -
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 (); }@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?Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@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?@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 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.@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-valuesAlso "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
:fooor?If you want the source code for it - qsqlresult.cpp
Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
@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-valuesAlso "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
:fooor?If you want the source code for it - qsqlresult.cpp
@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
