InstantBrokers

Unit: InstantBrokersCategory: Core

Overview

The InstantBrokers unit is the heart of InstantObjects' persistence architecture. It defines the abstract broker and connector infrastructure that enables object persistence across different database backends. The broker acts as the intermediary between your business objects and the physical storage layer.

Key Concepts:

Brokers - Handle the low-level persistence operations (store, retrieve, dispose)
Connectors - Manage connections to databases and coordinate brokers
Resolvers - Map object attributes to database fields/records
Generators - Generate database-specific SQL statements
Translators - Convert IQL (InstantObjects Query Language) to native SQL
Statement Cache - Caches prepared statements for performance

Architecture Types:

InstantObjects supports two fundamental broker architectures:

Navigational Brokers - Use dataset-based navigation (e.g., BDE, ADO, IBX)
SQL Brokers - Use parameterized SQL commands (e.g., FireDAC with optimized SQL)

Class Hierarchy

TInstantBroker (abstract)
└── TInstantCustomRelationalBroker (abstract)
    ├── TInstantNavigationalBroker (abstract)
    │   └── TInstantIBXBroker, TInstantADOBroker, etc.
    └── TInstantSQLBroker (abstract)
        └── TInstantFireDACBroker, TInstantDBXBroker, etc.

TInstantConnector (abstract)
└── TInstantRelationalConnector
    ├── TInstantConnectionBasedConnector
    │   ├── TInstantFireDACConnector
    │   ├── TInstantIBXConnector
    │   └── TInstantADOConnector
    └── TInstantJSONConnector
    └── TInstantXMLConnector

TInstantCustomRelationalBroker

Abstract base class for all relational brokers (both navigational and SQL-based).

Inheritance: TInstantBroker → TInstantCustomRelationalBroker

Key Properties

Property	Type	Description
`Connector`	TInstantRelationalConnector	Associated connector
`DBMSName`	string	Database management system name
`SQLDelimiters`	string	SQL identifier delimiters (e.g., "[]" for SQL Server)
`SQLQuote`	Char	SQL string quote character (usually `'`)
`SQLWildcard`	string	SQL wildcard character (usually `%`)
`StatementCacheCapacity`	Integer	Maximum cached statements (default: 20)
`AutoGenerateStdFields`	Boolean	Use Class, Id, UpdateCount fields for PK/refs (default: True)

Key Methods

pascal

constructor Create(AConnector: TInstantConnector);
destructor Destroy;

// Execute raw SQL statement
function Execute(const AStatement: string;
  AParams: TParams = nil;
  OnAssignParamValue: TAssignParamValue = nil): Integer;

Usage Pattern

pascal

// Base broker provides SQL execution
var
  RowsAffected: Integer;
  Params: TParams;
begin
  Params := TParams.Create(nil);
  try
    Params.CreateParam(ftString, 'Name', ptInput).AsString := 'John';

    RowsAffected := Broker.Execute(
      'UPDATE Contacts SET Status = 1 WHERE Name = :Name',
      Params
    );

    ShowMessage(Format('%d rows updated', [RowsAffected]));
  finally
    Params.Free;
  end;
end;

TInstantNavigationalBroker

Abstract broker for navigational (dataset-based) persistence.

Inheritance: TInstantCustomRelationalBroker → TInstantNavigationalBroker

Backward Compatibility: TInstantRelationalBroker = TInstantNavigationalBroker

Architecture

Navigational brokers work by:

Opening a TDataSet for each table
Navigating to records (using Locate, Seek, etc.)
Reading/writing fields directly through the dataset
Posting changes to the database

Key Components

Resolvers - One TInstantNavigationalResolver per table
Link Resolvers - Handle external storage for Parts/References

Protected Methods

pascal

// Create resolver for a table (must override)
function CreateResolver(const TableName: string): TInstantNavigationalResolver; virtual; abstract;

// Find existing resolver
function FindResolver(const TableName: string): TInstantNavigationalResolver;

// Ensure resolver exists
function EnsureResolver(Map: TInstantAttributeMap): TInstantCustomResolver; override;

Example Implementation

pascal

// From TInstantIBXBroker
function TInstantIBXBroker.CreateResolver(const TableName: string): TInstantNavigationalResolver;
begin
  Result := TInstantIBXResolver.Create(Self, TableName);
end;

When to Use

✅ Good for:

Legacy database drivers (BDE, older ADO)
Simple applications with few concurrent users
When you need dataset-based UI binding

❌ Avoid for:

High-performance requirements
Large-scale concurrent access
Modern applications (use SQL brokers instead)

TInstantSQLBroker

Abstract broker for SQL-based persistence with parameterized queries.

Inheritance: TInstantCustomRelationalBroker → TInstantSQLBroker

Architecture

SQL brokers work by:

Generating parameterized SQL statements
Executing SQL directly without opening datasets
Using result sets only for retrieval
Optimized for performance and concurrency

Key Properties

Property	Type	Description
`Generator`	TInstantSQLGenerator	SQL statement generator
`ResolverCount`	Integer	Number of cached resolvers
`Resolvers[Index]`	TInstantSQLResolver	Accessor for resolvers

Key Methods

pascal

// Create resolver for attribute map (must override)
function CreateResolver(Map: TInstantAttributeMap): TInstantSQLResolver; virtual; abstract;

