Using binding to select on null or value

[Mookie]

I am using QtSql module with a MySQL back end. I prefer to using bindValue() as much as is possible for input values in queries.

I have a case where I need to SELECT and look for a NULL in the WHERE clause.
A simplified example of what I need to do [effectively]:


SELECT * FROM rabbits WHERE color IS NULL;All the binding examples in the documentation show only INSERT statements, however. The documentation says to bind the QVariant(QVariant::String) to the ":color" term.


void do_query(QString color)
{
// stuff
query.prepare("SELECT * FROM rabbits WHERE color=:color");
if (color.isEmpty())
query.bindValue(":color", QVariant(QVariant::String));
else
query.bindValue(":color", color);
query.exec();
// more stuff
}Some of the documentation wording gives me pause and I think it is probably specific to an INSERT statement which would convert to "=NULL" when making the MySQL call underneath. A where statement would need to be converted to " IS NULL" instead.

Is there is a way use binding to SELECT items matching a NULL as well as those matching a value?

[wysota]

Two things that come to my mind:
* maybe there is a MySQL function that does what you want
* if not, you could write a stored procedure/function to do what you want

This is really a MySQL and not Qt related issue. Qt will not change the syntax of your query while binding variables. You need to find a way to express NULL and values with the same syntax.

[ChrisW67]

You could try something like:


query.prepare("SELECT * FROM rabbits WHERE coalesce(color, '{{NULL}}') = :color");
query.bindValue(":color", color.isEmpty()? "{{NULL}}": color);
query.exec();

This should work with MySQL and SQLite (it is ANSI standard) but it will be slower.
When the color column is NULL it is converted to a string "{{NULL}}" that you can do an equality test on. The placeholder value must not be a valid bunny colour.

[wysota]

The simplest way would just be:


bool ok = false;
if(color.isEmpty()){
ok = query.prepare("SELECT * FROM rabbits WHERE color IS NULL");
} else {
ok = query.prepare("SELECT * FROM rabbits WHERE color=:color");
query.bindValue(":color", color);
}
if(ok)
query.exec();
or:

query.prepare(QString("SELECT * FROM rabbits WHERE color") + (color.isEmpty() ? " IS NULL" : "=:color"));
if(!color.isEmpty()) query.bindValue(":color", color);
query.exec();

[Mookie]

Thank you both. Knowing that QtSql makes no attempt to rewrite the syntax helps.