[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_SQLiteits 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(); 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.