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



  • 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?


  • Moderators

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


  • 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'";
    ...
    

  • Moderators

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



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


  • Qt Champions 2016

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

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.