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:
- a connection to the database;
- a transaction with a link to the connection;
- a dataset derived from the database by a query that is linked to the transaction;
- a data source, linked to the dataset, that provides input to data aware controls;
- a data aware control such a DBGrid;
- a navigator linked to each data source.
In our first demonstration the corresponding components are:
- a single connection IBConnection1 with its DatabaseName property set to 'F:\Firebird\CONTRIBUTIONS.FDB';
- a single transaction SQLTransaction1 with its Database property set to IBConnection1;
- three components of type TSQLQuery (sqlqProgrammer, sqlqPrograms and sqlqCombined), with Transaction property set to SQLTransaction1;
- the corresponding components of type TDatasource (dsProgrammer, dsPrograms and dsCombined) with Dataset property set to sqlqProgrammer, sqlqPrograms and sqlqCombined, respectively;
- the corresponding components of type TDBGrid (dbgrdProgrammer, dbgrdPrograms and dbgrdCombined) with DataSource property set to dsProgrammer, dsPrograms and dsCombined, respectively;
- 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.
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
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.