Examples

Basic Query

Connect, run a SELECT, iterate the result set, and clean up.

uses EDBMMClient;
// uses UErrorCodes;  // add when checking EC_* constants (EC_HTTP_TIMEOUT, etc.)

var
  Conn  : TMMConnection;
  Script: TMMScript;
begin
  Conn := TMMConnection.Create(nil);
  try
    Conn.Database   := EDBDatabase1;
    Conn.APIURL     := 'https://myserver.com/edbapi.php';
    Conn.APIKey     := 'my-api-key';
    Conn.DBName     := 'my_db';
    Conn.DBUser     := 'myuser';
    Conn.DBPassword := 'mypassword';
    Conn.OnLog      := HandleLog;  // procedure(Sender: TObject; const Msg: string) of object

    Script := TMMScript.Create(nil);
    try
      Script.Connection := Conn;
      Script.OnLog      := HandleLog;
      Script.SQL.Text   :=
        'SELECT id, name, amount FROM orders WHERE status = ''pending''';
      Script.Execute;

      if not Script.Success then
      begin
        ShowMessage('Error: ' + Script.LastError.Msg);
        Exit;
      end;

      if Script.HasResultSet then
      begin
        while not Script.Table.EOF do
        begin
          ShowMessage(Script.Table.FieldByName('name').AsString);
          Script.Table.Next;
        end;
      end;
    finally
      FreeAndNil(Script);  // destructor drops the EDB temp table
    end;
  finally
    Conn.Free;
  end;
end;

DML with Transaction

Run INSERT/UPDATE/DELETE inside a transaction. On error, the server rolls back automatically and Script.RolledBack is set to True.

var
  Script: TMMScript;
begin
  Script := TMMScript.Create(nil);
  try
    Script.Connection     := Conn;
    Script.OnLog          := HandleLog;
    Script.UseTransaction := True;
    Script.SQL.Text :=
      'UPDATE orders SET status = ''shipped'' WHERE id = 42;' +
      'INSERT INTO shipment_log (order_id, shipped_at) VALUES (42, NOW());';
    Script.Execute;

    if Script.Success then
      ShowMessage(Format('%d statement(s) executed.', [Script.StatementsExec]))
    else
      ShowMessage(Format('Error (rolled back: %s): %s',
        [BoolToStr(Script.RolledBack, True), Script.LastError.Msg]));
  finally
    FreeAndNil(Script);
  end;
end;

Note: DDL statements cause an implicit commit in MySQL/MariaDB and cannot be rolled back even inside a transaction.

Upload Progress

Show a progress bar while uploading a large script. OnProgress is also called during result page fetching (spFetching) and result building (spBuilding).

var
  Script: TMMScript;
begin
  Script := TMMScript.Create(nil);
  try
    Script.Connection := Conn;
    Script.OnLog      := HandleLog;
    Script.SQL.Text    := LargeScript;
    Script.OnProgress := ScriptProgress;  // see TMMScriptProgressEvent
    Script.Execute;
    // ...
  finally
    FreeAndNil(Script);
  end;
end;

Where ScriptProgress is a method declared on your form or class:

procedure TMyForm.ScriptProgress(ASender: TMMScript; APhase: TMMScriptPhase;
  ACurrent, ATotal: Int64; const AStatus: string);
begin
  case APhase of
    spUploading:
      if ATotal > 0 then
        ProgressBar1.Position := Round(ACurrent / Max(1, ATotal) * 100);
    spBuilding:
      if ATotal > 0 then
        ProgressBar1.Position := Round(ACurrent / Max(1, ATotal) * 100);
  end;
  Application.ProcessMessages;
end;

Limiting BLOB Data

Cap the size of individual cell values and set the out-of-band threshold to keep result pages small.

Script.Connection         := Conn;
Script.OnLog              := HandleLog;
Script.BlobMaxSizeBytes   :=  2 * 1024 * 1024;  // 2 MB per cell; 0 = auto-derive from server limits
Script.InlineBlobMaxBytes := 64 * 1024;          // 64 KB OOB threshold; 0 = all inline

Script.SQL.Text := 'SELECT id, photo, description FROM products';
Script.Execute;

if Script.ResultIncomplete then
  ShowMessage('Result fetch was aborted early (server OOM or timeout) — partial data loaded');

Cancellation from Another Thread

Allow the user to abort a long-running script.

// Worker thread:
Script.Execute;
if Script.Cancelled then
  ShowMessage('Cancelled by user');

// Main thread (e.g. button click):
Script.Cancel;

Batch INSERT

Write multiple rows to a MySQL table in one operation.

