[Qt-interest] Re : QtSql - fail to send blob data

[BOUCARD Olivier]

Just a remark: Only InnoDb engine support transaction in MySQL and in most case MyIsam is used by default. So your test with transaction could be irrelevant.


What I understand from the MySQL doc even if  you use LONGBLOB the maximum BLOB size will be the maximum packet size:
"The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory."
So if your maximum packet size is 1M, even if you split into multiple insert you are still limited to 1M...




>________________________________
>De : Cyril <cyril.mailing.list at gmail.com>
>À : Qt-interest at qt.nokia.com
>Envoyé le : Jeudi 8 Septembre 2011 18h01
>Objet : Re: [Qt-interest] QtSql - fail to send blob data
>
>
>Sure, but that's not the problem. I initialize the field as an empty blob.
>Everything looks just as if the driver would not "close the packet" even though the successive chunks are sent one by one to the mysql server at each QSqlQuery::exec().
>Even enclosing each UPDATE query in an explicit transaction doesn't help.
>
>
>2011/9/8 Robert Hairgrove <evorgriahr at hispeed.ch>
>
>On Thu, 2011-09-08 at 16:29 +0200, Cyril wrote:
>>> Thanks for your answers.
>>> I am using MySQL server 5.5.8, MySQL database driver of Qt 4.7 ,
>>> libmysql 6.0.2, under windows 7.
>>> I tried the CONCAT syntax using hex-encoded blob but it still fails
>>> the same way. Here is the code :
>>>
>>>
>>> {
>>>   QSqlQuery q1(db);
>>>   q1.prepare("INSERT INTO t (id, data) VALUES(42, '')"); // field data
>>> is a LONGBLOB
>>>   q1.exec();
>>> }
>>>
>>>
>>> QByteArray bigData; // several megabytes of data to insert in DB
>>> const int chunkSize = 50000;
>>>
>>>
>>> int i = 0;
>>> while(!bigData.isEmpty()) {
>>>   QByteArray chunk = bigData.left(chunkSize);
>>>   bigData = bigData.mid(chunkSize);
>>>
>>>
>>>   QString str = QString::fromLatin1("UPDATE t SET data=CONCAT(data, 0x
>>> %1) WHERE id=42").arg(QString(chunk.toHex()));
>>>
>>>
>>>   QSqlQuery q2(db);
>>>   q2.prepare(str);
>>>   q2.exec(); //<<<<<< still fails when i==max_allowed_packet/chunkSize
>>>
>>>
>>>   i++;
>>> }
>>>
>>>
>>>
>>>
>>> Note that this technique works fine when using phpmyadmin or any other
>>> mysql interactive kind of console, which proves that the BLOB data
>>> doesn't get mangled by CONCAT.
>>> Anybody has an idea? Bug in QtSql?
>>
>>What happens if data is NULL on the first update? In that case, CONCAT
>>would also return NULL. This is safer:
>>
>>
>> QString str = QString::fromLatin1("UPDATE t "
>>
                "SET data=CONCAT(COALESCE(data,''), 0x%1) WHERE id=42")
>>                .arg(QString(chunk.toHex()));
>>
>>
>>
>>
>>_______________________________________________
>>Qt-interest mailing list
>>Qt-interest at qt.nokia.com
>>http://lists.qt.nokia.com/mailman/listinfo/qt-interest
>>
>
>_______________________________________________
>Qt-interest mailing list
>Qt-interest at qt.nokia.com
>http://lists.qt.nokia.com/mailman/listinfo/qt-interest
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20110908/3357f7de/attachment.html