InstantIBX

InterBase Express (IBX) broker for Firebird and InterBase databases.

Overview

The InstantIBX unit provides database persistence using InterBase Express (IBX), Borland/Embarcadero's native data access components for Firebird and InterBase databases.

Supported Databases:

• Firebird 1.5, 2.x, 3.x, 4.x, 5.x
• InterBase 6.x, 7.x, XE and later

Key Features:

• Native IBX component integration
• Automatic database creation with custom page size and character set
• Full transaction support with TIBTransaction
• SQL Dialect 3 support
• Delimited identifier support (optional)
• Boolean emulation using SMALLINT
• 64-bit platform support

Key Classes

TInstantIBXConnectionDef

Connection definition for IBX broker.

Properties

Property	Type	Default	Description
ConnectionString	string	''	Database file path (e.g., 'C:\Data\MyDB.fdb')
Params	string	''	IBX connection parameters (user_name, password, lc_ctype, etc.)
Options	TInstantIBXOptions	[]	Broker options (ibxUseDelimitedIdents)

Methods

CreateConnection

function CreateConnection(AOwner: TComponent): TCustomConnection; override;

Creates TIBDatabase with:

• DatabaseName from ConnectionString
• SQLDialect = 3
• Params from connection def

TInstantIBXConnector

Main connector component for IBX.

Inheritance:

TComponent → TInstantConnector → TInstantConnectionBasedConnector → TInstantIBXConnector

Properties

Property	Type	Default	Description
Connection	TIBDatabase	nil	IBX database connection component
Transaction	TIBTransaction	auto-created	Transaction component (read-only)
Options	TInstantIBXOptions	[]	Broker options
OnLogin	TIBDatabaseLoginEvent	nil	Custom login handler

Methods

GetTransaction

function GetTransaction: TIBTransaction;

Returns transaction object. Auto-creates on first access with:

• DefaultDatabase = Connection
• Params = 'read_committed'
• AutoStopAction = saCommit (Delphi 6+)

InternalCreateDatabase

procedure InternalCreateDatabase; override;

Creates Firebird/InterBase database with:

• Page Size: 4096 bytes (fixed)
• Character Set: From lc_ctype param (default: 'none')
• SQL Dialect: 3
• User/Password: From connection params

Example params:

USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8

TInstantIBXBroker

Broker for IBX data access.

Inheritance:

TInstantBroker → TInstantSQLBroker → TInstantIBXBroker

Properties

Property	Type	Description
Connector	TInstantIBXConnector	Parent connector
Dialect	Integer	SQL dialect (always 3)

Methods

DataTypeToColumnType

function DataTypeToColumnType(DataType: TInstantDataType; Size: Integer): string; override;

Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → DOUBLE PRECISION
dtCurrency  → DECIMAL(14,4)
dtBoolean   → SMALLINT
dtString    → VARCHAR(n)
dtMemo      → BLOB SUB_TYPE 1
dtDateTime  → TIMESTAMP
dtBlob      → BLOB
dtDate      → DATE
dtTime      → TIME
dtEnum      → INTEGER

CreateDataSet

function CreateDataSet(const AStatement: string; AParams: TParams = nil;
  OnAssignParamValue: TAssignParamValue = nil): TDataSet; override;

Creates TIBQuery with:

• Database = Connector.Connection
• Transaction = Connector.Transaction
• SQL.Text = AStatement

GetSQLDelimiters

function GetSQLDelimiters: string; override;

Returns:

• "" (double quotes) if ibxUseDelimitedIdents in Options
• '' (empty) otherwise

AssignDataSetParams

procedure AssignDataSetParams(DataSet: TDataSet; AParams: TParams;
  OnAssignParamValue: TAssignParamValue = nil); override;

Assigns parameters with boolean conversion:

• ftBoolean → AsInteger (0/1)

TInstantIBXResolver

Resolver with boolean field interpretation.

Inheritance:

TInstantCustomResolver → TInstantSQLResolver → TInstantIBXResolver

Methods

ReadBooleanField

function ReadBooleanField(DataSet: TDataSet; const FieldName: string;
  out AWasNull: boolean): Boolean; override;

Reads SMALLINT field as boolean (0 = False, non-zero = True).

TInstantIBXTranslator

IQL to SQL translator with boolean conversion.

Inheritance:

TInstantRelationalTranslator → TInstantIBXTranslator

Methods

TranslateConstant

function TranslateConstant(Constant: TInstantIQLConstant;
  Writer: TInstantIQLWriter): Boolean; override;

Translates IQL constants to SQL:

• TRUE → '1'
• FALSE → '0'

TInstantIBXQuery

Query class using IBX translator.

Inheritance:

TInstantQuery → TInstantSQLQuery → TInstantIBXQuery

Types and Constants

TInstantIBXOption

type
  TInstantIBXOption = (ibxUseDelimitedIdents);
  TInstantIBXOptions = set of TInstantIBXOption;

Options:

• ibxUseDelimitedIdents: Enable delimited identifiers (double quotes) for table/field names

Default Options

const
  DefaultInstantIBXOptions = [];

Usage Patterns

Basic Setup - Firebird

Design-time configuration:

// 1. Drop TIBDatabase on form
IBDatabase1.DatabaseName := 'C:\Data\MyDatabase.fdb';
IBDatabase1.SQLDialect := 3;
IBDatabase1.Params.Text :=
  'user_name=SYSDBA' + sLineBreak +
  'password=masterkey' + sLineBreak +
  'lc_ctype=UTF8';

// 2. Drop TInstantIBXConnector on form
InstantIBXConnector1.Connection := IBDatabase1;
InstantIBXConnector1.Connected := True;

Programmatic Connection

uses
  InstantIBX, IBX.IBDatabase;

procedure ConnectToFirebird(const DatabasePath: string);
var
  Connection: TIBDatabase;
  Connector: TInstantIBXConnector;
begin
  Connection := TIBDatabase.Create(nil);
  Connection.DatabaseName := DatabasePath;
  Connection.SQLDialect := 3;
  Connection.Params.Values['user_name'] := 'SYSDBA';
  Connection.Params.Values['password'] := 'masterkey';
  Connection.Params.Values['lc_ctype'] := 'UTF8';

  Connector := TInstantIBXConnector.Create(nil);
  Connector.Connection := Connection;
  Connector.IsDefault := True;
  Connector.Connected := True;

  ShowMessage('Connected to ' + DatabasePath);
end;

Creating Firebird Database

procedure CreateFirebirdDatabase(const DatabasePath: string);
var
  Connection: TIBDatabase;
  Connector: TInstantIBXConnector;
begin
  Connection := TIBDatabase.Create(nil);
  try
    Connection.DatabaseName := DatabasePath;
    Connection.SQLDialect := 3;
    Connection.Params.Values['user_name'] := 'SYSDBA';
    Connection.Params.Values['password'] := 'masterkey';
    Connection.Params.Values['lc_ctype'] := 'UTF8';  // Character set

    Connector := TInstantIBXConnector.Create(nil);
    try
      Connector.Connection := Connection;
      Connector.CreateDatabase;
      // Creates .fdb file with:
      // - Page size: 4096
      // - Character set: UTF8
      // - SQL Dialect: 3
      ShowMessage('Database created: ' + DatabasePath);
    finally
      Connector.Free;
    end;
  finally
    Connection.Free;
  end;
end;

Using Connection Definition

procedure ConnectViaConnectionDef;
var
  ConnDef: TInstantIBXConnectionDef;
  Connector: TInstantIBXConnector;
begin
  ConnDef := TInstantIBXConnectionDef.Create(nil);
  try
    ConnDef.ConnectionString := 'C:\Data\MyDatabase.fdb';
    ConnDef.Params :=
      'user_name=SYSDBA' + sLineBreak +
      'password=masterkey' + sLineBreak +
      'lc_ctype=UTF8';
    ConnDef.Options := [];  // Or [ibxUseDelimitedIdents]

    Connector := TInstantIBXConnector.Create(nil);
    try
      Connector.ConnectionDef := ConnDef;
      Connector.IsDefault := True;
      Connector.Connected := True;

      // Use connector...
    finally
      Connector.Free;
    end;
  finally
    ConnDef.Free;
  end;
end;

Using Delimited Identifiers

// Enable delimited identifiers for reserved words
Connector.Options := [ibxUseDelimitedIdents];

// Now tables/fields with reserved words are quoted:
// SELECT "Order", "User" FROM "MyTable"

Custom Login Handler

type
  TMainForm = class(TForm)
  private
    procedure IBXLogin(Database: TIBDatabase; LoginParams: TStrings);
  end;

procedure TMainForm.IBXLogin(Database: TIBDatabase; LoginParams: TStrings);
var
  Username, Password: string;
begin
  if PromptForCredentials(Username, Password) then
  begin
    LoginParams.Values['user_name'] := Username;
    LoginParams.Values['password'] := Password;
  end
  else
    Abort;
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  InstantIBXConnector1.OnLogin := IBXLogin;
  InstantIBXConnector1.Connected := True;
