### TZUpdateSQL: Custom DML Resolver Example

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Demonstrates how to use TZUpdateSQL to define explicit INSERT, UPDATE, DELETE, and REFRESH SQL statements for TZQuery when posting cached edits. Ensure all necessary ZEOS units are included.

```pascal
uses ZConnection, ZDataset, ZSqlUpdate;

var
  Conn      : TZConnection;
  Qry       : TZQuery;
  UpdSQL    : TZUpdateSQL;
begin
  Conn   := TZConnection.Create(nil);
  Qry    := TZQuery.Create(nil);
  UpdSQL := TZUpdateSQL.Create(nil);
  try
    Conn.Protocol := 'firebird';
    Conn.Database := 'localhost:C:\Data\hr.fdb';
    Conn.User := 'SYSDBA'; Conn.Password := 'masterkey';
    Conn.Connect;

    Qry.Connection := Conn;
    Qry.SQL.Text   := 'SELECT id, name, salary FROM employees';

    UpdSQL.InsertSQL.Text := 'INSERT INTO employees (id, name, salary) VALUES (:id, :name, :salary)';
    UpdSQL.ModifySQL.Text := 'UPDATE employees SET name=:name, salary=:salary WHERE id=:OLD_id';
    UpdSQL.DeleteSQL.Text := 'DELETE FROM employees WHERE id=:OLD_id';
    UpdSQL.RefreshSQL.Text:= 'SELECT id, name, salary FROM employees WHERE id=:OLD_id';

    Qry.UpdateObject := UpdSQL;
    Qry.Open;

    // Insert a new row
    Qry.Insert;
    Qry.FieldByName('id').AsInteger     := 999;
    Qry.FieldByName('name').AsString    := 'Alice';
    Qry.FieldByName('salary').AsFloat   := 65000;
    Qry.Post;

    // Commit all pending changes
    Qry.ApplyUpdates;
    Qry.CommitUpdates;
  finally
    UpdSQL.Free;
    Qry.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### TZStoredProc: Executing Stored Procedures Example

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Shows how to execute database stored procedures or functions using TZStoredProc. Input, output, and input/output parameters are managed via the Params property. Multiple result sets can be handled using NextResultSet.

```pascal
uses ZConnection, ZStoredProcedure, DB, SysUtils;

var
  Conn : TZConnection;
  Proc : TZStoredProc;
begin
  Conn := TZConnection.Create(nil);
  Proc := TZStoredProc.Create(nil);
  try
    Conn.Protocol := 'oracle';
    Conn.HostName := 'oraserver';
    Conn.Database := 'ORCL';
    Conn.User     := 'scott';
    Conn.Password := 'tiger';
    Conn.Connect;

    Proc.Connection     := Conn;
    Proc.StoredProcName := 'GET_EMPLOYEE';
    Proc.Prepare;

    // Set IN parameters
    Proc.Params.ParamByName('P_EMP_ID').AsInteger := 7839;
    Proc.ExecProc;

    // Read OUT parameters
    Writeln('Name: ',   Proc.Params.ParamByName('P_NAME').AsString);
    Writeln('Salary: ', Proc.Params.ParamByName('P_SALARY').AsFloat:0:2);

    // Alternatively, open as a result-set returning procedure
    Proc.Open;
    while not Proc.EOF do begin
      Writeln(Proc.FieldByName('ENAME').AsString);
      Proc.Next;
    end;
    Proc.Close;
  finally
    Proc.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### Connect to MySQL Database with TZConnection

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Configure and establish a connection to a MySQL database using TZConnection. Sets protocol, host, port, database, user, and password. Demonstrates setting driver-specific properties, auto-commit, transaction isolation level, and retrieving server/client versions. Includes examples for other database protocols like PostgreSQL, SQLite, Firebird, and SQL Server.

