SQLite WHERE clause with BLOB. Or how to make QT query BLOB as x'00000000'?

Kofr

CASE:
row is INTEGER SQL type
id is BLOB SQL type

I want to make this
SELECT row FROM 'table' WHERE id = x'00000007'
this works fine with SQLite browser app and my db.
But following qt does not work and returns no items"

QString queryContent = "SELECT row FROM \'" + tableName + "\' WHERE id = :id";
QSqlQuery query;
query.prepare(queryContent);
query.bindValue(":id", t_id);

comment: t_id is QByteArray

How to fix Qt implementation?

jsulm

@Kofr http://doc.qt.io/qt-5/qbytearray.html#toHex

QString queryContent = "SELECT row FROM \'" + tableName + "\' WHERE id = x':id'";
QSqlQuery query;
query.prepare(queryContent);
query.bindValue(":id", t_id.toHex());

Kofr

It converts to HEX correctly but query fails
QSqlQuery::value: not positioned on a valid record
does not work neither

QString queryContent = "SELECT row FROM \'" + QString::number(parentInt) + "\' WHERE id = x\':id\'";
    QSqlQuery query;
    query.prepare(queryContent);
    QByteArray idBA = t_id.toHex();
    qDebug() << idBA;
    query.bindValue(":id", idBA);

and

QString queryContent = "SELECT row FROM \'" + QString::number(parentInt) + "\' WHERE id = x':id'";
...

jsulm

@Kofr said in SQLite WHERE clause with BLOB. Or how to make QT query BLOB as x'00000000'?:

QSqlQuery

What does http://doc.qt.io/qt-5/qsqlquery.html#lastQuery return after you try to execute it?
Is there a row with the ID you're trying to retrieve?
Also I'm wondering what you are doing here:

"SELECT row FROM \'" + QString::number(parentInt) + "\' WHERE id = x\':id\'";

Is the name of the table a number?!
The table name should be bound as well.

Kofr

@jsulm your first answer is not right as Qt does not care what to bind Hex or byte array I think.

solution which works for me is

    QString queryContent = "SELECT row FROM \'" + QString::number(parentInt) + "\' WHERE id = x\'" + QString::fromLatin1(idBA) + "\'";

Instead of binding, making a string.

kshegunov

@Kofr
If the above snippet works, then you don't respect the SQL's type. You're trying to pass a string representation to Qt when it clearly expects a binary (BLOB is "Binary Large OBject"). So then the question:

How to fix Qt implementation?

comes back as: "How to fix your implementation".
Anyway, I'd suggest passing the appropriate type to the driver. My thoughts are that something like this should be working:

const char rawData[4] = { 0x00, 0x00, 0x00, 0x07 };
QByteArray id = QByteArray::fromRawData(rawData, 4);

QSqlQuery query;
if (!query.prepare(QStringLiteral("SELECT row FROM %1 WHERE id = :id").arg(tableName)))
    // Handle the error!

query.bindValue(":id", id); // Pass binary data for columns that expect binary

if (!query.exec())
    // Handle error again!