Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to bind IS NULL in sql WHERE?
Forum Updated to NodeBB v4.3 + New Features

How to bind IS NULL in sql WHERE?

Scheduled Pinned Locked Moved Unsolved General and Desktop
14 Posts 3 Posters 2.2k Views 1 Watching
  • 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.
  • JonBJ JonB

    @Tarae
    IIRC, did you try QVariant()?
    EDIT, No, I read that your QVariant(QVariant::String) should be correct. But that would be for a string-type NULL, that's correct for your 3 columns isn't it?
    https://doc.qt.io/qt-5/qsqlquery.html#bindValue

    To bind a NULL value, use a null QVariant; for example, use QVariant(QVariant::String) if you are binding a string.

    You should also define what "doesn't work" means, exactly?

    T Offline
    T Offline
    Tarae
    wrote on last edited by
    #5

    @JonB
    Doesn't work exactly means query.exec() is true but query doesn't contain any data, query.next() is false.

    1 Reply Last reply
    0
    • JonBJ JonB

      @Tarae
      On a different tack:

      WHERE cpu = ? AND ram = ? AND gpu = ? 
      

      These are presumably at best going to generate cpu = NULL etc. And you need cpu IS NULL etc. (I assume, don't know about Oracle SQL), so maybe you cannot do it this way to generate an IS NULL? Instead you have to code for this, to change your WHERE clause to cater for this?

      T Offline
      T Offline
      Tarae
      wrote on last edited by Tarae
      #6

      @JonB said in How to bind IS NULL in sql WHERE?:

      These are presumably at best going to generate cpu = NULL etc. And you need cpu IS NULL etc. (I assume, don't know about Oracle SQL), so maybe you cannot do it this way to generate an IS NULL? Instead you have to code for this, to change your WHERE clause to cater for this?

      I know I need IS NULL not = NULL but I hope there is Qt way how to do it withnout me coding it.

      JonBJ 1 Reply Last reply
      0
      • T Tarae

        @JonB said in How to bind IS NULL in sql WHERE?:

        These are presumably at best going to generate cpu = NULL etc. And you need cpu IS NULL etc. (I assume, don't know about Oracle SQL), so maybe you cannot do it this way to generate an IS NULL? Instead you have to code for this, to change your WHERE clause to cater for this?

        I know I need IS NULL not = NULL but I hope there is Qt way how to do it withnout me coding it.

        JonBJ Online
        JonBJ Online
        JonB
        wrote on last edited by JonB
        #7

        @Tarae
        If you read through this post from 2006(!)
        https://www.qtcentre.org/threads/42480-Using-binding-to-select-on-null-or-value
        it is the same question as yours, and the answer did seem to be it is your job to generate either IS NULL or = ? correctly for the value you are passing, as I suggested. Otherwise ISTM you are asking Qt to take a literal string containing = ? and alter what it passes to SQL, and I don't think it's going to do that for you during binding? I stand to be corrected.

        1 Reply Last reply
        2
        • fcarneyF Offline
          fcarneyF Offline
          fcarney
          wrote on last edited by fcarney
          #8

          DON'T DO THIS! Leaving for contextual purposes.
          I think you will have to do your own binding on the string:

          void sqlBinding(){
              QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",":memory:");
              db.open();
              QSqlQuery sq(db);
              bool prep = sq.prepare("CREATE TABLE computers (id, cpu, ram, gpu)");
              if(!prep){
                  qInfo() << "cannot create table" << sq.lastError();
                  return;
              }
              bool ex = sq.exec();
              if(!ex){
                  qInfo() << "cannot exec" << sq.lastError();
                  return;
              }
              //prep = sq.prepare("SELECT id FROM computers WHERE cpu = ? AND ram = ? AND gpu = ?");
          
              QString cpu("intel");
              QString ram("8gb");
              //QString gpu("intel");
              QString gpu("");
          
              auto mybind = [](auto str){
                  return str.size() ? QString("= '%1'").arg(str): "IS NULL";
              };
          
              QString select("SELECT id FROM computers WHERE cpu %1 AND ram %2 AND gpu %3");
              prep = sq.prepare(select.arg(mybind(cpu),mybind(ram),mybind(gpu)));
              if(!prep){
                  qInfo() << sq.lastQuery() << sq.lastError();
                  return;
              }
          
              qInfo() << sq.lastQuery();
              db.close();
          }
          

          Edit:
          Do the bind commands have injection protection or is it just a string replace function?

          C++ is a perfectly valid school of magic.

          JonBJ 1 Reply Last reply
          -1
          • fcarneyF fcarney

            DON'T DO THIS! Leaving for contextual purposes.
            I think you will have to do your own binding on the string:

            void sqlBinding(){
                QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",":memory:");
                db.open();
                QSqlQuery sq(db);
                bool prep = sq.prepare("CREATE TABLE computers (id, cpu, ram, gpu)");
                if(!prep){
                    qInfo() << "cannot create table" << sq.lastError();
                    return;
                }
                bool ex = sq.exec();
                if(!ex){
                    qInfo() << "cannot exec" << sq.lastError();
                    return;
                }
                //prep = sq.prepare("SELECT id FROM computers WHERE cpu = ? AND ram = ? AND gpu = ?");
            
                QString cpu("intel");
                QString ram("8gb");
                //QString gpu("intel");
                QString gpu("");
            
                auto mybind = [](auto str){
                    return str.size() ? QString("= '%1'").arg(str): "IS NULL";
                };
            
                QString select("SELECT id FROM computers WHERE cpu %1 AND ram %2 AND gpu %3");
                prep = sq.prepare(select.arg(mybind(cpu),mybind(ram),mybind(gpu)));
                if(!prep){
                    qInfo() << sq.lastQuery() << sq.lastError();
                    return;
                }
            
                qInfo() << sq.lastQuery();
                db.close();
            }
            

            Edit:
            Do the bind commands have injection protection or is it just a string replace function?

            JonBJ Online
            JonBJ Online
            JonB
            wrote on last edited by JonB
            #9

            @fcarney
            No offence, but I wouldn't do it that way. As per the old link, I would do the non-NULL case by sticking with prepare-and-bind, not QString("= '%1'").arg(str). Your substitution would have many limitations.

            fcarneyF 1 Reply Last reply
            0
            • JonBJ JonB

              @fcarney
              No offence, but I wouldn't do it that way. As per the old link, I would do the non-NULL case by sticking with prepare-and-bind, not QString("= '%1'").arg(str). Your substitution would have many limitations.

              fcarneyF Offline
              fcarneyF Offline
              fcarney
              wrote on last edited by
              #10

              @JonB said in How to bind IS NULL in sql WHERE?:

              As per the old link

              The old link assumes there is only one value that can be null. What if there are multiple columns that can be null? So with 2 columns can be null then I have 4 different strings instead of 2. With 3 columns I have 8 strings.

              C++ is a perfectly valid school of magic.

              JonBJ 1 Reply Last reply
              0
              • fcarneyF fcarney

                @JonB said in How to bind IS NULL in sql WHERE?:

                As per the old link

                The old link assumes there is only one value that can be null. What if there are multiple columns that can be null? So with 2 columns can be null then I have 4 different strings instead of 2. With 3 columns I have 8 strings.

                JonBJ Online
                JonBJ Online
                JonB
                wrote on last edited by JonB
                #11

                @fcarney
                No, you don't write it to have that many separate strings. You write one function which returns the appropriate string for the value you give it as parameter. Like

                QString clause(const QString name, const QVariant &value)
                {
                  if (value.isNull())
                      return QString("%1 IS NULL").arg(name);
                  else
                      return QString("%1 = ?").arg(name);    // and you'll do an `addBindValue()` in this case
                }
                

                Something based on that. And call that for each argument you need to add into a WHERE clause.

                fcarneyF 1 Reply Last reply
                1
                • JonBJ JonB

                  @fcarney
                  No, you don't write it to have that many separate strings. You write one function which returns the appropriate string for the value you give it as parameter. Like

                  QString clause(const QString name, const QVariant &value)
                  {
                    if (value.isNull())
                        return QString("%1 IS NULL").arg(name);
                    else
                        return QString("%1 = ?").arg(name);    // and you'll do an `addBindValue()` in this case
                  }
                  

                  Something based on that. And call that for each argument you need to add into a WHERE clause.

                  fcarneyF Offline
                  fcarneyF Offline
                  fcarney
                  wrote on last edited by
                  #12

                  @JonB So is the bind approach using database facilities to do the substitution then?

                  C++ is a perfectly valid school of magic.

                  JonBJ 1 Reply Last reply
                  0
                  • fcarneyF fcarney

                    @JonB So is the bind approach using database facilities to do the substitution then?

                    JonBJ Online
                    JonBJ Online
                    JonB
                    wrote on last edited by JonB
                    #13

                    @fcarney
                    Yes, because the work which goes on for = ? + a bound value (in the driver or at the server side or wherever) can do more than your equivalent attempt QString("= '%1'").arg(str). In many ways.

                    For a start, it may seem trivial, but your will fall over if the value passed in is a single ' (quote) character. There may be other funny string cases, or perhaps when the value is big. It is open to injection. It isn't right when the value is anything other than a string. That sort of thing :)

                    fcarneyF 1 Reply Last reply
                    1
                    • JonBJ JonB

                      @fcarney
                      Yes, because the work which goes on for = ? + a bound value (in the driver or at the server side or wherever) can do more than your equivalent attempt QString("= '%1'").arg(str). In many ways.

                      For a start, it may seem trivial, but your will fall over if the value passed in is a single ' (quote) character. There may be other funny string cases, or perhaps when the value is big. It is open to injection. It isn't right when the value is anything other than a string. That sort of thing :)

                      fcarneyF Offline
                      fcarneyF Offline
                      fcarney
                      wrote on last edited by
                      #14

                      @JonB
                      Okay, I didn't want to leave the old code in the thread without addressing the problems it has:

                      void sqlBinding(){
                          QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",":memory:");
                          db.open();
                          QSqlQuery sq(db);
                          bool prep = sq.prepare("CREATE TABLE computers (id, cpu, ram, gpu)");
                          if(!prep){
                              qInfo() << "cannot create table" << sq.lastError();
                              return;
                          }
                          bool ex = sq.exec();
                          if(!ex){
                              qInfo() << "cannot exec" << sq.lastError();
                              return;
                          }
                      
                          QStringList fields = {"cpu","ram","gpu"};
                          QStringList values1 = {"intel", "8gb", "intel"};
                          QStringList values2 = {"intel", "16gb", ""};
                      
                          auto myibind = [](auto str){
                              return str.size() ? str : nullptr;
                          };
                      
                          auto myinsert = [&sq, myibind](int id, auto list){
                              bool prep = sq.prepare("INSERT INTO computers (id, cpu, ram, gpu) VALUES (?, ?, ?, ?)");
                              if(!prep){
                                  qInfo() << sq.lastQuery() << sq.lastError();
                              }
                              sq.addBindValue(id);
                              sq.addBindValue(myibind(list[0]));
                              sq.addBindValue(myibind(list[1]));
                              sq.addBindValue(myibind(list[2]));
                              sq.exec();
                          };
                      
                          int id = 0;
                          myinsert(id++, values1);
                          myinsert(id++, values2);
                      
                          auto& values = values2;
                      
                          auto mybind = [](auto value, auto field){
                              return value.size() ? QString("%1 = ?").arg(field): QString("%1 IS NULL").arg(field);
                              //return QString("%1 = ?").arg(field);
                          };
                      
                          QString select("SELECT * FROM computers WHERE %1 AND %2 AND %3");
                          prep = sq.prepare(select.arg(mybind(values[0], fields[0]),mybind(values[1], fields[1]),mybind(values[2], fields[2])));
                          if(!prep){
                              qInfo() << sq.lastQuery() << sq.lastError();
                              return;
                          }
                          sq.addBindValue(values[0]);
                          sq.addBindValue(values[1]);
                          sq.addBindValue(values[2]);
                      
                          qInfo() << sq.lastQuery();
                          sq.exec();
                      
                          while(sq.next()){
                              qInfo() << sq.value(0).toString() << sq.value(1).toString() << sq.value(2).toString() << sq.value(3).toString();
                          }
                      
                          db.close();
                      }
                      

                      This keeps the usage of the binds to allow the db to escape, filter, etc.

                      C++ is a perfectly valid school of magic.

                      1 Reply Last reply
                      1

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved