Pictures in Blob-Fields

Reiner Berger posted 1 year ago in General

Hello I am a beginner in databases and in English. I am working with XAMPP 3.2.2, MariaDB 10.1.21 and HeidiSQL 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

1 attachment(s):
  • HeidiSQL_Fehler_Fotoimport_16052017
ansgar posted 1 year ago

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

1 attachment(s):
  • import-file-value
ansgar posted 1 year ago

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.

ansgar posted 1 year ago

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

1 attachment(s):
  • import-file-image-preview
kalvaro posted 1 year ago

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

Reiner Berger posted 1 year ago

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.

1 attachment(s):
  • HeidiSQL_Fehler_Fotoimport_16052017_2
Reiner Berger posted 1 year ago

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.

ansgar posted 1 year ago

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.

Reiner Berger posted 1 year ago

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.

Reiner Berger posted 1 year ago

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

ansgar posted 1 year ago

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.

Please login to leave a reply, or register at first.