Using Dates in SQLite

On this page we describe first creating at the sqlite3 prompt a database containing names of countries and the date of their last election and then a form-based application to display the data in DBEdit and DBDateEdit components.

We created a new database with the command sqlite3 Dates.sqlite then copied these SQL statements containing the data found here to the sqlite3 prompt:

CREATE TABLE Elections (Country VARCHAR(15) PRIMARY KEY, ElectionDate VARCHAR(10));
INSERT INTO Elections VALUES ('United Kingdom', '07/05/2015');
INSERT INTO Elections VALUES ('Belgium', '25/05/2014');
INSERT INTO Elections VALUES ('Sweden', '14/09/2014');
INSERT INTO Elections VALUES ('Greece', '25/01/2015');
INSERT INTO Elections VALUES ('Denmark', '18/06/2015');
INSERT INTO Elections VALUES ('Finland', '19/04/2015');
INSERT INTO Elections VALUES ('Latvia', '04/10/2014');
INSERT INTO Elections VALUES ('Slovenia', '13/07/2014');
.exit

The application enables you to edit, delete and insert records. When we added the date 16/03/2014 for Serbia, we continued after the warning about possible data corruption. (The DBDateEdit component does not allow a blank field so it does not clear the previous contents, which you can replace with the election date of the added country). The Pascal code of the unit (showing the components used and the code to commit changes to the database) and the text view of the form (showing you how to link up the components) follow the screenshot of the application in action.

Screenshot

Screenshot

Pascal Code of Unit

unit uShowDate;

{$mode objfpc}{$H+}

interface

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

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    DBDateEdit1: TDBDateEdit;
    DBEdit1: TDBEdit;
    DBNavigator1: TDBNavigator;
    Label1: 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

object Form1: TForm1
  Left = 334
  Height = 118
  Top = 233
  Width = 336
  Caption = 'Election Dates'
  ClientHeight = 118
  ClientWidth = 336
  OnClose = FormClose
  LCLVersion = '1.4.4.0'
  object DBNavigator1: TDBNavigator
    Left = 44
    Height = 25
    Top = 53
    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
  end
  object DBDateEdit1: TDBDateEdit
    Left = 229
    Height = 23
    Top = 13
    Width = 103
    CalendarDisplaySettings = [dsShowHeadings, dsShowDayNames]
    OKCaption = 'OK'
    CancelCaption = 'Cancel'
    DateOrder = doNone
    ButtonWidth = 23
    NumGlyphs = 1
    MaxLength = 0
    TabOrder = 1
    Text = '25/05/2014'
    DataField = 'ElectionDate'
    DataSource = DataSource1
  end
  object Label1: TLabel
    Left = 2
    Height = 15
    Top = 16
    Width = 122
    Caption = 'Date of last election for'
    ParentColor = False
  end
  object DBEdit1: TDBEdit
    Left = 128
    Height = 23
    Top = 13
    Width = 96
    DataField = 'Country'
    DataSource = DataSource1
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 2
  end
  object DataSource1: TDataSource
    DataSet = SQLQuery1
    left = 24
    top = 72
  end
  object SQLite3Connection1: TSQLite3Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'C:\Working\SQLiteBlob\Dates.sqlite'
    KeepConnection = False
    Transaction = SQLTransaction1
    LogEvents = []
    Options = []
    left = 296
    top = 32
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'Country'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'ElectionDate'
        DataType = ftString
        Precision = -1
        Size = 10
      end>
    Active = True
    Database = SQLite3Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Elections ORDER BY Country'
    )
    Params = <>
    left = 8
    top = 32
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Database = SQLite3Connection1
    left = 296
    top = 64
  end
end    
Programming - a skill for life!

How to display and edit selected contents of SQLite databases using Lazarus