how to save a image file ( say png ) into mysql database?

[AviMittal]

Hi All

Is there any way to save image/pdf files into the mysql database. I am Qt4/C++ using QMYSQL plugin.
I am able to store and retrieve txt files into the database but not any other type of files.

Please suggest.

[spirit]

create a BLOB field in MySQL database, then convert needed image in base64 using QByteArray::toBase64 and store it in database. to restore image you need to use QByteArray::fromBase64 and then create an image.

[AviMittal]

i tried the same but it didnt worked.

[spirit]

it works fine for me. what did you try?

[AviMittal]

i am doing it following way..

say have an image file named: apple.png
i opened the file and read it completely using file.readAll(). and saved it into QString (str).
then i converted this QString(str) into QbyteArray and then called toBase64(). and then tried to save it in database and in same way i tried to read it from database but it didnt worked.

Can you share your sample code for this?

thanx

[faldzip]

why are you saving data to QString and then converting it to QByteArray? isn't readAll() alredy returning QByteArray? try to readAll() straight into QByteArray without any QStirings

[AviMittal]

its not working for me

[faldzip]

can you show us your code?

[soxs060389]

Save given QImage directly into QByteArray:

QImage image;
         QByteArray ba;
         QBuffer buffer(&ba);
         buffer.open(QIODevice::WriteOnly);
         image.save(&buffer, "PNG");

QImage image;
         QByteArray ba;
         QBuffer buffer(&ba);
         buffer.open(QIODevice::WriteOnly);
         image.save(&buffer, "PNG");

To copy to clipboard, switch view to plain text mode 

(Taken from Documentation)

[yan]

create a BLOB field in MySQL database, then convert needed image in base64 using QByteArray::toBase64 and store it in database. to restore image you need to use QByteArray::fromBase64 and then create an image.

Why encode QByteArray to base 64?
BLOB is a binary entry and you can use the QByteArray directely.

[soxs060389]

SQL stores blob fields internally as 64bit sized byte arrays, so if you don't convert to base 64, you waste 50% of space, though it shouldn't really make anything work/break which previously worked/was broken....
Correct me if I am wrong, I am not very familiar with mySQL

[yan]

SQL stores blob fields internally as 64bit sized byte arrays, so if you don't convert to base 64, you waste 50% of space, though it shouldn't really make anything work/break which previously worked/was broken....
Correct me if I am wrong, I am not very familiar with mySQL

Base64 it just a special encoding of binary using 64 char :
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789+/"
It's generally use to transfer safely a binary throw network.

http://fr.wikipedia.org/wiki/Base64

If you convert a binary to base64 you increase the binary size by 137%.

But maybe i don't understand something...

[Gokulnathvc]

I have tried connecting the saving the image to the database, but it fails. i have used the following code for updating the database with the image data:

QScreenShot::QScreenShot(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::QScreenShot)
{
     ui->setupUi(this);
     c=0;
     bool ok=false;
     QDateTime dateTime = QDateTime::currentDateTime();
     QString dateTimeString = dateTime.toString();
 
     QDir().mkdir("D:\\QtImages\\");
     QDir().setCurrent("D:\\QtImages\\");
     QDir().mkdir("ScreenShots");
     QDir().setCurrent("ScreenShots");
     QDir().mkdir(dateTimeString);
 
     QMessageBox::warning(NULL,"DriveList",dateTimeString,QMessageBox::Ok);
 
     QStringList list=QSqlDatabase::drivers();
     QString driveList;
     for(int i=0;i<list.length();i++)
     {
         driveList += list[i];
     }
       //QMessageBox::warning(NULL,"DriveList",driveList,QMessageBox::Ok);
     QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
     db = QSqlDatabase::addDatabase("QODBC");
     db.setDatabaseName("DRIVER={MYSQL ODBC 3.51 Driver};FIL={MYSQL};DBQ=screengrabber");
     db.setHostName("localhost");
     db.setConnectOptions("CLIENT_ODBC");
     //db.setDatabaseName("screengrabber");
     db.setUserName("root");
     db.setPassword("1");
     ok = db.open();
 
     for(;;)
     {
 
            originalPixmap =QPixmap();
            originalPixmap = QPixmap::grabWindow(QApplication::desktop()->winId());
            QString strfname;
            strfname.sprintf("%d",c);
            originalPixmap.save("D:\\image"+strfname+".jpeg","jpeg");
            c++;
            char Data;
            QFile file("D:\\image"+strfname+".jpeg");
            file.open(QIODevice::ReadOnly);
            file.seek(0);
            QByteArray buf;
            buf=file.read(250000);
            QSqlQuery query;
            query.prepare("INSERT INTO log VALUES('grab_date='2011-04-26 15:55:09',ip_address='172.16.0.51',image=:val');");
            query.bindValue ( ":val", buf);
            QString strquery;
            strquery = "INSERT INTO log (grab_date,ip_address,image) VALUES ( '";
            strquery += '2011-04-26 15:55:09';
            strquery += "' , '";
            strquery += '172.16.0.51';
            strquery += "' , ";
            strquery += buf;
            strquery += " );";
 
          //QMessageBox::warning(NULL,"DriveList",strquery,QMessageBox::Ok);
 
            QSqlQuery insertQuery( strquery, db);
            bool done = insertQuery.exec();
 
            QFile newfile("D:\\img.txt");
            newfile.open(QIODevice::WriteOnly);
            newfile.write(buf);
 
            int iSecret, iRandom;
            iSecret = rand() % 20 + 1;
            iRandom=iSecret*60000;
 
            QMutex mutex;
            mutex.lock();
            QWaitCondition waitCondition;
            waitCondition.wait(&mutex, iRandom);
            mutex.unlock();
 
    }
 
}

