Difference between revisions of "How to Store and Retrieve Image on SQLite"

From Qt Wiki
Jump to: navigation, search
(expand code snippets into a full working example)
 
(4 intermediate revisions by 4 users not shown)
Line 1: Line 1:
[[Category:snippets]]<br />[[Category:HowTo]]
+
{{LangSwitch}}
  
'''English''' | [[How_to_Store_and_Retrieve_Image_on_SQLite_German|Deutsch]] | [[How_to_Store_and_Retrieve_an_Image_or_File_with_SQLite_Spanish|Español]] | [[How_to_Store_and_Retrieve_Image_on_SQLite_Bulgarian|Български]]
+
Images (or any other binary data, from files or otherwise) can be stored in a database.
 +
One way to do it is converting the data into a QByteArray, and insert it into the database as a [[wikipedia:Binary large object|Binary Large OBject]] (BLOB).
  
= How to Store and Retrieve an Image or File with SQLite =
+
Below is a full example of such a workflow, including reading the image back from the database and displaying it:
  
Images or any files can be stored in a database. Here is one way to do it using the following steps:
+
<source lang="cpp">
 +
#include <QtSql>
 +
#include <QtWidgets>
  
1. Read the system file into a QByteArray.<br />2. Store QByteArray as a Binary Large Object in database.
+
int main( int argc, char *argv[] )
 +
{
 +
    QApplication app( argc, argv );
  
For example :
+
    // Set up database
 +
    QString dbName( "myDatabase.db3" );
 +
    QFile::remove( dbName ); // delete sqlite file if it exists from a previous run
 +
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
 +
    db.setDatabaseName( dbName );
 +
    db.open();
 +
    QSqlQuery query = QSqlQuery( db );
 +
    query.exec( "CREATE TABLE IF NOT EXISTS imgTable ( filename TEXT, imagedata BLOB )" );
  
<code><br /> QFile file&amp;amp;#40;fileName&amp;amp;#41;;<br /> if (!file.open(QIODevice::ReadOnly)) return;<br /> QByteArray byteArray = file.readAll();
+
    // Generate an image (in this case a screenshot) and store it in a QByteArray
 +
    QScreen *screen = app.primaryScreen();
 +
    QPixmap inPixmap = screen->grabWindow( 0 );
 +
    QByteArray inByteArray;
 +
    QBuffer inBuffer( &inByteArray );
 +
    inBuffer.open( QIODevice::WriteOnly );
 +
    inPixmap.save( &inBuffer, "PNG" ); // write inPixmap into inByteArray in PNG format
  
QSqlQuery query;<br /> query.prepare(&quot;INSERT INTO imgtable (imgdata) VALUES (?)&quot;);<br /> query.addBindValue(byteArray);<br /> query.exec&amp;amp;#40;&amp;#41;;<br /></code>
+
    // Alternatively, load an image file directly into a QByteArray
 +
    // QFile file("screenshot.png");
 +
    // if (!file.open(QIODevice::ReadOnly)) return;
 +
    // QByteArray inByteArray = file.readAll();
  
Now, the image/file can be retrieved like any other data<br /><code><br /> QSqlQuery query(&quot;SELECT imgdata FROM imgtable&amp;quot;);<br /> query.next();<br /> QByteArray array = query.value(0).toByteArray();<br /></code>
+
    // Insert image bytes into the database
 +
    // Note: make sure to wrap the :placeholder in parenthesis
 +
    query.prepare( "INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', :imageData)" );
 +
    query.bindValue( ":imageData", inByteArray );
 +
    if( !query.exec() )
 +
        qDebug() << "Error inserting image into table:\n" << query.lastError();
  
Creating a QPixmap from QByteArray :<br /><code><br /> QPixmap pixmap = QPixmap();<br /> pixmap.loadFromData(array);<br /></code>
+
    // Get image data back from database
 +
    if( !query.exec( "SELECT imagedata from imgTable" ))
 +
        qDebug() << "Error getting image from table:\n" << query.lastError();
 +
    query.first();
 +
    QByteArray outByteArray = query.value( 0 ).toByteArray();
 +
    QPixmap outPixmap = QPixmap();
 +
    outPixmap.loadFromData( outByteArray );
 +
    db.close();
  
It is done. Now the pixmap can be used in a Button as icon or in label etc.
+
    // Display image
 +
    QLabel myLabel;
 +
    myLabel.setPixmap( outPixmap );
 +
    myLabel.show();
 +
 
 +
    return app.exec();
 +
}
 +
</source>
 +
 
 +
[[Category:Snippets]]
 +
[[Category:HowTo]]

Latest revision as of 14:00, 24 November 2015

En Ar Bg De El Es Fa Fi Fr Hi Hu It Ja Kn Ko Ms Nl Pl Pt Ru Sq Th Tr Uk Zh

Images (or any other binary data, from files or otherwise) can be stored in a database. One way to do it is converting the data into a QByteArray, and insert it into the database as a Binary Large OBject (BLOB).

Below is a full example of such a workflow, including reading the image back from the database and displaying it:

#include <QtSql>
#include <QtWidgets>

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

    // Set up database
    QString dbName( "myDatabase.db3" );
    QFile::remove( dbName ); // delete sqlite file if it exists from a previous run
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( dbName );
    db.open();
    QSqlQuery query = QSqlQuery( db );
    query.exec( "CREATE TABLE IF NOT EXISTS imgTable ( filename TEXT, imagedata BLOB )" );

    // Generate an image (in this case a screenshot) and store it in a QByteArray
    QScreen *screen = app.primaryScreen();
    QPixmap inPixmap = screen->grabWindow( 0 );
    QByteArray inByteArray;
    QBuffer inBuffer( &inByteArray );
    inBuffer.open( QIODevice::WriteOnly );
    inPixmap.save( &inBuffer, "PNG" ); // write inPixmap into inByteArray in PNG format

    // Alternatively, load an image file directly into a QByteArray
    // QFile file("screenshot.png");
    // if (!file.open(QIODevice::ReadOnly)) return;
    // QByteArray inByteArray = file.readAll();

    // Insert image bytes into the database
    // Note: make sure to wrap the :placeholder in parenthesis
    query.prepare( "INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', :imageData)" );
    query.bindValue( ":imageData", inByteArray );
    if( !query.exec() )
        qDebug() << "Error inserting image into table:\n" << query.lastError();

    // Get image data back from database
    if( !query.exec( "SELECT imagedata from imgTable" ))
        qDebug() << "Error getting image from table:\n" << query.lastError();
    query.first();
    QByteArray outByteArray = query.value( 0 ).toByteArray();
    QPixmap outPixmap = QPixmap();
    outPixmap.loadFromData( outByteArray );
    db.close();

    // Display image
    QLabel myLabel;
    myLabel.setPixmap( outPixmap );
    myLabel.show();

    return app.exec();
}