[Solved]How to save an image into a database

[clochydd]

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!

[ealione]

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)")@

[yeckel]

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();
work.commit();

}@

[clochydd]

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.

[ealione]

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.

[clochydd]

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?

[ealione]

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.

[clochydd]

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

[clochydd]

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

[ealione]

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