Searching a Firebird Database

This demonstration shows you how to use a parameter to simplify a search and also gives you the opportunity to run your own SQL queries. You cannot edit the database using this application.

Enter a forename in the edit box and the results appear in the first grid. The programs written by the selected programmer appear in the second grid. The SQL statement with the parameter is

SELECT * FROM Programmer WHERE Forename = :Chosen

As soon as you enter this into the Object Inspector as the SQL property of sqlqProgrammer, the parameter "Chosen" is added automatically to the Params property. The parameter acquires a value when you enter the text of the search:

procedure TfrmContributions.edtSearchEditingDone(Sender: TObject);
begin
  sqlqProgrammer.Close;
  sqlqProgrammer.params.parambyname('Chosen').asstring := edtSearch.Text;
  sqlqProgrammer.Open;
end;

Note that you must close the query to assign a value to the parameter.

You can try the pre-entered SQL statements in the combo box then enter your own. Each new statement is added to the list of statements and persists while the application is running. If you want to make your additions permanent you can modify the code to use these two instructions.

cboSQL.Items.SaveToFile('SQL.txt');
cboSQL.Items.LoadFromFile('SQL.txt');

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 FirebirdDemo2 in action

uFirebirdDemo2.pas

unit uFirebirdDemo2;

{$mode objfpc}{$H+}

interface

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

