PDA

View Full Version : SQL Select statement with binary comparison



xfurrier
20th June 2009, 09:09
I have an SQL table (PEOPLE) with a binary column (BINID, BINARY(4)). I want to run a SELECT statement with filter on that column, but no luck yet. And I'm getting pretty desperate.

When I do SELECT without WHERE, and do 'if' comparison afterwards I get the required result:


queryStr = QString("SELECT NAME, BINID FROM PEOPLE");
query.exec(queryStr);
qDebug() << queryStr << " result size: " << query.size();
while (query.next()) {
QString name = query.record().value(0).toString();
QByteArray binid = query.record().value(1).toByteArray();
if (binid == myBinID)
qDebug() << " - match: " << name;
}

with result:


"SELECT NAME, BINID FROM PEOPLE" result size: 5
- match: "Jo"


However, with WHERE statement I get no results:


QByteArray queryBA("SELECT NAME FROM PEOPLE WHERE BINID='");
queryBA.append(myBinID);
queryBA.append("'");
query.exec(queryBA);
qDebug() << queryBA << " result size: " << query.size();

with:


"SELECT NAME FROM PEOPLE WHERE BINID='ÿÃ'" result size: 0


Any help would be appreciated. Attached is the source file + MySQL database if you wish to try.

Thanks guys.

marcel
20th June 2009, 23:40
That happens because the bytearray is converted to a unicode character string. Why dont't you use parameterized queries (with bindValue)?

xfurrier
21st June 2009, 03:33
Hi Marcel,

Thanks for that. I've tried bindValue before, but did a silly mistake. Rather than using :binid I had ':binid' in SELECT statement.