```pascal
uses ZConnection, ZDataset, SysUtils;

var
  Conn: TZConnection;
begin
  Conn := TZConnection.Create(nil);
  try
    // --- MySQL ---
    Conn.Protocol := 'mysql';
    Conn.HostName := 'localhost';
    Conn.Port     := 3306;
    Conn.Database := 'mydb';
    Conn.User     := 'root';
    Conn.Password := 'secret';
    // Optional driver-specific properties
    Conn.Properties.Values['compress']   := 'yes';
    Conn.Properties.Values['timeout']    := '30';
    Conn.AutoCommit := True;
    Conn.TransactIsolationLevel := tiReadCommitted;
    Conn.Connect;
    Writeln('Server version: ', Conn.ServerVersionStr);
    Writeln('Client version: ', Conn.ClientVersionStr);

    // --- PostgreSQL ---
    // Conn.Protocol := 'postgresql';
    // Conn.Port     := 5432;

    // --- SQLite (no host/port) ---
    // Conn.Protocol := 'sqlite-3';
    // Conn.Database := '/path/to/mydb.sqlite3';

    // --- Firebird ---
    // Conn.Protocol := 'firebird';
    // Conn.Database := 'localhost:C:\Data\mydb.fdb';

    // --- SQL Server (via FreeTDS dblib) ---
    // Conn.Protocol := 'mssql';
    // Conn.Port     := 1433;

    // List available protocols at runtime
    var Protos := TStringList.Create;
    try
      Conn.GetProtocolNames(Protos);
      Writeln(Protos.Text);
    finally
      Protos.Free;
    end;

    // Execute DDL directly
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS log (id INT, msg TEXT)');

    Conn.Disconnect;
  finally
    Conn.Free;
  end;
end;
```

--------------------------------

### BLOB Handling with TDataset and DBC Layer

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Illustrates reading and writing Binary Large Objects (BLOBs) using ZeosLib. This example shows how to use `TDataset.CreateBlobStream` for component-layer usage and `IZResultSet.GetBlob` at the DBC layer.

```Pascal
uses ZConnection, ZDataset, DB, SysUtils, Classes;

var
  Conn      : TZConnection;
  Qry       : TZQuery;
  BlobStream: TStream;
  FileStream: TFileStream;
begin
  Conn := TZConnection.Create(nil);
  Qry  := TZQuery.Create(nil);
  try
    Conn.Protocol := 'postgresql';
    Conn.HostName := 'localhost';
    Conn.Database := 'media_db';
    Conn.User     := 'admin'; Conn.Password := 'pass';
    // For PostgreSQL OID blobs:
    Conn.Properties.Values['OidAsBlob'] := 'true';
    Conn.Connect;

    Qry.Connection := Conn;
    Qry.SQL.Text   := 'SELECT id, image_data, description FROM photos';
    Qry.Open;

    // --- Write BLOB from file ---
    if not Qry.EOF then begin
      Qry.Edit;
      BlobStream := Qry.CreateBlobStream(Qry.FieldByName('image_data'), bmWrite);
      FileStream := TFileStream.Create('photo.bmp', fmOpenRead);
      try
        BlobStream.CopyFrom(FileStream, FileStream.Size);
      finally
        FileStream.Free;
        BlobStream.Free;
      end;
      Qry.FieldByName('description').AsString := 'Uploaded photo';
      Qry.Post;
    end;

    // --- Read BLOB to file ---
    Qry.First;
    if not Qry.EOF then begin
      BlobStream := Qry.CreateBlobStream(Qry.FieldByName('image_data'), bmRead);
      FileStream := TFileStream.Create('output.bmp', fmCreate);
      try
        FileStream.CopyFrom(BlobStream, BlobStream.Size);
      finally
        FileStream.Free;
        BlobStream.Free;
      end;
    end;
  finally
    Qry.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### TZTransaction: Explicit Transaction Management Example

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Illustrates explicit transaction control using TZTransaction, allowing grouped commits and rollbacks across multiple TZQuery datasets. Ensure AutoCommit is set to False on both the connection and transaction objects.

```pascal
uses ZConnection, ZDataset, ZTransaction;

var
  Conn : TZConnection;
  Txn  : TZTransaction;
  Q1   : TZQuery;
begin
  Conn := TZConnection.Create(nil);
  Txn  := TZTransaction.Create(nil);
  Q1   := TZQuery.Create(nil);
  try
    Conn.Protocol := 'postgresql';
    Conn.HostName := 'localhost';
    Conn.Database := 'finance';
    Conn.User     := 'admin';
    Conn.Password := 'admin';
    Conn.AutoCommit := False;
    Conn.Connect;

    Txn.Connection              := Conn;
    Txn.TransactIsolationLevel  := tiSerializable;
    Txn.AutoCommit              := False;

    Q1.Connection  := Conn;
    Q1.Transaction := Txn;

    Txn.StartTransaction;
    try
      Q1.SQL.Text := 'UPDATE accounts SET balance = balance - :amount WHERE id = :src';
      Q1.Params.ParamByName('amount').AsFloat   := 500.00;
      Q1.Params.ParamByName('src').AsInteger    := 1;
      Q1.ExecSQL;

      Q1.SQL.Text := 'UPDATE accounts SET balance = balance + :amount WHERE id = :dst';
      Q1.Params.ParamByName('amount').AsFloat   := 500.00;
      Q1.Params.ParamByName('dst').AsInteger    := 2;
      Q1.ExecSQL;

      Txn.Commit;
      Writeln('Transfer committed.');
    except
      on E: Exception do begin
        Txn.Rollback;
        Writeln('Rolled back: ', E.Message);
      end;
    end;
  finally
    Q1.Free;
    Txn.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### TZConnection - Database Connection Component

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

