Editing a Firebird Database

We used this program to populate and edit our database of contributions to this website. You can use the first two grids to add, delete and edit records. The demonstrations in the following sections provide read-only access to this data.

Generally a database application needs one or more sets of connected components such as:

  1. a connection to the database;
  2. a transaction with a link to the connection;
  3. a dataset derived from the database by a query that is linked to the transaction;
  4. a data source, linked to the dataset, that provides input to data aware controls;
  5. a data aware control such a DBGrid;
  6. a navigator linked to each data source.

In our first demonstration the corresponding components are:

  1. a single connection IBConnection1 with its DatabaseName property set to 'F:\Firebird\CONTRIBUTIONS.FDB';
  2. a single transaction SQLTransaction1 with its Database property set to IBConnection1;
  3. three components of type TSQLQuery (sqlqProgrammer, sqlqPrograms and sqlqCombined), with Transaction property set to SQLTransaction1;
  4. the corresponding components of type TDatasource (dsProgrammer, dsPrograms and dsCombined) with Dataset property set to sqlqProgrammer, sqlqPrograms and sqlqCombined, respectively;
  5. the corresponding components of type TDBGrid (dbgrdProgrammer, dbgrdPrograms and dbgrdCombined) with DataSource property set to dsProgrammer, dsPrograms and dsCombined, respectively;
  6. the corresponding components of type TDBNavigator (dbnavProgrammer, dbnavPrograms and dbnavCombined) with DataSource property set to dsProgrammer, dsPrograms and dsCombined, respectively.

When the Connected property of the connection and the Active properties of the transaction and queries are True you can see the data in the grids at design time.

These tabs contain the database components:

  • Data Controls: TDBNavigator and TDBGrid
  • Data Access: TDataSource
  • SQLdb: TSQLQuery, TSQLTransaction, TSQLScript and TIBConnection

The delete button of each navigator did not meet our needs so instead you can run our own scripts via Delete buttons. (If you want to use more of the inbuilt functionality of the DBNavigator with a single short block of code for applying the updates to the database, see a later page on SQLite or Firebird on a Raspberry Pi). Use the + button of the navigator to add a record. You can use the last button of the Programmers and Programs navigators to refresh the data after attempting to commit changes by clicking an Update button. You can then check that the data has been accepted and has not contravened any of the constraints. For example, the age must be greater than 10 and less than 20. We understand that use of the argument 1 in our code sqlqProgrammer.ApplyUpdates(1); causes a single exception to be suppressed. (Use of the value 0 instead of 1 had resulted in an occasional irritating error message that could be ignored without repercussions). We do not complicate the program with error-trapping routines.

You can click on column headers to sort by that column. The SQL code to achieve this is simple.

The final grid data is derived from both tables using a convenient INNER JOIN in SQL to restrict the data shown to exactly what we want: 
SELECT Forename, Name, Description, Category FROM Programmer INNER JOIN programs ON ID = ProgrammerID ORDER BY FORENAME

You can carry out a search to match only part of a field with the LIKE keyword together with required letters and the wildcard % which matches any sequence of letters. For example, the expression Forename LIKE A% matches all forenames that begin with a capital A. In this demonstration you can use wildcards to search the descriptions of contributions.

We supply the source for all demonstration programs and the database Contributions.fdb in db_contributions.zip. The Firebird demonstrations are set up with the database in the F:\Firebird directory, so put it there or change the DatabaseName property of the connection to its filepath on your system. The demonstrations need either Firebird server to be running or the embedded Firebird files (fbclient.dll etc.) to be in your program folder.

Program FirebirdDemo1 in action

Program FirebirdDemo1 in action

This web tutorial was useful for writing Lazarus applications that use the Firebird database.

uFirebirdDemo1.pas

unit uFirebirdDemo1;

{$mode objfpc}{$H+}

interface

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

