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
}

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
}

To copy to clipboard, switch view to plain text mode 

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();

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

To copy to clipboard, switch view to plain text mode 

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();

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();

To copy to clipboard, switch view to plain text mode 

or:

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

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

To copy to clipboard, switch view to plain text mode 

[Mookie]

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