InstantFireDAC

Ember

cadero FireDAC-based broker supporting multiple database engines.

Overview

The InstantFireDAC unit provides a universal database broker built on Embarcadero's FireDAC data access library. It supports multiple database engines through a single, unified interface, making it the recommended broker for new InstantObjects applications.

Supported Databases:

• Microsoft SQL Server (MSSQL)
• Firebird / Interbase
• Oracle
• PostgreSQL (optional)
• MySQL (optional)
• SQLite (optional)
• Sybase ASA (optional)

Key advantages:

• Multi-database support via single broker
• Modern FireDAC architecture (replaces legacy BDE, dbExpress)
• High performance with parameterized queries
• Full transaction support
• Connection pooling and optimization
• Automatic database creation (Firebird/Interbase)
• 64-bit support with Linux compatibility

Key Classes

TInstantFireDACConnectionDef

Connection definition for FireDAC broker.

Properties

Property	Type	Default	Description
DriverId	string	''	FireDAC driver ID (e.g., 'MSSQL', 'FB', 'Ora')
Database	string	''	Database name or file path
Server	string	''	Database server address (e.g., 'localhost, 1433')
HostName	string	''	Legacy server name (deprecated, use Server)
Port	Integer	0	Server port number
User_Name	string	''	Database username
Password	string	''	Plain text password
EncryptedPassword	string	''	Encrypted password (preferred)
OSAuthent	Boolean	False	Use Windows/OS authentication
Protocol	string	''	Legacy protocol name (deprecated, use DriverId)
AdditionalParams	string	''	Additional FireDAC connection parameters
UseDelimitedIdents	Boolean	False	Use delimited identifiers (quotes) for table/field names
Isolation	TFDTxIsolation	xiUnspecified	Transaction isolation level
Catalog	string	''	Database catalog name

Methods

GetConnectionParams

function GetConnectionParams: string;

Returns connection string with all parameters formatted for FireDAC.

Example output:

DriverID=MSSQL
Server=localhost, 1433
User_Name=sa
EncryptedPassword=...
Database=MyDatabase
OSAuthent=No

UpdateConnectionParams

procedure UpdateConnectionParams(const AParams: string);

Parses connection string and updates properties.

TInstantFireDACConnector

Main connector component for FireDAC.

Inheritance:

TComponent → TInstantConnector → TInstantConnectionBasedConnector → TInstantFireDACConnector

Properties

Property	Type	Default	Description
Connection	TFDConnection	nil	FireDAC connection component
UseDelimitedIdents	Boolean	False	Enable delimited identifier quotes
OnLogin	TFDConnectionLoginEvent	nil	Custom login handler

Methods

CreateBroker

function CreateBroker: TInstantBroker; override;

Creates database-specific broker based on Connection.DriverName:

• 'MSSQL' → TInstantFireDACMSSQLBroker
• 'FB' / 'IB' → TInstantFireDACIbFbBroker
• 'Ora' → TInstantFireDACOracleBroker
• 'PG' → TInstantFireDACPgSQLBroker
• 'MySQL' → TInstantFireDACMySQLBroker
• 'SQLite' → TInstantFireDACSQLiteBroker
• 'ASA' → TInstantFireDACSybaseBroker

TInstantFireDACBroker

Abstract base broker for all FireDAC database engines.

Inheritance:

TInstantBroker → TInstantSQLBroker → TInstantFireDACBroker

Properties

Property	Type	Description
Connector	TInstantFireDACConnector	Parent connector

Methods

DataTypeToColumnType

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

Converts InstantObjects data type to database-specific column type.

CreateDataSet

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

Creates TFDQuery with SQL statement and parameters.

Execute

function Execute(const AStatement: string; AParams: TParams = nil;
  OnAssignParamValue: TAssignParamValue = nil): Integer; override;

Executes SQL statement and returns rows affected.

GetSQLDelimiters

function GetSQLDelimiters: string; override;

Returns delimiter characters for identifiers based on database engine:

• SQL Server: []
• MySQL: ``
• PostgreSQL, Oracle, Firebird: ""

Returns empty string if UseDelimitedIdents = False.

AssignDataSetParams

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

