Database Programming with FireDAC

39.1 FireDAC Overview

FireDAC: Delphi's universal, high-performance data access library. Drivers: SQLite, InterBase, Firebird, MySQL/MariaDB, PostgreSQL, Microsoft SQL Server, Oracle, IBM DB2, Informix, SAP SQL Anywhere, Microsoft Access, ODBC, MongoDB (Enterprise), DataSnap, Teradata, and more.

Features: parameterized queries, macros, array DML (batch), cached updates, local SQL, the in-memory TFDMemTable, connection pooling, async execution, monitoring/tracing, schema metadata, and data type mapping.

39.2 Core Components

SQL queries (SELECT/DML) Commands without a result set Driver link (e.g., TFDPhysSQLiteDriverLink) Wait cursor (VCL/FMX/Console)

ETL: move data between datasets, text files, and databases Connection definitions and pooling SQL across in-memory datasets Database event notifications

39.3 Connecting and Querying

Connection strings for other databases:

SQL Server: DriverName=MSSQL; Server=localhost; Database=Shop; OSAuthent=Yes

PostgreSQL: DriverName=PG; Server=...; User_Name=...; Password=...

MySQL: DriverName=MySQL (needs libmysql.dll) Firebird: DriverName=FB

Never concatenate user input into SQL. Use parameters (:Name) or the ExecSQL overloads with an array of values.

uses FireDAC.Comp.Client, FireDAC.Stan.Def, FireDAC.Stan.Async, FireDAC.DApt,
     FireDAC.Phys.SQLite, FireDAC.Stan.Param, Data.DB;

var Conn := TFDConnection.Create(nil);
try
  Conn.DriverName := 'SQLite';
  Conn.Params.Database := TPath.Combine(TPath.GetDocumentsPath, 'shop.db');
  Conn.Params.Add('LockingMode=Normal');
  Conn.LoginPrompt := False;
  Conn.Connected := True;

  Conn.ExecSQL('CREATE TABLE IF NOT EXISTS Products (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Price REAL)');
  Conn.ExecSQL('INSERT INTO Products (Name, Price) VALUES (:Name, :Price)', ['Laptop', 1200.0]);   // parameterized

  var Q := TFDQuery.Create(nil);
  try
    Q.Connection := Conn;
    Q.SQL.Text := 'SELECT Id, Name, Price FROM Products WHERE Price > :MinPrice ORDER BY Name';
    Q.ParamByName('MinPrice').AsFloat := 100;
    Q.Open;
    while not Q.Eof do
    begin
      Writeln(Format('%d: %s - %.2f', [Q.FieldByName('Id').AsInteger, Q.FieldByName('Name').AsString, Q.FieldByName('Price').AsFloat]));
      Q.Next;
    end;
    Writeln('Count: ', Conn.ExecSQLScalar('SELECT COUNT(*) FROM Products'));
  finally
    Q.Free;
  end;
finally
  Conn.Free;
end;

39.4 Transactions, Stored Procedures and Batch Operations

Conn.StartTransaction;
try
  Conn.ExecSQL('UPDATE Accounts SET Balance = Balance - :Amt WHERE Id = :Id', [500, 1]);
  Conn.ExecSQL('UPDATE Accounts SET Balance = Balance + :Amt WHERE Id = :Id', [500, 2]);
  Conn.Commit;
except
  Conn.Rollback;
  raise;
end;

var SP := TFDStoredProc.Create(nil);
SP.Connection := Conn;
SP.StoredProcName := 'usp_GetCustomerOrders';
SP.Prepare;
SP.ParamByName('@CustomerId').AsInteger := 5;
SP.Open;

// Array DML: insert 1000 rows in one round trip
Q.SQL.Text := 'INSERT INTO Logs (Msg) VALUES (:Msg)';
Q.Params.ArraySize := 1000;
for var I := 0 to 999 do Q.ParamByName('Msg').AsStrings[I] := 'Entry ' + I.ToString;
Q.Execute(1000, 0);

39.5 Cached Updates, Async and Pooling

Cached updates: CachedUpdates := True, edit offline, then ApplyUpdates/CommitUpdates (master-detail with TFDSchemaAdapter).

Async execution: ResourceOptions.CmdExecMode := amAsync with AfterOpen events, or run queries inside TTask with separate connections per thread.

Pooling: TFDManager with Pooled=True connection definitions. Each thread acquires its own connection.

Monitoring: TFDMoniFlatFileClientLink, FireDAC Monitor (FDMonitor).