SQLite Databases

If you have worked through the Firebird examples in the preceding pages you should be able to apply your knowledge to SQLite databases. We followed excellent instructions to set up SQLite on a PC (running Windows 10). We used the 32-bit version and added the directory C:\SQLite3 to both the User and System PATH environmental variables. We created the Contributions test database with a Programmer table by typing/copying these commands at the SQLite prompt and continuation prompts:

.open Contributions.sqlite
CREATE TABLE Programmer (ID INT NOT NULL PRIMARY KEY,
Forename VARCHAR(15) NOT NULL,
Surname VARCHAR(15),
YearGroup VARCHAR(3),
Age INT NOT NULL CHECK (AGE > 10 AND Age < 20));

We populated the table with these commands:

INSERT INTO PROGRAMMER VALUES (21, 'Christopher', 'Winward', 'L6', 16);
INSERT INTO PROGRAMMER VALUES (9, 'Max', 'Foster', 'L6', 16);
INSERT INTO PROGRAMMER VALUES (13, 'Jack', 'Fearn', 'U6', 17);
INSERT INTO PROGRAMMER VALUES (1, 'Matt', 'Tucker', 'L6', 16);
INSERT INTO PROGRAMMER VALUES (2, 'Michael', 'Wardley', 'U6', 18);
INSERT INTO PROGRAMMER VALUES (40, 'James', 'Hall', 'L6', 16);
INSERT INTO PROGRAMMER VALUES (30, 'Peter', 'Hearnshaw', 'L6', 16);

We confirmed the inserts with SELECT * From Programmer; then tried INSERT INTO PROGRAMMER VALUES (1, 'Jim', 'Ditto', 'L6', 16);, which obtained the desired response:

Error: UNIQUE constraint failed: Programmer.ID

(Support for SQL foreign key constraints was not introduced until SQLite version 3.6.19 and it is disabled by default. Enter PRAGMA foreign_keys = ON; for each connection to enable the constraints.)

The test command INSERT INTO PROGRAMMER VALUES (41, 'Jo', 'Old', 'L6', 20); also received an expected error message:

Error: CHECK constraint failed: Programmer

The first demonstration is for display of data (without any updating) in a DBGrid using default settings for field names and column widths. We also added to the form a SQLite3Connection, DataSource, SQLTransaction, SQLQuery and DBNavigator then, using the Object Inspector, "hooked up" the components in the usual way as seen in the text view of the form. The database Contributions.sqlite is in the project folder. You might need to enter its full pathname as the DatabaseName property of the SQLTransaction before setting the Active property of the SQLQuery to True. You should see data in the design view of the grid. To output a better header than YearGroup, change the SQL property of the SQLQuery to SELECT ID, Forename, Surname, YearGroup AS "Year Group", Age FROM Programmer ORDER BY ID.

See below a screenshot of the output and the code a demonstration of updating data in DBEdit boxes. You might also like to try (1) updating with a DBGrid as demonstrated on the next page for a Raspberry Pi and (2) remote access from a web page using Smart Pascal.

Output

Pascal Code of Unit for Displaying Data in a DBGrid

unit Unit1;

{$mode objfpc}{$H+}

interface

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

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

end.

Text View of Form with DBGrid

object Form1: TForm1
  Left = 383
  Height = 176
  Top = 209
  Width = 584
  Caption = 'Programmers'
  ClientHeight = 176
  ClientWidth = 584
  LCLVersion = '1.4.0.4'
  object DBNavigator1: TDBNavigator
    Left = 164
    Height = 25
    Top = 140
    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 DBGrid1: TDBGrid
    Left = 8
    Height = 116
    Top = 8
    Width = 568
    Color = clWindow
    Columns = <>
    DataSource = DataSource1
    Enabled = False
    TabOrder = 1
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'Contributions.sqlite'
    KeepConnection = False
    Transaction = SQLTransaction1
    LogEvents = []
    Options = []
    left = 76
    top = 132
  end
  object DataSource1: TDataSource
    DataSet = SQLQuery1
    left = 12
    top = 132
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'ID'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end    
      item
        Name = 'Forename'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'Surname'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'YearGroup'
        DataType = ftString
        Precision = -1
        Size = 3
      end    
      item
        Name = 'Age'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end>
    Active = True
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Programmer ORDER BY ID'
    )
    Params = <>
    left = 452
    top = 132
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = SQLite3Connection1
    left = 548
    top = 132
  end
