InterBase

InterBase is Embarcadero's lightweight, embeddable SQL database. Trysil's InterBase driver is close to Firebird, but uses InterBase-specific SQL: generators (CREATE GENERATOR / GEN_ID) for sequences and ROWS pagination. That last one dates the driver: ROWS m TO n arrived in InterBase 2007, which is the minimum a paged query needs.

Unit: Trysil.Data.FireDAC.InterBase Delphi Edition: Community

SQL Dialect 3 required

InterBase databases used with Trysil must be created with SQL Dialect 3. Dialect 3 is required for the column types Trysil maps to — BOOLEAN, 64-bit NUMERIC(18,0), and TIMESTAMP.

Trysil does not create databases — provisioning the database (and choosing its dialect) is up to you. The DDL generated by the Trysil Expert for InterBase assumes a Dialect 3 database.

isql creates a Dialect 1 database unless you issue SET SQL DIALECT 3; before CREATE DATABASE; most GUI tools (IBConsole) default to Dialect 3.

Type notes

• 64-bit integers (Int64): InterBase has no BIGINT keyword (a Firebird extension) — use NUMERIC(18,0).
• GUID (TGUID): CHAR(16) CHARACTER SET OCTETS (16-byte binary).
• Boolean: native BOOLEAN (Dialect 3 only).
• Money (Currency): DECIMAL(18,4). InterBase caps numeric precision at 18 digits, so the DECIMAL(19,4) used on most other engines is rejected. 18 digits still cover any realistic amount.

Character set

A database created with a Unicode default character set - UTF8, or UNICODE_FSS on older files - must be told to the connection:

TTInterBaseConnection.RegisterConnection(
  'Main', 'localhost', 'SYSDBA', 'masterkey', 'C:\data\database.ib', 'UTF8');

Without it the connection opens with no character set, and the server stops transliterating. Two things follow, and both are quiet. Text travels as raw bytes in each direction, so anything outside ASCII comes back changed. And the server stops counting characters in a VARCHAR(n), counting the bytes behind it instead: a column declared for 100 characters accepts 300 ASCII ones on a UTF8 database, and the column metadata Trysil reads reports that width too, so the guard that refuses a string longer than its column has nothing to refuse.

The parameter is optional and defaults to the previous behaviour, so an application that has been storing raw bytes keeps reading them back the same way. Declare it on a new database, or after checking what is really stored in an old one - SELECT RDB$CHARACTER_SET_NAME FROM RDB$DATABASE says what the database was created with.

Setup

uses
  Trysil.Data.FireDAC.InterBase;

TTInterBaseConnection.RegisterConnection(
  'Main', 'localhost', 'SYSDBA', 'masterkey', 'C:\data\database.ib');

LConnection := TTInterBaseConnection.Create('Main');
try
  LContext := TTContext.Create(LConnection);
  try
    // Use the context...
  finally
    LContext.Free;
  end;
finally
  LConnection.Free;
end;

Connection Pooling

Enable connection pooling for server applications:

uses
  Trysil.Data.FireDAC.ConnectionPool;

TTFireDACConnectionPool.Instance.Config.Enabled := True;

Sequences

InterBase uses generators for primary key generation (InterBase has no CREATE SEQUENCE statement — that is a Firebird extension):

CREATE GENERATOR PersonsID;

The next value is retrieved using GEN_ID:

SELECT GEN_ID(PersonsID, 1) FROM RDB$DATABASE;

Map it to the entity:

[TTable('Persons')]
[TSequence('PersonsID')]
type
  TPerson = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;
    // ...
  end;

Schema Example

CREATE TABLE Persons (
  ID INTEGER NOT NULL,
  Firstname VARCHAR(100) NOT NULL,
  Lastname VARCHAR(100) NOT NULL,
  VersionID INTEGER NOT NULL,
  CONSTRAINT PK_Persons PRIMARY KEY (ID)
);

CREATE GENERATOR PersonsID;