Firebird Database

Firebird Database — Skill

Use this skill when working with Firebird database in Delphi projects via FireDAC.

When to Use

• When configuring FireDAC connection with Firebird
• When creating tables, generators, stored procedures, triggers, domains and views
• When implementing Repositories with FireDAC + Firebird
• When working with transactions, isolation levels and concurrency
• When planning schema migrations (versioned scripts)
• When optimizing queries and indexes for Firebird

Firebird Versions

Version	Relevant News
2.5	Trace API, LIST() aggregate, Windows Trusted Auth
3.0	Native BOOLEAN, IDENTITY columns, Packages, UDR (replaces UDF), Window Functions (OVER), Encryption
4.0	DECFLOAT, INT128, TIME/TIMESTAMP WITH TIME ZONE, Replication, Batch API, LATERAL join
5.0	WHEN NOT MATCHED BY SOURCE, Parallel Backup, SQL Security hardening, Profiler

Recommendation: Use Firebird 3.0+ for new projects. Avoid deprecated features like UDFs.

FireDAC connection with Firebird

Minimum Configuration

unit MeuApp.Infra.Database.Connection;

interface

uses
  FireDAC.Comp.Client,
  FireDAC.Phys.FB,        //Driver Firebird
  FireDAC.Phys.FBDef,     //Defaults do Firebird
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.DApt;

type
  ///<summary>
  ///Firebird connection factory via FireDAC.
  ///</summary>
  TFirebirdConnectionFactory = class
  public
    ///<summary>
    ///Creates and configures a Firebird connection.
    ///</summary>
    ///<param name="ADatabasePath">Full path of the .fdb file</param>
    ///<param name="AUserName">User (default: SYSDBA)</param>
    ///<param name="APassword">Bank password</param>
    ///<returns>FireDAC connection configured and opened</returns>
    class function CreateConnection(
      const ADatabasePath: string;
      const AUserName: string = 'SYSDBA';
      const APassword: string = 'masterkey'
    ): TFDConnection;

    ///<summary>
    ///Creates a connection via Embedded Server (without fbserver).
    ///</summary>
    class function CreateEmbeddedConnection(
      const ADatabasePath: string
    ): TFDConnection;
  end;

implementation

uses
  System.SysUtils;

class function TFirebirdConnectionFactory.CreateConnection(
  const ADatabasePath: string;
  const AUserName: string;
  const APassword: string): TFDConnection;
begin
  if ADatabasePath.Trim.IsEmpty then
    raise EArgumentException.Create('ADatabasePath não pode ser vazio');

  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'FB';
    Result.Params.Database := ADatabasePath;
    Result.Params.UserName := AUserName;
    Result.Params.Password := APassword;

    { Configurações recomendadas }
    Result.Params.Values['CharacterSet'] := 'UTF8';
    Result.Params.Values['Protocol'] := 'TCPIP';     // Local: 'Local'
    Result.Params.Values['Server'] := 'localhost';
    Result.Params.Values['Port'] := '3050';
    Result.Params.Values['SQLDialect'] := '3';        //ALWAYS Dialect 3
    Result.Params.Values['PageSize'] := '16384';      // 16KB recomendado

    { Opções do driver FireDAC }
    Result.FormatOptions.StrsTrim2Len := True;         //Trim CHAR for VARCHAR
    Result.FetchOptions.Mode := fmAll;                 // Fetch completo
    Result.ResourceOptions.AutoReconnect := True;      //Automatic reconnection
    Result.TxOptions.Isolation := xiReadCommitted;     //Standard isolation

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

class function TFirebirdConnectionFactory.CreateEmbeddedConnection(
  const ADatabasePath: string): TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'FB';
    Result.Params.Database := ADatabasePath;

    { Embedded: sem servidor, sem user/password obrigatórios no FB3+ }
    Result.Params.Values['Protocol'] := 'Local';
    Result.Params.Values['CharacterSet'] := 'UTF8';
    Result.Params.Values['SQLDialect'] := '3';

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;