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

[Cyril]

This works. I am afraid I'll have to stick to that but I really don't
understand why the maximum BLOB size is limited by the max allowed packet
size setting.

2011/9/8 BOUCARD Olivier <boucard_olivier at yahoo.fr>

> 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<http://dev.mysql.com/doc/refman/5.0/en/blob.html>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/20110909/a045b1d5/attachment.html