var
  Updater: TMMUpdater;
begin
  Updater := TMMUpdater.Create(nil);
  try
    Updater.Connection  := Conn;
    Updater.TargetTable := 'products';
    Updater.Mode        := umInsert;
    Updater.OnLog       := HandleLog;

    if not Updater.Prepare then
    begin
      ShowMessage('Prepare failed');
      Exit;
    end;

    Updater.Table.Append;
    Updater.Table.FieldByName('name').AsString   := 'Widget A';
    Updater.Table.FieldByName('price').AsFloat   := 9.99;
    Updater.Table.FieldByName('stock').AsInteger := 50;
    Updater.Table.Post;

    Updater.Table.Append;
    Updater.Table.FieldByName('name').AsString   := 'Widget B';
    Updater.Table.FieldByName('price').AsFloat   := 14.50;
    Updater.Table.FieldByName('stock').AsInteger := 120;
    Updater.Table.Post;

    Updater.Execute;
    if not Updater.Success then
      ShowMessage('Insert failed: ' + Updater.LastError.Msg)
    else
      ShowMessage(Format('%d row(s) inserted.', [Updater.RowsAffected]));
  finally
    FreeAndNil(Updater);
  end;
end;

Prefetch + UPDATE

Load existing rows from the server, modify them locally, and write them back. Use UpdateColumns to include only the columns that need to be updated — this keeps the buffer small and ensures that BLOB or CLOB columns not listed are never fetched and never accidentally overwritten.

var
  Updater: TMMUpdater;
begin
  Updater := TMMUpdater.Create(nil);
  try
    Updater.Connection  := Conn;
    Updater.TargetTable := 'orders';
    Updater.Mode        := umUpdate;
    // Include only the columns to update; 'attachment' (BLOB) is intentionally
    // excluded -- it is never fetched and never written back.
    Updater.UpdateColumns := ['id', 'status', 'updated_at'];
    Updater.OnLog         := HandleLog;

    if not Updater.Prepare then
    begin
      ShowMessage('Prepare failed — check log for details');
      Exit;
    end;

    if not Updater.Prefetch('status = ''pending'' AND region = ''EU''') then
    begin
      ShowMessage('Prefetch failed — check log for details');
      Exit;
    end;

    Updater.Table.First;
    while not Updater.Table.EOF do
    begin
      Updater.Table.Edit;
      Updater.Table.FieldByName('status').AsString       := 'processed';
      Updater.Table.FieldByName('updated_at').AsDateTime := Now;
      Updater.Table.Post;
      Updater.Table.Next;
    end;

    Updater.Execute;
    if not Updater.Success then
      ShowMessage('Update failed: ' + Updater.LastError.Msg)
    else
      ShowMessage(Format('%d row(s) updated.', [Updater.RowsAffected]));
  finally
    FreeAndNil(Updater);
  end;
end;

Batch UPDATE

Update specific columns for each row, identified by primary key.

var
  Updater: TMMUpdater;
begin
  Updater := TMMUpdater.Create(nil);
  try
    Updater.Connection  := Conn;
    Updater.TargetTable := 'products';
    Updater.Mode        := umUpdate;
    // PHP detects 'id' as PK -> WHERE id = ? per row; price, stock -> SET
    Updater.UpdateColumns := ['id', 'price', 'stock'];
    Updater.OnLog      := HandleLog;
    Updater.OnProgress := UpdaterProgress;  // see TMMUpdaterProgressEvent

    if not Updater.Prepare then
    begin
      ShowMessage('Prepare failed');
      Exit;
    end;

    Updater.Table.Append;
    Updater.Table.FieldByName('id').AsInteger    := 1;
    Updater.Table.FieldByName('price').AsFloat   := 11.00;
    Updater.Table.FieldByName('stock').AsInteger := 80;
    Updater.Table.Post;

    Updater.Table.Append;
    Updater.Table.FieldByName('id').AsInteger    := 2;
    Updater.Table.FieldByName('price').AsFloat   := 16.00;
    Updater.Table.FieldByName('stock').AsInteger := 200;
    Updater.Table.Post;

    Updater.Execute;
    if not Updater.Success then
      ShowMessage('Update failed: ' + Updater.LastError.Msg)
    else
      ShowMessage(Format('%d row(s) updated.', [Updater.RowsAffected]));
  finally
    FreeAndNil(Updater);
  end;
end;

Batch INSERT from Local EDB Table

When the source data already lives in a local EDB table, populate the buffer with a single SQL statement instead of appending rows one by one.

var
  Updater: TMMUpdater;
  Qry    : TEDBQuery;
