Qt/QSqlQuery: Binary data is interpreted as string when binding to BLOB field

Question

I'm reading binary data from files and try to store it in a LONGBLOB field in a MySql database.
I'm using Python and PySide for this.

My problem is, that the binary data is interpreted as a string, meaning the data is truncated when a newline occurs in the data.

This seems to be a Python or PySide problem, because if I add data manually in the MySql prompt it works perfectly.
Edit: I just tried it with normal Qt using C++ and I have the same issue there.

My table definition is as follows:
CREATE TABLE blobtest (id INT PRIMARY KEY AUTO_INCREMENT, data LONGBLOB NOT NULL;

Below is a reproducer.

from PySide.QtSql import QSqlDatabase, QSqlQuery, QSql

def testit():
    db = QSqlDatabase.addDatabase("QMYSQL")
    db.setHostName("localhost")
    db.setDatabaseName("testdb")
    db.setUserName("user")
    db.setPassword("pass")
    db.open()

    query = QSqlQuery(db)
    data = "start\n\0\n\0\n\0end"
    query.prepare("INSERT INTO blobtest (data) VALUES(:data)")
    query.bindValue(":data", data, QSql.In | QSql.Binary)
    query.exec_()

    db.close()

###############################################################################

if __name__ == "__main__":
    testit()

Edit: here is the same reproducer in its c++ variant. I'm using a QByteArray for holding the data, but it still is truncated

#include <QtSql/QtSql>
#include <QtCore/QByteArray>

int main(int argc, char* argv[]) {
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testdb");
    db.setUserName("user");
    db.setPassword("pass");
    db.open();

    QSqlQuery query = QSqlQuery(db);
    QByteArray data = QByteArray("start\n\0\n\0\n\0\nend");
    query.prepare("INSERT INTO blobtest (data) VALUES(:data)");
    query.bindValue(":data", data, QSql::In | QSql::Binary);
    query.exec();

    db.close();
    return 0;
}

Does anyone have an idea what I could be doing wrong?

Answer 1

The data is really truncated when a NULL Character ('\0') is found in the data. In C++ you have to Create the QByteArray in this way:

QByteArray data = QByteArray("start\n\0\n\0\n\0\nend", 15);

I do not know python but I think that you have to create a QByteArray too.