FireDAC.Firebird Sample

This sample demonstrates how to use FireDAC to work with Firebird database.

Location

You can find the Firebird sample project at:

• Start | Programs | Embarcadero RAD Studio 10.2 Tokyo | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Getting Started\Firebird
• Subversion Repository:
  • Delphi: http://sourceforge.net/p/radstudiodemos/code/HEAD/tree/branches/RADStudio_Tokyo/Object%20Pascal/Database/FireDAC/Samples/Getting%20Started/Firebird

Description

The Firebird getting started sample shows how to use FireDAC with Firebird databases. This sample allows the user to do:

• Temporary connection definition at run time.
• Master-details relationship between datasets using TFDQuery.
• Transactions handling with TFDTransaction.
• Management of Firebird database such as: managing users, creating database backup/restoring database from backup and validating a database.

How to Use the Sample

1. Navigate to the location given above and open GettingStarted.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
GettingStarted.dproj GettingStarted.dpr	The project itself.
fGettingStarted.pas fGettingStarted.fmx	The main form.

Implementation

The sample implements the following features.

Creating temporary connection definition

The simplest way to configure connection to Firebird database at run time is to build a temporary connection definition:

with dbMain do begin
  Close;
  // create temporary connection definition
  with Params do begin
    Clear;
    Add('DriverID=FB');
    Add('Server=localhost');
    Add('Database=x:\path_to_db\db.fdb');
    Add('User_Name=sysdba');
    Add('Password=masterkey');
  end;
  Open;
end;

In the sample the temporary definition is created when the Connect button is clicked.

Master Details

In this sample, the Categories and Products tables have one-to-many relation by CategoryID field. The qryCategories.SQL property is set up as follows:

select * from Categories

In addition, the qryProducts.SQL property is set up as follows:

select * from Products
where CategoryID = :CategoryID

Finally, the qryProducts.MasterSource property is set to dsCategories, while the MasterFields property is set to CategoryID. This creates a master-details relationship between the datasets.

Transaction handling

Handling of InterBase transactions is demonstrated using TFDTransaction + TFDConnection components. Both are configured as follows: trnMain.Connection property is set to dbMain connection and dbMain.Transaction property is set to trnMain.

Starting a transaction:

trnMain.StartTransaction;

Committing the transaction:

trnMain.Commit;

Rolling back the transaction:

trnMain.Rollback;

Executing queries

Simple queries execution is demonstrated via ExecSQL method of TFDConnection.

Updating records:

dbMain.ExecSQL('update {id Products} set UnitPrice = UnitPrice * :P1 + :P2 ' +
               'where ProductID < 3', [Random(5), Random(3)]);

Getting a scalar value from the database:

dMaxPrice := dbMain.ExecSQLScalar('select MAX(UnitPrice) from {id Products}');

Service work with Firebird database

The sample performs the following management of databases:

• Security management.
• Database maintenance.
• Tracing server activity

All the management of databases is done using the following components TFDIBBackup, TFDIBRestore, TFDIBRestore, TFDIBSecurity, TFDIBTrace and TFDIBValidate.

This demo demonstrates work with TFDIBBackup, TFDIBRestore, TFDIBSecurity and TFDIBValidate components:

with ibSecurity do begin
  Host := 'localhost';
  UserName := 'sysdba';
  Password := 'masterkey';
  AUserName := 'John_Doe';
  case rgSecActions.ItemIndex of
  C_ADD_USER:
    begin
      APassword := 'p@ssword';
      AddUser;
    end;
  C_DELETE_USER:
    begin
      DeleteUser;
    end;
  C_DISPLAY_USER:
    begin
      DisplayUser;
      with Memo1.Lines do begin
        Add('First name: ' + AFirstName);
        Add('Middle name: ' + AMiddleName);
        Add('Last name: ' + ALastName);
      end;
    end;
  C_MODIFY_USER:
    begin
      APassword := 'p@ssword_changed';
      ModifyUser;
    end;
  end;
end;
…
with ibBackup do begin
  Host := 'localhost';
  UserName := 'sysdba';
  Password := 'masterkey';
  AUserName := 'John_Doe';
  BackupFiles.Clear;
  BackupFiles.Add('x:\path_to_backup\db.backup');
  Backup;
end;
…
with ibRestore do begin
  Host := 'localhost';
  UserName := 'sysdba';
  Password := 'masterkey';
  AUserName := 'John_Doe';
  BackupFiles.Clear;
  BackupFiles.Add('x:\path_to_backup\db.backup');
  Restore;
end;
…
with ibValidate do begin
  Host := 'localhost';
  UserName := 'sysdba';
  Password := 'masterkey';
  AUserName := 'John_Doe';
  case rgValActions.ItemIndex of
  C_ANALYZE:   Analyze;
  C_CHECKONLY: CheckOnly;
  C_REPAIR:    Repair;
  C_SWEEP:     Sweep;
  end;
end;

Uses

• TFDQuery
• TFDTransaction
• TFDConnection
• TFDIBBackup
• TFDIBRestore
• TFDIBSecurity
• TFDIBTrace
• TFDIBValidate

See Also

• Connect to Firebird (FireDAC)
• Master-Detail Relationship
• Firebird and Interbase Servers Questions (FireDAC)

Samples

• FireDAC Interbase sample
• FireDAC MS Access sample
• FireDAC MS SQL sample
• FireDAC MySQL sample
• FireDAC SQLite sample