[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
CREATE TABLE IF NOT EXISTS "test" (
"value" BLOB NOT NULL
);
You would use
CREATE TABLE IF NOT EXISTS "test" (
"value" BLOB NOT NULL
) STRICT;
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”)
Scott
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
Scott
-----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
CREATE TABLE IF NOT EXISTS "test" (
"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();
-->
"763130492d46ab4c9c7bb0e9789b29c3ab1e4723dea6c8e5d2f9b266a275e1fbe4a6"
Christian
_______________________________________________
Interest mailing list
Interest at qt-project.org<mailto:Interest at qt-project.org>
https://lists.qt-project.org/listinfo/interest
-------------- 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