Using Dates in Firebird Databases on a Raspberry Pi

See the preceding page for notes on getting started with Firebird on the Raspberry Pi. Follow these instructions to create a database with a table containing a field of type DATE:

1. Using the Root Terminal, change directory with cd /var/lib/firebird/2.5/data.
2. Enter the command isql-fb to obtain the Firebird prompt SQL>.
3. Enter these commands to create the database and table.

   CREATE DATABASE 'Dates.fdb' user 'student' password 'pp4s';
   CREATE TABLE Elections (Country VARCHAR(15) PRIMARY KEY, ElectionDate DATE);
   

4. Enter these commands to populate the table. (You can copy the whole block then press Enter).

   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');

5. Enter the instruction EXIT; to commit the changes and return to the system prompt.

Five of the many possible date string formats for June 25th 2004 from the documentation are '25.6.2004', '06/25/2004', '6-25-04', '20040625' and 'June 25, 2004'.

We now provide details of a Lazarus form-based demonstration that enables the user to view and edit records in the Election table created above. We added to the form a button, a DBNavigator and DBCalendar from the Data Controls tab, a Datasource from the Data Access tab and a SQLQuery, SQLScript, SQLTransaction and IBConnection from the SQLdb tab. (We tried first without a SQLScript and button and found that changes made to the date using the DBCalendar were not retained on returning to the record). The SQLite equivalent with a DBDateEdit on a PC with a more recent version of Lazarus was much more straightforward.

The Pascal code of the unit and the text view of the form follow a screenshot of the application running on the Pi.

Pi Screenshot

Pascal Code of Unit

unit Unit1; 

{$mode objfpc}{$H+}

interface

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

type
  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    DBCalendar1: TDBCalendar;
    DBEdit1: TDBEdit;
    DBNavigator1: TDBNavigator;
    IBConnection1: TIBConnection;
    SQLQuery1: TSQLQuery;
    SQLScript1: TSQLScript;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    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;

procedure TForm1.Button1Click(Sender: TObject);
var
  DateString: string;
begin
  // Change dashes to dots to convert from US date format
  DateString := ansiReplaceStr(DBCalendar1.Date, '-', '.');
  // Build the query
  SQLScript1.Script.Text := 'UPDATE Elections SET ElectionDate = ' +  QuotedStr(DateString) +
                            ' WHERE Country = ' + QuotedStr(DBEdit1.Text) + ';';
  // Execute and commit to the database
  SQLScript1.Execute;
  SQLTransaction1.CommitRetaining;
  SQLQuery1.Refresh;
end;

end.    

Text View of Form

object Form1: TForm1
  Left = 293
  Height = 240
  Top = 225
  Width = 355
  Caption = 'Election Dates'
  ClientHeight = 240
  ClientWidth = 355
  OnClose = FormClose
  LCLVersion = '0.9.30.4'
  object DBNavigator1: TDBNavigator
    Left = 74
    Height = 25
    Top = 208
    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
    TabOrder = 0
  end
  object DBCalendar1: TDBCalendar
    Left = 128
    Height = 190
    Top = 8
    Width = 220
    DateTime = 41784
    DataField = 'ELECTIONDATE'
    DataSource = Datasource1
  end
  object DBEdit1: TDBEdit
    Left = 8
    Height = 21
    Top = 10
    Width = 112
    DataField = 'COUNTRY'
    DataSource = Datasource1
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 2
  end
  object Button1: TButton
    Left = 8
    Height = 25
    Top = 168
    Width = 104
    Caption = 'Commit date'
    OnClick = Button1Click
    TabOrder = 3
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 32
    top = 48
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = '/var/lib/firebird/2.5/data/Dates.fdb'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    HostName = 'localhost'
    LogEvents = []
    left = 32
    top = 96
  end
  object SQLQuery1: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <    
      item
        Name = 'COUNTRY'
        DataType = ftString
        Precision = -1
        Size = 15
      end    
      item
        Name = 'ELECTIONDATE'
        DataType = ftDate
        Precision = -1
        Size = 0
      end>
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Elections ORDER BY Country'
    )
    Params = <>
    left = 72
    top = 88
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = IBConnection1
    left = 28
    top = 208
  end
  object SQLScript1: TSQLScript
    DataBase = IBConnection1
    Transaction = SQLTransaction1
    Directives.Strings = (
      'SET TERM'
      'COMMIT'
      '#IFDEF'
      '#IFNDEF'
      '#ELSE'
      '#ENDIF'
      '#DEFINE'
      '#UNDEF'
      '#UNDEFINE'
    )
    Terminator = ';'
    CommentsinSQL = True
    UseSetTerm = True
    UseCommit = True
    UseDefines = True
    left = 72
    top = 40
  end
end