Using Images as Blobs in SQLite

On this page we describe first a console application to create a database containing names of countries and images of their flags and then a form-based application to display the data in DBText and DBImage components. We convert the flag images to long strings, which are then stored as blobs. Interestingly, Wikipedia describes how the BLOB field type was named initially after a film before being given the backronym Binary Large OBject.

We create the test database Countries.sqlite by Pascal code, with the help of Yury Plashenkov's example and his source code of SQLite3.pas, SQLite3Utils.pas and SQLite3Wrap.pas. These files are in the project folder together with the flag images used in our TImageList demonstration.

Pascal Code of Console Project

This code will also create the required database with Lazarus version 0.9.30.4 on a Raspberry Pi.

program SQLiteCreateBlobDB;

uses
  SQLite3, SQLite3Wrap, Classes, Sysutils;
const
  CountryNames: array[1..5] of string = ('Argentina', 'Australia', 'Austria', 'Belgium', 'Botswana');
var
  DB: TSQLite3Database;
  Stmt: TSQLite3Statement;
  Stream: TMemoryStream;
  i: Integer;
begin
  DB := TSQLite3Database.Create;
  DB.Open('Countries.sqlite');
  DB.Execute('CREATE TABLE Flags (Country VARCHAR(15) PRIMARY KEY, Flag BLOB)');
  for i := 1 to 5 do
    begin
      Stmt := DB.Prepare('INSERT INTO Flags VALUES (' + quotedStr(CountryNames[i]) + ',?)');
      Stream := TMemoryStream.Create;
      Stream.LoadFromFile(CountryNames[i] + '.png');
      Stmt.BindBlob(1, Stream.Memory, Stream.Size);
      Stream.Free;
      Stmt.Step;
    end;
  Stmt.Free;
end.
    

Application to Display Text and Images

The code of the unit below the screenshot of the display shows the seven components that we added to the form without any Pascal code of our own. You will see the first country name and flag in the design view of the form when you have linked up the components (using the Object Inspector) with the help of the text view of the form below. Note that this will not work with Lazarus version 0.9.30.4 on the Raspberry Pi. If you need to use a Pi you could adapt our workaround for viewing Firebird blob images by replacing the IBConnection by a SQLite3Connection.

Screenshot

Pascal Code of Unit

unit Unit1;

{$mode objfpc}{$H+}

interface

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

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

var
  Form1: TForm1;

implementation

{$R *.lfm}

end.
    

Text View of Form

object Form1: TForm1
  Left = 543
  Height = 129
  Top = 314
  Width = 299
  Caption = 'Blob Image Demo'
  ClientHeight = 129
  ClientWidth = 299
  LCLVersion = '1.4.0.4'
  object DBImage1: TDBImage
    Left = 168
    Height = 59
    Top = 8
    Width = 61
    DataField = 'Flag'
    DataSource = DataSource1
  end
  object DBText1: TDBText
    Left = 64
    Height = 30
    Top = 16
    Width = 91
    DataField = 'Country'
    DataSource = DataSource1
    Font.Height = -21
    ParentColor = False
    ParentFont = False
  end
  object DBNavigator1: TDBNavigator
    Left = 24
    Height = 25
    Top = 80
    Width = 241
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 241
    DataSource = DataSource1
    Options = []
    TabOrder = 0
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast]
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = SQLite3Connection1
    left = 32
    top = 24
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'C:\Working\SQLiteBlob\Countries.sqlite'
    KeepConnection = False
    Transaction = SQLTransaction1
    LogEvents = []
    Options = []
    left = 120
    top = 40
  end
  object DataSource1: TDataSource
    DataSet = SQLQuery1
    left = 240
    top = 40
  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 = 0
      end>
    Active = True
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'Select * FROM Flags'
    )
    Params = <>
    left = 240
    top = 8
  end
end