type
  TfrmContributions = class(TForm)
    btnUpdate: TButton;
    btnUpdatePrograms: TButton;
    btnRestore: TButton;
    btnDeleteProgrammer: TButton;
    btnDeleteProgram: TButton;
    dsProgrammer: TDatasource;
    dsPrograms: TDatasource;
    dsCombined: TDatasource;
    dbgrdProgrammer: TDBGrid;
    dbgrdPrograms: TDBGrid;
    dbgrdCombined: TDBGrid;
    dbnavProgrammer: TDBNavigator;
    dbnavPrograms: TDBNavigator;
    dbnavCombined: TDBNavigator;
    edtSearch: TEdit;
    IBConnection1: TIBConnection;
    sqlqProgrammer: TSQLQuery;
    sqlqPrograms: TSQLQuery;
    sqlqCombined: TSQLQuery;
    SQLScript1: TSQLScript;
    SQLTransaction1: TSQLTransaction;
    StaticText2: TStaticText;
    procedure btnDeleteProgramClick(Sender: TObject);
    procedure btnUpdateClick(Sender: TObject);
    procedure btnUpdateProgramsClick(Sender: TObject);
    procedure btnDeleteProgrammerClick(Sender: TObject);
    procedure dbgrdCombinedTitleClick(Column: TColumn);
    procedure dbgrdProgrammerTitleClick(Column: TColumn);
    procedure dbgrdProgramsTitleClick(Column: TColumn);
    procedure edtSearchChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  end;
var
  frmContributions: TfrmContributions;

implementation

{$R *.lfm}

procedure TfrmContributions.btnUpdateClick(Sender: TObject);
begin
  sqlqProgrammer.Edit;
  sqlqProgrammer.Post;
  sqlqProgrammer.ApplyUpdates(1);
  SQLTransaction1.CommitRetaining;
  sqlqCombined.Refresh;
end;

