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