Assigns parameters to TFDQuery, handling data type conversions:

• ftBoolean → INTEGER (if database doesn't support boolean)
• ftBlob → RawByteString
• ftDateTime → ftTimeStamp
• ftCurrency → ftBCD

Database-Specific Brokers

TInstantFireDACMSSQLBroker

Microsoft SQL Server broker.

Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → FLOAT
dtCurrency  → MONEY
dtBoolean   → BIT
dtString    → VARCHAR(n)
dtMemo      → TEXT
dtDateTime  → DATETIME
dtBlob      → IMAGE
dtDate      → DATETIME
dtTime      → DATETIME
dtEnum      → INTEGER

Features:

• Native BIT type for booleans
• Uses IMAGE for binary data
• Supports MONEY for currency

Connection Example:

ConnectionDef.DriverId := 'MSSQL';
ConnectionDef.Server := 'localhost, 1433';
ConnectionDef.Database := 'MyDatabase';
ConnectionDef.OSAuthent := True;  // Windows authentication

TInstantFireDACIbFbBroker

Firebird / Interbase broker.

Data Type Mapping:

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

Features:

• Automatic database creation with custom page size and character set
• Uses SMALLINT for booleans (0/1)
• BLOB SUB_TYPE 1 for text (memo)
• Default page size: 8192 bytes
• Default charset: UTF-8

Database Creation:

Connection.Params.Values['CharacterSet'] := 'utf8';
Connection.Params.Values['PageSize'] := '8192';  // 4096, 8192, or 16384
Connector.CreateDatabase;

Valid Page Sizes:

• 4096 (recommended for small databases)
• 8192 (default, recommended for most cases)
• 16384 (for large databases, FB 2.0+)

InternalCreateDatabase

procedure InternalCreateDatabase; override;

Creates Firebird/Interbase database with:

• Character set: UTF-8 (default)
• SQL Dialect: 3
• Page size: 8192 (configurable via PageSize param)

TInstantFireDACOracleBroker

Oracle database broker.

Data Type Mapping:

dtInteger   → NUMBER(10,0)
dtFloat     → NUMBER(28,15)
dtCurrency  → NUMBER(18,4)
dtBoolean   → NUMBER(1,0)
dtString    → VARCHAR2(n)
dtMemo      → CLOB
dtDateTime  → DATE
dtBlob      → BLOB
dtDate      → DATE
dtTime      → DATE
dtEnum      → NUMBER(10,0)

Features:

• Uses NUMBER for numeric types with precision/scale
• CLOB for large text
• Automatic data type mapping rules for Oracle peculiarities

Connection Configuration:

// Oracle-specific data type mapping rules applied automatically
with Connection.FormatOptions do
begin
  OwnMapRules := True;
  // NUMBER(1,0) → Boolean
  // NUMBER(5,0) → Int32
  // NUMBER(10,0) → Int32
  // NUMBER(18,4) → Currency
end;

TInstantFireDACPgSQLBroker

PostgreSQL broker.

Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → FLOAT8
dtCurrency  → DECIMAL(18,4)
dtBoolean   → BOOLEAN
dtString    → VARCHAR(n)
dtMemo      → TEXT
dtDateTime  → TIMESTAMP
dtBlob      → BYTEA
dtDate      → TIMESTAMP
dtTime      → TIMESTAMP
dtEnum      → INTEGER

Features:

• Native BOOLEAN type support
• BYTEA for binary data
• FLOAT8 (64-bit float) for floating point

TInstantFireDACMySQLBroker

MySQL broker.

Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → FLOAT
dtCurrency  → DECIMAL(18,4)
dtBoolean   → TINYINT(1)
dtString    → VARCHAR(n)
dtMemo      → TEXT
dtDateTime  → DATETIME
dtBlob      → BLOB
dtDate      → DATE
dtTime      → TIME
dtEnum      → INTEGER

Features:

• Uses TINYINT(1) for booleans
• Separate DATE and TIME types
• Custom DROP INDEX syntax (via TInstantMySQLGenerator)

Custom Generator

TInstantMySQLGenerator provides MySQL-specific SQL generation:

function InternalGenerateDropIndexSQL(Metadata: TInstantIndexMetadata): string;
// Returns: ALTER TABLE TableName DROP INDEX IndexName

TInstantFireDACSQLiteBroker

SQLite broker with ALTER TABLE emulation.

Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → REAL
dtCurrency  → DECIMAL(18,4)
dtBoolean   → INTEGER
dtString    → VARCHAR(n)
dtMemo      → TEXT
dtDateTime  → TIMESTAMP
dtBlob      → BLOB
dtDate      → TIMESTAMP
dtTime      → TIMESTAMP
dtEnum      → INTEGER

Features:

• ALTER TABLE emulation (SQLite doesn't support ALTER TABLE natively)
• Multi-statement approach:
  1. Create temporary table
  2. Copy data
  3. Drop original table
  4. Recreate table with new structure
  5. Copy data back
  6. Drop temporary table

TInstantDBBuildSQLiteAlterTableSQLCommand

Special command for ALTER TABLE emulation:

function GetSQLStatementCount: Integer; override;
// Returns: 6 (multi-step process)

Generated SQL sequence:

-- 1. Create temporary table
CREATE TABLE MyTable_IOTmp_ AS SELECT * FROM MyTable;

-- 2. Drop original table
DROP TABLE MyTable;

-- 3. Create new table with modified structure
CREATE TABLE MyTable (...);

-- 4. Copy data back
INSERT INTO MyTable SELECT * FROM MyTable_IOTmp_;

-- 5. Drop temporary table
DROP TABLE MyTable_IOTmp_;

TInstantFireDACResolver

Resolver with boolean field interpretation.

Inheritance:

TInstantCustomResolver → TInstantSQLResolver → TInstantFireDACResolver

Methods

ReadBooleanField

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

Reads boolean value from dataset, interpreting INTEGER as boolean if needed:

• TBooleanField → Read as boolean
• Other fields → AsInteger <> 0

Used by databases that don't support native boolean:

• Oracle (NUMBER(1,0))
• Firebird (SMALLINT)
• MySQL (TINYINT(1))

TInstantFireDACTranslator

IQL to SQL translator with boolean conversion.

Inheritance:

TInstantRelationalTranslator → TInstantFireDACTranslator

Methods

TranslateConstant

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

Translates IQL constants to SQL:

• TRUE → '1'
• FALSE → '0'
• [identifier] → identifier (removes brackets for special names)

TInstantFireDACQuery

Query class using FireDAC translator.

Inheritance:

TInstantQuery → TInstantSQLQuery → TInstantFireDACQuery

Helper Functions

AssignFireDACDriverIds

procedure AssignFireDACDriverIds(Strings: TStrings);

Populates string list with supported driver IDs based on conditional compilation:

• S_FD_MSSQLId ('MSSQL')
• S_FD_FBId ('FB')
• S_FD_IBId ('IB')
• S_FD_OraId ('Ora')
• S_FD_PGId ('PG')
• S_FD_MySQLId ('MySQL')
• S_FD_SQLiteId ('SQLite')
• S_FD_ASAId ('ASA')

AssignFireDACIsolation

procedure AssignFireDACIsolation(Strings: TStrings);

Populates string list with transaction isolation levels:

• xiUnspecified
• xiDirtyRead
• xiReadCommitted
• xiRepeatableRead
• xiSerializable
• xiSnapshot

Usage Patterns

Basic Setup - SQL Server

Design-time configuration:

// 1. Drop TFDConnection on form
FDConnection1.DriverName := 'MSSQL';
FDConnection1.Params.Values['Server'] := 'localhost, 1433';
FDConnection1.Params.Values['Database'] := 'MyDatabase';
FDConnection1.Params.Values['OSAuthent'] := 'Yes';

// 2. Drop TInstantFireDACConnector on form
InstantFireDACConnector1.Connection := FDConnection1;
InstantFireDACConnector1.Connected := True;

Basic Setup - Firebird

// 1. Configure FireDAC connection
FDConnection1.DriverName := 'FB';
FDConnection1.Params.Values['Server'] := 'localhost';
FDConnection1.Params.Values['Database'] := 'C:\Data\MyDatabase.fdb';
FDConnection1.Params.Values['User_Name'] := 'SYSDBA';
FDConnection1.Params.Values['Password'] := 'masterkey';
FDConnection1.Params.Values['CharacterSet'] := 'utf8';

// 2. Configure connector
InstantFireDACConnector1.Connection := FDConnection1;
InstantFireDACConnector1.Connected := True;

Programmatic Connection

uses
  InstantFireDAC, FireDAC.Comp.Client;

procedure ConnectToSQLServer(const Server, Database: string);
var
  Connection: TFDConnection;
  Connector: TInstantFireDACConnector;
begin
  Connection := TFDConnection.Create(nil);
  Connection.DriverName := 'MSSQL';
  Connection.Params.Values['Server'] := Server;
  Connection.Params.Values['Database'] := Database;
  Connection.Params.Values['OSAuthent'] := 'Yes';
  Connection.Params.Values['ODBCAdvanced'] := 'TrustServerCertificate=yes';

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

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

Creating Firebird Database

procedure CreateFirebirdDatabase(const DatabasePath: string);
var
  Connection: TFDConnection;
  Connector: TInstantFireDACConnector;
begin
  Connection := TFDConnection.Create(nil);
  try
    Connection.DriverName := 'FB';
    Connection.Params.Values['Server'] := 'localhost';
    Connection.Params.Values['Database'] := DatabasePath;
    Connection.Params.Values['User_Name'] := 'SYSDBA';
    Connection.Params.Values['Password'] := 'masterkey';
    Connection.Params.Values['CharacterSet'] := 'utf8';
    Connection.Params.Values['PageSize'] := '8192';

    Connector := TInstantFireDACConnector.Create(nil);
    try
      Connector.Connection := Connection;
      Connector.CreateDatabase;  // Creates .fdb file
      ShowMessage('Database created: ' + DatabasePath);
    finally
      Connector.Free;
    end;
  finally
    Connection.Free;
  end;
end;

Using Connection Definition

procedure ConnectViaConnectionDef;
var
  ConnDef: TInstantFireDACConnectionDef;
  Connector: TInstantFireDACConnector;
begin
  ConnDef := TInstantFireDACConnectionDef.Create(nil);
  try
    ConnDef.DriverId := 'MSSQL';
    ConnDef.Server := 'localhost, 1433';
    ConnDef.Database := 'MyDatabase';
    ConnDef.OSAuthent := True;
    ConnDef.UseDelimitedIdents := False;

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

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

Oracle with Custom Data Type Mapping

procedure ConnectToOracle;
var
  Connection: TFDConnection;
  Connector: TInstantFireDACConnector;
begin
  Connection := TFDConnection.Create(nil);
  Connection.DriverName := 'Ora';
  Connection.Params.Values['Server'] := 'localhost:1521/ORCL';
  Connection.Params.Values['User_Name'] := 'system';
  Connection.Params.Values['Password'] := 'password';

  // Custom Oracle mapping rules applied automatically
  // NUMBER(1,0) → Boolean
  // NUMBER(5,0) → Int32
  // NUMBER(10,0) → Int32
  // NUMBER(18,4) → Currency

  Connector := TInstantFireDACConnector.Create(nil);
  Connector.Connection := Connection;
  Connector.Connected := True;
end;

Using Delimited Identifiers

// Enable delimited identifiers for reserved words
Connector.UseDelimitedIdents := True;

// Now tables/fields with reserved words are quoted:
// SQL Server: [Order], [User]
// MySQL: `Order`, `User`
// PostgreSQL, Oracle: "Order", "User"

Transaction Isolation Levels

procedure UseSnapshotIsolation;
var
  ConnDef: TInstantFireDACConnectionDef;
begin
  ConnDef := TInstantFireDACConnectionDef.Create(nil);
  ConnDef.DriverId := 'MSSQL';
  ConnDef.Isolation := xiSnapshot;  // Snapshot isolation
  ConnDef.Server := 'localhost';
  ConnDef.Database := 'MyDatabase';

  // Connection will use snapshot isolation for transactions
end;

Custom Login Handler

type
  TMainForm = class(TForm)
  private
    procedure FireDACLogin(AConnection: TFDCustomConnection;
      AParams: TFDConnectionDefParams);
  end;

procedure TMainForm.FireDACLogin(AConnection: TFDCustomConnection;
  AParams: TFDConnectionDefParams);
var
  Username, Password: string;
begin
  if PromptForCredentials(Username, Password) then
  begin
    AParams.UserName := Username;
    AParams.Password := Password;
  end
  else
    Abort;
end;

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

Encrypted Passwords

uses
  FireDAC.Comp.UI;

procedure UseEncryptedPassword;
var
  ConnDef: TInstantFireDACConnectionDef;
  PlainPassword, EncryptedPassword: string;
begin
  PlainPassword := 'mypassword';

  // Encrypt password
  EncryptedPassword := TFDManager.EncodePassword(PlainPassword);

  ConnDef := TInstantFireDACConnectionDef.Create(nil);
  ConnDef.EncryptedPassword := EncryptedPassword;  // Use encrypted
  // Don't set Password property
end;

Performance Optimization

procedure OptimizeFireDACConnection(Connection: TFDConnection);
begin
  // Speed-up reading (applied automatically by InstantFireDAC)
  Connection.ResourceOptions.DirectExecute := True;

  // Exclude use of macros for SQL statements
  Connection.ResourceOptions.MacroCreate := False;
  Connection.ResourceOptions.MacroExpand := False;

  // Fetch all records at once
  Connection.FetchOptions.Mode := fmAll;
end;

Building Database

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

  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;
    Builder.TargetModel := Model;
    Builder.BuildCommandSequence;
    Builder.CommandSequence.Execute;
    ShowMessage('Database created successfully');
  finally
    Builder.Free;
  end;
end;

Evolving Database Schema

procedure EvolveDatabase(Connector: TInstantFireDACConnector;
  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;

Connection String Format

SQL Server

DriverID=MSSQL
Server=localhost, 1433
Database=MyDatabase
OSAuthent=Yes
ODBCAdvanced=TrustServerCertificate=yes

Or with username/password:

DriverID=MSSQL
Server=localhost
User_Name=sa
Password=mypassword
Database=MyDatabase

Firebird

DriverID=FB
Server=localhost
Database=C:\Data\MyDatabase.fdb
User_Name=SYSDBA
Password=masterkey
CharacterSet=utf8
PageSize=8192

Oracle

DriverID=Ora
Server=localhost:1521/ORCL
User_Name=system
Password=password

PostgreSQL

DriverID=PG
Server=localhost
Port=5432
Database=mydb
User_Name=postgres
Password=password

MySQL

DriverID=MySQL
Server=localhost
Port=3306
Database=mydb
User_Name=root
Password=password
CharacterSet=utf8

SQLite

DriverID=SQLite
Database=C:\Data\MyDatabase.db

Best Practices

1. Use Encrypted Passwords

// Always use EncryptedPassword instead of Password
ConnDef.EncryptedPassword := TFDManager.EncodePassword('mypassword');

2. Use OS Authentication When Possible (SQL Server)

ConnDef.OSAuthent := True;
// No need to store credentials

3. Enable Connection Pooling

Connection.Params.Values['Pooled'] := 'True';
Connection.Params.Values['POOL_MaximumItems'] := '50';

4. Use Transactions for Data Integrity

Connector.StartTransaction;
try
  // Perform multiple operations
  Obj1.Store;
  Obj2.Store;
  Obj3.Delete;

  Connector.CommitTransaction;
except
  Connector.RollbackTransaction;
  raise;
end;

5. Use Delimited Identifiers for Reserved Words

Connector.UseDelimitedIdents := True;
// Prevents errors with tables named "User", "Order", etc.

6. Set Appropriate Isolation Level

// For read-heavy applications
ConnDef.Isolation := xiReadCommitted;

// For applications requiring snapshot isolation
ConnDef.Isolation := xiSnapshot;

7. Test Database Creation Before Deployment (Firebird)

procedure TestFirebirdCreation;
var
  TestPath: string;
begin
  TestPath := ExtractFilePath(ParamStr(0)) + 'test.fdb';
  try
    CreateFirebirdDatabase(TestPath);
    DeleteFile(TestPath);  // Clean up
    ShowMessage('Database creation test successful');
  except
    on E: Exception do
      ShowMessage('Database creation failed: ' + E.Message);
  end;
end;

8. Verify Driver Installation

procedure CheckDriverInstalled(const DriverID: string);
var
  Manager: TFDManager;
begin
  Manager := TFDManager;
  if not Manager.IsConnectionDefStored(DriverID) then
    raise Exception.CreateFmt('FireDAC driver "%s" not installed', [DriverID]);
end;

9. Use Connection Definition Files

// Save connection definition
ConnDef.SaveToFile('MyApp.fireDAC');

// Load connection definition
ConnDef.LoadFromFile('MyApp.fireDAC');

10. Monitor Connection State

if not Connector.Connected then
begin
  Connector.Connect;
  // Handle reconnection logic
end;

Troubleshooting

"Protocol not supported"

Problem: Exception when creating broker.

Cause: Driver not compiled in or driver name mismatch.

Solution:

// Verify driver ID matches compiled support
{$DEFINE MSSQL_SUPPORT}  // In InstantFireDAC.pas

// Check driver name
ShowMessage(Connection.DriverName);  // Must match expected value

Boolean Values Not Working

Problem: Boolean fields not reading/writing correctly.

Diagnosis:

// Check if database supports native boolean
ShowMessage(BoolToStr(Broker.UseBooleanFields, True));

Solution:

• SQL Server, PostgreSQL: Use native BOOLEAN/BIT
• Oracle, Firebird, MySQL: Use INTEGER (0/1)

Database Creation Fails (Firebird)

Problem: InternalCreateDatabase raises exception.

Solutions:

// Check page size
Connection.Params.Values['PageSize'] := '8192';  // Valid: 4096, 8192, 16384

// Check character set
Connection.Params.Values['CharacterSet'] := 'utf8';

// Check file path
Database := 'C:\Data\MyDatabase.fdb';  // Must be writable

Connection Fails with "Trust Server Certificate"

Problem: SQL Server connection fails with SSL error.

Solution:

Connection.Params.Values['ODBCAdvanced'] := 'TrustServerCertificate=yes';

Parameter Type Mismatch

Problem: Exception when executing parameterized query.

Cause: AssignParam doesn't handle specific data type correctly.

Solution:

// Ensure parameter data type matches database column type
Param.DataType := ftInteger;  // Not ftString for numeric column

Delimited Identifiers Not Working

Problem: Reserved words still cause errors.

Solution:

// Enable at connector level
Connector.UseDelimitedIdents := True;

// Verify delimiters
ShowMessage(Broker.GetSQLDelimiters);  // Should show [], "", or ``

Transaction Isolation Issues

Problem: Deadlocks or dirty reads.

Solution:

// Set appropriate isolation level
ConnDef.Isolation := xiReadCommitted;  // For most cases
// OR
ConnDef.Isolation := xiSnapshot;  // For SQL Server snapshot isolation

See Also

• InstantBrokers - Broker architecture
• InstantPersistence - Persistence infrastructure
• InstantDBBuild - Database building
• InstantDBEvolution - Schema evolution
• InstantCommand - IQL query language

Source Code

File: InstantFireDAC.pasLocation: Source/Brokers/FireDAC/

Summary

The InstantFireDAC broker is the recommended broker for new InstantObjects applications. It provides:

Advantages:

• Multi-database support (MSSQL, Firebird, Oracle, PostgreSQL, MySQL, SQLite)
• Modern FireDAC architecture with high performance
• Full transaction support with configurable isolation
• Automatic database creation (Firebird/Interbase)
• 64-bit and Linux support
• Connection pooling and optimization

Key Features:

• Database-specific brokers created automatically based on DriverName
• Boolean field handling for databases without native boolean type
• Delimited identifier support for reserved words
• Encrypted password support
• Custom login handlers
• OS authentication (SQL Server)

Best for: Any new InstantObjects application requiring enterprise database support. Use: SQL Server, Firebird, or Oracle for production applications.