How can i store and retrive image / sound files to and from Remaote sql server

[Askar]

Dear Friends,
Now i can store and retrieve sound and image files in local database. now i want to store and retrieve the same in remote sql server database.

I can store the binary data into remote sql by converting the byteArray to QString while using the insert command. while retrieving i could'nt get original format.
thanks,
Askar

[spirit]

use base64 for encoding/decoding binary data and then store/restore it in a database.

[Askar]

use base64 for encoding/decoding binary data and then store/restore it in a database.

could you explain with a example code pls

now i can insert binary data to remote sql server, by storing QByteArray to QString in the insert statement.

while retrieving from remote sql server it gives binary base64 encoded format data as string, i tried to decode string by using QByteArray::fromBase64(); but i couldnt get that original file

[spirit]

have a look at QByteArray::toBase64 and QByteArray::fromBase64.

[Askar]

have a look at QByteArray::toBase64 and QByteArray::fromBase64.

I cont apply toBase64 while insert operation ...Qt gives the following runtime error from sql server

<h1>Url Too Long</h1>

[soxs060389]

I made up a quick dummy snippet which is supposed to load a file from the current directory (some png file) and encode it into databse and reload it from there and save it under another name to the ucrrent direcotry:

#include <QtDebug>
#include <QtSql>
#include <QtCore>
#include <QImage>
#include <QApplication>
#include <QWidget>
#include <QVariant>
#include <QByteArray>
#include <QFile>
#include <QBuffer>
 
int main (int argc, char **argv){
    QApplication app(argc,argv);
    QImage img("./A.png");
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testdb");
    db.setUserName("root");
    db.setPassword("abc123"); // not rly
    if (!db.open() )
        qDebug("Mising db / unable to open");
    else {
        QSqlTableModel model;
        model.setTable("testtable");
 
        int row = 0;
        model.insertRows( row, 1);
 
        QByteArray ba;
        QBuffer buffer(&ba);
        buffer.open(QIODevice::WriteOnly);
        img.save(&buffer, "PNG");
        model.setData(model.index(row,0),ba.toBase64());
        model.submitAll();
 
        QVariant v(model.data(model.index(row,0)));
 
        // this won't work
        QFile f("./clone.png");
        f.open(QIODevice::ReadWrite | QIODevice::Truncate);
        if (!f.error())
        {
            QByateArray ba2 = v.toByteArray();
            ba2.toBase64();
            f.write(ba2);
            f.close();
        }
        else
            qDebug("unable to open file f");
    }
// not really reqired, probably totally useless... indeed useless...
    QWidget * a = new QWidget();
    app.setApplicationName("halligalli sql");
    app.setActiveWindow(a);
    a->show();
    return app.exec();
}

#include <QtDebug>
#include <QtSql>
#include <QtCore>
#include <QImage>
#include <QApplication>
#include <QWidget>
#include <QVariant>
#include <QByteArray>
#include <QFile>
#include <QBuffer>

