CODEWITHSUNDEEP
mysqldelphiado
Mawg
Mawg

Reputation: 40215

How to save a (Jpeg) image to MySql and retrieve it later?

Looking for a code sample, preferably using TADOConnection.

I want to save the TPicture of a TImage to a MySql (preferably ODBC, rather than just MySql) database and later I want to crate a TImage and retrieve the picture to its TPicture property.

Any code snippets, or links to same?

Upvotes: 1

Views: 17097

Answers (3)

vcldeveloper
vcldeveloper

Reputation: 7489

You can use BLOB fields. Suppose you have an instance of TAdoDataset, and you want to edit an image field. You can use a code like this:

  AStream := TMemoryStream.Create;
  try
    Image1.Picture.Graphic.SaveToStream(AStream);
    AStream.Position := 0;
    if ADODataSet1.Active then
    begin
      ADODataSet1.Edit;
      TBlobField(ADODataSet1.FieldByName('MyField')).LoadFromStream(AStream);
      ADODataSet1.Post;
    end;
  finally
    AStream.Free;
  end;

You can also use a DBImage control which is data-aware, and loading any image into it means loading that image into the field.

To retrieve the field data, and load it into a TImage instance, you can have a code like this:

var
  AStream : TMemoryStream;
begin
  AStream := TMemoryStream.Create;
  try
    if ADODataSet1.Active then
    begin
      TBlobField(ADODataSet1.FieldByName('MyField')).SaveToStream(AStream);
      AStream.Position := 0;
      Image1.Picture.Graphic.LoadFromStream(AStream);
    end;
  finally
    AStream.Free;
  end;
end;

Warning: In this code I assumed Image1.Picture.Graphic is not Nil. If your image control is empty, then Picture.Graphic property is Nil, and the code above will give you Access Violation. You have two options to avoid this:

  1. Instead of saving the image into a memory stream, save it into a local temp file, then load the picture using Image1.Picture.LoadFromFile. LoadFromFile checks file extension, and according to the file extension, creates a proper object from one of TGraphics descendant classes. For example if the file is JPEG, it creates an instance of TJPEGImage class, and loads the data into it.
  2. You yourself create the proper object and assign it to Image1.Picture.Graphic. For example, if your image is JPEG, create an instance of TJPEGImage, load the stream into it, then assign it to Image1.Picture.Graphic.

If you are inserting or updating a DB record using SQL commands (INSERT/UPDATE), you have to use SQL parameters in your SQL command, then you can load the stream into the parameter which represents your image field:

///Sample SQL Command:

INSERT INTO My_Table_Name
(MyField1, MyField2) 
VALUES (:prmMyField1, :prmMyField2)


/// Sending INSERT command to DB server

var
  AStream : TMemoryStream;
begin
  AStream := TMemoryStream.Create;
  try
    Image1.Picture.Graphic.SaveToStream(AStream);
    AStream.Position := 0;
     // Save some random data into the first param
    ADOCommand1.Parameters.ParamByName('prmMyField1').Value := 1;
    // Save image stream into the second param
    ADOCommand1.Parameters.ParamByName('prmMyField2').LoadFromStream(AStream);
    ADOCommand1.Execute;
  finally
    AStream.Free;
  end;
end;

Upvotes: 12

Srikar Appalaraju
Srikar Appalaraju

Reputation: 73708

You storing the image itself in MySQL? Why would you do that? There are several benchmarks done in this regard which concludes that this practice is not scalable.

Better thing to do is to maintain all your images on disk & have a table which contains the links to all the images.

Simple query on Google - 'mysql image store' or 'mysql image store benchmarks' would get you the appropriate links.

Upvotes: 2

RBA
RBA

Reputation: 12584

maybe this can help you:

http://www.scalabium.com/faq/dct0065.htm - save a component to a stream

and after that save the stream to MySql

http://www.devarticles.com/c/a/Delphi-Kylix/Saving-Images-and-Binary-Files-to-a-Database-with-Delphi-2/

and http://en.allexperts.com/q/Delphi-1595/2008/7/Save-big-file-Mysql.htm

hope that's what you want, and I understand well what you're asking.

best regards,

Upvotes: 1

Related Questions