[Qt-interest] Fwd: Store/Retrive Image (or file) into/from QSqlite BLOB field

[Diego Schulz]

On Mon, Jan 9, 2012 at 6:59 AM, Michele Ciampichetti
<cia.michele at gmail.com>wrote:

>
>
> ---------- Forwarded message ----------
> From: Michele Ciampichetti <cia.michele at gmail.com>
> Date: 2012/1/9
> Subject: Re: [Qt-interest] Store/Retrive Image (or file) into/from QSqlite
> BLOB field
> To: Esdras Beleza <listas at esdrasbeleza.com>
>
>
> Dear Esdras,
> thanks for your answer but... how I can do it? :) I need to subclass the
> QSqlRelationDelegate? how Can I use the QDataWidgetMapper with
> QPixmap::loadFromData()?
>
> Could you post a small example?
>
>
> Thanks a lot for your time
>
> Michele
>
>
> 2012/1/9 Esdras Beleza <listas at esdrasbeleza.com>
>
>>
>>
>> On 9 January 2012 04:52, Michele Ciampichetti <cia.michele at gmail.com>wrote:
>>
>>> Good morning to all,
>>> How I can store/retreive Image (or file) into/from QSqlite BLOB field
>>> using QDataWidgetMapper and QLabel (to show the picture) and
>>> QSqlRelationalTableModel?
>>>
>>> Could you send my any example?
>>>
>>> Thanks a lot for your time
>>>
>>>
>>
>>
>> It's not the full answer, but I think you can get the data of a blob
>> field, load into a pixmap using QPixmap::loadFromData() and load then use
>> QLabel::setPixmap() to put the pixmap into a QLabel.
>>
>> (I made it all night long.)
>>
>> Esdras
>>
>>

I tried to implement a proof of concept after reading your request. I'm
using PostgreSQL but the example should be easily adaptable to suit your
needs. This is a simple facility to store images in a single table.

First, I created a 'pixmaps' table with two columns, an integer 'id' that
autoincrements, and a text 'base64data' that stores the data encoded in
base64 (so your SQL backups will look a bit better than raw image data).
Then I implemented two simple functions to insert and retrieve QPixmaps.

-- SQL
-- PostgreSQL
CREATE TABLE pixmaps(id SERIAL NOT NULL PRIMARY KEY, base64data TEXT) ;

-- SQLite3
CREATE TABLE pixmaps(id INTEGER PRIMARY KEY, base64data TEXT) ;



// C++

int savePixmap(QPixmap &pmap){

    QByteArray bytes;
    QBuffer buffer(&bytes);
    buffer.open(QIODevice::WriteOnly);

    pmap.save(&buffer,"PNG");

    QSqlQuery query(QSqlDatabase::database("default"));
    query.exec(QString("INSERT INTO pixmaps(id,base64data) VALUES
(DEFAULT,'%1') RETURNING id").arg(bytes.toBase64().data()));

    if(query.first()){
        return query.value(0).toInt();
    }

    return -1;
}



QPixmap retrievePixmap(int id){

    QSqlQuery query(QSqlDatabase::database("default"));
    query.exec(QString("SELECT base64data FROM pixmaps WHERE
id=%1").arg(id));

    if(query.first()){
        QByteArray base64 = query.value(0).toByteArray();
        QByteArray bytes = QByteArray::fromBase64(base64);

        QPixmap pixmap;
        pixmap.loadFromData(bytes,"PNG");
        return pixmap;
    }

    return QPixmap();
}


Bear in mind that I'm using the RETURNING clause in the INSERT sentence,
which is available in PostgreSQL but probably is not available in
SQLite3. If that's the case, you can just return
query.lastInsertId().toInt(), which doesn't work with PostgreSQL without
using a deprecated feature (WITH OIDS).

If you need help to make it work with SQLite3, feel free to ask for help.

Regards,

diego
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.qt-project.org/pipermail/qt-interest-old/attachments/20120118/c5658108/attachment.html