SQL Select statement with binary comparison

[xfurrier]

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;
}

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;
}

To copy to clipboard, switch view to plain text mode 

with result:

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

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

To copy to clipboard, switch view to plain text mode 

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();

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

To copy to clipboard, switch view to plain text mode 

with:

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

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

To copy to clipboard, switch view to plain text mode 

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

Thanks guys.

[marcel]

That happens because the bytearray is converted to a unicode character string. Why dont't you use parameterized queries (with bindValue)?

[xfurrier]

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.