Results 1 to 5 of 5

Thread: Using binding to select on null or value

  1. #1
    Join Date
    Jun 2006
    Posts
    43
    Thanks
    9
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Using binding to select on null or value

    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.

    Qt Code:
    1. void do_query(QString color)
    2. {
    3. // stuff
    4. query.prepare("SELECT * FROM rabbits WHERE color=:color");
    5. if (color.isEmpty())
    6. query.bindValue(":color", QVariant(QVariant::String));
    7. else
    8. query.bindValue(":color", color);
    9. query.exec();
    10. // more stuff
    11. }
    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?

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,013 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Using binding to select on null or value

    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.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  3. #3
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,671
    Thanks
    13
    Thanked 1,595 Times in 1,523 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Using binding to select on null or value

    You could try something like:
    Qt Code:
    1. query.prepare("SELECT * FROM rabbits WHERE coalesce(color, '{{NULL}}') = :color");
    2. query.bindValue(":color", color.isEmpty()? "{{NULL}}": color);
    3. 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.

  4. The following user says thank you to ChrisW67 for this useful post:

    Mookie (26th June 2011)

  5. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,013 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Using binding to select on null or value

    The simplest way would just be:

    Qt Code:
    1. bool ok = false;
    2. if(color.isEmpty()){
    3. ok = query.prepare("SELECT * FROM rabbits WHERE color IS NULL");
    4. } else {
    5. ok = query.prepare("SELECT * FROM rabbits WHERE color=:color");
    6. query.bindValue(":color", color);
    7. }
    8. if(ok)
    9. query.exec();
    To copy to clipboard, switch view to plain text mode 
    or:
    Qt Code:
    1. query.prepare(QString("SELECT * FROM rabbits WHERE color") + (color.isEmpty() ? " IS NULL" : "=:color"));
    2. if(!color.isEmpty()) query.bindValue(":color", color);
    3. query.exec();
    To copy to clipboard, switch view to plain text mode 
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  6. The following user says thank you to wysota for this useful post:

    Mookie (26th June 2011)

  7. #5
    Join Date
    Jun 2006
    Posts
    43
    Thanks
    9
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Using binding to select on null or value

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

Similar Threads

  1. QUdpSocket binding doesn't work
    By bhuff70 in forum Newbie
    Replies: 16
    Last Post: 5th April 2014, 21:56
  2. Binding data to a a QGraphicsItem (or something like that)
    By been_1990 in forum Qt Programming
    Replies: 18
    Last Post: 13th August 2009, 09:33
  3. QtScript Binding problem with QWidget
    By zack in forum Qt Programming
    Replies: 3
    Last Post: 17th February 2009, 10:38
  4. QUdpSocket binding
    By db in forum Qt Programming
    Replies: 0
    Last Post: 13th March 2008, 12:24
  5. binding
    By mickey in forum General Discussion
    Replies: 9
    Last Post: 26th September 2006, 22:54

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.