The TZConnection component manages database connections. It allows configuration of connection properties, transaction control, and execution of direct SQL commands. The example demonstrates connecting to MySQL, PostgreSQL, SQLite, Firebird, and SQL Server, as well as listing available protocols and executing DDL.

```APIDOC
## TZConnection - Database Connection Component

### Description
`TZConnection` is the central component that manages a connection to a database. It exposes `Protocol`, `HostName`, `Port`, `Database`, `User`, `Password`, and `LibraryLocation` properties. Once configured it supports `Connect`/`Disconnect`, transaction control (`StartTransaction`, `Commit`, `Rollback`), and utility methods (`ExecuteDirect`, `GetProtocolNames`, `GetTableNames`, `Ping`).

### Usage Example
```pascal
uses ZConnection, ZDataset, SysUtils;

var
  Conn: TZConnection;
begin
  Conn := TZConnection.Create(nil);
  try
    // --- MySQL ---
    Conn.Protocol := 'mysql';
    Conn.HostName := 'localhost';
    Conn.Port     := 3306;
    Conn.Database := 'mydb';
    Conn.User     := 'root';
    Conn.Password := 'secret';
    // Optional driver-specific properties
    Conn.Properties.Values['compress']   := 'yes';
    Conn.Properties.Values['timeout']    := '30';
    Conn.AutoCommit := True;
    Conn.TransactIsolationLevel := tiReadCommitted;
    Conn.Connect;
    Writeln('Server version: ', Conn.ServerVersionStr);
    Writeln('Client version: ', Conn.ClientVersionStr);

    // --- PostgreSQL ---
    // Conn.Protocol := 'postgresql';
    // Conn.Port     := 5432;

    // --- SQLite (no host/port) ---
    // Conn.Protocol := 'sqlite-3';
    // Conn.Database := '/path/to/mydb.sqlite3';

    // --- Firebird ---
    // Conn.Protocol := 'firebird';
    // Conn.Database := 'localhost:C:\Data\mydb.fdb';

    // --- SQL Server (via FreeTDS dblib) ---
    // Conn.Protocol := 'mssql';
    // Conn.Port     := 1433;

    // List available protocols at runtime
    var Protos := TStringList.Create;
    try
      Conn.GetProtocolNames(Protos);
      Writeln(Protos.Text);
    finally
      Protos.Free;
    end;

    // Execute DDL directly
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS log (id INT, msg TEXT)');

    Conn.Disconnect;
  finally
    Conn.Free;
  end;
end;
```

### Properties
- **Protocol** (string) - The database protocol to use (e.g., 'mysql', 'postgresql', 'sqlite-3').
- **HostName** (string) - The hostname or IP address of the database server.
- **Port** (integer) - The port number for the database connection.
- **Database** (string) - The name of the database to connect to.
- **User** (string) - The username for authentication.
- **Password** (string) - The password for authentication.
- **LibraryLocation** (string) - The location of the native client libraries.
- **Properties** (TStrings) - A collection of driver-specific properties.
- **AutoCommit** (boolean) - Whether to automatically commit transactions.
- **TransactIsolationLevel** (TTransactionIsolationLevel) - The transaction isolation level.

### Methods
- **Connect** - Establishes a connection to the database.
- **Disconnect** - Closes the database connection.
- **StartTransaction** - Begins a new transaction.
- **Commit** - Commits the current transaction.
- **Rollback** - Rolls back the current transaction.
- **ExecuteDirect** (SQL: string) - Executes a SQL statement directly against the database.
- **GetProtocolNames** (List: TStrings) - Populates the provided TStrings with a list of available database protocols.
- **GetTableNames** - Retrieves a list of table names from the database.
- **Ping** - Checks if the database connection is active.
```