// Convert data type to column type (must override)
function DataTypeToColumnType(DataType: TInstantDataType; Size: Integer): string; virtual; abstract;

// SQL generator class (can override for custom SQL)
class function GeneratorClass: TInstantSQLGeneratorClass; virtual;

// Dataset management
function AcquireDataSet(const AStatement: string; AParams: TParams = nil): TDataSet;
procedure ReleaseDataSet(const ADataSet: TDataSet);

// Find existing resolver
function FindResolver(AMap: TInstantAttributeMap): TInstantSQLResolver;

// Parameter string for logging
function GetParamsStr(AParams: TParams): string;

Example Implementation

pascal

// From TInstantFireDACBroker
function TInstantFireDACBroker.CreateResolver(Map: TInstantAttributeMap): TInstantSQLResolver;
begin
  Result := TInstantFireDACResolver.Create(Self, Map);
end;

function TInstantFireDACBroker.DataTypeToColumnType(DataType: TInstantDataType; Size: Integer): string;
begin
  case DataType of
    dtString: Result := Format('VARCHAR(%d)', [Size]);
    dtInteger: Result := 'INTEGER';
    dtFloat: Result := 'DOUBLE PRECISION';
    dtCurrency: Result := 'DECIMAL(18, 4)';
    dtBoolean: Result := 'BOOLEAN';
    dtDateTime: Result := 'TIMESTAMP';
    dtBlob: Result := 'BLOB';
    dtMemo: Result := 'BLOB SUB_TYPE TEXT';
  else
    Result := 'VARCHAR(255)';
  end;
end;

class function TInstantFireDACBroker.GeneratorClass: TInstantSQLGeneratorClass;
begin
  Result := TInstantFireDACGenerator;
end;

When to Use

✅ Recommended for:

All new projects (especially with FireDAC)
High-performance requirements
Multi-user applications
Modern database backends
Production systems

TInstantRelationalConnector

Base connector class for all relational connectors.

Inheritance: TInstantConnector → TInstantRelationalConnector

Key Properties

Property	Type	Description
`Broker`	TInstantCustomRelationalBroker	Associated broker instance
`DBMSName`	string	Database system name
`SQLEngine`	TInstantSQLEngine	SQL dialect (seGenericSQL, seInterbase, etc.)
`ReadObjectListWithNoLock`	Boolean	Use NOLOCK hint when reading (SQL Server)

Events

Event	Type	Description
`OnGetDataSet`	TInstantGetDataSetEvent	Custom dataset creation
`OnInitDataSet`	TInstantInitDataSetEvent	Initialize created dataset

Protected Methods

pascal

// Get dataset for SQL (can override)
procedure GetDataSet(const CommandText: string; var DataSet: TDataSet); virtual;

// Initialize dataset (can override)
procedure InitDataSet(const CommandText: string; DataSet: TDataSet); virtual;

// Create schema from model
function InternalCreateScheme(Model: TInstantModel): TInstantScheme; override;

Example: Custom Dataset Provider

pascal

procedure TMainForm.CustomGetDataSet(Sender: TObject;
  const CommandText: string; var DataSet: TDataSet);
begin
  // Provide custom dataset (e.g., from a pool)
  DataSet := DataSetPool.Acquire;
  TCustomQuery(DataSet).SQL.Text := CommandText;
end;

procedure TMainForm.CustomInitDataSet(Sender: TObject;
  const CommandText: string; DataSet: TDataSet);
begin
  // Initialize dataset (e.g., set connection)
  TCustomQuery(DataSet).Connection := SharedConnection;
  TCustomQuery(DataSet).Open;
end;

// Hook up events
FireDACConnector.OnGetDataSet := CustomGetDataSet;
FireDACConnector.OnInitDataSet := CustomInitDataSet;

TInstantConnectionBasedConnector

Connector that wraps a TCustomConnection descendant.

Inheritance: TInstantRelationalConnector → TInstantConnectionBasedConnector

Key Properties

Property	Type	Description
`Connection`	TCustomConnection	Wrapped database connection
`LoginPrompt`	Boolean	Prompt for credentials (default: True)

Key Methods

pascal

constructor Create(AOwner: TComponent);

// Check if connection is assigned
function HasConnection: Boolean;

Protected Methods

pascal

// Called before changing Connection property
procedure BeforeConnectionChange; virtual;

// Called after changing Connection property
procedure AfterConnectionChange; virtual;

// Assign login options from connector to connection
procedure AssignLoginOptions; virtual;

// Verify connection is assigned
procedure CheckConnection;

// Connection state management
function GetConnected: Boolean; override;
procedure InternalConnect; override;
procedure InternalDisconnect; override;

Example: FireDAC Connector

pascal

var
  FDConnection: TFDConnection;
  Connector: TInstantFireDACConnector;
begin
  // Create connection
  FDConnection := TFDConnection.Create(Self);
  FDConnection.DriverName := 'FB';
  FDConnection.Params.Database := 'C:\Data\MyApp.fdb';
  FDConnection.LoginPrompt := False;

  // Create connector
  Connector := TInstantFireDACConnector.Create(Self);
  Connector.Connection := FDConnection;  // Wrap connection
  Connector.IsDefault := True;

  // Connection is managed through connector
  Connector.Connected := True;  // Opens FDConnection automatically
end;

TInstantCustomResolver