QScreenShot::QScreenShot(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::QScreenShot)
{
     ui->setupUi(this);
     c=0;
     bool ok=false;
     QDateTime dateTime = QDateTime::currentDateTime();
     QString dateTimeString = dateTime.toString();

     QDir().mkdir("D:\\QtImages\\");
     QDir().setCurrent("D:\\QtImages\\");
     QDir().mkdir("ScreenShots");
     QDir().setCurrent("ScreenShots");
     QDir().mkdir(dateTimeString);

     QMessageBox::warning(NULL,"DriveList",dateTimeString,QMessageBox::Ok);

     QStringList list=QSqlDatabase::drivers();
     QString driveList;
     for(int i=0;i<list.length();i++)
     {
         driveList += list[i];
     }
       //QMessageBox::warning(NULL,"DriveList",driveList,QMessageBox::Ok);
     QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
     db = QSqlDatabase::addDatabase("QODBC");
     db.setDatabaseName("DRIVER={MYSQL ODBC 3.51 Driver};FIL={MYSQL};DBQ=screengrabber");
     db.setHostName("localhost");
     db.setConnectOptions("CLIENT_ODBC");
     //db.setDatabaseName("screengrabber");
     db.setUserName("root");
     db.setPassword("1");
     ok = db.open();

     for(;;)
     {

            originalPixmap =QPixmap();
            originalPixmap = QPixmap::grabWindow(QApplication::desktop()->winId());
            QString strfname;
            strfname.sprintf("%d",c);
            originalPixmap.save("D:\\image"+strfname+".jpeg","jpeg");
            c++;
            char Data;
            QFile file("D:\\image"+strfname+".jpeg");
            file.open(QIODevice::ReadOnly);
            file.seek(0);
            QByteArray buf;
            buf=file.read(250000);
            QSqlQuery query;
            query.prepare("INSERT INTO log VALUES('grab_date='2011-04-26 15:55:09',ip_address='172.16.0.51',image=:val');");
            query.bindValue ( ":val", buf);
            QString strquery;
            strquery = "INSERT INTO log (grab_date,ip_address,image) VALUES ( '";
            strquery += '2011-04-26 15:55:09';
            strquery += "' , '";
            strquery += '172.16.0.51';
            strquery += "' , ";
            strquery += buf;
            strquery += " );";

          //QMessageBox::warning(NULL,"DriveList",strquery,QMessageBox::Ok);

            QSqlQuery insertQuery( strquery, db);
            bool done = insertQuery.exec();

            QFile newfile("D:\\img.txt");
            newfile.open(QIODevice::WriteOnly);
            newfile.write(buf);

            int iSecret, iRandom;
            iSecret = rand() % 20 + 1;
            iRandom=iSecret*60000;

            QMutex mutex;
            mutex.lock();
            QWaitCondition waitCondition;
            waitCondition.wait(&mutex, iRandom);
            mutex.unlock();

    }

}

To copy to clipboard, switch view to plain text mode