[Solved]How to save an image into a database

ealione

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

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&#40;&#41;;
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.