Binding array of integers to QSqlQuery

Mr. Puskevit

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(&#41;.text(), QMessageBox::Ok);
            }
            else
            {
                while(query.next())
                {
                    idList.push_back(query.value(0).toInt());
                }
            }

Debug info

Mr. Puskevit

Almost a week and no response thanks anyway people. I assume its some kind of bug.

SGaist

Hi and welcome to devnet,

Did you check what query you are currently executing ?

Mr. Puskevit

Hi,

Thanks for response. Yes I did. I checked with executedQuery. It returns value in queryStr.

mmoll

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)

Mr. Puskevit

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.

UcefAZ

I had the same problem and the solution that worked for me is explained in :
https://stackoverflow.com/a/49592807/5982838