Inserting blob into mysql using QT?

[maverick_pol]

Hi,

I am not so strong in mysql/sql and have this, hopefully, easy question:

How to store/read a .png file in mysql database using Qt.
I have a table containing 2 columns: snap_time,snap; (datetime,blob)

I tried this, but I get an error:

QSqlQuery query;
	QByteArray bArray;
	QBuffer buffer(&bArray);
	buffer.open(QIODevice::WriteOnly);
	QDataStream out(&buffer);
	out << _pixmap.toImage();
	buffer.close();
	query.prepare("insert into memos(snap,snap_time) values (:snap, :time)");
	query.bindValue(":snap",bArray->constData());
	query.bindValue(":time",_time.toString("yyyy-MM-dd hh:mm:ss"));
	if(!query.exec())
		QMessageBox::warning(0,"Error inserting snap","SNAP");

QSqlQuery query;
	QByteArray bArray;
	QBuffer buffer(&bArray);
	buffer.open(QIODevice::WriteOnly);
	QDataStream out(&buffer);
	out << _pixmap.toImage();
	buffer.close();
	query.prepare("insert into memos(snap,snap_time) values (:snap, :time)");
	query.bindValue(":snap",bArray->constData());
	query.bindValue(":time",_time.toString("yyyy-MM-dd hh:mm:ss"));
	if(!query.exec())
		QMessageBox::warning(0,"Error inserting snap","SNAP");

To copy to clipboard, switch view to plain text mode 

Thank you for help

[maverick_pol]

Anyone?

Kacper

[babygal]

can you change line 10 to :

query.bindValue(":time",_time.toString("yyyyMMddhhmmss"));

query.bindValue(":time",_time.toString("yyyyMMddhhmmss"));

To copy to clipboard, switch view to plain text mode 

[ecanela]

i create a class to manage BLOB fields. i tested in SQLITE and FIREBIRD. i think must work in MYSQL

class SqlFieldBlob : public QObject
{
    Q_OBJECT
public:
    explicit SqlFieldBlob(QObject *parent = 0);
 
    QByteArray data() { return p_byteArray; }
    bool saveData(const QString &filename);
 
public slots:
    void setData(const QByteArray &byteArray) { p_byteArray = byteArray;}
    void setFileName(const QString &filename);
    void setPixmap(const QPixmap &pixmap);
 
private:
    QByteArray p_byteArray;
};

class SqlFieldBlob : public QObject
{
    Q_OBJECT
public:
    explicit SqlFieldBlob(QObject *parent = 0);

    QByteArray data() { return p_byteArray; }
    bool saveData(const QString &filename);

public slots:
    void setData(const QByteArray &byteArray) { p_byteArray = byteArray;}
    void setFileName(const QString &filename);
    void setPixmap(const QPixmap &pixmap);

private:
    QByteArray p_byteArray;
};

To copy to clipboard, switch view to plain text mode 

and using something like

SqlFieldBlob blob;
    blob.setFileName(fileName);
 
    QSqlQuery query2;
    query2.prepare("INSERT INTO files (file_name, file_data) "
                   "VALUES (:id, :id2)");
    query2.bindValue("id", fileName);
    query2.bindValue("id2", blob.data());
    query2.exec();

SqlFieldBlob blob;
    blob.setFileName(fileName);

    QSqlQuery query2;
    query2.prepare("INSERT INTO files (file_name, file_data) "
                   "VALUES (:id, :id2)");
    query2.bindValue("id", fileName);
    query2.bindValue("id2", blob.data());
    query2.exec();

To copy to clipboard, switch view to plain text mode 

[ecanela]

code to convert a QPixmap in a QByteArray

QByteArray BlobClass::convertPixmap(const QPixmap &pixmap)
{
    QByteArray bytes;
    QBuffer buffer(&bytes);
    buffer.open(QIODevice::WriteOnly);
    pixmap.save(&buffer, "PNG");
    return buffer.data();
}

QByteArray BlobClass::convertPixmap(const QPixmap &pixmap)
{
    QByteArray bytes;
    QBuffer buffer(&bytes);
    buffer.open(QIODevice::WriteOnly);
    pixmap.save(&buffer, "PNG");
    return buffer.data();
}

To copy to clipboard, switch view to plain text mode