Using Blobs in Firebird Databases on a Raspberry Pi

See our notes on getting started with Firebird on the Raspberry Pi. Follow these instructions to create a database with a table containing a field of type BLOB:

  1. Using the Root Terminal, change directory with cd /var/lib/firebird/2.5/data.
  2. Enter the command isql-fb to obtain the Firebird prompt SQL>.
  3. Enter this command to create the database

    CREATE DATABASE 'Countries.fdb' user 'student' password 'pp4s';

  4. Enter the instruction EXIT; to commit the changes and return to the system prompt.
  5. Create a Lazarus application to populate the table. Add to the form a SQLQuery, SQLTransaction and IBConnection then copy this code into the source editor.
    unit Unit1; 
    
    {$mode objfpc}{$H+}
    
    interface
    
    uses
      Classes, SysUtils, IBConnection, sqldb, FileUtil, Forms, Controls, Graphics,
      db, Dialogs, StdCtrls;
    
    type
      TForm1 = class(TForm)
        IBConnection1: TIBConnection;
        SQLQuery1: TSQLQuery;
        SQLTransaction1: TSQLTransaction;
        procedure FormCreate(Sender: TObject);
      end;
    
    var
      Form1: TForm1; 
    
    implementation
    
    {$R *.lfm}
    
    procedure TForm1.FormCreate(Sender: TObject);
    const
      CountryNames: array[1..5] of string = ('Argentina', 'Australia', 'Austria', 'Belgium', 'Botswana');
    var
      i: integer;
    begin
      SQLQuery1.SQL.Text := 'RECREATE TABLE Flags (Country VARCHAR(15) PRIMARY KEY, ' +
                            'Flag BLOB);';
      SQLquery1.ExecSQL;
      SQLTransaction1.Commit;
      for i := 1 to 5 do
        begin
          SQLQuery1.SQL.Text := 'INSERT INTO Flags VALUES(:Country,:Flag);';
          SQLQuery1.ParamByName('Country').AsString := CountryNames[i];
          SQLQuery1.ParamByName('Flag').LoadFromFile(CountryNames[i] + '.png', ftBlob);
         SQLquery1.ExecSQL;
        end;
      SQLTransaction1.Commit;
      ShowMessage('Database populated!');
      halt;
    end;
    
    end.    
    
    

This page was most useful for storing and retrieving images.

We now provide details of two Lazarus form-based demonstration that enables the user to view records in the Flags table created above. The first uses a DBImage, and works with recent versions of Lazarus on a PC but not with the version of Lazarus that is currently available on the Pi with apt-get install lazarus. The code of dbimage.inc has been revised substantially. You can use this on a PC with Countries.fdb copied from the Pi. Close Lazarus and run the executable directly to avoid error messages. The second method is a workaround for the Raspberry Pi which extracts the images to files which are viewed in an Image component.

Code of Unit for Lazarus 1.4 Blob Image Viewer

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
 Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
 Graphics, Dialogs, DbCtrls;

type
 TForm1 = class(TForm)
  DataSource1: TDataSource;
  DBImage1: TDBImage;
  DBNavigator1: TDBNavigator;
  DBText1: TDBText;
  IBConnection1: TIBConnection;
  SQLQuery1: TSQLQuery;
  SQLTransaction1: TSQLTransaction;
 end;

var
 Form1: TForm1;

implementation

{$R *.lfm}

end.    

Text View of Form for Lazarus 1.4 Blob Image Viewer

