QByteArray as blob data inserted partially in sqlite3 (c++)

Question

I am trying to insert an image data which I have downloaded with QNetworkAccessManager as QByteArray. I am trying to insert this QByteArray as BLOB. My table creation query is

CREATE TABLE TILE_IMAGE(TILE_ID VARCHAR(1050), IMAGE_DATA BLOB,INSERTED_AT DATETIME,USED_AT DATETIME, PRIMARY KEY(TILE_ID))

I created the insert query like this,

    query = QString("INSERT INTO ");
    query.append("TILE_IMAGE");
    query.append(QString("(TILE_ID, IMAGE_DATA ,INSERTED_AT ,USED_AT) VALUES(\'"));
    query.append(*tileId);
    query.append(QString("\',\'"));
    query.append(QString(*imageData));
    query.append(QString("\',DATETIME('NOW'),DATETIME('NOW'))"));

But when I print the query it shows,

"INSERT INTO TILE_IMAGE(TILE_ID, IMAGE_DATA ,INSERTED_AT ,USED_AT) VALUES('ivbase_TILED_MAP_VIEW_16_37396_22531','Ã¿Ã˜Ã¿Ã ',DATETIME('NOW'),DATETIME('NOW'))"

See the BLOB data is only 4 length long. And has some weird values. The screen shot of what the *imageData contains is, enter image description here

The image 256*256*4 length long. Its a jpeg image.

EDIT: I followed CL's code and now the IMAGE_DATA column has some diamon shape characters. The image is below. enter image description here

score 0 · Answer 1 · answered Jul 11 '13 at 07:43

0

You cannot convert the binary contents of blob into a string. You must use parameters instead:

QSqlQuery query;
query.prepare("INSERT INTO TILE_IMAGE(TILE_ID, IMAGE_DATA) VALUES(?,?)");
query.bindValue(0, *tileId);
query.bindValue(1, *imageData);
query.exec();

answered Jul 11 '13 at 07:43

CL.

173,858
17
217
259

Hi. I changed the code like yours. Now its showing some diamond characters. which are also 4 length long. I edited my post with the picture. Thanks for the reply. – Tahlil Jul 11 '13 at 09:10

score 0 · Accepted Answer · answered Jul 21 '13 at 05:21

How I managed to do it is

    QString query = QString("INSERT INTO ");
    query.append("TILE_IMAGE");
    query.append(QString("(TILE_ID, IMAGE_DATA ,INSERTED_AT ,USED_AT) VALUES(\'"));
    query.append(*tileId);
    query.append(QString("\',\'"));
    query.append(QString(imageData->toBase64()));
    query.append(QString("\',DATETIME('NOW'),DATETIME('NOW'))"));
    load(query, QUERY_TYPE_CHANGE,false);

though it is still showing the diamond shaped characters when I read it from the database like this it worked,

    QString query;
    query = QString("SELECT");
    query.append(" IMAGE_DATA FROM TILE_IMAGE WHERE TILE_ID=\'");
    query.append(tileId).append(QString("\'"));
    int id = getId();
    load(query, id,true);
    query = resultList.value(0).toMap().value("IMAGE_DATA").value<QString>();
    QByteArray arr = QByteArray::fromBase64(query.toLatin1());

It will be helpful though to know why it is showing the diamond shaped characters?

QByteArray as blob data inserted partially in sqlite3 (c++)

2 Answers2