Base class for all resolvers that map objects to storage.

Inheritance: TInstantStreamable → TInstantCustomResolver

Responsibilities

Map object attributes to database fields/records
Read attributes from storage
Write attributes to storage
Handle conflicts and concurrency

Key Methods

pascal

constructor Create(ABroker: TInstantCustomRelationalBroker);

// High-level operations
procedure DisposeMap(AObject: TInstantObject; Map: TInstantAttributeMap;
  ConflictAction: TInstantConflictAction; Info: PInstantOperationInfo);

procedure RetrieveMap(AObject: TInstantObject; const AObjectId: string;
  Map: TInstantAttributeMap; ConflictAction: TInstantConflictAction;
  Info: PInstantOperationInfo; const AObjectData: TInstantAbstractObjectData = nil);

procedure StoreMap(AObject: TInstantObject; Map: TInstantAttributeMap;
  ConflictAction: TInstantConflictAction; Info: PInstantOperationInfo);

// Object-level operations
procedure DisposeObject(AObject: TInstantObject; Conflict: TInstantConflictAction);
procedure StoreObject(AObject: TInstantObject; Conflict: TInstantConflictAction);

Protected Methods

pascal

// Override in descendants to implement persistence
procedure InternalDisposeMap(AObject: TInstantObject;
  Map: TInstantAttributeMap; ConflictAction: TInstantConflictAction;
  Info: PInstantOperationInfo); virtual;

procedure InternalRetrieveMap(AObject: TInstantObject;
  const AObjectId: string; Map: TInstantAttributeMap;
  ConflictAction: TInstantConflictAction; AInfo: PInstantOperationInfo;
  const AObjectData: TInstantAbstractObjectData = nil); virtual;

procedure InternalStoreMap(AObject: TInstantObject;
  Map: TInstantAttributeMap; ConflictAction: TInstantConflictAction;
  Info: PInstantOperationInfo); virtual;

// Key violation handling
function KeyViolation(AObject: TInstantObject; const AObjectId: string;
  E: Exception): EInstantKeyViolation;

TInstantNavigationalResolver

Resolver for navigational brokers using datasets.

Inheritance: TInstantCustomResolver → TInstantNavigationalResolver

Backward Compatibility: TInstantResolver = TInstantNavigationalResolver

Key Properties

Property	Type	Description
`Broker`	TInstantNavigationalBroker	Associated broker
`DataSet`	TDataSet	Dataset for the table
`ObjectClassName`	string	Current object's class name
`ObjectId`	string	Current object's ID
`TableName`	string	Physical table name

Key Methods

pascal

constructor Create(ABroker: TInstantNavigationalBroker; const ATableName: string);
destructor Destroy;

Protected Methods

pascal

// Create dataset for table (must override)
function CreateDataSet: TDataSet; virtual; abstract;

// Locate object in dataset (must override)
function Locate(const AObjectClassName, AObjectId: string): Boolean; virtual; abstract;

// Create link resolver for external storage (must override)
function CreateNavigationalLinkResolver(const ATableName: string): TInstantNavigationalLinkResolver; virtual; abstract;

// Dataset operations
procedure Open; virtual;
procedure Close; virtual;
procedure Append; virtual;
procedure Edit; virtual;
procedure Post; virtual;
procedure Delete; virtual;
procedure Cancel; virtual;

// Attribute reading/writing (can override)
procedure ReadString(Attribute: TInstantString); virtual;
procedure WriteString(Attribute: TInstantString); virtual;
procedure ReadInteger(Attribute: TInstantInteger); virtual;
procedure WriteInteger(Attribute: TInstantInteger); virtual;
// ... (similar for all attribute types)

procedure ReadReference(Attribute: TInstantReference); virtual;
procedure WriteReference(Attribute: TInstantReference); virtual;
procedure ReadParts(Attribute: TInstantParts); virtual;
procedure WriteParts(Attribute: TInstantParts); virtual;

// Clearing attributes
procedure ClearString(Attribute: TInstantString); virtual;
procedure ClearInteger(Attribute: TInstantInteger); virtual;
// ... (similar for all attribute types)

Example Implementation

pascal

// From TInstantIBXResolver
function TInstantIBXResolver.CreateDataSet: TDataSet;
var
  Query: TIBQuery;
begin
  Query := TIBQuery.Create(nil);
  Query.Database := Broker.Connection.Database;
  Query.Transaction := Broker.Connection.Transaction;
  Query.SQL.Text := Format('SELECT * FROM %s', [TableName]);
  Result := Query;
end;

function TInstantIBXResolver.Locate(const AObjectClassName, AObjectId: string): Boolean;
begin
  Result := DataSet.Locate('Class;Id', VarArrayOf([AObjectClassName, AObjectId]), []);
end;

procedure TInstantIBXResolver.ReadString(Attribute: TInstantString);
var
  Field: TField;
begin
  Field := DataSet.FieldByName(Attribute.Metadata.FieldName);
  if Field.IsNull then
    Attribute.Reset
  else
    Attribute.Value := Field.AsString;
end;

procedure TInstantIBXResolver.WriteString(Attribute: TInstantString);
var
  Field: TField;
begin
  Field := DataSet.FieldByName(Attribute.Metadata.FieldName);
  if Attribute.IsNull then
    Field.Clear
  else
    Field.AsString := Attribute.Value;
