Using Firebird Databases on a Raspberry Pi

The Smart Pascal tutorial Databases and Datasets has instructions for installing and testing Firebird and a demonstration of remote access from a web page. The test is carried out with the CONTRIBUTIONS.FDB database created at the isql prompt on a PC. Here we show you how to create on your Raspberry Pi the database CONTRIBUTIONS_PI.FDB, with the same Programmer table as CONTRIBUTIONS.FDB. See the following pages for the handling of date and blob fields.

Creating a Firebird Database at the isql-fb Prompt

  1. If you have not already done so, carry out steps 1, 2 and 6 of our instructions.
  2. Using the Root Terminal, change directory with cd /var/lib/firebird/2.5/data.
  3. Enter the command isql-fb to obtain the Firebird prompt SQL>.
  4. Enter these commands to create the database and table with granted options. (You can copy the whole block then press Enter).

    CREATE DATABASE 'CONTRIBUTIONS_PI.FDB' user 'student' password 'pp4s';
    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));
    GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON Programmer TO PUBLIC WITH GRANT OPTION;

    If you want to delete a database, perhaps because it is easier to start afresh, the simple command DROP DATABASE; will remove the current database.

  5. Enter these commands to populate the table.

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

  6. Enter the instruction EXIT; to commit the changes and return to the system prompt.
  7. Test the access with this Lazarus console application, (adapted from code in the Free Pascal wiki).
    program FB_ConnectionTest;
    uses
      db, IBConnection;
    
    function CreateConnection: TIBConnection;
    begin
      result := TIBConnection.Create(nil);
      result.Hostname := 'localhost';
      result.DatabaseName := '/var/lib/firebird/2.5/data/CONTRIBUTIONS_PI.FDB';
      result.UserName := 'student';
      result.Password := 'pp4s';
    end;
    
    var
      AConnection : TIBConnection;
    
    begin
      AConnection := CreateConnection;
      AConnection.Open;
      if Aconnection.Connected then
        writeln('Successful connect!')
      else
        writeln('A failed connection should raise an exception, preventing this output!');
      AConnection.Close;
      AConnection.Free;
      readln;
    end.    
    

Firebird Update Demonstration

This is a Lazarus form-based demonstration that enables the user to view, delete, edit and add records to the Programmer table created above. See the next page for an example of the use of dates.

We added to the form a DBNavigator and DBGrid (from the Data Controls tab), a Datasource (Data Access tab) and a SQLQuery, SQLTransaction and IBConnection (SQLdb tab). In this quick example we have used the default settings for the grid columns. See our DBGrid demonstration if you need help with customising columns. 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

Pi Screenshot

Pascal Code of Unit

unit uFB_UpdateDemo;

{$mode objfpc}{$H+}

interface

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

type
  TForm1 = class(TForm)
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    IBConnection1: TIBConnection;
    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 = 116
  Height = 171
  Top = 380
  Width = 587
  Caption = 'Firebird Update Demonstration'
  ClientHeight = 171
  ClientWidth = 587
  OnClose = FormClose
  LCLVersion = '0.9.30.4'
  object DBGrid1: TDBGrid
    Left = 8
    Height = 100
    Top = 16
    Width = 568
    Color = clWindow
    Columns = <>
    DataSource = Datasource1
    TabOrder = 0
  end
  object DBNavigator1: TDBNavigator
    Left = 168
    Height = 25
    Top = 128
    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 = 1
  end
  object Datasource1: TDatasource
    DataSet = SQLQuery1
    left = 24
    top = 120
  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 = IBConnection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'SELECT * FROM Programmer ORDER BY Surname'
    )
    Params = <>
    left = 488
    top = 128
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = IBConnection1
    left = 432
    top = 128
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = '/var/lib/firebird/2.5/data/CONTRIBUTIONS_PI.FDB'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    HostName = 'localhost'
    LogEvents = []
    left = 112
    top = 120
  end
end
    
Programming - a skill for life!

Creating, editing, and searching a Firebird database, printing a report, stored procedures and ClientDataSets