begin
  Updater := TMMUpdater.Create(nil);
  try
    Updater.Connection  := Conn;
    Updater.TargetTable := 'products';
    Updater.Mode        := umInsert;
    Updater.UpdateColumns := ['name', 'price', 'stock'];
    Updater.OnLog       := HandleLog;

    if not Updater.Prepare then
    begin
      ShowMessage('Prepare failed');
      Exit;
    end;

    Qry := TEDBQuery.Create(nil);
    try
      Qry.SessionName  := Conn.Database.SessionName;
      Qry.DatabaseName := Conn.Database.DatabaseName;
      Qry.SQL.Text :=
        'INSERT INTO "' + Updater.TempTableName + '" (name, price, stock) ' +
        'SELECT name, price, stock FROM local_products WHERE active = TRUE';
      Qry.ExecSQL;
    finally
      Qry.Free;
    end;

    Updater.Execute;
    if not Updater.Success then
      ShowMessage('Insert failed: ' + Updater.LastError.Msg)
    else
      ShowMessage(Format('%d row(s) inserted.', [Updater.RowsAffected]));
  finally
    FreeAndNil(Updater);
  end;
end;

TempTableName is valid from the moment Prepare returns until Drop is called (or the destructor runs). Updater.Table and direct SQL access operate on the same EDB temporary table and can be combined freely.

Copy Result Set to Local EDB Table

After Execute, the result rows live in an EDB temporary table. Use an INSERT ... SELECT to copy them into a persistent local EDB table without iterating Table row by row. Useful for caching, local reporting, or preparing data for a subsequent write-back.

uses EDBMMClient;

var
  Script: TMMScript;
  Qry   : TEDBQuery;
begin
  Script := TMMScript.Create(nil);
  Qry    := TEDBQuery.Create(nil);
  try
    Script.Connection := Conn;
    Script.OnLog      := HandleLog;
    Script.SQL.Text    := 'SELECT id, name, amount, status FROM orders';
    Script.Execute;

    if not Script.Success then
    begin
      ShowMessage('Error: ' + Script.LastError.Msg);
      Exit;
    end;

    if Script.HasResultSet then
    begin
      Qry.SessionName  := Conn.Database.SessionName;
      Qry.DatabaseName := Conn.Database.DatabaseName;

      // Copy pending rows into a persistent local EDB work table
      Qry.SQL.Text :=
        'INSERT INTO "pending_work" (id, name, amount) ' +
        'SELECT id, name, amount ' +
        'FROM "' + Script.TempTableName + '" ' +
        'WHERE status = ''pending''';
      Qry.ExecSQL;

      ShowMessage(Format('%d row(s) in result; pending rows copied.',
                         [Script.RowCount]));
    end;
  finally
    FreeAndNil(Script);
    Qry.Free;
  end;
end;

Script.Table remains open and valid after the INSERT. TempTableName is valid for the lifetime of the TMMScript object (or longer when KeepTempTable = True).

Aggregate from One Table, Write to Another

Use this pattern when the source and destination are different MySQL tables, or when the data needs aggregation or a JOIN before being written. For reading from and writing back to the same table, use TMMUpdater.Prefetch instead.

This example reads daily sales records, aggregates units sold per product, and updates the running total in the products table. Prefetch cannot do this because the source (daily_sales) and the destination (products) are different tables.

uses EDBMMClient;

var
  Script : TMMScript;
  Updater: TMMUpdater;
  Qry    : TEDBQuery;
