Storing Images in Database Ms.Access

Overview
Beside plain simple data, such as strings and numbers, in most (if not all) modern database we usually can also store digital values of nearly unlimited size. We can use this for example to store images, text files, executables, and binary libraries. This kind of values usually called BLOB, short for binary large object. Fields to store these values also refered as blob fields.

See this wikipedia page for more information about BLOB.

The following database is known to support blob fields (note: the list covers only a very small fraction of database supporting blob fields).

MySQL
MS SQL Server
MS Access
Interbase
Firebird

Handling BLOB value in Delphi datasets
There is no primitive data type in Delphi directly available to handle BLOB value. Therefore we have to settle with handling BLOB as stream. TBlobStream is a descendant of TStream that was defined to handle BLOB values as stream. See here for official information on TBlobStream.

Excerpt from Delphi documentation about TBlobStream:

Quote

DBTables.TBlobStream is a stream object that provides services which allow applications to read from or write to field objects that represent Binary large object (BLOB) fields.

Use DBTables.TBlobStream to access or modify the value of a BLOB field in a BDE-enabled dataset. TBlob stream works with persistent TBlobField objects (including descendants of TBlobField such as TGraphicField and TMemoField). BLOB fields use BLOB streams to read data from and write data to the dataset.

DBTables.TBlobStream allows objects that have no specialized knowledge of how data is stored in a BLOB field to read or write such data by employing the uniform stream mechanism.

To use a BLOB stream, create an instance of DBTables.TBlobStream, use the methods of the stream to read or write the data, and then free the BLOB stream. Do not use the same instance of DBTables.TBlobStream to access data from more than one record. Instead, create a new DBTables.TBlobStream object every time you need to read or write BLOB data on a new record.

The most important thing you should note is that you can not reuse an instance of TBlobStream. You must free it as soon as you are done with it, and create new one for another record.

Another important thing you should remember is that it is prefered to call a dataset'sCreateBlobStream method instead of directly call TBlobStream's constructor. Because this approach will give the dataset a chance to prepare itself.

Writing BLOB field
Basic steps in writing to a BLOB field:

If the dataset is no in editing mode, put the dataset into the mode by either calling Edit orInsert.
Create a TBlobStream by calling respected dataset's CreateBlobStream method and specifying the field and bmWrite as access mode.
Fill the returned TBlobStream with your binary value.
Free the TBlobStream.
Store the changes by posting the modification, either by calling Post or CheckBrowseMode.

Reading BLOB field
Basic steps in writing to a BLOB field:

Create a TBlobStream by calling respected dataset's CreateBlobStream method and specifying the field and bmRead as access mode.
Read the content of the returned TBlobStream
Free the TBlobStream.
Use the read content according to task in hand

In next sections I show you the implementations of the above steps in order to store and read images to and from some database.

Handling Images in MS Access
For our demo project with MS Access, create a new Delphi application project. Drop these components to the main forms and rename them respectively:

TADOConnection, cnnMain
TADOTable, tblMovies
TDataSource, dsMovies
TOpenPictureDialog, OpenPictureDialog1
TADOCommand, qCmd
TDBGrid, DBGrid1
TImage, Image1
TBitBtn, btnOpenUrl
TBitBtn, btnAddPict
TBitBtn, btnAddPictSQL

Arrange the controls/components into a layout something like this:

Wire tblMovies's and qCmd's Connection properties to cnnMain. Set dsMovies' Dataset totblMovies. Wire DBGrid1's DataSource to dsMovies. Build the connection string of cnnMain by double clicking it. Make sure the connection string pointing to the sample ms access datase attached here (download and save it somewhere in your computer).

Set Movies to tblMovies' TableName property. Build the persistent fields for tblMovies by double clicking it and then Add all fields in the dialog that pops up.

Give the following SQL command to qCmd's CommandText.

UPDATE Movies SET Picture=:Picture WHERE [ID]=:ID

Check that qCmd has two parameters, Picture and ID, by opening its Parameters property in object inspector.

Writing Image With Table-kind of Dataset
What I mean with Table-kind datasets are datasets that containing records, e.g. TTable,TADOTable, TQuery with SELECT query, and TADODataset with SELECT query. For these kind of datasets, we can store BLOBs directly to the corresponding fields. This is where TBlobStream plays important role.

In our demo project we will use a TADOTable which we named tblMovies.

