Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Update: Forum Guidelines & Code of Conduct


    Qt World Summit: Early-Bird Tickets

    Binding array of integers to QSqlQuery

    General and Desktop
    4
    7
    3859
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • M
      Mr. Puskevit 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(&#41;.text(), QMessageBox::Ok);
                  }
                  else
                  {
                      while(query.next())
                      {
                          idList.push_back(query.value(0).toInt());
                      }
                  }
      

      Debug info

      1 Reply Last reply Reply Quote 0
      • M
        Mr. Puskevit last edited by

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

        1 Reply Last reply Reply Quote 0
        • SGaist
          SGaist Lifetime Qt Champion last edited by

          Hi and welcome to devnet,

          Did you check what query you are currently executing ?

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          1 Reply Last reply Reply Quote 0
          • M
            Mr. Puskevit last edited by

            Hi,

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

            1 Reply Last reply Reply Quote 0
            • M
              mmoll 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)

              1 Reply Last reply Reply Quote 0
              • M
                Mr. Puskevit 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.

                1 Reply Last reply Reply Quote 0
                • U
                  UcefAZ last edited by

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

                  1 Reply Last reply Reply Quote 0
                  • First post
                    Last post