object Form1: TForm1
  Left = 402
  Height = 112
  Top = 318
  Width = 193
  Caption = 'Flags'
  ClientHeight = 112
  ClientWidth = 193
  LCLVersion = '1.4.0.4'
  object DBImage1: TDBImage
    Left = 112
    Height = 48
    Top = 16
    Width = 56
    DataField = 'FLAG'
    DataSource = DataSource1
  end
  object DBText1: TDBText
    Left = 8
    Height = 31
    Top = 24
    Width = 101
    DataField = 'COUNTRY'
    DataSource = DataSource1
    Font.Height = -23
    ParentColor = False
    ParentFont = False
  end
  object DBNavigator1: TDBNavigator
    Left = 8
    Height = 25
    Top = 80
    Width = 169
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 169
    DataSource = DataSource1
    Options = []
    TabOrder = 0
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast]
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'C:\fdb\Countries.fdb'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    HostName = 'localhost'
    LogEvents = []
    left = 56
    top = 8
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'COUNTRY'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'FLAG'
        DataType = ftBlob
        Precision = -1
        Size = 8
      end>
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Flags'
    )
    InsertSQL.Strings = (
      ''
    )
    Params = <>
    left = 8
    top = 8
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = IBConnection1
    left = 8
    top = 48
  end
  object DataSource1: TDataSource
    DataSet = SQLQuery1
    left = 56
    top = 40
  end
end    

Workaround

The Pascal code of the unit and the text view of the form follow a screenshot of the application running on the Pi.

Workaround on Raspberry Pi

Workaround on Raspberry Pi

Pascal Code of Workaround Unit

unit Unit1; 

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
  Graphics, Dialogs, DbCtrls, ExtCtrls;

type
  TForm1 = class(TForm)
    Datasource1: TDatasource;
    DBNavigator1: TDBNavigator;
    DBText1: TDBText;
    IBConnection1: TIBConnection;
    Image1: TImage;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
    procedure FormCreate(Sender: TObject);
  end;

var
  Form1: TForm1; 

implementation

{$R *.lfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  BlobStream : TStream;
  M: TFileStream;
  filename: string;
  i: integer;
begin
  if not FileExists('Argentina.png') then
    begin
      // Extract images from database and store image files in project directory
      for i := 1 to 5 do
        begin
          filename := SQLQuery1.FieldByName('Country').AsString + '.png';
          BlobStream := SQLQuery1.CreateBlobStream(SQLQuery1.FieldByName('Flag'), bmread);
          M := TFileStream.Create(filename, fmCreate);
          BlobStream.Position:= 0;
          M.CopyFrom(BlobStream, BlobStream.Size);
          M.Free;
          BlobStream.Free;
          SQLQuery1.Next;
        end;
      SQLQuery1.First;
    end;
  Image1.Picture.LoadFromFile('Argentina.png');
end;

procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
begin
  Image1.Picture.LoadFromFile(DBText1.CAption + '.png');
end;

end.
    

Text View of Workaround Form

object Form1: TForm1
  Left = 185
  Height = 105
  Top = 239
  Width = 182
  Caption = 'Flags'
  ClientHeight = 105
  ClientWidth = 182
  OnCreate = FormCreate
  LCLVersion = '0.9.30.4'
  object DBText1: TDBText
    Left = 5
    Height = 1
    Top = 14
    Width = 1
    DataField = 'COUNTRY'
    DataSource = Datasource1
    Font.Height = -19
    ParentColor = False
    ParentFont = False
  end
  object Image1: TImage
    Left = 104
    Height = 66
    Top = 8
    Width = 72
  end
  object DBNavigator1: TDBNavigator
    Left = 9
    Height = 25
    Top = 73
    Width = 160
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 160
    DataSource = Datasource1
    OnClick = DBNavigator1Click
    TabOrder = 0
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast]
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = '/var/lib/firebird/2.5/data/Countries.fdb'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    HostName = 'localhost'
    LogEvents = []
    left = 48
    top = 24
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'COUNTRY'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'FLAG'
        DataType = ftBlob
        Precision = -1
        Size = 8
      end>
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Flags'
    )
    Params = <>
    left = 48
    top = 72
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = IBConnection1
    left = 8
    top = 72
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 8
    top = 24
  end
end    

Programming - a skill for life!

How to use Firebird databases on a Raspberry Pi with Lazarus