Databases

A single Kitto^x application can work on multiple databases, of different kinds. The Databases node in Config.yaml contains one or more subnodes, each containing the connection params to a database. The subnode called Main is the one used by default for database access. Give a different name to each subnode as in the following example:

Databases:
  Main: ADO
    Connection:
      .............................
  OTHER: ADO
    Connection:
    ..............................

Adapters

An adapter is a wrapper that allows Kitto^x to connect to a particular database (or a family of databases) through a particular data access library. Currently Kitto^x has three adapters:

• DBX to use Delphi's DBExpress data access layer.
• ADO to use Microsoft's ADO.
• FD to use Delphi's FireDac data access layer.

More adapters can be developed and plugged in as needed.

The adapter is specified by name as the value of each database's subnode.

Database types

Each adapter detects the type of database it is using and uses this information to affect the behaviour of Kitto^x, for example to generate slightly different SQL statements depending on the particular dialect the database understands.

Connection subnode

You specify each database's settings inside a Connection subnode whose structure depends on the adapter. For example, the ADO adapter accepts a single ConnectionString subnode with all params in the typical ADO format:

Example:

Databases:
  Main: ADO
    Connection:
      ConnectionString: >
        Provider=SQLOLEDB.1;User ID=sa;Password=xxx;
        Initial Catalog=MyDatabase;Data Source=%COMPUTERNAME%

DBX allows for specifying params as direct subnodes:

Databases:
  Main: DBX
    Connection:
      DriverName: DevartSQLServer
      HostName: %COMPUTERNAME%
      DataBase: MyDatabase
      User_Name: sa
      Password: xxx
      GetDriverFunc: getSQLDriverSQLServer
      LibraryName: dbexpsda40.dll
      VendorLib: sqloledb.dll

FD allows for specifying params as direct subnodes, specific to DriverID:

Databases:
  FDMSSQL: FD
    Connection:
      DriverID: MSSQL
      Server: %COMPUTERNAME%\MSSQL2K8R2
      ApplicationName: %APPTITLE%
      Database: HelloKitto
      OSAuthent: Yes
      Isolation: RepeatableRead
    
  FDFb: FD
    Connection:
      DriverID: FB
      # Alias of database defined in databases.conf (Firebird 3.0+)
      # or Aliases.conf (Firebird 2.5)
      Database: HELLOKITTO
      User_Name: SYSDBA
      Password: masterkey
      Server: localhost
      CharacterSet: UTF8
      Protocol: TCPIP

  FDPG: FD
    Connection:
      DriverID: PG
      Server: localhost
      Port: 5432
      Database: hellokitto
      User_Name: kittox_admin
      Password: ********
      CharacterSet: UTF8
      # Schema sets the PostgreSQL search_path so unqualified table names
      # resolve to this schema. Defaults to 'public' if omitted.
      Schema: hellokitto
      ApplicationName: %APPTITLE%
      POOL_MaximumItems: 50

PostgreSQL Schema and search_path

PostgreSQL groups database objects into schemas (the equivalent of SQL Server schemas, not databases). When the SQL refers to an unqualified table (for example KITTO_USERS rather than taskitto.KITTO_USERS), PostgreSQL resolves the name through the session-level search_path.

The Schema parameter in the Connection subnode is honored by the FD adapter in two ways:

1. It is passed to FireDAC as MetaDefSchema, which scopes metadata queries (e.g. TFDMetaInfoQuery) to that schema.
2. After every physical connection is opened, the framework executes SET search_path TO {Schema}, public on that connection. This makes all unqualified names resolve to your schema first, with public as fallback. It happens via TEFDBFDConnection.AfterConnectionOpen, which the FireDAC pool calls for every physical connection it creates — so the search_path is guaranteed on every session, including pooled ones.

If you need a different setup (different role, different search_path order, custom session GUCs), you can set them at the database level once with:

ALTER ROLE kittox_admin SET search_path TO myschema, public;

or use the more general AfterOpenCommandText hook below.

FireDAC driver registration

When using the FD (FireDAC) adapter, the physical driver unit for the target database must be compiled into your application. There are two approaches:

Approach 1: Explicit uses in UseKitto.pas (recommended for clarity)

Add the FireDAC driver units directly in your UseKitto.pas:

