Qt 5.8 MySQL Input Medium Blob have diffrent size then Output

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.

Code:

---

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();
    }
}

---

What is the MySQL datatype for "termsServiceContract"?

Quote:

---

Originally Posted by d_stranz

What is the MySQL datatype for "termsServiceContract"?

---

termsServiceFileName VARCHAR(45)
termsServiceContract MEDIUMBLOB

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:

Code:

---

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

---

Can you give that a try?

Quote:

---

Originally Posted by 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:

Code:

---

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

---

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 ??

Quote:

---

Originally Posted by Pablik

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.

Quote:

---

Originally Posted by jefftee

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

Quote:

---

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.

Quote:

---

Originally Posted by Pablik

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.