procedure TfrmContributions.btnDeleteProgramClick(Sender: TObject);
begin
  //Enclose name in quotes and end with a semicolon
  SQLScript1.Script.Text:= 'DELETE FROM Programs WHERE NAME = ''' +
                            dbgrdPrograms.Columns[0].Field.AsString + ''';';
  SQLScript1.Execute;
  SQLTransaction1.CommitRetaining;
  sqlqPrograms.Refresh;
  sqlqCombined.Refresh;

end;

procedure TfrmContributions.btnUpdateProgramsClick(Sender: TObject);
begin
  sqlqPrograms.Edit;
  sqlqPrograms.Post;
  sqlqPrograms.ApplyUpdates(1);
  SQLTransaction1.CommitRetaining;
  sqlqCombined.Refresh;
end;

procedure TfrmContributions.btnDeleteProgrammerClick(Sender: TObject);
begin
  SQLScript1.Script.Text:= 'Delete from Programmer WHERE ID = ' + dbgrdProgrammer.Columns[0].Field.AsString + ';';
  SQLScript1.Execute;
  SQLTransaction1.CommitRetaining;
  sqlqProgrammer.Refresh;
  sqlqCombined.Refresh;
end;

procedure TfrmContributions.dbgrdCombinedTitleClick(Column: TColumn);
begin
  sqlqCombined.Close;
  sqlqCombined.SQL.Text := 'SELECT Forename, Name, Description, Category FROM ' +
                           'Programmer INNER JOIN Programs ON ID = ProgrammerID ' +
                           'ORDER BY ' + Column.Fieldname;
  sqlqCombined.Open;
end;

procedure TfrmContributions.dbgrdProgrammerTitleClick(Column: TColumn);
begin
  sqlqProgrammer.Close;
  sqlqProgrammer.SQL.Text := 'Select * from Programmer ORDER BY ' + Column.Fieldname;
  sqlqProgrammer.Open;
end;

procedure TfrmContributions.dbgrdProgramsTitleClick(Column: TColumn);
begin
  sqlqPrograms.Close;
  sqlqPrograms.SQL.Text := 'Select * from Programs ORDER BY ' + Column.Fieldname;
  sqlqPrograms.Open;
end;

procedure TfrmContributions.edtSearchChange(Sender: TObject);
const
   QueryStart = 'SELECT Forename, Name, Description, Category FROM ' +
                'Programmer INNER JOIN Programs ON ID = ProgrammerID ' +
                'WHERE Description LIKE ';
begin
  sqlqCombined.Close;
  sqlqCombined.SQL.Text := QueryStart + '''%'+edtSearch.Text+'%''';
  sqlqCombined.Open;
end;

procedure TfrmContributions.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  sqlqProgrammer.Close;
  sqlqPrograms.Close;
  sqlqCombined.Close;
  SQLTransaction1.Active:= False;
  IBConnection1.Connected:= False;
end;

end.

uFirebirdDemo1.lfm

object frmContributions: TfrmContributions
  Left = 367
  Height = 629
  Top = 160
  Width = 774
  Caption = 'Contributions'
  ClientHeight = 629
  ClientWidth = 774
  OnClose = FormClose
  LCLVersion = '0.9.30'
  object btnUpdate: TButton
    Left = 520
    Height = 25
    Top = 184
    Width = 168
    Caption = 'Update programmers'
    OnClick = btnUpdateClick
    TabOrder = 0
  end
  object dbgrdProgrammer: TDBGrid
    Left = 128
    Height = 184
    Top = 0
    Width = 531
    Color = clWindow
    Columns = <    
      item
        Title.Caption = 'ID'
        Width = 60
        FieldName = 'ID'
      end    
      item
        Title.Caption = 'Forename'
        Width = 140
        FieldName = 'FORENAME'
      end    
      item
        Title.Caption = 'Surname'
        Width = 140
        FieldName = 'SURNAME'
      end    
      item
        Title.Caption = 'Year Group'
        Width = 100
        FieldName = 'YEARGROUP'
      end    
      item
        Title.Caption = 'Age'
        Width = 50
        FieldName = 'AGE'
      end>
    DataSource = dsProgrammer
    TabOrder = 1
    OnTitleClick = dbgrdProgrammerTitleClick
  end
  object dbnavProgrammer: TDBNavigator
    Left = 264
    Height = 25
    Top = 184
    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 = dsProgrammer
    TabOrder = 2
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbInsert, nbRefresh]
  end
  object dbgrdPrograms: TDBGrid
    Left = 0
    Height = 156
    Top = 224
    Width = 774
    Color = clWindow
    Columns = <    
      item
        MaxSize = 50
        Title.Caption = 'Program Name'
        Width = 145
        FieldName = 'NAME'
      end    
      item
        MaxSize = 100
        Title.Caption = 'Description'
        Width = 350
        FieldName = 'DESCRIPTION'
      end    
      item
        MaxSize = 30
        Title.Caption = 'Category'
        Width = 125
        FieldName = 'CATEGORY'
      end    
      item
        Title.Caption = 'Programmer ID'
        Width = 110
        FieldName = 'PROGRAMMERID'
      end>
    DataSource = dsPrograms
    TabOrder = 3
    OnTitleClick = dbgrdProgramsTitleClick
  end
  object dbnavPrograms: TDBNavigator
    Left = 256
    Height = 25
    Top = 384
    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 = dsPrograms
    TabOrder = 4
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbInsert, nbRefresh]
  end
  object btnUpdatePrograms: TButton
    Left = 624
    Height = 25
    Top = 384
    Width = 125
    Caption = 'Update Programs'
    OnClick = btnUpdateProgramsClick
    TabOrder = 5
  end
  object dbgrdCombined: TDBGrid
    Left = 0
    Height = 178
    Top = 416
    Width = 775
    AutoEdit = False
    Color = clWindow
    Columns = <    
      item
        Title.Caption = 'Programmer'
        Width = 120
        FieldName = 'FORENAME'
      end    
      item
        Title.Caption = 'Program'
        Width = 145
        FieldName = 'NAME'
      end    
      item
        Title.Caption = 'Description'
        Width = 350
        FieldName = 'DESCRIPTION'
      end    
      item
        Title.Caption = 'Category'
        Width = 120
        FieldName = 'CATEGORY'
      end>
    DataSource = dsCombined
    TabOrder = 6
    OnTitleClick = dbgrdCombinedTitleClick
  end
  object dbnavCombined: TDBNavigator
    Left = 256
    Height = 25
    Top = 597
    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 = dsCombined
    TabOrder = 7
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast]
  end
  object btnRestore: TButton
    Left = 672
    Height = 25
    Top = 601
    Width = 75
    Caption = 'View all'
    TabOrder = 8
  end
  object edtSearch: TEdit
    Left = 136
    Height = 28
    Top = 597
    Width = 112
    OnChange = edtSearchChange
    TabOrder = 9
  end
  object StaticText2: TStaticText
    Left = 7
    Height = 17
    Top = 601
    Width = 128
    Caption = 'Search Description:'
    TabOrder = 10
  end
  object btnDeleteProgrammer: TButton
    Left = 48
    Height = 25
    Top = 184
    Width = 201
    Caption = 'Delete Selected Programmer'
    OnClick = btnDeleteProgrammerClick
    TabOrder = 11
  end
  object btnDeleteProgram: TButton
    Left = 7
    Height = 25
    Top = 384
    Width = 176
    Caption = 'Delete Selected Program'
    OnClick = btnDeleteProgramClick
    TabOrder = 12
  end
  object dsProgrammer: TDatasource
    DataSet = sqlqProgrammer
    left = 120
    top = 16
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'F:\Firebird\CONTRIBUTIONS.FDB'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    left = 228
    top = 45
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caCommitRetaining
    Database = IBConnection1
    left = 40
    top = 8
  end
  object sqlqProgrammer: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'select * from Programmer order by Forename'
    )
    UpdateSQL.Strings = (
      ''
    )
    InsertSQL.Strings = (
      ''
    )
    DeleteSQL.Strings = (
      ''
    )
    Params = <>
    UpdateMode = upWhereChanged
    UsePrimaryKeyAsKey = False
    left = 312
    top = 16
  end
  object sqlqPrograms: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'Select * from Programs ORDER BY Name'
    )
    UpdateSQL.Strings = (
      ''
    )
    DeleteSQL.Strings = (
      ''
    )
    Params = <>
    UpdateMode = upWhereChanged
    UsePrimaryKeyAsKey = False
    left = 392
    top = 16
  end
  object dsPrograms: TDatasource
    DataSet = sqlqPrograms
    left = 712
    top = 128
  end
  object sqlqCombined: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'SELECT Forename, Name, Description, Category FROM Programmer INNER JOIN programs ON ID = ProgrammerID ORDER BY FORENAME'
    )
    Params = <>
    left = 727
    top = 36
  end
  object dsCombined: TDatasource
    DataSet = sqlqCombined
    left = 647
    top = 37
  end
  object SQLScript1: TSQLScript
    DataBase = IBConnection1
    Transaction = SQLTransaction1
    Directives.Strings = (
      'SET TERM'
      'COMMIT'
      '#IFDEF'
      '#IFNDEF'
      '#ELSE'
      '#ENDIF'
      '#DEFINE'
      '#UNDEF'
      '#UNDEFINE'
    )
    Script.Strings = (
      ''
    )
    Terminator = ';'
    CommentsinSQL = True
    UseSetTerm = True
    UseCommit = True
    UseDefines = True
    left = 40
    top = 64
  end
end

FirebirdDemo1.lpr

program FirebirdDemo1;

{$mode objfpc}{$H+}

uses
  Interfaces, Forms, uFirebirdDemo1;

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TfrmContributions, frmContributions);
  Application.Run;
end.
PrevGreen03.png
NextGreen03.png
UpGreen03.png
Programming - a skill for life!

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

  1. Planning a Database
    How to plan a database with links to avoid redundancy of data
  2. Installing Firebird
    How to download Firebird and to install it on your PC
  3. Creating a Firebird Database
    How create a Firebird relational database using isql commands
  4. Editing a Firebird Database
    Demonstration of populating and editing a Firebird database using a Lazarus application
  5. Searching a Firebird Database
    Demonstration of searching a Firebird database using queries with parameters
  6. Creating and Printing a Report
    Demonstration of creating and printing a report from data in a Firebird database
  7. Creating and Using Stored Procedures
    Demonstration of creating and using stored procedures in a Firebird database
  8. SQLite Databases
    How to display and edit selected contents of SQLite databases using Lazarus
  9. Using ClientDataSets in Delphi
    Using ClientDataSets with a Master-Detail Relationship
  10. Using MemDataSets in Lazarus
    How to set-up and use the TMemDataset component in Lazarus
  11. Using BufDataSets in Lazarus
    How to set-up and use the TBufDataset component in Lazarus
  12. Using Firebird Databases on a Raspberry Pi
    How to use Firebird databases on a Raspberry Pi with Lazarus
dita powered logo dita powered™. Copyright © 2010-17 Pascal Programming for Schools. All rights reserved.