How to bind IS NULL in sql WHERE?

[Tarae]

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]

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

[Tarae]

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

    query.addBindValue(QVariant());

Doesn't work.

[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?

[Tarae]

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

[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]

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

[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?

[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]

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

[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]

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

[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]

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