Recent

Author Topic: Load MySQL Blob into TImage  (Read 3292 times)

HopefulGuy

  • New Member
  • *
  • Posts: 28
Load MySQL Blob into TImage
« on: May 08, 2021, 09:17:53 am »
I have a question. I'm working on a program that reads information from MariaDB (mysql clone) tables. Some of these tables contain BLOB fields that are actual jpeg files. So far, the only way I have found to actually load this into a TImage is to write the contents of the blob field to a file, then use the TImage.LoadFromFile routine. Isn't there a better way? Some way I can just take the TImage data and dump it straight into the database, and then later read the field straight back into the TImage?

Thanks!

lucamar

  • Hero Member
  • *****
  • Posts: 4219
Re: Load MySQL Blob into TImage
« Reply #1 on: May 08, 2021, 11:53:30 am »
If for some reason you don't want to use TDBImage and have to do it "by hand", you can always save the data to a (memory) stream rather than a file and load the image from there with TImage.LoadFromStream(), though note that the advisability of doing it like that depends on the size of the BLOB and the available memory.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus/FPC 2.0.8/3.0.4 & 2.0.12/3.2.0 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: Load MySQL Blob into TImage
« Reply #2 on: May 08, 2021, 08:43:48 pm »
I didn't see TDBImage, thanks for pointing that out. I'll have to experiment with that. Hopefully that will work.

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: Load MySQL Blob into TImage
« Reply #3 on: May 15, 2021, 07:09:48 am »
If for some reason you don't want to use TDBImage and have to do it "by hand", you can always save the data to a (memory) stream rather than a file and load the image from there with TImage.LoadFromStream(), though note that the advisability of doing it like that depends on the size of the BLOB and the available memory.

So, I looked at a TDBImage, and it looks like it MAY work, But I'm not 100% sure. One thing I'm confused about is how I would translate an image loaded from a local file into a blob on the database? If it's just replacing, that's one thing. But if I need to add a new image to the system, then that constitutes a new record in the image table. So how do I tell Lazarus that what I want it to do is to insert a new record into the sub-table when I add a new image? I can see the TDBImage.Picture.LoadFromFile() routine, and that's great for the picture from a file. But once that happens, a DB Aware component is going to try to update an existing record, or, if I'm in insert mode, insert a new record. But the record can not be inserted without extra data that is not present in the TDBImage (namely a record number for the person in the main table who owns that image, and the sequenc number of that picture).

I'm sure that some of this has to do with the SQL that would be reading the image from the table in the first place, but I'm still lost! Any help is appreciated.

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 1228
Re: Load MySQL Blob into TImage
« Reply #4 on: May 15, 2021, 08:20:14 am »
hello,
you can have a look to my database example (in Lazarus\examples\Database\Image_Mushrooms ) :
it is for sqlite but using sqldb components.
Quote
- The images are stored in blob field without extension at the beginning.
With this you can view blob images with database browser editor
(e.g. sqlite2009pro).
- In the database there is also a field with images links (filenames).
- The linked images are stored in the folder images of the project.
- You can see the linked images in a Timage.
- You can change the images in the database:
- for Tdbimage (image in db): double click on the component and choose your
image.
- for Timage (linked image): click on the button near the image filename
(you must be in edit mode).
- Transaction commits when you click on Tdbnavigator refresh button or on close
form.
- Small pictures of the mushrooms are in the sqlite3Database. Largest images are
in files in the folder images.

Friendly, J.P

Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Load MySQL Blob into TImage
« Reply #5 on: May 15, 2021, 08:25:01 am »
If you want to add new record then call insert first.

Dataset.insert
DBImage.picture.loadfromfile

HopefulGuy

  • New Member
  • *
  • Posts: 28
Re: Load MySQL Blob into TImage
« Reply #6 on: May 15, 2021, 08:14:17 pm »
hello,
you can have a look to my database example (in Lazarus\examples\Database\Image_Mushrooms ) :
it is for sqlite but using sqldb components.

