Images in a database

Question

My program manages "items" (think of them as stately homes) and associates with each item zero, one, or more images. Until now these are outside the database. The items in the database point to images by file name and path. Images are held in a dedicated folder tree. Now it is required to move the images into the database itself. Image types required to be handled are: jpeg (the most common, sometimes as large as 4 Mb) bmp (also very common, and quite large) png emf wmf gif (static only, rare, but does occur).

Currently, when a file is associated with an Item, nothing is done with the image other than to create an entry in the Images database table. When items are being reviewed by the user, image thumbnails are presented using ShellBrowser to access the file and generate a 96 x 96 thumbnail bitmap that is presented on screen.

My plan is to store the images and thumbnails in the Images table as two new BLOB fields per record, Full Image and Thumbnail, both being bitmaps.

This is my first foray into images in a database so please bear with me as I try to get up to speed.

Question 1: Does the following code segment convert the above 6 file types to a full image bitmap ? It seems to do so.

uses 
  Graphics, Jpeg, pngimage, GIFImg; 

procedure TForm1.Button1Click (Sender: TObject); 
var 
  Picture: TPicture; 
  Bitmap: TBitmap; 
begin 
  Picture := TPicture.Create; 
  try 
    Picture.LoadFromFile('C:\imagedata.dat'); 
    Bitmap := TBitmap.Create; 
    try 
      Bitmap.Width := Picture.Width; 
      Bitmap.Height := Picture.Height; 
      Bitmap.Canvas.Draw(0, 0, Picture.Graphic); 
      Bitmap.SaveToFile('C:\test.bmp'); 
    finally 
      Bitmap.Free; 
    end; 
  finally 
    Picture.Free; 
  end; 
end; 

Question 2: How to move the bitmap into the Blob field, using a TStream or a TMemoryStream ? advantages and problems one versus the other. Code moving bitmap to stream and from stream to BLOB ??

Question 3: My feeling is that storing the original files in the Blob field would occupy less space but be more difficult to present on screen. Any thoughts ?

Answer 1

1. TPicture.LoadFromFile() uses the file extension to know which TGraphic class to use for loading the data. Unless you have created a custom class, none of the standard classes use .dat.

2. use TDataSet.CreateBlobStream() with TGraphic.SaveToStream()/LoadFromStream(). I wouldn't forcibly convert all images to BMP for storage, though. That will bloat the database. JPGs and PNGs are much more compact.

3. images stored in a blob can be displayed in a TDBImage. Or, you can simply load the image yourself and then assign it to TImage.

Answer 2

Since I have now managed to answer my original question I feel it is appropriate to post my final "research" code in case it might be of use to others in the future.

Need it be said that it has been decided to keep images outside the database to avoid bloat, but to keep thumbnails inside the database.

uses Vcl.imaging.pngimage, Vcl.imaging.GIFImg, Vcl.imaging.jpeg;
    
procedure TImagesInABLOB.LoadImageButtonClick (Sender: TObject);
var
  Name_And_Path_File_Selected:  string;
  File_Extension:   string;
  Local_ShellBrowser:   TShellBrowser;
  Thumbnail_Bitmap:     TBitmap;
  Thumbnail_Stream:     TMemoryStream;
  ADO_Stream: TADOBlobStream;
    
  mRect: TRect;
  nRect: TRect;
label
  Load_Thumbnail_Into_DB;
begin
// Using ShellBrowser, create a 96 x 96 pixel thumbnail image as a bitmap.
// ShellBrowser returns nil when the image type is .emf or .wmf.
// The bitmap created is upside down and must be rotated 180 degrees.
  Local_ShellBrowser := TShellBrowser.Create(Self);
  try
    Local_ShellBrowser.FullPath := Name_And_Path_File_Selected;
    Thumbnail_Bitmap := Local_ShellBrowser.GetThumbnailBitmap (96, 96, False);
  finally
    Local_ShellBrowser.Free;
  end;
    
  if (Thumbnail_Bitmap <> nil) then
    begin                                          // Turn bitmap upside down.
      mRect := rect (0, 0, Thumbnail_Bitmap.Width, Thumbnail_Bitmap.Height);
      nRect := rect (0, Thumbnail_Bitmap.Height - 1, Thumbnail_Bitmap.Width, -1);   
      Thumbnail_Bitmap.Canvas.CopyRect(mRect, Thumbnail_Bitmap.Canvas, nRect);      
      goto Load_Thumbnail_Into_DB;
    end;
