[Solved]How to save an image into a database



  • Hi have a table in my database containing a column named images of type bytea.

    I have connected to she database using

    @db = QSqlDatabase::addDatabase("QPSQL");@

    set HostName, UserName and the rest.

    Then I load an image from file

    @imageObject = new QImage();
    imageObject->load(imagePath);@

    imageObject is a QImage.

    How can I now save this image to my table?



  • Have you read this?
    "http://qt-project.org/wiki/How_to_Store_and_Retrieve_Image_on_SQLite":http://qt-project.org/wiki/How_to_Store_and_Retrieve_Image_on_SQLite

    its for SQLite but I think you can easily change it into other types of databases



  • Hi kolegs,
    I have indeed read the article you have linked. But my table remained empty.



  • Hi ealione,

    this is how I save my images in a PostgreSQL database:
    @
    CREATE TABLE imagines
    (
    imagi integer NOT NULL,
    image bytea,
    CONSTRAINT imaginesx PRIMARY KEY (imagi)
    )
    @

    @
    QSqlQuery q;
    QString filename;
    QPixmap mypix (fileName);
    QByteArray baImg;
    QBuffer buffer(&baImg);
    buffer.open( QBuffer::WriteOnly );

    QImage image(fileName);

    QPixmap::fromImage(image).save(&buffer, "JPG");

    q.prepare("INSERT INTO imagines (imagi, pos, image) VALUES (:index, :image);");
    q.bindValue(":index", 1000);
    q.bindValue(":image", baImg);
    if (!q.exec()) {
    qDebug() << q.lastError();
    }

    @

    This works perfectly and I saved over 3000 images 'til today.
    Hope it helps!



  • Hey Clochydd,
    Thank you for the example, it works indeed. Hoe did you tackle the data size issue though, because I get

    @QSqlError("54000", "QPSQL: Unable to create query", "ERROR: index row requires 80240 bytes, maximum size is 8191
    (54000)")@



  • Hi,

    I didn't found an easy way to do that so I'm usigng pqxx and temporary file for saving images:

    @void DatabaseInterface::store_image(OcrData ocr_data)
    {
    C->prepare( "insert_picture", "INSERT INTO water_log( value, image, confidence, time ) VALUES ($1, $2, $3, now())" );
    std::string file_name = "/tmp/ocr_tmp_image.png";
    std::ifstream file(file_name.c_str());
    std::string content((std::istreambuf_iterator<char>(file)),std::istreambuf_iterator<char>());

    pqxx::binarystring bytea_blob( content);
    pqxx::work work( *C );
    pqxx::result result = work.prepared( "insert_picture" )( ocr_data.ocr_string )( bytea_blob )( ocr_data.confidence ).exec&#40;&#41;;
    work.commit();
    

    }@



  • Hi ealione,
    it seems you try to use the bytea field as an index, I suggest to use a separate integer field as the index.



  • Hi yeckel,
    I will have pqxx in mind as an option.

    Clochydd I am not quite sure what you mean. I have the exact table with you and I pass the ByteArray in the exact same way.



  • IMHO, your error messages "...index row requires 80240 bytes..." means that the indexed field is too large and I presume you try to use the bytea field as an index.
    Could you post your postgres CREATE TABLE statement?



  • Sure, its as you showed in your first post with some very small changes

    @QSqlQuery query(db);
    query.exec("CREATE TABLE imQt "
    "(im_id integer NOT NULL, "
    "im_data bytea, "
    "CONSTRAINT imaginesx PRIMARY KEY (im_data)) ");@

    Now when you pass data to your table you use

    @(imagi, pos, image) @

    and then as values you only bind imagi and image. Not sure what pos is.

    EDIT
    Yes it seems you are right I am indeed using the wrong indexes in my table creation.



  • Then you may simply change "PRIMARY KEY (im_data)" to "PRIMARY KEY (im_id)"! Hope it works - happy coding!



  • Ignore the pos field - I made the sample from a bigger table and pos is not necessary for our experiment...



  • Yes, your example was correct all along, my bad.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.