Why doesn't bind work with this query



  • OK the following query works

        QSqlQuery query;
        query.prepare("select `boxes`.`boxID`,`boxName`,`boxLocation` from `boxes`"\
                      "LEFT JOIN `boxContents` on `boxes`.`boxID`=`boxContents`.`boxID` WHERE `itemName` LIKE '%be%'");
        query.exec();
    

    I am searching for the word 'be' in the database.

    However I want to do this using a string because I want to make a function and so I did this.

        QString search="be";
        QSqlQuery query;
        query.prepare("select `boxes`.`boxID`,`boxName`,`boxLocation` from `boxes`"\
                      "LEFT JOIN `boxContents` on `boxes`.`boxID`=`boxContents`.`boxID` WHERE `itemName` LIKE '%:search%'");
        query.bindValue(":search",search);
        query.exec();
    

    And I am not getting any results. It is like the bind isn't working.

    What could be wrong?


  • Lifetime Qt Champion

    Hi,

    Please check the return value of exec and print the error if the call fails.



  • @SGaist

    I did this

    qDebug() << query.lastError();
    

    And the response I got was this

    QSqlError("", "", "")
    

    So no error, but the bind does not seem to be working. I know the query is fine however, as the first bit of code works.



  • Try comparing the actual query strings that are being sent to the server for your two queries:

    qDebug() << query.lastQuery();
    


  • @mawh1960
    Here are the two strings from query.lastquery();

    "select `boxes`.`boxID`,`boxName`,`boxLocation` from `boxes`LEFT JOIN `boxContents` on `boxes`.`boxID`=`boxContents`.`boxID` WHERE `itemName` LIKE '%:search%'"
    "select `boxes`.`boxID`,`boxName`,`boxLocation` from `boxes`LEFT JOIN `boxContents` on `boxes`.`boxID`=`boxContents`.`boxID` WHERE `itemName` LIKE '%be%'"
    

    The second one works, and that is when I hard code in the letters "be".
    The first one didn't work.

    LOL sometimes php is so much easier heh heh


  • Qt Champions 2016

    Hi
    You can use a tool like http://sqlitebrowser.org/
    to verify your select actually works. Meaning it returns any rows.



  • @mrjj

    The select does actually work, I know it works. What is not working is the bind in QT. I already tried it in SQLite manager in firefox.

    Also the select works in QT when I hard code the value. It doesn't work when I use :search variable in the line.
    For some reason the value is not binding, even though I have put the bind line in.



  • I would say I solved the problem, but it was a post I found on stack overflow that solved it really. Here is the solution

     QString search="be";
        QSqlQuery query;
        query.prepare("select `boxes`.`boxID`,`boxName`,`boxLocation` from `boxes`"\
                      "LEFT JOIN `boxContents` on `boxes`.`boxID`=`boxContents`.`boxID` WHERE `itemName` LIKE '%'||:search||'%'");
        query.bindValue(":search",search);
        query.exec();
    

    Instead of

    LIKE '%:search%'
    

    I had to put

    LIKE '%'||:search||'%'
    

    I haven't come across this before because I mainly work in websites using php and mysql.
    However for this project I am using QT and sqlite.

    This is the stack overflow post that solved my question
    http://stackoverflow.com/questions/13111130/qt-qsqlquery-prepare-and-bindvalue-not-working


  • Qt Champions 2016

    Ok
    super
    So it was the actual bind syntax that was wrong when using the like operator
    Thx for reporting back :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.