end;

TInstantSQLResolver

Resolver for SQL brokers using parameterized queries.

Inheritance: TInstantCustomResolver → TInstantSQLResolver

Key Properties

Property	Type	Description
`Broker`	TInstantSQLBroker	Associated SQL broker
`Map`	TInstantAttributeMap	Attribute-to-field mapping
`SelectSQL`	string	Generated SELECT statement
`InsertSQL`	string	Generated INSERT statement
`UpdateSQL`	string	Generated UPDATE statement
`DeleteSQL`	string	Generated DELETE statement
`UpdateConcurrentSQL`	string	UPDATE with concurrency check
`DeleteConcurrentSQL`	string	DELETE with concurrency check
`SelectExternalSQL`	string	SELECT for external parts
`SelectExternalPartSQL`	string	SELECT for specific external part
`InsertExternalSQL`	string	INSERT for external parts
`DeleteExternalSQL`	string	DELETE for external parts
`SelectVirtualSQL`	string	SELECT for virtual attributes
`SelectPrimaryKeysSQL`	string	SELECT just primary keys

Key Methods

pascal

constructor Create(ABroker: TInstantSQLBroker; AMap: TInstantAttributeMap);

SQL Generation

SQL statements are automatically generated by TInstantSQLGenerator based on the attribute map. The resolver caches these statements for reuse.

Example Generated SQL:

sql

-- SelectSQL for TContact
SELECT Class, Id, UpdateCount, Name, Email, CategoryClass, CategoryId
FROM Contacts
WHERE Class = :IO_Class AND Id = :IO_Id

-- InsertSQL for TContact
INSERT INTO Contacts (Class, Id, UpdateCount, Name, Email, CategoryClass, CategoryId)
VALUES (:IO_Class, :IO_Id, :IO_UpdateCount, :Name, :Email, :CategoryClass, :CategoryId)

-- UpdateConcurrentSQL for TContact (with optimistic locking)
UPDATE Contacts
SET UpdateCount = :IO_UpdateCount, Name = :Name, Email = :Email,
    CategoryClass = :CategoryClass, CategoryId = :CategoryId
WHERE Class = :IO_Class AND Id = :IO_Id AND UpdateCount = :IO_Concur

-- SelectExternalSQL for Parts collection
SELECT PartClass, PartId, Seq
FROM ContactPhones
WHERE Class = :IO_Class AND Id = :IO_Id
ORDER BY Seq

Protected Methods

pascal

// Add parameters for attributes
procedure AddAttributeParam(Attribute: TInstantAttribute; Params: TParams); virtual;
procedure AddAttributeParams(Params: TParams; AObject: TInstantObject; Map: TInstantAttributeMap);

// Add base parameters (Class, Id, UpdateCount)
procedure AddBaseParams(Map: TInstantAttributeMap; Params: TParams;
  AClassName, AObjectId: string; AUpdateCount: Integer = -1);

// Add concurrency parameter
procedure AddConcurrencyParam(Params: TParams; AUpdateCount: Integer);

// Read attributes from dataset
procedure ReadAttributes(AObject: TInstantObject; const AObjectId: string;
  Map: TInstantAttributeMap; DataSet: TDataSet);

procedure ReadAttribute(AObject: TInstantObject; const AObjectId: string;
  AttributeMetadata: TInstantAttributeMetadata; DataSet: TDataSet); virtual;

// Read field helpers
function ReadStringField(DataSet: TDataSet; const FieldName: string; out AWasNull: boolean): string; virtual;
function ReadIntegerField(DataSet: TDataSet; const FieldName: string; out AWasNull: boolean): Integer; virtual;
function ReadBooleanField(DataSet: TDataSet; const FieldName: string; out AWasNull: boolean): Boolean; virtual;
function ReadDateTimeField(DataSet: TDataSet; const FieldName: string; out AWasNull: boolean): TDateTime; virtual;
function ReadFloatField(DataSet: TDataSet; const FieldName: string; out AWasNull: boolean): Double; virtual;
// ... (similar for other types)

// Execute statement with conflict handling
function ExecuteStatement(const AStatement: string; AParams: TParams;
  Info: PInstantOperationInfo; ConflictAction: TInstantConflictAction;
  AObject: TInstantObject): Integer;

// Error translation
function TranslateError(AObject: TInstantObject; E: Exception): Exception; virtual;

Concurrency Control

SQL resolvers implement optimistic concurrency control using the UpdateCount field:

pascal

// When storing, check UpdateCount hasn't changed
UPDATE Contacts
SET ...fields..., UpdateCount = UpdateCount + 1
WHERE Class = :Class AND Id = :Id AND UpdateCount = :OldUpdateCount

// If no rows affected, another user modified the object
if RowsAffected = 0 then
  raise EInstantConflict.Create('Object was modified by another user');

TInstantSQLGenerator

Generates database-specific SQL statements.

Inheritance: TObject → TInstantSQLGenerator

Key Methods

pascal

constructor Create(ABroker: TInstantSQLBroker); virtual;

