Why doesn't bind work with this query

Asimov

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?

SGaist

Hi,

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

Asimov

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

mawh1960

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

qDebug() << query.lastQuery();

Asimov

@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

mrjj

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

Asimov

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

Asimov

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

mrjj

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