Pictures in Blob-Fields

Hello I am a beginner in databases and in English. I am working with XAMPP 3.2.2, MariaDB 10.1.21 and HeidiSQL 9.4.0.5162. With the HeidiSQL-Function "Werkzeuge / Dateien in TEXT/BLOBs importieren" I try to import a picture in jpg-Format with a size of 1.11MB. I wish to store it in a record so that I can display it in a view and on a report. I get always the error in the attachment. In the internet I learnt, that it is not so easy to store pictures in records without php knowledge. Questions:

• What is my mistake that I get this error ?
• Dos exist a tutorial for HeideSQL where I can find a description how to handle pictures ?

Thank you very much for your help

You need to provide the value '%filecontent%' for the blob column in which you want to insert the file contents. See my attached screenshot.

If after pressing "Dateien importieren", you get some error like "Lost connection to server", then the image(s) are too big for sending as a query. You can fix that error by increasing the maximum allowed packet size, by firing such a query:

SET @@global.max_allowed_packet = 100 * 1048576; -- 100MB

After firing that query you need to restart your HeidiSQL session in order to make this setting effective.

When that all succeeded, you can activate the image preview on a blob field in a data grid. See attachment on how that works.

Last but not least, HeidiSQL has absolutely nothing to do with PHP.

Tank you very much for your answer.

Now I get the new error as attached but if I add a new record all foreign key work o.k. In the fields which are connected with the foreign tables I can choose only the values which are content of the foreign tables.

An other point is, that I can insert the value '%filecontent%' only in "Werkzeuge / Dateien in TEXT/BLOBs importieren". In the field "Standard" of the table-definition it is not possible. Is that correct or e wrong definition from me.

Thank you very much.

I allow me to ask whether there are any ideas what I did define not correct in the tables or the foreign keys. Thank you very much.

I guess your foreign keys are fine. You just need to enter a static value in the relevant field "STD_StiMu_Herkunft". For example, if there is an id "4" in the referenced table, you could enter a "4" in the import dialog.

Thank you for your support but it did not work.

Now I deleted all foreign keys and tried to import a picture in the field from an existing record. Not possible. HeidiSQL added automatically a new record.

I think this automatically adding of a new record is the reason for the problems with the foreign keys. At the end of the import of the picture the system tries to close the record but the fields with the foreign keys are empty and therefore they do not match with the foreign tables and this triggers the error-messages.

But till now I did not found out why only one picture resp. one new record with a picture can be added to a table. I think I am still doing something wrong.

I would be very thankful for help.

New records are added but the content of the field will not be recognised as picture.

Yes, the "Import files" dialog is for creating new rows, not for updating existing data. This is probably a bit unclear there.

Supported image types for the preview window are PNG, JPG, BMP and GIF.

Yes, the "Import files" dialog is for creating new rows, not for updating existing data.

That is a very common needed function, isnt it? Any chance to see that in the next update? I miss that.

I dont want to create a new row in my case.

Just like: Right click cell -> Insert value -> Select File ... (file browser opens).

@ansgar Any chances here to implement that in future release?

@WebCF the request is a bit unclear here, apart from being 5 years old.

Is there a ticket in the tracker for that?

No, I dont found any ticket in the tracker (github?).

The request is to update existing row blob field with a file. The current solution requires adding a new row afaik.

Should I create an issue?

Yes, creating a ticket with a good description and probably a way to solve the issue raises the chance that this will be done.

Done: https://github.com/HeidiSQL/HeidiSQL/issues/1553

So, I think we're done here :)