ADO (ActiveX Data Objects)

ADOExpress Step-by-Step-from-Scratch Tech Note

Delphi 5, ADOExpress, Access and Images

Using MS Office Access Sample Database "Northwind"

Summary: Many PCs with Microsoft Office have a sample Access database called "Northwind," which is useful for database experiments. This project shows that while the "normal" data can be manipulated in Northwind using Delphi and ADOExpress, there is no easy way (without yet-another-third-party-add-on) to display the images in this Access database using only Delphi 5 and ADOExpress. However, a crude "hack" is shown that does extract a TBitmap from the Photo TBlobField. More must be understood about this "hack" before the technique can be considered reliable and used in a "real" application.

A.  Verify Access File Exists
B.  Accessing the Northwind Database Using ADO
C.  Attempts to Display Photo BLOB as Image
- Using DBImage (doesn't work)
- The "Hack" Job (works)

A.  Verify Access File Exists

  1. Using Windows Explorer, verify existence of network-accessible Access 97 database at
    \\ProStar\C\Program Files\Microsoft Office\Office\Samples\Northwind.mdb. Your location will be different. The Northwind sample database is on many machines that have MS Access.
  2. Using Access, view the design of the Employees table

  3. Open the Employees table and view the raw data in the table.


    4. Note that the "Photo" field is an "OLE Object" according to the design view but is a "Bitmap Image" in the Photo data column.

  4. Double click on the words "Bitmap Image" in the first row of this table to display the picture of Nancy Davolio. The MS Paint application will be launched to display the image:

  5. Exit MS Paint. Exit MS Access.

B.   Accessing the Northwind Database Using ADO

  1. Start Delphi 5.
  2. In the Object Inspector for Form1:
    Name: FormADOBLOB
    Caption: ADO and Access BLOBs
    Height: 480
    Position: poScreenCenter
    Width: 640
  3. File | New Data Module | OK
    Change name from DataModule2 to DataModuleNorthwind
  4. File | Save Project As | Save in: C:\Temp (or other desired directory) |
    ScreenADOBLOB.pas (instead of unit1.pas) | Save |
    DataNorthwind.pas (instead of unit2.pas) | Save |
    ADOBLOB.dpr (instead project1.dpr) | Save

    (Note: My convention for program XXXX is to have a FormXXXX defined in the ScreenXXXX.PAS unit with a XXXX.DPR project file. Likewise, a DataModuleYYYY is saved in a DataYYYY.PAS unit.)
  5. With the Components Tab visible and selected on the DataModuleNorthwind "Form," select the ADO tab on the VCL Palette. Double click on the ADOConnection icon to add an instance to the "form".  Configure ADOConnection1 with these properties:
    Name: ADOConnection
    Login Prompt: False
  6. Click on the ellipses in the Connection String entry area. Select Use Connection String and press the Build button. Select Microsoft Jet 4.0 OLE DB Provider, as shown below:


  7. Select the Next button and on the Connection tab press the Ellipses ("…") button to the right of the database name entry area.. Browse and to select the database name:
    \\ProStar\C\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
    Press the Open button.

    8.  


  8. Press the OK button twice.
  9. From the ADO VCL tab, double click on the ADOQuery to place an instance on DataModuleNorthWind. Specify the following properties:
    Name: ADOQueryNorthwind
    Connection: ADOConnection

    Click on the ellipses button in the entry area of the SQL property and enter this as the SQL string, and the press the OK button.

    10. Be sure to spell everything correctly in the SQL query. Otherwise, you may later see a terse and cryptic error messages.

  10. From the Data Access tab on the VCL Palette, double click on the Data Source icon to add an instance to DataModuleNorthwind. Configure DataSource1 with these properties:
    Name: DataSourceNorthwind
    DataSet: ADOQueryNorthwind

  11. Select the DataDiagram tab on DataModuleNorthwind and drag each of the icons in the tree view (ADOQueryNorthwind, DataSourceNorthWind, Fields, Parameters) to the Data Diagram area. Resize boxes as necessary so all text appears correctly. Reposition boxes as desired.

  12. Right click on the field icon in the Tree View of DataModuleNorthwind and select Add all fields. Fields 0 through 6 should be appear in the Tree View.
  13. From View Unit, select ScreenADOBLOB. OK
  14. In the Implementation section of ScreenADOBLOB, add "USES DataNorthwind;"
  15. Select Toggle Form / Unit
  16. From the Data Controls tab on the VCL Palette, double click on the DBGrid icon to place an instance on FormADOBLOB. Configure DBGrid1 with the following properties:
    Name: DBGridNorthwind
    Height: 200
    DataSource: DataModuleNorthwind.DataSourceNorthwind (Select from pull-down list. If a selection is not shown, there is something wrong with the "USES" statement from step 19.)
    Options.dgRowSelect: TRUE
    Width: 600
  17. Select View Unit. Select DataNorthwind. OK. Select Toggle Form/Unit. Select icon for ADOQueryNorth. In the object inspector, set Active = TRUE. (If you see an error here, verify the SQL statement in Step 14 is correct.)
  18. Select View Unit. Select ScreenADOBLOB. OK. Select Toggle Form/Unit. Verify data are shown in design mode
  19. Project | Build ADOBLOB | Run

    20. What are the special characters showing in the Address field? The string, such as "Apt. 2A" for Nancy Davolio above, does not display in the Access database when these data are displayed. Apparently the special characters shown as the vertical bars are a carriage return ($0D) followed by a line feed ($0A). The Address field should correctly be viewed only in a multi-line display component, such as a Memo box.

  20. Scroll all the way to the right to view the Photo field.


    21. How can the BLOB Photo field be displayed as a bitmap?

  21. Exit the Program.
  22. File | Close All | Yes (to "Save Changes")
    23.

C.  Attempts to Display Photo BLOB as Image

The DBImage component isn't "smart enough" to display a BLOB (for now).

  1. Re-open the project by double clicking on the ADOBLOB.dpr file from the Windows Explorer.
  2. Add a DBImage to FormADOBLOB by double clicking on the DBImage icon on the Data Controls tab of the VCL palette. Change these properties:
    Data Source: DataModuleNorthwind.DataSourceNorthwind
    Data Field: Photo


    3. Why doesn't Borland support Bitmaps stored in Access databases?

  3. File | Close All | No (to "Save Changes")

Loading Bitmap from BlobField Stream. The following works, but isn't very general.

  1. Re-open the project by double clicking on the ADOBLOB.dpr file from the Windows Explorer.
  2. From the Standard VCL component tab, double click on the Button icon to add a button to FormADOBLOB. Double click on Button1 and add this OnClick handler:

    3. procedure TFormADOBLOB.Button1Click(Sender: TObject);
    begin
      DataModuleNorthwind.ADOQueryNorthwindPhoto.SaveToFile('BLOB.DAT')
    end;
  3. Project | Build ADOBLOB | Run. With the Nancy Davolio the selected item, press Button1.
  4. Close the Program | File | Close All | No (to "Save Changes")
  5. View the BLOB.DAT file with a Hex/ASCII viewer. Note several lines of header information followed by what appears to be the bitmap  scanlines.
 1 151C2F00020000000D000E0014002100 ../...........!.
 2 FFFFFFFF4269746D617020496D616765 ....Bitmap Image
 3 005061696E742E506963747572650001 .Paint.Picture..
 4 05000002000000070000005042727573 ...........PBrus
 5 6800000000000000000020540000424D h......... T..BM
 6 16540000000000007600000028000000 .T......v...(...
 7 C0000000DF0000000100040000000000 ................
 8 A0530000CE0E0000D80E000000000000 .S..............
 9 00000000000000000000800000800000 ................
10 00808000800000008000800080800000 ................
11 C0C0C000808080000000FF0000FF0000 ................
12 00FFFF00FF000000FF00FF00FFFF0000 ................
13 FFFFFF00FF0CB0C9000B090900000A00 ................
14 9009000000000909A09A900B09000A90 ................
15 A00000000FFFEFFFFFFFFFFFFFFFFFCB ................
16 9CFCFEFAFFFFFFFFEDFFFEDEFFDEFEFC ................
17 FFFFDADA00D900009009009000000000 ................
18 090A00090BC0000900900000000A00AC ................
19 A0E0E0E0F0E9CA9000A9CB0C00009090 ................
20 E0000009090B0000D009009000000900 ................
21 009A000FFFFFFFFFFFFEFFFFFFFFFCAD ................
22 EBDBDFDFDFFFFFFFFFEFEDFFFEFFFFFF ................
23 FEFCAF0C9A0A0D00009A000000000000 ................
24 0009090A000B009A9000090000900C09 ................
25 00900900FA90ADA00090B00B00000000 ................
...

    Obviously line 2 says the data stream is a "Bitmap Image." I assume in Lines 4-5 that PBrush ("Paintbrush") is the program that should be used to open the file.

    The "repeating pattern" in the above hex dump is likely the same portion of a scanline. This suggests dumping the ADOQueryNorthwindPhoto TBlobField to a stream, and loading a Bitmap using this stream.

    The first two bytes of BMP files normally are "BM", so that it appears that the BMP file starts in line 5, at byte 14 above -- byte 78 within the file.

    Who knows how to interpret the information in the first 78 bytes of this "OLE Object" (Access) / TBlobFIeld (ADOExpress)?
  1. Re-open the project by double clicking on the ADOBLOB.dpr file from the Windows Explorer.

    From the Additional VCL tab, double click on the Image icon to add an instance to FormADOBLOB. Set these properties for Image1:
    Name: Image
    Width: 192
    Height: 223
  2. From the Standard VCL component tab, double click on the Button icon to add a button to FormADOBLOB. Set these properties:

    Name: ButtonShowImage
    Caption: Show Image

    Double click on Button1 and add this OnClick handler:

    procedure TFormADOBLOB.ButtonShowImageClick(Sender: TObject);
      VAR
        Bitmap      : TBitmap;
        MemoryStream: TMemoryStream;
    begin
      MemoryStream := TMemoryStream.Create;
      TRY
        DataModuleNorthwind.ADOQueryNorthwindPhoto.SaveToStream(MemoryStream);
        // Who can explain why 78 bytes should be skipped here?
        MemoryStream.Seek(78, soFromBeginning);
        Bitmap := TBitmap.Create;
        TRY
          Bitmap.LoadFromStream(MemoryStream);
          Image.Picture.Graphic := Bitmap
        FINALLY
          Bitmap.Free
        END
      FINALLY
        MemoryStream.Free
      END
    end;

    Francisco Leong suggests a slightly different approach:

    VAR
      P:  TADOBlobStream;
    ...
    BEGIN
      P := TADOBlobStream.Create(BlobImageField, bmRead);
      P.Seek(78, soFromBeginning);
      TRY
        Bitmap1.LoadFromStream(P);
      FINALLY
        P.Free
      END
    END;

  1. Project | Build ADOBLOB | Run. With the Nancy Davolio the selected item, press the Show Image button..

I don't understand why Borland doesn't make using bitmaps in Access databases a little easier than this using ADOExpress.  I started a thread in borland.public.delphi.database.ado about "ADOExpress with Access: Not Ready for Prime Time?"  The response by Mark Edington from Borland at least acknowledged that this should be addressed and eventually will be.

Also see:  Pictures inside a database -- Working with BLOBs. Storing pictures in Access

Links Verified 24 Jan 2000
Updated 18 Feb 2002


since 24 Jan 2000