[Interest] Qt Sql BLOB data converting

Scott Bloom scott at towel42.com
Mon Nov 28 20:23:32 CET 2022

Just a POINT of note.  Since SQL Lite has moved to “flexible typing” (its been a while) they have also added a strict datatype table

Which I have been using myself.

For this case, rather than

        "value" BLOB NOT NULL

You would use

        "value" BLOB NOT NULL

Then all types are returned as defined, and inserts may fail (IMO correctly) whereas before they would succeed.

I have found creating strict tables correct this an other issues (such as a string that has the value 00001234 being automatically converted to an integer, and then the value.toString() returning “1234”)


From: Yauheni Pervenenka <yauheni.pervenenka at viber.com>
Sent: Monday, November 28, 2022 11:16 AM
To: Scott Bloom <scott at towel42.com>
Cc: Christian Ehrlicher <Ch.Ehrlicher at gmx.de>; interest at qt-project.org
Subject: Re: [Interest] Qt Sql BLOB data converting

Scott, thanks you for your time

On Mon, Nov 28, 2022 at 10:11 PM Scott Bloom <scott at towel42.com<mailto:scott at towel42.com>> wrote:
@Christian Ehrlicher you beat me to the punch.

If you dump out the value (not the size) you will see the QVariant is a of type QString not QByteArray

        const auto value = query.value(0);
        qDebug() << "value = " << value;
        auto tmp = QVariant( value.toByteArray() );
        qDebug() << "tmp = " << tmp;

value =  QVariant(QString, "v10I-F½L\u009C{░?x\u009B)δ\u001EG#?????fóu??H")
tmp =  QVariant(QByteArray, "v10I-F\xC2\xABL\xC2\x9C{\xC2\xB0\xEF\xBF\xBDx\xC2\x9B)\xC3\xAB\x1EG#\xDE\xA6\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xBF\xBD""f\xC2\xA2u\xEF\xBF\xBD\xEF\xBF\xBD\xC4\xA6")

This is the root cause of the problem.

Ive hit this before, the query.value function should return a QByteArray type of QVariant, not a QString type when the column type is of type blob.

    auto record = db.record( "test" );
    qDebug() << "record =" << record;

returns the correct record type

record = QSqlRecord(1)
 0: QSqlField("value", QByteArray, tableName: "test", required: yes, generated: yes, autoValue: false, readOnly: false) ""

Whats interesting, is in my qsql_sqlite.cpp (the driver for the sql database,

sqlite3_column_decltype16 returns "BLOB" but sqlite3_column_type returns SQLITE_TEXT

in the init columns function, to determine which Qt type to use, it first gets the string name of the column, if empty then use  sqlite3_column_type otherwise it uses qGetColumnType

So the Record is correct, but the return type is of QString not QByteArray


-----Original Message-----
From: Interest <interest-bounces at qt-project.org<mailto:interest-bounces at qt-project.org>> On Behalf Of Christian Ehrlicher
Sent: Monday, November 28, 2022 10:41 AM
To: Yauheni Pervenenka <yauheni.pervenenka at viber.com<mailto:yauheni.pervenenka at viber.com>>; interest at qt-project.org<mailto:interest at qt-project.org>
Subject: Re: [Interest] Qt Sql BLOB data converting

Am 28.11.2022 um 19:16 schrieb Yauheni Pervenenka via Interest:
> Thiago, hello, thanks for your reply, sorry have no instructions for
> create and insert, but have data base with one value which can't be
> parsed correct(db is cut to one value for provide example)
> https://bugreports.qt.io/browse/QTBUG-108992
Your database looks strange to me - even though your column type according to the schema is BLOB:

sqlite> .schema
         "value" BLOB NOT NULL

the return value of sqlite3_column_type() is SQLITE_TEXT (3).

Therefore the return value is treated as string and the data is screwed up. If I change the return value to SQLITE_BLOB (4) in the debugger, all is correct:

qDebug() << value.toByteArray().toHex();




Interest mailing list
Interest at qt-project.org<mailto:Interest at qt-project.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20221128/9f84773e/attachment-0001.htm>

More information about the Interest mailing list