Databases - Delphi/Firebird

Databases - Delphi/Firebird - Blobs

Blobs (Binary Large Objects) are used to store large amounts of data, such as

Images
Sound files
Raw binary data
Formatted text

In general, it is a BAD idea to store these types of data in blobs, I prefer to store the data as separate files and have the database simply point to them ... but there are exceptions.

In most file oriented databases (such as dBase, FoxPro, Paradox)

The blobs are stored in a separate file
Storage is allocated as an integer number of pages
A blob pointer is stored in the main table

In Firebird

The entire database is stored in a single file
Placing data in blobs slows down all database operations

Creating Blobs | Reading Blobs - Example | Writing Blobs - Example

Creating Blobs

Blob fields are normally created like this.

CREATE TABLE "DataCollection" (
  "ID"           VARCHAR( 5),
  "Barcode"      VARCHAR(15),   
  "DateTime"     TIMESTAMP,
  "DataArray"    BLOB                /*  SUB_TYPE 1  */
);

Reading Blobs

Delphi 5 provides 2 methods to read tables

IBTable - These are fairly slow
IBQuery - Much faster

Either way, the blob can be read using either of these MemoryStream's

TMemoryStream
TBlobStream
TIBBlobStream - has extra properties

var
  Stream  : TMemoryStream;
  bf      : TBlobField;
  AnArray : array[1..5000] of single; // A buffer to hold the data
begin
  bf := Data_IBTable.FieldByName(Field) as TBlobField;

  Stream := TMemoryStream.Create;
  try
    bf.SaveToStream(Stream);
    ArraySize  := high(AnArray) * SizeOf(single);  // compute the maximum number of bytes to copy

    Stream.Position :=0;             // after loading stream, *position* is the last byte
    Stream.Read(AnArray, ArraySize); // copy up to "ArraySize" bytes to array
  finally
    Stream.Free;
  end;

These next 2 are based on the Delphi help

var
  Stream : TBlobStream;
begin
  Stream := TBlobStream.Create(BlobFieldObject, bmRead);
  try
    // use the stream here
  finally
    Stream.Free;
  end;

In many ways, this is the simplest example because you don't have to explicitly cast the field

var
  Stream : TBlobStream;
begin
  Stream := Table2.CreateBlobStream(Table2.FieldByName('Comments'), bmReadWrite);
  try
    // use the stream here
  finally
    Stream.Free;
  end;

Reading Blobs - Example

This is a relatively complex example - using TMemoryStream.

In one project, the contents of an array is stored in a blob field. The size of the array is data dependent and varies from record to record.

The IBTable_Extensions unit adds several methods to TIBTable and TIBQuery - the only method shown here is BlobToArrayOfSingle.

Type
   TValues = array[1..MaxPts] of single;
   PValues = ^TValues;

  TExtended_IBTable = class(TIBTable)
    procedure BlobToArrayOfSingle(Field: string; var AnArray: array of single);
  end;

  TExtended_IBQuery = class(TIBQuery)
    procedure BlobToArrayOfSingle(Field: string; var AnArray: array of single);
  end;

var
   Yvalues      : array [0..17] of PValues ; // an array of pointers to arrays of single
   DataSetToUse : TExtended_IBQuery        ;

begin  // some method
   DataSetToUse.BlobToArrayOfSingle('SPECTRUM_Y', YValues^);
end

procedure TExtended_IBTable.BlobToArrayOfSingle(Field: string; var AnArray: array of single);
var
//AnArray    : array[1..5000] of single; // Just a comment on the expected format of this parameter
  bf         : TBlobField;
//StreamSize : integer;
  ArraySize  : integer;
  Stream     : TMemoryStream;
begin
  bf := self.FieldByName(Field) as TBlobField;

  Stream := TMemoryStream.Create;
  try
    bf.SaveToStream(Stream);
//  StreamSize := Stream.Size; // just for debug
    ArraySize  := high(AnArray) * SizeOf(single);  // compute the maximum number of bytes to copy

    Stream.Position :=0; // after loading stream, *position* is the last byte
    Stream.Read(AnArray, ArraySize); // copy up to "ArraySize" bytes to array
  finally
    Stream.Free;
  end;
end;

Writing Blobs

Writing blobs is an issue

IBTable's provide the simplest interface ... but is a bit slower than an IBQuery
IBQuery provides a much faster interface, but an sql string can not represent binary data
Apparently IBQuery parameters can handle binary data

Writing Blobs - Example

var
 Stream : TStream ; // TBlobStream does not work
 ar1 : tArrayOfSingle;
begin
  Spectra_IBTable.Active := true;
  Spectra_IBTable.Insert;
  Spectra_IBTable.FieldByName('TYPE_NUM').AsInteger := Spectra_Type_int;

  Stream := Spectra_IBTable.CreateBlobStream(Spectra_IBTable.FieldByName('SPECTRUM_y'), bmWrite);
  try
    // use the stream here
    ar1 := JCAMP_1.YData_Pointer;
    Stream.Write(ar1[0], JCAMP_1.ArrayLength * SizeOf(single) );
    Spectra_IBTable.Post;
  finally
    Stream.Free;  // free the stream for each record
  end;

This code fails

    Stream.Write(ar1, JCAMP_1.ArrayLength * SizeOf(single) );

Don't forget the array index!

Author: Robert Clemenzi - clemenzi@cpcug.org