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());
                }
            }

[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