end;

Transaction Management

procedure PerformTransactionalOperation;
begin
  InstantIBXConnector1.StartTransaction;
  try
    // Perform operations
    Obj1.Store;
    Obj2.Store;
    Obj3.Delete;

    InstantIBXConnector1.CommitTransaction;
  except
    InstantIBXConnector1.RollbackTransaction;
    raise;
  end;
end;

Character Set Configuration

// UTF-8 encoding (recommended)
Connection.Params.Values['lc_ctype'] := 'UTF8';

// ISO-8859-1 (Western European)
Connection.Params.Values['lc_ctype'] := 'ISO8859_1';

// No character set conversion
Connection.Params.Values['lc_ctype'] := 'NONE';

Connection Parameters

// Basic connection
Connection.Params.Text :=
  'user_name=SYSDBA' + sLineBreak +
  'password=masterkey' + sLineBreak +
  'lc_ctype=UTF8';

// With role
Connection.Params.Text :=
  'user_name=MYUSER' + sLineBreak +
  'password=mypass' + sLineBreak +
  'sql_role_name=ADMIN' + sLineBreak +
  'lc_ctype=UTF8';

// Embedded server (Firebird Embedded)
Connection.Params.Text :=
  'lc_ctype=UTF8';
// No user_name/password needed for embedded

Building Database

procedure BuildDatabase(Connector: TInstantIBXConnector;
  Model: TInstantModel);
var
  Builder: TInstantDBBuilder;
begin
  Connector.Connect;

  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;
    Builder.TargetModel := Model;
    Builder.BuildCommandSequence;

    Connector.StartTransaction;
    try
      Builder.CommandSequence.Execute;
      Connector.CommitTransaction;
      ShowMessage('Database created successfully');
    except
      Connector.RollbackTransaction;
      raise;
    end;
  finally
    Builder.Free;
  end;
end;

Evolving Database Schema

procedure EvolveDatabase(Connector: TInstantIBXConnector;
  Model: TInstantModel);
var
  Evolver: TInstantDBEvolver;
begin
  Evolver := TInstantDBEvolver.Create(nil);
  try
    Evolver.Connector := Connector;
    Evolver.TargetModel := Model;
    Evolver.BuildCommandSequence;

    if Evolver.CommandSequence.Count > 0 then
    begin
      Connector.StartTransaction;
      try
        Evolver.CommandSequence.Execute;
        Connector.CommitTransaction;
        ShowMessage('Database evolved successfully');
      except
        Connector.RollbackTransaction;
        raise;
      end;
    end
    else
      ShowMessage('Database schema is up to date');
  finally
    Evolver.Free;
  end;
end;

Database Creation Details

Page Size

Fixed at 4096 bytes in InternalCreateDatabase.

Note: Modern Firebird supports larger page sizes (8192, 16384), but InstantIBX uses 4096 for compatibility.

To use custom page size: Create database manually using IBConsole or isql, then connect with InstantIBX.

Character Sets

Supported character sets (common):

• UTF8 - Unicode (recommended)
• ISO8859_1 - Western European
• WIN1252 - Windows Western European
• NONE - No character set conversion

Default: NONE (if lc_ctype not specified)

SQL Dialect

Always uses SQL Dialect 3 (modern Firebird standard).

Dialect 3 features:

• DATE, TIME, and TIMESTAMP as separate types
• DECIMAL and NUMERIC types
• Double-quoted identifiers
• No implicit string truncation

Connection String Format

Local Database

C:\Data\MyDatabase.fdb

TCP/IP Connection

server:C:\Data\MyDatabase.fdb

or

server/port:C:\Data\MyDatabase.fdb

Examples:

localhost:C:\Data\MyDatabase.fdb
192.168.1.100:C:\Data\MyDatabase.fdb
firebird.mycompany.com:C:\Data\MyDatabase.fdb
firebird.mycompany.com/3051:C:\Data\MyDatabase.fdb

Embedded Server

C:\Data\MyDatabase.fdb

With fbembed.dll in application directory (no Firebird server needed).

Best Practices

1. Always Use UTF-8 Character Set

Connection.Params.Values['lc_ctype'] := 'UTF8';

2. Use Transactions for All Operations

Connector.StartTransaction;
try
  // Operations
  Connector.CommitTransaction;
except
  Connector.RollbackTransaction;
  raise;
end;

3. Close Connections Properly

try
  // Use connector
finally
  Connector.Disconnect;
end;

4. Use Delimited Identifiers for Reserved Words