begin
  Script  := TMMScript.Create(nil);
  Updater := TMMUpdater.Create(nil);
  Qry     := TEDBQuery.Create(nil);
  try
    Qry.SessionName  := Conn.Database.SessionName;
    Qry.DatabaseName := Conn.Database.DatabaseName;

    // Step 1: aggregate sales from daily_sales into a local EDB temp table
    Script.Connection := Conn;
    Script.OnLog      := HandleLog;
    Script.SQL.Text :=
      'SELECT product_id, SUM(qty_sold) AS total_sold ' +
      'FROM daily_sales ' +
      'WHERE sale_date >= ''2026-01-01'' ' +
      'GROUP BY product_id';
    Script.Execute;

    if not Script.Success then
    begin
      ShowMessage('Read failed: ' + Script.LastError.Msg);
      Exit;
    end;

    if Script.RowCount = 0 then
    begin
      ShowMessage('No sales data found.');
      Exit;
    end;

    // Step 2: prepare updater for the destination table (products)
    Updater.Connection  := Conn;
    Updater.OnLog       := HandleLog;
    Updater.TargetTable := 'products';
    Updater.Mode        := umUpdate;
    // PHP detects 'id' as PK -> routes it to WHERE automatically
    Updater.UpdateColumns := ['id', 'total_sold'];

    if not Updater.Prepare then
    begin
      ShowMessage('Prepare failed -- check log for details');
      Exit;
    end;

    // Step 3: transfer aggregated rows into the updater buffer --
    // column rename (product_id -> id) handled in SQL, no Delphi loop needed
    Qry.SQL.Text :=
      'INSERT INTO "' + Updater.TempTableName + '" (id, total_sold) ' +
      'SELECT product_id, total_sold ' +
      'FROM "' + Script.TempTableName + '"';
    Qry.ExecSQL;

    // Step 4: write aggregated totals back to MySQL
    Updater.Execute;

    if not Updater.Success then
      ShowMessage('Write failed: ' + Updater.LastError.Msg)
    else
      ShowMessage(Format('%d product(s) updated.', [Updater.RowsAffected]));
  finally
    FreeAndNil(Script);
    FreeAndNil(Updater);
    Qry.Free;
  end;
end;

Named SQL Parameters

Use :name placeholders in SQL or Where to pass values safely without string concatenation. Parameters are substituted as correctly-quoted SQL literals before the query is sent to the server.

Security note: Parameter substitution is client-side literal encoding, not server-side prepared statements. It is safe against SQL injection under normal conditions (MySQL with utf8mb4 charset and default sql_mode): numeric, boolean, and binary values carry no user-controlled text; string values have backslash escaped to \\ and single quotes doubled to '' before being wrapped in single quotes. If the MySQL server is configured with NO_BACKSLASH_ESCAPES, the backslash doubling is redundant but harmless for injection safety — single-quote doubling alone is sufficient in that mode. Multibyte charset attacks (e.g. GBK) are not a concern when the connection uses utf8mb4.

var
  Script: TMMScript;
begin
  Script := TMMScript.Create(nil);
  try
    Script.Connection := Conn;
    Script.OnLog      := HandleLog;
    Script.SQL.Text   :=
      'SELECT id, name, amount FROM orders ' +
      'WHERE status = :status AND region = :region AND amount > :min_amount';

    // Params are not cleared automatically between calls.
    // If the same TMMScript is reused, clear and re-create params as needed.
    Script.Params.CreateParam(ftString, 'status',     ptInput).AsString := 'pending';
    Script.Params.CreateParam(ftString, 'region',     ptInput).AsString := 'EU';
    Script.Params.CreateParam(ftFloat,  'min_amount', ptInput).AsFloat  := 100.0;

    Script.Execute;

    if Script.Success and Script.HasResultSet then
    begin
      while not Script.Table.EOF do
      begin
        ShowMessage(Script.Table.FieldByName('name').AsString);
        Script.Table.Next;
      end;
    end;
  finally
    FreeAndNil(Script);
  end;
end;

Named parameters also work in TMMUpdater.Where:

Updater.Where   := 'region = :region';
Updater.Params.CreateParam(ftString, 'region', ptInput).AsString := 'EU';

Checking Error Codes

Check LastError.Code against EC_* constants from UErrorCodes to distinguish error categories and respond accordingly.

uses EDBMMClient, UErrorCodes;

var
  Script: TMMScript;
begin
  Script := TMMScript.Create(nil);
  try
    Script.Connection := Conn;
    Script.OnLog      := HandleLog;
    Script.SQL.Text    := 'SELECT * FROM very_large_table';
    Script.Execute;

    if not Script.Success then
    begin
      if Script.Cancelled then
        ShowMessage('Query cancelled.')
      else
      case Script.LastError.Code of
        EC_HTTP_TIMEOUT:
          ShowMessage('Query timed out. Add a LIMIT clause or increase ' +
                      'max_execution_time in php.ini.');
        EC_PHP_MEMORY_EXHAUSTED:
          ShowMessage('PHP ran out of memory. Increase memory_limit in php.ini ' +
                      'or narrow the query with a WHERE clause.');
        EC_MYSQL_QUERY:
          ShowMessage('MySQL error: ' + Script.LastError.Msg);
      else
        ShowMessage('Error [E' + Script.LastError.Code.ToString + ']: ' +
                    Script.LastError.Msg);
      end;
    end;
  finally
    FreeAndNil(Script);
  end;
end;

Add UErrorCodes to your uses clause to compare LastError.Code against EC_* constants. The TAppError type is visible through EDBMMClient without the extra unit.

See Also

• EDBApiClient Library
• Configuration
• Using the Library