SQLite Databases
.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));
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