// DDL generation
function GenerateCreateTableSQL(Metadata: TInstantTableMetadata): string;
function GenerateDropTableSQL(Metadata: TInstantTableMetadata): string;
function GenerateAddFieldSQL(Metadata: TInstantFieldMetadata): string;
function GenerateDropFieldSQL(Metadata: TInstantFieldMetadata): string;
function GenerateAlterFieldSQL(OldMetadata, NewMetadata: TInstantFieldMetadata): string;
function GenerateCreateIndexSQL(Metadata: TInstantIndexMetadata): string;
function GenerateDropIndexSQL(Metadata: TInstantIndexMetadata): string;

// DML generation
function GenerateSelectSQL(Map: TInstantAttributeMap): string;
function GenerateInsertSQL(Map: TInstantAttributeMap): string;
function GenerateUpdateSQL(Map: TInstantAttributeMap): string;
function GenerateDeleteSQL(Map: TInstantAttributeMap): string;
function GenerateUpdateConcurrentSQL(Map: TInstantAttributeMap): string;
function GenerateDeleteConcurrentSQL(Map: TInstantAttributeMap): string;

// External storage SQL
function GenerateSelectExternalSQL(Map: TInstantAttributeMap): string;
function GenerateSelectExternalPartSQL(Map: TInstantAttributeMap): string;
function GenerateInsertExternalSQL(Map: TInstantAttributeMap): string;
function GenerateDeleteExternalSQL(Map: TInstantAttributeMap): string;

// Utility SQL
function GenerateSelectTablesSQL: string;
function GenerateSelectPrimaryKeysSQL(Map: TInstantAttributeMap): string;
function GenerateSelectVirtualSQL(Map: TInstantAttributeMap): string;

Protected Methods

pascal

// Override these to customize SQL generation
function InternalGenerateCreateTableSQL(Metadata: TInstantTableMetadata): string; virtual;
function InternalGenerateInsertSQL(Map: TInstantAttributeMap): string; virtual;
function InternalGenerateSelectSQL(Map: TInstantAttributeMap): string; virtual;
function InternalGenerateUpdateSQL(Map: TInstantAttributeMap): string; virtual;
function InternalGenerateDeleteSQL(Map: TInstantAttributeMap): string; virtual;
// ... (similar for all SQL types)

// Helper methods
function BuildFieldList(Map: TInstantAttributeMap; Additional: array of string): string;
function BuildParamList(Map: TInstantAttributeMap; Additional: array of string): string;
function BuildAssignmentList(Map: TInstantAttributeMap; Additional: array of string): string;
function BuildWhereStrByKeyFields(Map: TInstantAttributeMap): string;
function EmbraceTable(const TableName: string): string; virtual;
function EmbraceField(const FieldName: string): string; virtual;
function BuildParam(const AName: string): string; virtual;
function GetDelimiters: string; virtual;

Example: Custom Generator

pascal

// Custom generator for PostgreSQL
type
  TInstantPostgreSQLGenerator = class(TInstantSQLGenerator)
  protected
    function EmbraceField(const FieldName: string): string; override;
    function EmbraceTable(const TableName: string): string; override;
    function InternalGenerateSelectSQL(Map: TInstantAttributeMap): string; override;
  end;

function TInstantPostgreSQLGenerator.EmbraceField(const FieldName: string): string;
begin
  Result := '"' + FieldName + '"';  // PostgreSQL uses double quotes
end;

function TInstantPostgreSQLGenerator.EmbraceTable(const TableName: string): string;
begin
  Result := '"' + LowerCase(TableName) + '"';  // PostgreSQL is case-sensitive
end;

function TInstantPostgreSQLGenerator.InternalGenerateSelectSQL(Map: TInstantAttributeMap): string;
begin
  Result := inherited InternalGenerateSelectSQL(Map);
  // Add PostgreSQL-specific hints or features
  if Broker.Connector.ReadObjectListWithNoLock then
    Result := Result + ' FOR SHARE';  // PostgreSQL equivalent of NOLOCK
end;

TInstantRelationalTranslator

Translates IQL (InstantObjects Query Language) to native SQL.

Inheritance: TInstantQueryTranslator → TInstantRelationalTranslator

Key Properties

Property	Type	Description
`Context`	TInstantTranslationContext	Translation context with metadata
`Query`	TInstantCustomRelationalQuery	Associated query
`Connector`	TInstantRelationalConnector	Database connector
`Delimiters`	string	SQL identifier delimiters
`Quote`	Char	SQL string quote character
`Wildcard`	string	SQL wildcard (for LIKE)

Translation Process

Parse IQL statement into object tree
Create translation context with metadata
Build table paths and joins
Translate IQL objects to SQL fragments
Generate complete SQL statement

Protected Methods

pascal

// Main translation method (override to customize)
function TranslateObject(AObject: TInstantIQLObject; Writer: TInstantIQLWriter): Boolean; override;