type
  TfrmContributions = class(TForm)
    cboSQL: TComboBox;
    dbgrdCombined: TDBGrid;
    DBNavigator1: TDBNavigator;
    dsProgrammer: TDatasource;
    dsPrograms: TDatasource;
    dsCombined: TDatasource;
    dbgrdProgrammer: TDBGrid;
    dbgrdPrograms: TDBGrid;
    dbnavPrograms: TDBNavigator;
    edtSearch: TEdit;
    IBConnection1: TIBConnection;
    sqlqProgrammer: TSQLQuery;
    sqlqPrograms: TSQLQuery;
    sqlqCombined: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    StaticText1: TStaticText;
    procedure cboSQLEditingDone(Sender: TObject);
    procedure cboSQLSelect(Sender: TObject);
    procedure edtSearchEditingDone(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure sqlqProgrammerAfterScroll(DataSet: TDataSet);
    procedure UpdateDetail;
  end;
var
  frmContributions: TfrmContributions;

implementation
{$R *.lfm}

procedure TfrmContributions.UpdateDetail;
begin
  sqlqPrograms.Close;
  sqlqPrograms.params.parambyname('ProgramID').asstring := sqlqProgrammer.Fields[0].AsString;
  sqlqPrograms.Open;
end;

procedure TfrmContributions.cboSQLSelect(Sender: TObject);
begin
  sqlqCombined.Close;
  sqlqCombined.SQL.Text := cboSQL.Text;
  sqlqCombined.Open;
end;

procedure TfrmContributions.cboSQLEditingDone(Sender: TObject);
var
  i : integer;
  Found : Boolean;
  CurrentText: string;
begin
  sqlqCombined.Close;
  CurrentText:= cboSQL.Text;
  sqlqCombined.SQL.Text := CurrentText;
  //Add SQL statement if it does not match another in the combo box.
  Found := False;
  for i := 0 to cboSQL.Items.Count - 1 do
    begin
      if cboSQL.Items[i] = CurrentText then
        Found := True;
    end;
  if not Found then
    cboSQL.AddItem(cboSQL.Text, nil);
  sqlqCombined.Open;
end;

procedure TfrmContributions.edtSearchEditingDone(Sender: TObject);
begin
  sqlqProgrammer.Close;
  sqlqProgrammer.params.parambyname('Chosen').asstring := edtSearch.Text;
  sqlqProgrammer.Open;
end;

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

procedure TfrmContributions.sqlqProgrammerAfterScroll(DataSet: TDataSet);
begin
  UpdateDetail;
end;

end.

uFirebirdDemo2.lfm

object frmContributions: TfrmContributions
  Left = 35
  Height = 519
  Top = 249
  Width = 981
  Caption = 'Contributions'
  ClientHeight = 519
  ClientWidth = 981
  OnClose = FormClose
  LCLVersion = '0.9.30'
  object dbgrdProgrammer: TDBGrid
    Left = 0
    Height = 184
    Top = 0
    Width = 320
    Color = clWindow
    Columns = <    
      item
        Title.Caption = 'Forename'
        Width = 140
        FieldName = 'FORENAME'
      end    
      item
        Title.Caption = 'Surname'
        Width = 140
        FieldName = 'SURNAME'
      end>
    DataSource = dsProgrammer
    TabOrder = 0
  end
  object dbgrdPrograms: TDBGrid
    Left = 336
    Height = 180
    Top = 0
    Width = 640
    Color = clWindow
    Columns = <    
      item
        MaxSize = 50
        Title.Caption = 'Program Name'
        Width = 145
        FieldName = 'NAME'
      end    
      item
        MaxSize = 100
        Title.Caption = 'Description'
        Width = 330
        FieldName = 'DESCRIPTION'
      end    
      item
        MaxSize = 30
        Title.Caption = 'Category'
        Width = 125
        FieldName = 'CATEGORY'
      end>
    DataSource = dsPrograms
    TabOrder = 1
  end
  object dbnavPrograms: TDBNavigator
    Left = 712
    Height = 25
    Top = 192
    Width = 224
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 224
    DataSource = dsPrograms
    TabOrder = 2
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbRefresh]
  end
  object edtSearch: TEdit
    Left = 136
    Height = 28
    Top = 189
    Width = 120
    OnEditingDone = edtSearchEditingDone
    TabOrder = 3
    Text = 'James'
  end
  object DBNavigator1: TDBNavigator
    Left = 392
    Height = 25
    Top = 459
    Width = 217
    BevelOuter = bvNone
    ChildSizing.EnlargeHorizontal = crsScaleChilds
    ChildSizing.EnlargeVertical = crsScaleChilds
    ChildSizing.ShrinkHorizontal = crsScaleChilds
    ChildSizing.ShrinkVertical = crsScaleChilds
    ChildSizing.Layout = cclLeftToRightThenTopToBottom
    ChildSizing.ControlsPerLine = 100
    ClientHeight = 25
    ClientWidth = 217
    DataSource = dsCombined
    TabOrder = 4
    VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbRefresh]
  end
  object dbgrdCombined: TDBGrid
    Left = 0
    Height = 232
    Top = 221
    Width = 976
    Color = clWindow
    Columns = <    
      item
        Title.Caption = 'ID'
        Width = 50
        FieldName = 'ID'
      end    
      item
        Title.Caption = 'FORENAME'
        Width = 120
        FieldName = 'FORENAME'
      end    
      item
        Title.Caption = 'SURNAME'
        Width = 120
        FieldName = 'SURNAME'
      end    
      item
        Title.Caption = 'AGE'
        Width = 40
        FieldName = 'AGE'
      end    
      item
        Title.Caption = 'NAME'
        Width = 145
        FieldName = 'NAME'
      end    
      item
        Title.Caption = 'DESCRIPTION'
        Width = 330
        FieldName = 'DESCRIPTION'
      end    
      item
        Title.Caption = 'CATEGORY'
        Width = 130
        FieldName = 'CATEGORY'
      end>
    DataSource = dsCombined
    TabOrder = 5
  end
  object cboSQL: TComboBox
    Left = 0
    Height = 28
    Top = 489
    Width = 936
    ItemHeight = 20
    ItemIndex = 0
    Items.Strings = (
      'SELECT * FROM Programmer INNER JOIN Programs ON ID = ProgrammerID ORDER BY Forename; '
      'SELECT * FROM Programmer INNER JOIN Programs ON ID = ProgrammerID ORDER BY Surname; '
      'SELECT * FROM Programmer INNER JOIN Programs ON ID = ProgrammerID WHERE Name LIKE ''S%''  ORDER BY Name, Category; '
      'SELECT * FROM Programmer ORDER BY Forename;'
      'SELECT * FROM Programs ORDER by Category, Name;'
      'SELECT FIRST 5 Forename, Surname FROM Programmer ORDER BY Forename, Surname ;'
    )
    OnEditingDone = cboSQLEditingDone
    OnSelect = cboSQLSelect
    TabOrder = 6
    Text = 'SELECT * FROM Programmer INNER JOIN Programs ON ID = ProgrammerID ORDER BY Forename; '
  end
  object StaticText1: TStaticText
    Left = 16
    Height = 24
    Top = 189
    Width = 114
    Caption = 'Enter forename:'
    TabOrder = 7
  end
  object dsProgrammer: TDatasource
    AutoEdit = False
    DataSet = sqlqProgrammer
    left = 120
    top = 16
  end
  object IBConnection1: TIBConnection
    Connected = False
    LoginPrompt = False
    DatabaseName = 'F:\Firebird\CONTRIBUTIONS.FDB'
    KeepConnection = False
    Password = 'pp4s'
    Transaction = SQLTransaction1
    UserName = 'student'
    left = 228
    top = 24
  end
  object SQLTransaction1: TSQLTransaction
    Active = False
    Action = caCommitRetaining
    Database = IBConnection1
    left = 40
    top = 8
  end
  object sqlqProgrammer: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    AfterScroll = sqlqProgrammerAfterScroll
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'SELECT * FROM Programmer WHERE Forename = :Chosen'
    )
    UpdateSQL.Strings = (
      ''
    )
    InsertSQL.Strings = (
      ''
    )
    DeleteSQL.Strings = (
      ''
    )
    Params = <    
      item
        DataType = ftUnknown
        Name = 'Chosen'
        ParamType = ptUnknown
      end>
    left = 312
    top = 16
  end
  object sqlqPrograms: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'SELECT * from Programs WHERE ProgrammerID = :ProgramID ORDER BY Name'
    )
    UpdateSQL.Strings = (
      ''
    )
    DeleteSQL.Strings = (
      ''
    )
    Params = <    
      item
        DataType = ftUnknown
        Name = 'ProgramID'
        ParamType = ptUnknown
      end>
    left = 392
    top = 16
  end
  object dsPrograms: TDatasource
    DataSet = sqlqPrograms
    left = 712
    top = 128
  end
  object dsCombined: TDatasource
    DataSet = sqlqCombined
    left = 647
    top = 37
  end
  object sqlqCombined: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'SELECT * FROM Programmer INNER JOIN Programs ON ID = ProgrammerID'
      'ORDER BY Forename;'
    )
    Params = <>
    left = 875
    top = 300
  end
end

FirebirdDemo2.lpr

program FirebirdDemo2;

{$mode objfpc}{$H+}

uses
  Interfaces, Forms, uFirebirdDemo2;

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TfrmContributions, frmContributions);
  Application.Run;
end.