Solved 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?
-
Hi,
Please check the return value of exec and print the error if the call fails.
-
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
-
Hi
You can use a tool like http://sqlitebrowser.org/
to verify your select actually works. Meaning it returns any rows. -
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 -
Ok
super
So it was the actual bind syntax that was wrong when using the like operator
Thx for reporting back :)