--------------------------------

### Execute Parameterized SQL Queries with TZQuery

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Demonstrates how to use TZQuery for SELECT, INSERT, UPDATE, DELETE, and batch DML operations. Requires setting up TZConnection and configuring query parameters.

```pascal
uses ZConnection, ZDataset, DB, SysUtils;

var
  Conn : TZConnection;
  Qry  : TZQuery;
begin
  Conn := TZConnection.Create(nil);
  Qry  := TZQuery.Create(nil);
  try
    Conn.Protocol := 'postgresql';
    Conn.HostName := 'localhost';
    Conn.Database := 'hr';
    Conn.User     := 'hr_user';
    Conn.Password := 'pass';
    Conn.Connect;

    Qry.Connection := Conn;

    // --- SELECT with parameters ---
    Qry.SQL.Text := 'SELECT id, name, salary FROM employees WHERE dept_id = :dept_id';
    Qry.Params.ParamByName('dept_id').AsInteger := 10;
    Qry.Open;
    while not Qry.EOF do begin
      Writeln(Qry.FieldByName('name').AsString, ' - ', Qry.FieldByName('salary').AsFloat:0:2);
      Qry.Next;
    end;
    Qry.Close;

    // --- INSERT/UPDATE/DELETE (ExecSQL) ---
    Qry.SQL.Text := 'UPDATE employees SET salary = :sal WHERE id = :id';
    Qry.Params.ParamByName('sal').AsFloat   := 75000;
    Qry.Params.ParamByName('id').AsInteger  := 42;
    Qry.ExecSQL;
    Writeln('Rows affected: ', Qry.RowsAffected);

    // --- Batch DML (10 inserts in one round-trip) ---
    Qry.SQL.Text := 'INSERT INTO log (id, msg, ts) VALUES (:id, :msg, :ts)';
    Qry.Params.BatchDMLCount := 5;
    for var i := 0 to 4 do begin
      Qry.Params[0].AsIntegers[i]   := i + 1;
      Qry.Params[1].AsStrings[i]    := 'Event ' + IntToStr(i);
      Qry.Params[2].AsDateTimes[i]  := Now;
    end;
    Qry.ExecSQL;  // sends all 5 rows in one call
    Assert(Qry.RowsAffected = 5);

    // --- Cached updates with manual edit/post ---
    Qry.SQL.Text := 'SELECT * FROM employees WHERE dept_id = :d';
    Qry.Params.ParamByName('d').AsInteger := 10;
    Qry.Open;
    if not Qry.EOF then begin
      Qry.Edit;
      Qry.FieldByName('salary').AsFloat := 80000;
      Qry.Post;
    end;
    Qry.ApplyUpdates;   // flush pending changes to DB
    Qry.CommitUpdates;
  finally
    Qry.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### Low-Level Database Connectivity with DBC Layer

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Demonstrates direct database access using the `ZDbcIntfs` interface, including connection, statement execution, prepared statements, and transaction management. Ensure the appropriate driver unit is imported to register it with `DriverManager`.

```Pascal
uses ZDbcIntfs, ZDbcMySql, ZDbcPostgreSql, ZDbcInterbase6, SysUtils;
// Note: import the driver unit(s) to register them with DriverManager

var
  Connection : IZConnection;
  Stmt       : IZStatement;
  PrepStmt   : IZPreparedStatement;
  RS         : IZResultSet;
  URL        : string;
begin
  // URL format: zdbc:<protocol>://<host>:<port>/<database>?UID=<user>;PWD=<pass>
  URL := 'zdbc:mysql://localhost:3306/mydb?UID=root;PWD=secret';
  Connection := DriverManager.GetConnectionWithParams(URL, nil);
  Connection.SetAutoCommit(False);
  Connection.SetTransactionIsolation(tiReadCommitted);
  Connection.Open;

  try
    // -- Plain statement (no params) --
    Stmt := Connection.CreateStatement;
    Stmt.SetResultSetConcurrency(rcUpdatable);
    RS   := Stmt.ExecuteQuery('SELECT id, name FROM customers');
    while RS.Next do begin
      Writeln(RS.GetInt(1), ' ', RS.GetString(2));   // 1-based column index
      // or by name: RS.GetStringByName('name')
    end;

    // -- Prepared statement with parameters --
    PrepStmt := Connection.PrepareStatement(
      'INSERT INTO orders (customer_id, total, created_at) VALUES (?, ?, ?)');
    PrepStmt.SetInt(1, 42);
    PrepStmt.SetDouble(2, 199.99);
    PrepStmt.SetTimestamp(3, Now);
    PrepStmt.ExecuteUpdatePrepared;

    // -- ResultSet update & delete --
    RS := Stmt.ExecuteQuery('SELECT id, price FROM products WHERE stock > 0');
    while RS.Next do begin
      if RS.GetDouble(2) > 1000 then begin
        RS.UpdateDouble(2, RS.GetDouble(2) * 0.9);  // 10% discount
        RS.UpdateRow;
      end;
    end;

    Connection.Commit;
  except
    Connection.Rollback;
    raise;
  end;

  if not Connection.IsClosed then
    Connection.Close;