Put the following codes for btnAddPict's event handler:

procedure TForm1.btnAddPictClick(Sender: TObject);
var
  vBlobStream: TStream;
  vFileStream: TStream;
  vEditing: Boolean;
begin
  // do we have record to store the picture to? Don't continue if not
  if tblMovies.IsEmpty then Exit;
  // do the user select a picture file? Dont continue if not
  if not OpenPictureDialog1.Execute then Exit;
  // are we in editing mode? Enter into one if not
  vEditing := tblMovies.State in [dsEdit, dsInsert];
  if not vEditing then tblMovies.Edit; // enter editing mode if not in one
  try
    // create writable TBlobStream for field 'Picture' of tblMovies
    [b]vBlobStream := tblMovies.CreateBlobStream(tblMoviesPicture, bmWrite);[/b]
    try
      // create stream of the selected picture file
      vFileStream := TFileStream.Create(OpenPictureDialog1.FileName, fmOpenRead or fmShareDenyNone);
      try
        // copy the content of picture stream to the TBlobStream
        [b]vBlobStream.CopyFrom(vFileStream, 0);[/b]
      finally
        vFileStream.Free;
      end;
    finally
      // freeing writable TBlobStream also writes its content to the underlying field
      vBlobStream.Free;
    end;
  finally
    // post the changes if we are the one putting into editing mode
    if not vEditing then tblMovies.CheckBrowseMode;
  end;
end;

I think the code and the comments pretty much explained everything about the process.

Writing Image with SQL command
In this case we want to store images using SQL command INSERT or UPDATE. It will be different with the method we used with table-kind of datasets, since obviously here we don't have a TField to help us out. No TBlobStream for us here. However we have TParameter objects to work with.

Basically TParameter is a way to communicate with our sql command. We pass values usingTParameter, and we also read values using TParameter. Declaration of TParameter is done directly inside the sql command itself. Just add a colon (:) in front of the parameter's name, and later Delphi will create instance(s) of corresponding TParameter(s) for you.

For our example here, we have given this sql: UPDATE Movies SET Picture=:Picture WHERE [ID]=:ID. The bold parts are the ones
that define our two TParameter-s, i.e. Picture and ID. Of course initially these TParameter-s have no value. So before we execute the sql, we need to give values to them. TParameter is capable to hold BLOB value, like I show you in the OnClick event handler of btnAddPictSQL below:

procedure TForm1.btnAddPictSQLClick(Sender: TObject);
var
  vFileStream: TStream;
begin
  // do we have record to store the picture to? Don't continue if not
  if tblMovies.IsEmpty then Exit;
  // do the user select a picture file? Dont continue if not
  if not OpenPictureDialog1.Execute then Exit;
  // load the picture file into a stream
  vFileStream := TFileStream.Create(OpenPictureDialog1.FileName, fmOpenRead or fmShareDenyNone);
  try
    // give ID of the current movie record to the :ID parameter of qCmd
    qCmd.Parameters.ParamByName('ID').Value := tblMoviesID.Value;
    // Assign the content of FS stream to the :Picture parameter
    [b]qCmd.Parameters.ParamByName('Picture').LoadFromStream(vFileStream, ftBlob);[/b]
    // execute the sql
    qCmd.Execute;

    // refresh the table so it shows the new picture
    RefreshTable;
  finally
    vFileStream.Free;
  end;
end;

Reading Image from Dataset
Here is the code in our demo project to read image from the dataset (in this case tblMovies).

procedure TForm1.LoadPicture;
var
  BS: TStream;
  vGraphic: TGraphic;
begin
  // clear the currently displayed picture
  Image1.Picture.Graphic := nil;
  // do we have something to display?
  if tblMovies.IsEmpty then Exit;

  // Get a TBlobStream instance from the content of field Picture of tblMovies, with read-only access
  BS := tblMovies.CreateBlobStream(tblMovies.FieldByName('Picture'), bmRead);
  try
    // create the object to parse the image information into jpg picture
    vGraphic := TJPEGImage.Create;
    try
      // give the content of our TBlobStream to the jpg image object
      vGraphic.LoadFromStream(BS);
      
      // give the jpg image object to TImage to display
      Image1.Picture.Graphic := vGraphic;
    finally
      vGraphic.Free;
    end;
  finally
    BS.Free;
  end;
end;