// If the image file extension is .emf or .wmf, call an appropriate routine to
// to create a thumbnail bitmap from the metafile.
  File_Extension := LowerCase(ExtractFileExt(Name_And_Path_File_Selected));
  if ((Thumbnail_Bitmap = nil) and (File_Extension = '.emf')) then
    begin
      Get_EMF_Thumbnail (Name_And_Path_File_Selected, 96, 96, Thumbnail_Bitmap);
      if (Thumbnail_Bitmap = nil) then
        JFMessageDlg ('Thumbnail could not be created from the .emf file.', mtError, [mbOK], [Txt_Btn_Caption_OK]);
  goto Load_Thumbnail_Into_DB;
    end;
    
  if ((Thumbnail_Bitmap = nil) and (File_Extension = '.wmf')) then
    begin
      Get_WMF_Thumbnail (Name_And_Path_File_Selected, 96, 96, Thumbnail_Bitmap);
      if (Thumbnail_Bitmap = nil) then
        JFMessageDlg ('Thumbnail could not be created from the .wmf file.', mtError, [mbOK], [Txt_Btn_Caption_OK]);
    end;
// If Thumbnail_Bitmap exists, load the thumbnail bitmap into the current record
// record of the Oracle table and release the thumbnail bitmap.

Load_Thumbnail_Into_DB:
  if (Thumbnail_Bitmap <> nil) then
    begin
      if ((QryImages_In_BLOB.State <> dsEdit) and (QryImages_In_BLOB.State <> dsInsert)) then
        QryImages_In_BLOB.Edit;
        ADO_Stream := TADOBlobStream.Create(QryImages_In_BLOBTHUMBNAIL_IMAGE, bmWrite);
      try
        Thumbnail_Bitmap.SaveToStream(ADO_Stream);
      finally
        ADO_Stream.Free;
      end;
    
      Thumbnail_Bitmap.Free;
    end;
// Post any change made to the current record.
  if ((QryImages_In_BLOB.State = dsEdit) or (QryImages_In_BLOB.State = dsInsert)) then
    QryImages_In_BLOB.Post;
end;
{--------------------------------------------------------------------------------}
procedure TImagesInABLOB.Get_EMF_Thumbnail (const Path: Unicodestring; Width: 
                               Integer; Height: Integer; out   Bitmap: TBitmap);
var
  APicture: TPicture;
begin
  if (LowerCase(ExtractFileExt(Path)) = '.emf') then
    begin
      APicture := TPicture.Create;
      try
        APicture.LoadFromFile(Path);
        Bitmap := TBitmap.Create;       // Must be freed by the caller.
        Bitmap.PixelFormat := pf24bit;
        Bitmap.Width  := Width;
        Bitmap.Height := Height;
        Bitmap.Canvas.Lock;   // Important in multithreaded applications,
                              // see http://qc.embarcadero.com/wc/qcmain.aspx?d=55871
        Bitmap.Canvas.StretchDraw(Rect(0, 0, Width, Height), APicture.Graphic);
        Bitmap.Canvas.Unlock;
      finally
        APicture.Free;
      end;
    end;
end;
{------------------------------------------------------------------------}
procedure TImagesInABLOB.Get_WMF_Thumbnail (const Path:   Unicodestring; Width: 
                               Integer; Height: Integer; out   Bitmap: TBitmap);
var
  APicture: TPicture;
begin
  if (LowerCase(ExtractFileExt(Path)) = '.wmf') then
    begin
      APicture := TPicture.Create;
      try
        APicture.LoadFromFile(Path);
        Bitmap := TBitmap.Create;       // Must be freed by the caller.
        Bitmap.PixelFormat := pf24bit;
        Bitmap.Width  := Width;
        Bitmap.Height := Height;
        Bitmap.Canvas.Lock;   // Important in multithreaded applications,
                              // see http://qc.embarcadero.com/wc/qcmain.aspx?d=55871
        Bitmap.Canvas.StretchDraw(Rect(0, 0, Width, Height), APicture.Graphic);
        Bitmap.Canvas.Unlock;
      finally
        APicture.Free;
      end;
    end;
end;