end;
```

--------------------------------

### Executing SQL Scripts with TZSQLProcessor

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Shows how to use TZSQLProcessor to execute multi-statement SQL scripts from a file or inline. Includes configuration for statement delimiters, cleanup, and custom error handling callbacks.

```pascal
uses ZConnection, ZSqlProcessor, SysUtils;

procedure RunScript(Conn: TZConnection; const ScriptFile: string);
var
  Proc : TZSQLProcessor;
begin
  Proc := TZSQLProcessor.Create(nil);
  try
    Proc.Connection    := Conn;
    Proc.DelimiterType := dtDefault;   // ';' separates statements
    Proc.CleanupStatements := True;    // strip comments / whitespace

    Proc.OnError := procedure(Processor: TZSQLProcessor; StatIdx: Integer;
                               E: Exception; var Action: TZErrorHandleAction)
      begin
        Writeln('Error in stmt #', StatIdx, ': ', E.Message);
        Action := eaSkip;   // skip and continue; or eaFail / eaAbort / eaRetry
      end;

    Proc.AfterExecute := procedure(Processor: TZSQLProcessor; StatIdx, RowsAffected: Integer)
      begin
        Writeln('Stmt #', StatIdx, ' OK, rows affected: ', RowsAffected);
      end;

    // Load from file or assign inline
    Proc.LoadFromFile(ScriptFile);
    // Or: Proc.Script.Text := 'CREATE TABLE t1 (id INT); CREATE TABLE t2 (name TEXT);';

    Proc.Execute;
    Writeln('Script completed. Statements executed: ', Proc.StatementCount);
  finally
    Proc.Free;
  end;
end;
```

--------------------------------

### Using TZSequence for Database Sequences

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Demonstrates manual retrieval of sequence values and automatic population of primary key fields on insert when linked to a TZQuery. Ensure the TZConnection is properly configured and connected before using TZSequence.

```pascal
uses ZConnection, ZDataset, ZSequence;

var
  Conn : TZConnection;
  Seq  : TZSequence;
  Qry  : TZQuery;
begin
  Conn := TZConnection.Create(nil);
  Seq  := TZSequence.Create(nil);
  Qry  := TZQuery.Create(nil);
  try
    Conn.Protocol := 'postgresql';
    Conn.HostName := 'localhost';
    Conn.Database := 'mydb';
    Conn.User     := 'user';
    Conn.Password := 'pass';
    Conn.Connect;

    Seq.Connection   := Conn;
    Seq.SequenceName := 'employees_id_seq';
    Seq.BlockSize    := 1;  // fetch one value at a time

    // Manual usage
    var NextId := Seq.GetNextValue;
    Writeln('Next sequence value: ', NextId);
    Writeln('SQL form: ', Seq.GetNextValueSQL);  // e.g. "SELECT nextval('employees_id_seq')"

    // Auto-populate PK on insert via TZQuery
    Qry.Connection    := Conn;
    Qry.SQL.Text      := 'SELECT id, name FROM employees';
    Qry.Sequence      := Seq;
    Qry.SequenceField := 'id';   // 'id' will be filled from sequence before insert
    Qry.Open;
    Qry.Insert;
    Qry.FieldByName('name').AsString := 'Bob';
    Qry.Post; // id is assigned automatically from the sequence
  finally
    Qry.Free;
    Seq.Free;
    Conn.Free;
  end;
end;
```

--------------------------------

### Create and Populate an In-Memory Dataset with TZMemTable

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

TZMemTable provides an in-memory dataset independent of a database connection. Define the structure using FieldDefs and then use standard dataset methods for data manipulation. Ensure ZDataset and DB are in the uses clause.

```Pascal
uses ZDataset, DB, SysUtils;