int main (int argc, char **argv){
    QApplication app(argc,argv);
    QImage img("./A.png");
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testdb");
    db.setUserName("root");
    db.setPassword("abc123"); // not rly
    if (!db.open() )
        qDebug("Mising db / unable to open");
    else {
        QSqlTableModel model;
        model.setTable("testtable");

        int row = 0;
        model.insertRows( row, 1);

        QByteArray ba;
        QBuffer buffer(&ba);
        buffer.open(QIODevice::WriteOnly);
        img.save(&buffer, "PNG");
        model.setData(model.index(row,0),ba.toBase64());
        model.submitAll();

        QVariant v(model.data(model.index(row,0)));

        // this won't work
        QFile f("./clone.png");
        f.open(QIODevice::ReadWrite | QIODevice::Truncate);
        if (!f.error())
        {
            QByateArray ba2 = v.toByteArray();
            ba2.toBase64();
            f.write(ba2);
            f.close();
        }
        else
            qDebug("unable to open file f");
    }
// not really reqired, probably totally useless... indeed useless...
    QWidget * a = new QWidget();
    app.setApplicationName("halligalli sql");
    app.setActiveWindow(a);
    a->show();
    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

Someone can tell me wehere my mistake is?

[spirit]

try this example, works fine for me.

#include <QtGui>
#include <QtSql>
#include <QApplication>
 
int main(int argc, char **argv)
{
	QApplication app(argc, argv);
 
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("pictures");
	if (!db.open())
		return db.lastError().number();
 
	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE IF NOT EXISTS pictures (id INT PRIMARY KEY, pic BLOB)");
 
	QImage image("flower.jpg");
	qDebug() << image.size();
	QByteArray byteArray;
	QBuffer buffer(&byteArray);
	buffer.open(QIODevice::WriteOnly);
	image.save(&buffer, "jpg");
 
	query.prepare("INSERT INTO pictures (id, pic) VALUES (?, ?)");
	query.addBindValue(1);
	query.addBindValue(byteArray);
	qDebug() << query.exec();
 
	qDebug() << query.exec("SELECT pic FROM pictures WHERE id = 1");
	qDebug() << query.next();
 
	byteArray = query.value(0).toByteArray();
	QPixmap pixmap = QPixmap::fromImage(QImage::fromData(byteArray, "jpg"));
	qDebug() << pixmap.size();
 
	QLabel label;
	label.setPixmap(pixmap);
	label.show();
 
	return app.exec();
}

#include <QtGui>
#include <QtSql>
#include <QApplication>

int main(int argc, char **argv)
{
	QApplication app(argc, argv);

	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("pictures");
	if (!db.open())
		return db.lastError().number();

	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE IF NOT EXISTS pictures (id INT PRIMARY KEY, pic BLOB)");

	QImage image("flower.jpg");
	qDebug() << image.size();
	QByteArray byteArray;
	QBuffer buffer(&byteArray);
	buffer.open(QIODevice::WriteOnly);
	image.save(&buffer, "jpg");

	query.prepare("INSERT INTO pictures (id, pic) VALUES (?, ?)");
	query.addBindValue(1);
	query.addBindValue(byteArray);
	qDebug() << query.exec();

	qDebug() << query.exec("SELECT pic FROM pictures WHERE id = 1");
	qDebug() << query.next();

	byteArray = query.value(0).toByteArray();
	QPixmap pixmap = QPixmap::fromImage(QImage::fromData(byteArray, "jpg"));
	qDebug() << pixmap.size();

	QLabel label;
	label.setPixmap(pixmap);
	label.show();

	return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[soxs060389]

Found my mistake, first row within my table had zer0 (NULL) in the blob field :-S .. dumb

[soxs060389]

And what was all the mess about toBase64() and fromBase64().

if I use that, I can't recreate the image from db?

Greets

[spirit]

try my example, it stores to database and recreates an image from database.
you don't need to use base64 at all. it was my mistake, I thought that you need to store an xml with an image into databse.

[soxs060389]

oh, ok... you may be forgiven.. things just happen.

So my code works too, (just drop the *Base64 stuff) and I prefer at as I disklike SQL syntax blah... ;-)

Note: no my program code is not cluttered as the example ;-) it was just rapid typing...

[thaihoangluu]

try this example, works fine for me.

#include <QtGui>
#include <QtSql>
#include <QApplication>
 
int main(int argc, char **argv)
{
	QApplication app(argc, argv);
 
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("pictures");
	if (!db.open())
		return db.lastError().number();
 
	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE IF NOT EXISTS pictures (id INT PRIMARY KEY, pic BLOB)");
 
	QImage image("flower.jpg");
	qDebug() << image.size();
	QByteArray byteArray;
	QBuffer buffer(&byteArray);
	buffer.open(QIODevice::WriteOnly);
	image.save(&buffer, "jpg");
 
	query.prepare("INSERT INTO pictures (id, pic) VALUES (?, ?)");
	query.addBindValue(1);
	query.addBindValue(byteArray);
	qDebug() << query.exec();
 
	qDebug() << query.exec("SELECT pic FROM pictures WHERE id = 1");
	qDebug() << query.next();
 
	byteArray = query.value(0).toByteArray();
	QPixmap pixmap = QPixmap::fromImage(QImage::fromData(byteArray, "jpg"));
	qDebug() << pixmap.size();
 
	QLabel label;
	label.setPixmap(pixmap);
	label.show();
 
	return app.exec();
}

#include <QtGui>
#include <QtSql>
#include <QApplication>

int main(int argc, char **argv)
{
	QApplication app(argc, argv);

	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName("pictures");
	if (!db.open())
		return db.lastError().number();

	QSqlQuery query;
	qDebug() << query.exec("CREATE TABLE IF NOT EXISTS pictures (id INT PRIMARY KEY, pic BLOB)");

	QImage image("flower.jpg");
	qDebug() << image.size();
	QByteArray byteArray;
	QBuffer buffer(&byteArray);
	buffer.open(QIODevice::WriteOnly);
	image.save(&buffer, "jpg");

	query.prepare("INSERT INTO pictures (id, pic) VALUES (?, ?)");
	query.addBindValue(1);
	query.addBindValue(byteArray);
	qDebug() << query.exec();

	qDebug() << query.exec("SELECT pic FROM pictures WHERE id = 1");
	qDebug() << query.next();

	byteArray = query.value(0).toByteArray();
	QPixmap pixmap = QPixmap::fromImage(QImage::fromData(byteArray, "jpg"));
	qDebug() << pixmap.size();

	QLabel label;
	label.setPixmap(pixmap);
	label.show();

	return app.exec();
}

To copy to clipboard, switch view to plain text mode 

oh great...thanks so much...