Qt Forum

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

    Update: Forum Guidelines & Code of Conduct

    Unsolved How to bind IS NULL in sql WHERE?

    General and Desktop
    3
    14
    689
    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.
    • T
      Tarae last edited by

      Imagine SQL (Oracle) database with computers.
      I want id of computer(s) with certain hw.

      QSqlQuery query;
      query.prepare("SELECT id FROM computers WHERE cpu = ? AND ram = ? AND gpu = ? ");
      

      This works well:

      query.addBindValue("Intel Core i5-8400");
      query.addBindValue("HyperX HX424C15FBK2/16");
      query.addBindValue("Nvidia GTX 1660S");
      

      But then there are computers without gpu and it doesn't work:

      query.addBindValue("Intel Core i5-8400");
      query.addBindValue("HyperX HX424C15FBK2/16");
      

      I've tried:

      query.addBindValue("");
      query.addBindValue("null");
      query.addBindValue(NULL);
      query.addBindValue(QString());
      query.addBindValue(QVariant(QVariant::String));
      

      Nothing works.
      Can you help me please?

      JonB 1 Reply Last reply Reply Quote 0
      • JonB
        JonB @Tarae last edited by 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 2 Replies Last reply Reply Quote 0
        • T
          Tarae @JonB last edited by

          @JonB Thak you for your idea.
          I have tried:

              query.addBindValue(QVariant());
          

          Doesn't work.

          JonB 1 Reply Last reply Reply Quote 0
          • JonB
            JonB @Tarae last edited by 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 1 Reply Last reply Reply Quote 1
            • T
              Tarae @JonB last edited by

              @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 Reply Quote 0
              • T
                Tarae @JonB last edited by 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.

                JonB 1 Reply Last reply Reply Quote 0
                • JonB
                  JonB @Tarae last edited by JonB

                  @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 Reply Quote 2
                  • fcarney
                    fcarney last edited by 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?

                    C++ is a perfectly valid school of magic.

                    JonB 1 Reply Last reply Reply Quote -1
                    • JonB
                      JonB @fcarney last edited by 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.

                      fcarney 1 Reply Last reply Reply Quote 0
                      • fcarney
                        fcarney @JonB last edited by

                        @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.

                        JonB 1 Reply Last reply Reply Quote 0
                        • JonB
                          JonB @fcarney last edited by 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.

                          fcarney 1 Reply Last reply Reply Quote 1
                          • fcarney
                            fcarney @JonB last edited by

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

                            C++ is a perfectly valid school of magic.

                            JonB 1 Reply Last reply Reply Quote 0
                            • JonB
                              JonB @fcarney last edited by 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 :)

                              fcarney 1 Reply Last reply Reply Quote 1
                              • fcarney
                                fcarney @JonB last edited by

                                @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 Reply Quote 1
                                • First post
                                  Last post