Binding array of integers to QSqlQuery
-
wrote on 17 Jul 2014, 16:31 last edited by VRonin
Hi all,
I'm trying to bind array of integers to my query. Problem is query.next() returns false. If I type values statically into the string instead of using placeholder, query returns results. What am i doing wrong ?
Thanks
Edit: BTW i forgot to mention that I'm using SQLite 3.8.5
if(man->open()) { QString queryStr = "SELECT CurrencyID FROM Transactions WHERE SafeID IN (:sIDs) GROUP BY CurrencyID"; QSqlQuery query(man->getDB()); if(query.prepare(queryStr)) { QStringList idstrings; foreach(int id, safeList) { idstrings << QString::number(id); } QString numberlist = idstrings.join(","); query.bindValue(":sIDs",numberlist); if(!query.exec()) { QMessageBox::critical(0,"Error","SafeOperations::getUniqueCurrencyIDs : " + query.lastError().text(), QMessageBox::Ok); } else { while(query.next()) { idList.push_back(query.value(0).toInt()); } }
-
wrote on 21 Jul 2014, 22:20 last edited by
Almost a week and no response thanks anyway people. I assume its some kind of bug.
-
Hi and welcome to devnet,
Did you check what query you are currently executing ?
-
wrote on 22 Jul 2014, 08:12 last edited by
Hi,
Thanks for response. Yes I did. I checked with executedQuery. It returns value in queryStr.
-
wrote on 22 Jul 2014, 14:07 last edited by
When you use bindValue with the string of values, the resulting query is equivalent to "SELECT CurrencyID FROM Transactions WHERE SafeID IN ('1,6,7,...,9,10') GROUP BY CurrencyID", which is not what you want (notice that there are quotation marks around your list, i.e. you test against a single string).
Apparently there's little you can do (see e.g. http://betteratoracle.com/posts/20-how-do-i-bind-a-variable-in-list) -
wrote on 22 Jul 2014, 15:10 last edited by VRonin
Hi mmoll,
Thank you for replying my question. I've made 2 workarounds for my case.
if(man->open()) { QStringList idstrings; foreach(int id, safeList) { idstrings << QString::number(id); } QString numberlist = idstrings.join(","); QString queryStr = "SELECT CurrencyID FROM Transactions WHERE SafeID IN ("+numberlist+") GROUP BY CurrencyID"; QSqlQuery query(man->getDB()); if(query.prepare(queryStr)) { //query.bindValue(":sIDs",numberlist); if(!query.exec()) {
if(man->open()) { QStringList idstrings; foreach(int id, safeList) { idstrings << QString::number(id); } QString numberlist = idstrings.join(","); QString queryStr = "SELECT CurrencyID FROM Transactions WHERE SafeID IN (:sIDs) GROUP BY CurrencyID"; QSqlQuery query(man->getDB()); if(query.prepare(queryStr)) { query.bindValue(":sIDs",numberlist); if(!query.exec()) {
Both of them work, I've been using the second one for sometime. I'm not sure "quotation mark" case is my problem.
-
wrote on 31 May 2021, 08:39 last edited by
I had the same problem and the solution that worked for me is explained in :
https://stackoverflow.com/a/49592807/5982838