var
  MemTbl : TZMemTable;
begin
  MemTbl := TZMemTable.Create(nil);
  try
    // Define structure manually
    MemTbl.FieldDefs.Add('id',   ftInteger,  0, True);
    MemTbl.FieldDefs.Add('name', ftString,  50, True);
    MemTbl.FieldDefs.Add('score',ftFloat,    0, False);
    MemTbl.CreateDataset;  // creates fields from FieldDefs and opens the table

    // Insert rows
    MemTbl.Insert;
    MemTbl.FieldByName('id').AsInteger    := 1;
    MemTbl.FieldByName('name').AsString   := 'Alice';
    MemTbl.FieldByName('score').AsFloat   := 99.5;
    MemTbl.Post;

    MemTbl.Insert;
    MemTbl.FieldByName('id').AsInteger    := 2;
    MemTbl.FieldByName('name').AsString   := 'Bob';
    MemTbl.FieldByName('score').AsFloat   := 87.0;
    MemTbl.Post;

    // Iterate
    MemTbl.First;
    while not MemTbl.EOF do begin
      Writeln(MemTbl.FieldByName('name').AsString, ': ',
              MemTbl.FieldByName('score').AsFloat:0:1);
      MemTbl.Next;
    end;
  finally
    MemTbl.Free;
  end;
end;
```

--------------------------------

### SQL Test Configuration Template

Source: https://github.com/marsupilami79/zeoslib/blob/master/documentation/development/misc/test_framework.html

This is a template for the test_config.ini file. It defines active SQL servers and their connection parameters. Copy this to test_config.ini and set your server details.

```ini
[common]
active=mysql-3.23

[mysql-3.23]
host=localhost
port=3306
database=zeoslib
user=root
password=
extra=

[postgresql-7.23]
host=localhost
port=
database=zeoslib
user=root
password=
extra=
........
```

--------------------------------

### Selecting RDB$DB_KEY in isql

Source: https://github.com/marsupilami79/zeoslib/blob/master/documentation/development/coding/RDB$DB_KEY.txt

Demonstrates how to select the RDB$DB_KEY from a table in isql. By default, db_keys are returned as hex values.

```sql
SQL> SELECT RDB$DB_KEY FROM MYTABLE;
RDB$DB_KEY
================
000000B600000002
000000B600000004
000000B600000006
000000B600000008
000000B60000000A
```

--------------------------------

### Retrieving Database Metadata with TZSQLMetadata

Source: https://context7.com/marsupilami79/zeoslib/llms.txt

Illustrates how to use TZSQLMetadata to query database catalog information such as tables, columns, and primary keys. Requires a connected TZConnection and setting the appropriate MetadataType.

```pascal
uses ZConnection, ZSqlMetadata, DB, SysUtils;

var
  Conn : TZConnection;
  Meta : TZSQLMetadata;
begin
  Conn := TZConnection.Create(nil);
  Meta := TZSQLMetadata.Create(nil);
  try
    Conn.Protocol := 'postgresql';
    Conn.HostName := 'localhost';
    Conn.Database := 'mydb';
    Conn.User     := 'admin'; Conn.Password := 'pass';
    Conn.Connect;

    Meta.Connection := Conn;

    // List all tables in schema 'public'
    Meta.MetadataType := mdTables;
    Meta.Schema       := 'public';
    Meta.Open;
    Writeln('Tables:');
    while not Meta.EOF do begin
      Writeln('  ', Meta.FieldByName('TABLE_NAME').AsString);
      Meta.Next;
    end;
    Meta.Close;

    // List columns for table 'employees'
    Meta.MetadataType := mdColumns;
    Meta.TableName    := 'employees';
    Meta.Open;
    Writeln('Columns of employees:');
    while not Meta.EOF do begin
      Writeln('  ', Meta.FieldByName('COLUMN_NAME').AsString,
              ' : ', Meta.FieldByName('TYPE_NAME').AsString);
      Meta.Next;
    end;
    Meta.Close;

    // List primary keys
    Meta.MetadataType := mdPrimaryKeys;
    Meta.TableName    := 'employees';
    Meta.Open;
    while not Meta.EOF do begin
      Writeln('PK column: ', Meta.FieldByName('COLUMN_NAME').AsString);
      Meta.Next;
    end;
    Meta.Close;
  finally
    Meta.Free;
    Conn.Free;
  end;
end;
```