uses
  Kitto.Html.All,
  EF.DB.FD,
  // FireDAC drivers — include only the ones you need
  FireDAC.Phys.MSSQL, FireDAC.Phys.MSSQLMeta,   // SQL Server
  FireDAC.Phys.IBBase, FireDAC.Phys.FB,           // Firebird
  // FireDAC.Phys.PG, FireDAC.Phys.PGWrapper,     // PostgreSQL
  // FireDAC.Phys.MySQL, FireDAC.Phys.MySQLWrapper,// MySQL
  // FireDAC.Phys.Oracle, FireDAC.Phys.OracleMeta, // Oracle
  // FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteWrapper,// SQLite
  Kitto.Auth.DB;

This is the approach used by the HelloKitto and TasKitto examples.

Approach 2: Conditional defines in the project (used by KEmployee)

The EF.DB.FD unit includes driver units conditionally via {$IFDEF} directives. Add the appropriate define to your project options (Project → Options → Delphi Compiler → Conditional defines):

Define	Enables
IBFB_SUPPORT	Firebird and InterBase (FireDAC.Phys.FB, FireDAC.Phys.IB)
MSSQL_SUPPORT	SQL Server (FireDAC.Phys.MSSQL)
ORACLE_SUPPORT	Oracle (FireDAC.Phys.Oracle)
PGSQL_SUPPORT	PostgreSQL (FireDAC.Phys.PG)
MYSQL_SUPPORT	MySQL (FireDAC.Phys.MySQL)
SQLITE_SUPPORT	SQLite (FireDAC.Phys.SQLite)

SQL Server ODBC driver and query isolation

When using the FD (FireDAC) adapter with DriverID: MSSQL, KittoX has to deal with a regression that affects recent Microsoft ODBC drivers and breaks server-side rule cascades that iterate a result set while triggering nested queries.

The regression

FireDAC normally picks the newest installed SQL Server ODBC driver. On a recent Delphi install that is Microsoft ODBC Driver 17 for SQL Server or Microsoft ODBC Driver 18 for SQL Server. The legacy SQL Server Native Client 11.0 (deprecated by Microsoft, no longer shipped with new SQL Server installs) is the driver KittoX has historically run on.

Even with MARS=Yes (Multiple Active Result Sets) enabled, the ODBC Driver 17/18 does not isolate sibling TFDQuery statements that share a TFDConnection. When one TFDQuery is open and a second one is opened on the same connection — for example because an AfterFieldChange rule on the parent record opens its own query while the framework is loading derived reference fields — the underlying ODBC statement gets rebound and the first query's Fields list is silently invalidated. The first query's FieldByName then fails with EDatabaseError 'Field "X" not found' even though the SELECT explicitly listed the column. The Native Client 11.0 does not have this regression.

This affects code patterns that are common in ported Kitto1 applications: a rule opens a TFDQuery, iterates fields, and at every assignment to another record field (which fires FieldChanged and triggers nested cascades / nested rules) it then keeps reading from its own dataset.

The fix — per-query TFDConnection

Every TEFDBQuery acquired via TKConfig.Database.CreateDBQuery runs on its own private TFDConnection, acquired from a dedicated FireDAC pooled connection definition (TFDManager.AddConnectionDef is registered automatically at connection open). Because the FireDAC pool reuses physical connections across acquisitions, the cost is negligible.

This isolates each query at the TFDConnection level, so MARS effectiveness is no longer required. The same code now works on Native Client 11, ODBC Driver 17, and ODBC Driver 18.

The fix preserves transactional semantics: when the parent wrapper's TFDConnection is currently inside a transaction (InTransaction = True), the query reuses the shared connection so it stays inside the transaction.

The fix is unconditional — no YAML configuration knobs to set, FireDAC keeps auto-detecting the ODBC driver as before.

Firebird version compatibility

The FD adapter targeting Firebird (DriverID: FB) is compatible with all Firebird versions from 2.5 up to the latest 5.x. The KEmployee example is verified against Firebird 5.0 64-bit on the standard EMPLOYEE.FDB sample database shipped with the Firebird installer.

Notes for Firebird 3.0+ servers:

• Wire encryption — Firebird 3.0+ enables wire encryption by default. FireDAC's native client (fbclient.dll) handles it transparently; no additional configuration is required on the KittoX side.
• Authentication plugins — Firebird 3.0+ uses Srp / Srp256 by default. The default SYSDBA account works out of the box. Legacy applications that still rely on Legacy_Auth need AuthServer = Srp, Srp256, Legacy_Auth and UserManager = Srp, Legacy_UserManager in firebird.conf.
• Database aliases — Aliases moved from Aliases.conf (2.5) to databases.conf (3.0+). Existing connection strings keep working as long as the alias is declared in whichever file the running server uses.
• 64-bit deployment — Match the Firebird server bitness with the application: Firebird 5.0 64-bit requires Win64 builds of the KittoX app. All KittoX example projects (KEmployee.dproj, KEmployeeISAPI.dproj, KEmployeeDesktop.dproj) default to Win64; the Apache module (mod_kemployee.dproj) stays Win32 for compatibility with 32-bit Apache builds.

All projects must have the same defines

When you have multiple deployment projects for the same application (Standalone, Desktop, ISAPI, Apache), make sure all .dproj files include the same conditional defines. Otherwise the driver will be registered in one project but not in another, causing "Object factory missing" errors at runtime.

AfterOpenCommandText

If specified, the value of Connection/!AfterOpenCommandText is executed as a SQL command each time a connection is made. Useful if special connection setup is needed. The command text may contain macros.

Accessing the database at runtime

Kitto^x exposes two entry points for obtaining a database connection from Delphi code. They differ in ownership and lifetime.

TKConfig.Database — cached per-request (recommended for rules and handlers)

Returns the per-thread cached connection for the Main database. The connection is created lazily on first access during a web request and released automatically by the engine at the end of the request (TKWebEngine.SimpleHandleRequest calls TKConfig.ClearDatabase in its finally block).

// In a Rule, a custom handler, or any per-request code:
var
  LQuery: TEFDBQuery;
begin
  LQuery := TKConfig.Database.CreateDBQuery;
  try
    LQuery.CommandText := 'SELECT COUNT(*) FROM CUSTOMERS WHERE ACTIVE = 1';
    LQuery.Open;
    Result := LQuery.DataSet.Fields[0].AsInteger;
  finally
    FreeAndNil(LQuery);
  end;
end;

Do NOT FreeAndNil the connection

Inside a single request, multiple callers share the same connection reference returned by TKConfig.Database. Calling FreeAndNil on it would leave a dangling pointer in the per-thread cache that the next caller inside the same request would dereference. Free only the query or command you created, not the connection itself.

One useful side effect of the cache: a sequence like TKConfig.Database.StartTransaction; ... TKConfig.Database.ExecuteImmediate(...); ... TKConfig.Database.CommitTransaction; now runs on a single connection, so the transaction semantics hold as expected.

TKConfig.Instance.CreateDBConnection(ADatabaseName) — owned by the caller

Use this factory when you need a dedicated connection (different database, background thread outside the request lifecycle, long-lived object that survives multiple requests). The caller owns the returned connection and must release it with FreeAndNil.

var
  LDBConnection: TEFDBConnection;
  LQuery: TEFDBQuery;
begin
  LDBConnection := TKConfig.Instance.CreateDBConnection('Logging');
  try
    LQuery := LDBConnection.CreateDBQuery;
    try
      LQuery.CommandText := 'INSERT INTO AUDIT_LOG (MSG) VALUES (:MSG)';
      LQuery.Params.ParamByName('MSG').AsString := AMessage;
      LQuery.Execute;
    finally
      FreeAndNil(LQuery);
    end;
  finally
    FreeAndNil(LDBConnection);
  end;
end;

The framework itself uses CreateDBConnection internally in units like Kitto.Auth.DB, Kitto.AccessControl.DB, Kitto.Metadata.DataView, and Kitto.Html.Form — each one creates a connection, uses it in a local try/finally block, and frees it before returning. Application code should follow the same pattern whenever it needs a dedicated connection.

TKConfig.Instance.InDBConnection / InDBTransaction — RAII helpers

For ad-hoc blocks, the InDBConnection and InDBTransaction helpers take an anonymous procedure and handle creation, transaction scope, and cleanup automatically:

TKConfig.Instance.InDBTransaction('Main',
  procedure (ADBConnection: TEFDBConnection)
  begin
    ADBConnection.ExecuteImmediate('UPDATE ...');
    ADBConnection.ExecuteImmediate('INSERT ...');
    // Commit on success, Rollback on exception — handled internally
  end);