Connector.Options := [ibxUseDelimitedIdents];

5. Set SQL Dialect to 3

Connection.SQLDialect := 3;  // Modern syntax

6. Backup Before Schema Changes

// Use gbak to backup
procedure BackupDatabase(const DatabaseFile, BackupFile: string);
begin
  // Execute: gbak -b -user SYSDBA -password masterkey database.fdb backup.fbk
end;

7. Monitor Transaction State

if Connector.Transaction.InTransaction then
  ShowMessage('Transaction active');

8. Use Connection Pooling for Multi-User Apps

// Create connector pool
// Reuse connections instead of creating new ones

Troubleshooting

"Database file not found"

Problem: EIBInterBaseError with SQLCode -902.

Solutions:

• Verify file path is correct
• Check file permissions
• Ensure Firebird server is running (if using TCP/IP)
• Check fbclient.dll / gds32.dll is accessible

"Unavailable database"

Problem: Cannot connect to database.

Solutions:

// Check server is running
// Verify connection string format:
Connection.DatabaseName := 'localhost:C:\Data\MyDB.fdb';

// Check Firebird service status
// Windows: services.msc → "Firebird Server"

Boolean Values Not Working

Problem: Boolean fields not reading/writing correctly.

Cause: Firebird uses SMALLINT for booleans (0/1).

Solution: InstantIBX handles this automatically via TInstantIBXResolver.

Character Encoding Issues

Problem: Special characters corrupted.

Solution:

// Set UTF-8 character set
Connection.Params.Values['lc_ctype'] := 'UTF8';

// Verify database character set
// SELECT RDB$CHARACTER_SET_NAME FROM RDB$DATABASE;

Transaction Errors

Problem: "Cannot commit inactive transaction".

Solution:

// Always check transaction state
if Connector.Transaction.InTransaction then
  Connector.CommitTransaction;

Page Size Limitations

Problem: Need larger page size than 4096.

Solution:

// Create database manually with desired page size:
// isql
// CREATE DATABASE 'C:\Data\MyDB.fdb'
//   USER 'SYSDBA' PASSWORD 'masterkey'
//   PAGE_SIZE 8192
//   DEFAULT CHARACTER SET UTF8;

// Then connect with InstantIBX

Dialect Mismatch

Problem: "SQL dialect 1 is not supported".

Cause: Database created with dialect 1.

Solution:

// Recreate database or upgrade dialect
// gfix -sql_dialect 3 database.fdb

Connection Pooling Issues

Problem: Too many simultaneous connections.

Solution:

// Limit max connections in firebird.conf:
// MaxUserConnections = 50

// Or implement application-level pooling

Performance Optimization

1. Use Prepared Statements

// Queries are automatically prepared by TIBQuery
Query.SQL.Text := 'SELECT * FROM MyTable WHERE Id = :Id';
Query.ParamByName('Id').AsString := 'ABC';
Query.Open;

2. Optimize Buffer Sizes

Connection.Params.Values['num_buffers'] := '256';  // Default: 75

3. Use Read-Committed Isolation

Transaction.Params.Clear;
Transaction.Params.Add('read_committed');  // Default

4. Enable Forced Writes

// In firebird.conf:
// ForcedWrites = 1  (safer, slightly slower)
// ForcedWrites = 0  (faster, less safe)

5. Sweep Configuration

// Set sweep interval
// gfix -sweep 20000 database.fdb

See Also

• InstantFireDAC - Modern multi-database broker (recommended for new projects)
• InstantBrokers - Broker architecture
• InstantPersistence - Persistence infrastructure
• InstantDBBuild - Database building
• InstantDBEvolution - Schema evolution

Source Code

File: InstantIBX.pasLocation: Source/Brokers/IBX/

Summary

The InstantIBX broker provides native Firebird/InterBase persistence using InterBase Express (IBX).

Key Features:

• Native IBX component integration
• Automatic database creation (page size 4096, UTF-8)
• Full transaction support with auto-created TIBTransaction
• Boolean emulation using SMALLINT (0/1)
• Delimited identifier support
• SQL Dialect 3

Best for:

• Firebird/InterBase applications
• Legacy projects using IBX
• Applications requiring Firebird Embedded

Consider instead:

• InstantFireDAC - For new projects (modern, multi-database support)
• Supports Firebird and InterBase with better performance

Data Types:

• Booleans → SMALLINT (0/1)
• Strings → VARCHAR(n)
• Memo → BLOB SUB_TYPE 1
• Currency → DECIMAL(14,4)
• DateTime → TIMESTAMP