Qt 5.8 MySQL Input Medium Blob have diffrent size then Output

[Pablik]

Hi, i have problem with keep binay file in MySQL, when i load file to QByteArray he has size 142652, after insert to MySQL his size is 279514. Why ??
File type is ".pdf" after load file to disk i cant open this file, i have error.

void OrderView::on_pushButtonSave_clicked()
{
    QString filePath = QFileDialog::getOpenFileName(this);
 
    QByteArray byteArray;
    QFileInfo fileInfo(filePath);
    QFile file(filePath);
    if(file.open(QIODevice::ReadOnly))
    {
        byteArray = file.readAll();
        file.close();
    }
 
    QSqlQuery query("UPDATE `orders` SET `termsServiceFileName`=?,`termsServiceContent`=? WHERE `orderID`=?");
    query.addBindValue( fileInfo.fileName() );
    query.addBindValue(  byteArray );
    query.addBindValue( editOrderID );
    query.exec();
 
    QMessageBox::critical(this,"", QString::number( byteArray.size()  ));  //Message show "142652" but in MyPhpAdmin show "[BLOB - 273 KB]" WHY???
 
}
 
void OrderView::on_pushButtonLoad_clicked()
{
    QSqlQuery query("SELECT `termsServiceFileName`, `termsServiceContent` FROM `orders` WHERE `orderID`=?");
    query.addBindValue( editOrderID );
    query.exec();
    query.next();
 
    QByteArray byteArray = query.value(1).toByteArray();
    QMessageBox::critical(this,"",  QString::number( byteArray.size() ));     //Message show "279514"
 
 
    QString filePath = QFileDialog::getSaveFileName(this);
    QFile file(filePath);
    if(file.open(QIODevice::ReadWrite))
    {
        file.write( byteArray );
        file.close();
    }
}

void OrderView::on_pushButtonSave_clicked()
{
    QString filePath = QFileDialog::getOpenFileName(this);

    QByteArray byteArray;
    QFileInfo fileInfo(filePath);
    QFile file(filePath);
    if(file.open(QIODevice::ReadOnly))
    {
        byteArray = file.readAll();
        file.close();
    }

    QSqlQuery query("UPDATE `orders` SET `termsServiceFileName`=?,`termsServiceContent`=? WHERE `orderID`=?");
    query.addBindValue( fileInfo.fileName() );
    query.addBindValue(  byteArray );
    query.addBindValue( editOrderID );
    query.exec();
    
    QMessageBox::critical(this,"", QString::number( byteArray.size()  ));  //Message show "142652" but in MyPhpAdmin show "[BLOB - 273 KB]" WHY???

}

void OrderView::on_pushButtonLoad_clicked()
{
    QSqlQuery query("SELECT `termsServiceFileName`, `termsServiceContent` FROM `orders` WHERE `orderID`=?");
    query.addBindValue( editOrderID );
    query.exec();
    query.next();

    QByteArray byteArray = query.value(1).toByteArray();
    QMessageBox::critical(this,"",  QString::number( byteArray.size() ));     //Message show "279514"


    QString filePath = QFileDialog::getSaveFileName(this);
    QFile file(filePath);
    if(file.open(QIODevice::ReadWrite))
    {
        file.write( byteArray );
        file.close();
    }
}

To copy to clipboard, switch view to plain text mode 

[d_stranz]

What is the MySQL datatype for "termsServiceContract"?

[Pablik]

What is the MySQL datatype for "termsServiceContract"?

termsServiceFileName VARCHAR(45)
termsServiceContract MEDIUMBLOB

[jefftee]

When you use the QSqlQuery constructor overload for QSqlQuery::QSqlQuery(const QString &query = QString(), QSqlDatabase db = QSqlDatabase()), the SQL statement is executed. I suspect what you want is something similar to:

QSqlQuery query(db);
query.prepare("UPDATE orders SET termsServiceFileName=?, termsServiceContent=? WHERE orderID=?");
query.addBindValue(fileInfo.fileName());
query.addBindValue(byteArray);
query.addBindValue(editOrderID);
query.exec();

QSqlQuery query(db);
query.prepare("UPDATE orders SET termsServiceFileName=?, termsServiceContent=? WHERE orderID=?");
query.addBindValue(fileInfo.fileName());
query.addBindValue(byteArray);
query.addBindValue(editOrderID);
query.exec();

To copy to clipboard, switch view to plain text mode 

Can you give that a try?

[Pablik]

When you use the QSqlQuery constructor overload for QSqlQuery::QSqlQuery(const QString &query = QString(), QSqlDatabase db = QSqlDatabase()), the SQL statement is executed. I suspect what you want is something similar to:

QSqlQuery query(db);
query.prepare("UPDATE orders SET termsServiceFileName=?, termsServiceContent=? WHERE orderID=?");
query.addBindValue(fileInfo.fileName());
query.addBindValue(byteArray);
query.addBindValue(editOrderID);
query.exec();

QSqlQuery query(db);
query.prepare("UPDATE orders SET termsServiceFileName=?, termsServiceContent=? WHERE orderID=?");
query.addBindValue(fileInfo.fileName());
query.addBindValue(byteArray);
query.addBindValue(editOrderID);
query.exec();

To copy to clipboard, switch view to plain text mode 

Can you give that a try?

Result is the same.

I foud something interesting. On ".txt" file work properly but on ".jpg" or ".pdf" dont work properly. Maybe when i load data from file "byteArray = file.readAll();" i have to set something like load binary data ??

Or NO, Because when i load ".jpg" file to QByteArray and save to ".jpg" file without MySQL then work propertly.
Maybe when i insert QByteArray to MySQL the i have to specify its binary data no text data ??

When i add "query.addBindValue(byteArray);" MySQL think it is text data, how insert this data as binary data ??

[jefftee]

I foud something interesting. On ".txt" file work properly but on ".jpg" or ".pdf" dont work properly. Maybe when i load data from file "byteArray = file.readAll();" i have to set something like load binary data ??

I suspect your problem is that MySQL is (for some reason) encoding your data in some way, perhaps UTF-8 for example. This shouldn't be happening from what I see regarding the doc for blob data, but the size of your data expands when inserted. When you read it from the database, you get the result that matches the size you see in the actual column, which would indicate that MySQL is simply returning the data to you that it has stored w/o any type of transformation/decoding of the data.

I suspect this may be a bug with MySQL, because it doesn't appear that blob data should be encoded at all, but that looks like what is happening here...

One thing you could try and I suspect it would work, would be to base64 encode your data before you insert it and base64 decode it after you read it out the the database.

Good luck.

[Pablik]

One thing you could try and I suspect it would work, would be to base64 encode your data before you insert it and base64 decode it after you read it out the the database.

Good luck.

It's work, when i convert QByteArra::toBase64() the size is the same in QByteArra and in MySQL, but when i load from MySQL toBase64() how to convert to orginal QByteArra ??

OK i found QByteArra::fromBase64()

Everything work, thx for help

[d_stranz]

I suspect your problem is that MySQL is (for some reason) encoding your data in some way, perhaps UTF-8 for example.

This was also my suspicion, and base64-encoding would have been my next suggestion but jefftee has a faster keyboard. Glad this worked.

[jefftee]

Everything work, thx for help

Glad you got it working. The base64 encoding will bloat your data unnecessarily but at least it can survive the round trip into and out of the database! If you have other version(s) of MySQL you can play with, I'd do some testing because I suspect that it's a bug with the version you're using.