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.
SELECT * FROM Programmer WHERE Forename = :ChosenAs 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.