end

Editing a SQLite Database

This example shows the data in DBEdit boxes instead of a grid and enables the user to edit, insert and delete records. You can browse through the edited data using the DBNavigator, and the data is committed to the database (using code from Mr C's A-Level Computing) when the form is closed. Attempting to save the new data gives an error message that the database is locked when you execute the application from within the Lazarus IDE but not when you close Lazarus then run the executable. You could place the commit code instead in the AfterPost event procedure of the SQLQuery if you want to update the database on disk immediately after each change is posted to the dataset in memory.

Output

Pascal Code of Unit for Updating Data in DBEdit Boxes

unit Unit1;

{$mode objfpc}{$H+}

interface

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

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    dbedtID, dbedtForename, dbedtSurname, dbedtYearGroup, dbedtAge: TDBEdit;
    DBNavigator1: TDBNavigator;
    Label1, Label2, Label3, Label4, Label5: TLabel;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery1.Edit;
  SQLQuery1.UpdateMode := UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
end;

end.

Text View of Form with DBEdit Boxes

object Form1: TForm1
  Left = 421
  Height = 96
  Top = 263
  Width = 337
  Caption = 'Programmers'
  ClientHeight = 96
  ClientWidth = 337
  OnClose = FormClose
  LCLVersion = '1.4.0.4'
  object DBNavigator1: TDBNavigator
    Left = 48
    Height = 25
    Top = 64
    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, nbInsert, nbDelete, nbEdit, nbPost, nbCancel]
  end
  object dbedtID: TDBEdit
    Left = 8
    Height = 23
    Top = 32
    Width = 24
    DataField = 'ID'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 0
    TabOrder = 5
  end
  object dbedtForename: TDBEdit
    Left = 48
    Height = 23
    Top = 32
    Width = 80
    DataField = 'Forename'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 1
  end
  object dbedtSurname: TDBEdit
    Left = 136
    Height = 23
    Top = 32
    Width = 80
    DataField = 'Surname'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 2
  end
  object dbedtYearGroup: TDBEdit
    Left = 232
    Height = 23
    Top = 32
    Width = 48
    DataField = 'YearGroup'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 3
    TabOrder = 3
  end
  object dbedtAge: TDBEdit
    Left = 304
    Height = 23
    Top = 32
    Width = 24
    DataField = 'Age'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 0
    TabOrder = 4
  end
  object Label1: TLabel
    Left = 8
    Height = 15
    Top = 10
    Width = 13
    Caption = 'ID'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object Label2: TLabel
    Left = 53
    Height = 15
    Top = 10
    Width = 56
    Caption = 'Forename'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object Label3: TLabel
    Left = 138
    Height = 15
    Top = 10
    Width = 50
    Caption = 'Surname'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object Label4: TLabel
    Left = 233
    Height = 15
    Top = 10
    Width = 62
    Caption = 'Year Group'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object Label5: TLabel
    Left = 304
    Height = 15
    Top = 10
    Width = 22
    Caption = 'Age'
    Font.Style = [fsBold]
    ParentColor = False
    ParentFont = False
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'C:\Working\SQLiteContributionsUpdate\Contributions.sqlite'
    KeepConnection = False
    Transaction = SQLTransaction1
    LogEvents = []
    Options = []
    left = 104
    top = 8
  end
  object DataSource1: TDataSource
    DataSet = SQLQuery1
    left = 8
    top = 64
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = SQLite3Connection1
    left = 200
    top = 8
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'ID'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end    
      item
        Name = 'Forename'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'Surname'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'YearGroup'
        DataType = ftString
        Precision = -1
        Size = 3
      end    
      item
        Name = 'Age'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end>
    Active = True
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Programmer ORDER BY ID'
    )
    Params = <>
    left = 296
    top = 64
  end
end