// Translate specific IQL constructs
function TranslateClassRef(ClassRef: TInstantIQLClassRef; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslateClause(Clause: TInstantIQLClause; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslateConstant(Constant: TInstantIQLConstant; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslateFunction(AFunction: TInstantIQLFunction; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslatePath(Path: TInstantIQLPath; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslateSpecifier(Specifier: TInstantIQLSpecifier; Writer: TInstantIQLWriter): Boolean; virtual;
function TranslateKeyword(const Keyword: string; Writer: TInstantIQLWriter): Boolean; override;

// Helpers
function ReplaceWildcard(const Str: string): string;
function IncludeOrderFields: Boolean; virtual;

Translation Example

Input IQL:

sql

SELECT * FROM TContact
WHERE Category.Name = 'Customer' AND City LIKE 'New%'
ORDER BY Name

Output SQL (Generic):

sql

SELECT T0.Class, T0.Id, T0.UpdateCount, T0.Name, T0.Email, T0.City,
       T0.CategoryClass, T0.CategoryId
FROM Contacts T0
LEFT JOIN Categories T1 ON T1.Class = T0.CategoryClass AND T1.Id = T0.CategoryId
WHERE (T1.Name = 'Customer') AND (T0.City LIKE 'New%')
ORDER BY T0.Name

Output SQL (SQL Server with NOLOCK):

sql

SELECT T0.Class, T0.Id, T0.UpdateCount, T0.Name, T0.Email, T0.City,
       T0.CategoryClass, T0.CategoryId
FROM Contacts T0 WITH (NOLOCK)
LEFT JOIN Categories T1 WITH (NOLOCK)
  ON T1.Class = T0.CategoryClass AND T1.Id = T0.CategoryId
WHERE (T1.Name = 'Customer') AND (T0.City LIKE 'New%')
ORDER BY T0.Name

TInstantStatementCache

Caches prepared SQL statements and their datasets for reuse.

Inheritance: TComponent → TInstantStatementCache

Purpose

The statement cache improves performance by:

Reusing prepared statements
Avoiding repeated SQL parsing
Maintaining open dataset connections
Reducing database round-trips

Key Properties

Property	Type	Description
`Capacity`	Integer	Maximum number of cached statements

Key Methods

pascal

constructor Create(AOwner: TComponent);
destructor Destroy;

// Get cached statement or create new
function GetStatement(const StatementText: string): TInstantStatement;

// Get statement by index
function GetStatementByIndex(const AIndex: Integer): TInstantStatement;

// Add statement to cache
function AddStatement(const StatementText: string; const StatementDataSet: TDataSet): Integer;

// Remove statement from cache
function RemoveStatement(const StatementText: string): Boolean;

// Find index of dataset
function IndexOfStatementDataSet(const StatementDataSet: TDataSet): Integer;

Example Usage

pascal

// Broker uses cache automatically
var
  Stmt: TInstantStatement;
begin
  // First call: creates and caches
  Stmt := Broker.StatementCache.GetStatement('SELECT * FROM Contacts WHERE Id = :Id');
  try
    Stmt.ParamByName('Id').AsString := 'CONT001';
    Stmt.DataSet.Open;
    // Use dataset...
  finally
    // Don't free! Cache owns it
  end;

  // Second call with same SQL: reuses cached statement
  Stmt := Broker.StatementCache.GetStatement('SELECT * FROM Contacts WHERE Id = :Id');
  // Much faster - no preparation needed
end;

Cache Management

pascal

// Set capacity (triggers automatic cleanup)
Broker.StatementCacheCapacity := 50;  // Default is 20

// Cache automatically:
// 1. Keeps most recently used statements
// 2. Discards oldest when capacity exceeded
// 3. Closes and frees dataset when discarded

TInstantBrokerCatalog / TInstantSQLBrokerCatalog

Read database metadata from an existing database.

Inheritance:

TInstantCatalog → TInstantBrokerCatalog → TInstantSQLBrokerCatalog

Purpose

Broker catalogs enable:

Reading existing database schema
Comparing model to database
Generating evolution scripts
Reverse engineering databases

Key Methods

pascal

// TInstantBrokerCatalog
constructor Create(const AScheme: TInstantScheme; const ABroker: TInstantBroker);
property Broker: TInstantBroker read GetBroker;

// TInstantSQLBrokerCatalog
class function FormatOffset(const APageCount, ARecordCount: integer): string; virtual;
property Broker: TInstantSQLBroker read GetBroker;

Usage in Database Evolution

pascal

uses
  InstantDBEvolution;

var
  Catalog: TInstantSQLBrokerCatalog;
  CurrentScheme: TInstantScheme;
  TargetScheme: TInstantScheme;
  Evolution: TInstantDBEvolution;
begin
  // Create catalog from existing database
  CurrentScheme := TInstantScheme.Create;
  Catalog := FireDACBroker.CreateCatalog(CurrentScheme);
  try
    Catalog.InitScheme;  // Read from database

    // Get target scheme from model
    TargetScheme := FireDACConnector.CreateScheme;
    try
      // Compare and generate evolution
      Evolution := TInstantDBEvolution.Create(FireDACConnector);
      try
        Evolution.CurrentScheme := CurrentScheme;
        Evolution.TargetScheme := TargetScheme;
        Evolution.Execute;  // Apply changes
      finally
        Evolution.Free;
      end;
    finally
      TargetScheme.Free;
    end;
  finally
    Catalog.Free;
    CurrentScheme.Free;
  end;
end;

Common Usage Patterns

Creating a Custom Broker

pascal

// 1. Define broker class
type
  TInstantMyBroker = class(TInstantSQLBroker)
  protected
    function CreateResolver(Map: TInstantAttributeMap): TInstantSQLResolver; override;
    function DataTypeToColumnType(DataType: TInstantDataType; Size: Integer): string; override;
    class function GeneratorClass: TInstantSQLGeneratorClass; override;
  public
    function CreateCatalog(const AScheme: TInstantScheme): TInstantSQLBrokerCatalog; override;
  end;

// 2. Define resolver class
type
  TInstantMyResolver = class(TInstantSQLResolver)
  protected
    // Override if needed for special handling
    procedure ReadAttribute(AObject: TInstantObject; const AObjectId: string;
      AttributeMetadata: TInstantAttributeMetadata; DataSet: TDataSet); override;
  end;

// 3. Define generator class
type
  TInstantMyGenerator = class(TInstantSQLGenerator)
  protected
    function EmbraceField(const FieldName: string): string; override;
    function EmbraceTable(const TableName: string): string; override;
    function InternalGenerateCreateTableSQL(Metadata: TInstantTableMetadata): string; override;
  end;

// 4. Define connector class
type
  TInstantMyConnector = class(TInstantConnectionBasedConnector)
  public
    class function BrokerClass: TInstantBrokerClass; override;
    class function ConnectionDefClass: TInstantConnectionDefClass; override;
  published
    property Connection;  // Make visible
  end;

Optimizing with Statement Cache

pascal

// Set appropriate capacity based on your query patterns
procedure TMainForm.FormCreate(Sender: TObject);
begin
  // Default is 20 - increase for query-heavy applications
  FireDACConnector.Broker.StatementCacheCapacity := 100;

  // Monitor cache efficiency
  LogMessage(Format('Cache size: %d statements',
    [FireDACConnector.Broker.StatementCache.Capacity]));
end;

// Reuse same SQL text for cache hits
const
  SQL_GET_CONTACT = 'SELECT * FROM Contacts WHERE Id = :Id';

function TMainForm.LoadContact(const AId: string): TContact;
var
  Stmt: TInstantStatement;
begin
  // Cache hit if SQL was used before
  Stmt := FireDACConnector.Broker.StatementCache.GetStatement(SQL_GET_CONTACT);
  Stmt.ParamByName('Id').AsString := AId;
  // Execute and retrieve...
end;

Custom SQL Execution

pascal

// Execute raw SQL through broker
function DeleteOrphanedRecords(Connector: TInstantFireDACConnector): Integer;
var
  SQL: string;
begin
  SQL := 'DELETE FROM Phones WHERE ContactId NOT IN (SELECT Id FROM Contacts)';
  Result := Connector.Broker.Execute(SQL);
  ShowMessage(Format('%d orphaned records deleted', [Result]));
end;

// Execute with parameters
function UpdateContactStatus(Connector: TInstantFireDACConnector;
  const AStatus: string; const ACategoryId: string): Integer;
var
  Params: TParams;
begin
  Params := TParams.Create(nil);
  try
    Params.CreateParam(ftString, 'Status', ptInput).AsString := AStatus;
    Params.CreateParam(ftString, 'CategoryId', ptInput).AsString := ACategoryId;

    Result := Connector.Broker.Execute(
      'UPDATE Contacts SET Status = :Status WHERE CategoryId = :CategoryId',
      Params
    );
  finally
    Params.Free;
  end;
end;

Handling Concurrency Conflicts

pascal

procedure SaveContactWithRetry(Contact: TContact);
var
  MaxRetries: Integer;
  Retry: Integer;
  Saved: Boolean;
begin
  MaxRetries := 3;
  Retry := 0;
  Saved := False;

  while (not Saved) and (Retry < MaxRetries) do
  begin
    try
      Contact.Store;  // Uses UpdateConcurrentSQL
      Saved := True;
    except
      on E: EInstantConflict do
      begin
        Inc(Retry);
        if Retry >= MaxRetries then
          raise
        else
        begin
          // Refresh and retry
          Contact.Refresh;
          ShowMessage('Object was modified. Retrying...');
        end;
      end;
    end;
  end;
end;

// Or use conflict actions
Contact.Store(caIgnore);  // Overwrite without checking
Contact.Store(caRefresh);  // Refresh on conflict
Contact.Store(caFail);  // Raise exception (default)

Best Practices

Choose the Right Broker Type
- Use SQL brokers (FireDAC) for new projects
- Use navigational brokers only for legacy compatibility
Configure Statement Cache
- Set StatementCacheCapacity appropriately (20-100)
- Higher values for query-intensive applications
- Monitor memory usage with very large caches
Use AutoGenerateStdFields
- Keep AutoGenerateStdFields = True (default)
- Ensures consistent primary key and reference handling
- Simplifies database schema
Handle Concurrency
- Always use concurrent SQL (UpdateConcurrentSQL, DeleteConcurrentSQL)
- Implement retry logic for conflict resolution
- Use appropriate conflict actions
Custom SQL Execution
- Use Broker.Execute for bulk operations
- Bypass object layer for better performance
- Always use parameters to prevent SQL injection
Database-Specific Optimization
- Override GeneratorClass for custom SQL
- Use database-specific features (NOLOCK, hints, etc.)
- Implement custom translators for IQL optimization
Error Handling
- Catch EInstantConflict for concurrency errors
- Catch EInstantKeyViolation for uniqueness errors
- Use TranslateError to convert database-specific exceptions

Performance Considerations

Statement Caching

Impact: 20-50% improvement for repeated queries
Trade-off: Memory usage vs. preparation time
Recommendation: Cache capacity = (number of unique queries) × 1.5

Navigational vs SQL Brokers

Navigational:
- Slower due to dataset overhead
- More memory per operation
- Limited concurrency
SQL:
- Faster parameterized execution
- Minimal memory footprint
- Better concurrency support

Batch Operations

pascal

// Bad: One-by-one through objects
for Contact in ContactList do
  Contact.Status := 'Active';
  Contact.Store;  // N database round-trips
end;

// Good: Single SQL statement
Connector.Broker.Execute(
  'UPDATE Contacts SET Status = ''Active'' WHERE CategoryId = :Id',
  Params
);  // 1 database round-trip

Troubleshooting

Problem: "Statement cache growing too large"

Solution: Reduce StatementCacheCapacity or use parameterized queries consistently

Problem: "Concurrency conflicts on every save"

Solution: Check that UpdateCount is being properly incremented; verify database triggers aren't interfering

Problem: "Poor query performance"

Solution:

Switch from navigational to SQL broker
Increase statement cache capacity
Add database indexes
Use ReadObjectListWithNoLock for read-only queries

Problem: "Custom SQL not working"

Cause: Broker doesn't support direct SQL execution Solution: Use SQL broker, or execute through connection directly

Problem: "Translation errors in IQL"

Solution:

Check for unsupported IQL constructs
Implement custom translator for database-specific SQL
Use native SQL as workaround

Version History

Version 3.0 - Introduced SQL broker architecture
Version 3.5 - Added statement caching
Version 4.0 - Improved concurrency control
Version 4.2 - 64-bit support
Version 4.3 - FireDAC broker (recommended for new projects)

InstantBrokers ​

Overview ​

Class Hierarchy ​

TInstantCustomRelationalBroker ​

Key Properties ​

Key Methods ​

Usage Pattern ​

TInstantNavigationalBroker ​

Architecture ​

Key Components ​

Protected Methods ​

Example Implementation ​

When to Use ​

TInstantSQLBroker ​

Architecture ​

Key Properties ​

Key Methods ​

Example Implementation ​

When to Use ​

TInstantRelationalConnector ​

Key Properties ​

Events ​

Protected Methods ​

Example: Custom Dataset Provider ​

TInstantConnectionBasedConnector ​

Key Properties ​

Key Methods ​

Protected Methods ​

Example: FireDAC Connector ​

TInstantCustomResolver ​

Responsibilities ​

Key Methods ​

Protected Methods ​

TInstantNavigationalResolver ​

Key Properties ​

Key Methods ​

Protected Methods ​

Example Implementation ​

TInstantSQLResolver ​

Key Properties ​

Key Methods ​

SQL Generation ​

Protected Methods ​

Concurrency Control ​

TInstantSQLGenerator ​

Key Methods ​

Protected Methods ​

Example: Custom Generator ​

TInstantRelationalTranslator ​

Key Properties ​

Translation Process ​

Protected Methods ​

Translation Example ​

TInstantStatementCache ​

Purpose ​

Key Properties ​

Key Methods ​

Example Usage ​

Cache Management ​

TInstantBrokerCatalog / TInstantSQLBrokerCatalog ​

Purpose ​

Key Methods ​

Usage in Database Evolution ​

Common Usage Patterns ​

Creating a Custom Broker ​

Optimizing with Statement Cache ​

Custom SQL Execution ​

Handling Concurrency Conflicts ​

Best Practices ​

Performance Considerations ​

Statement Caching ​

Navigational vs SQL Brokers ​

Batch Operations ​

Troubleshooting ​

Problem: "Statement cache growing too large" ​

Problem: "Concurrency conflicts on every save" ​

Problem: "Poor query performance" ​

Problem: "Custom SQL not working" ​

Problem: "Translation errors in IQL" ​

See Also ​

InstantBrokers

Overview

Class Hierarchy

TInstantCustomRelationalBroker

Key Properties

Key Methods

Usage Pattern

TInstantNavigationalBroker

Architecture

Key Components

Protected Methods

Example Implementation

When to Use

TInstantSQLBroker

Architecture

Key Properties

Key Methods

Example Implementation

When to Use

TInstantRelationalConnector

Key Properties

Events

Protected Methods

Example: Custom Dataset Provider

TInstantConnectionBasedConnector

Key Properties

Key Methods

Protected Methods

Example: FireDAC Connector

TInstantCustomResolver

Responsibilities

Key Methods

Protected Methods

TInstantNavigationalResolver

Key Properties

Key Methods

Protected Methods

Example Implementation

TInstantSQLResolver

Key Properties

Key Methods

SQL Generation

Protected Methods

Concurrency Control

TInstantSQLGenerator

Key Methods

Protected Methods

Example: Custom Generator

TInstantRelationalTranslator

Key Properties

Translation Process

Protected Methods

Translation Example

TInstantStatementCache

Purpose

Key Properties

Key Methods

Example Usage

Cache Management

TInstantBrokerCatalog / TInstantSQLBrokerCatalog

Purpose

Key Methods

Usage in Database Evolution

Common Usage Patterns

Creating a Custom Broker

Optimizing with Statement Cache

Custom SQL Execution

Handling Concurrency Conflicts

Best Practices

Performance Considerations

Statement Caching

Navigational vs SQL Brokers

Batch Operations

Troubleshooting

Problem: "Statement cache growing too large"

Problem: "Concurrency conflicts on every save"

Problem: "Poor query performance"

Problem: "Custom SQL not working"

Problem: "Translation errors in IQL"

See Also