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:
- Using the Root Terminal, change directory with cd /var/lib/firebird/2.5/data.
- Enter the command isql-fb to obtain the Firebird prompt SQL>.
- 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);
- 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'); - 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