I looked at that, but that's not quite what I'm after. I completely understand adding a new record with an image component where the image is part of the main record. But that's not the case with what I'm trying to do. In my case, one master record will connected to an unknown number of records in a different table (one to many relationship). But the records in that secondary table need more than just an image. There's actually three fields. IDNo = The Record ID of the person who sent the images (main table link), imgno is a simple counter of all images sent by that person, and picdata is the blob containing the actual picture. Not everyone will have a picture, and many will have 10+ pictures. What I need is a way, without using a sub-form, to be able to iterate through the existing pictures (if any) for the contact displayed on the main form, and to add a new picture or delete an existing picture, again without affecting the main table at all.



egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Load MySQL Blob into TImage
« Reply #7 on: May 16, 2021, 02:35:20 am »
Hi HopefulGuy,

I'm sorry if my comments are not helpful to you. But your questions are mixing many subjects at the same time.

1.  TSQLquery and DB-aware controls will do retrieving and storing image to database... LAZARUS will to those things for you.

2.
Quote
how do I tell Lazarus that what I want it to do is to insert a new record into the sub-table when I add a new image?

The answer is insert a record first, and then assign image file. ==> you said you know this.


3.  Now your question is how to manage one-to-many relation tables.  This is database design issue--- not image nor blob field issue.

    Table 1, master table, woud look like

            IDNo    firstName   LastName  Address ....
           --------  -------------   ------------- ----------

     Table 2, picture table, is defined as (you wrote as: )

            IDNo     imgno         picdata
            ------     --------        ----------

   
     Now, let's go to algorithm. Assume you have chosen a person --- ie. IDno is fixed. Now you want to insert an image. Steps to follow are:

           a.   Insert new record   
                        dataset.insert;     // or dataset.delete to remove currently selected image record.
           b.  Fill in IDNo   
                         dataSet.FieldbyName('IDNo').AsInteger := masterdataset.fieldByName('IDNo').AsInteger;
           c.  Fill in the imgno
                     this is rather complex. you are not recommended to use this kind of index.
                     But anyway you can count the current number of records for this person and give the number (not 
                     plus one, because we already inserted).
           d.  Fill image
                     DBImage.Picture.Loadfromfile

           e.  If you want to store this record to DB,    DataSet.ApplyUpdates;  from you Lazarus application.


4.  Your LAZARUS application DataSets

     Now it's time to think about your application design. You need two separate queries, one for the master table and another for the image table (there is a way that you can combine them into one query. But let's forget about it for now).
 
     And you have to think how to design it.

    1)  If the total number of persons and images are not large, you can always download whole tables to your application and operate on it.

         SQLPerson.SQL.Text := 'select * from table1 order by IDNO';
         SQLImage.SQL.Text := 'select * from table2 order by IDNo, imgno';

      And you link the two table using keyfield, and so on. Then once a record of SQLPerson is selected, only those picture records will be active.


     2)  if you want to download all the persons, but only pictures of those persons, then you have to set it up as :

          SQLPerson.SQL.Text := 'select * from table1 order by IDNO';
          SQLImage.SQL.Text := 'select * from table2 where IDNO=:idno order by IDNo, imgno';

          And SQLImage must be re-opened whenever SQLPerson's record is changed // or does keyfield do it? 

     3) there may be other options, but let's do the second option for now.

    So you need two TSQLQueries and two TDataSource. Whether you put these on separate forms or on the same form is NOT important AT ALL. 

     I hope you add two DBGrids and TDBNavigators on the form, and link SQLConnection, SQLTransaction, SQLQueries, Datasources, DBGrids and DBNavigators, and then check whether you've set everything correctly.  You may also put other Data-aware controls like TDBEditor, TDBMemo, TDBImage, etc. just by liking them to datasets via datasource +  datafield.


      Now inserting, deleting, or updating any record of any queries, are done as we've discussed previoiusly. But at the beginning state you'd better play with DBNavigator. Not much codes are necessary.

      AGAIN, operation on these queries are just on local copy of the physical DB table --- even though you have opened whole table. You need to call ApplyUpdates to store them permanently.

              SQLPerson.ApplyUpdates or SQLImage.ApplyUpdates will do for each dataset (dataset=TSQLQuery here. There are other TDataSet descendants, like SQLiteDataset, TIBTable, TTable of Delphi, etc.).


Sorry if this is worthless and redundant comment. But I think you need to understand the basic database application development architecture first.

 

TinyPortal © 2005-2018