PDA

View Full Version : How can i store and retrive image / sound files to and from Remaote sql server



Askar
14th September 2009, 11:16
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
14th September 2009, 11:23
use base64 for encoding/decoding binary data and then store/restore it in a database.

Askar
14th September 2009, 12:33
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
14th September 2009, 13:08
have a look at QByteArray::toBase64 and QByteArray::fromBase64.

Askar
14th September 2009, 13:56
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
8th October 2009, 21:32
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();
}

Someone can tell me wehere my mistake is?

spirit
9th October 2009, 10:43
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();
}

soxs060389
9th October 2009, 13:35
Found my mistake, first row within my table had zer0 (NULL) in the blob field :-S .. dumb

soxs060389
9th October 2009, 17:48
And what was all the mess about toBase64() and fromBase64().

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

Greets

spirit
9th October 2009, 20:04
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. :o

soxs060389
9th October 2009, 20:50
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
21st May 2012, 06:02
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();
}



oh great...thanks so much... :D