Using the Library

TMMConnection, TMMScript, and TMMUpdater are TComponent-based classes.
Each TMMScript and TMMUpdater owns its own HTTP client and is fully independent of any other instance.
Use one TMMConnection per thread and share it across as many TMMScript and TMMUpdater instances as needed.

The library exposes two main operations:

TMMScript — Running Scripts

Execute performs the complete upload–execute–materialise cycle:

  1. Uploads SQL to the server.
  2. Executes it on the MySQL/MariaDB server.
  3. If the last statement returns a result set, creates a local EDB temporary table and fills it with all rows.
  4. Sets all result properties; the previous result (if any) is dropped first.
uses EDBMMClient;           // TMMConnection, TMMScript, TMMUpdater
// uses UErrorCodes;   // add when checking EC_* constants directly

var Script: TMMScript;
Script := TMMScript.Create(nil);
try
  Script.Connection := Conn;
  Script.SQL.Text   := 'SELECT id, name 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
    // Script.Table is open and ready; iterate directly
    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;

Re-calling Execute drops the previous result and replaces it.
The caller owns Script and frees it; the destructor drops the EDB temp table automatically.

Multi-statement scripts are supported; only the result set of the last statement is returned.

DDL statements (CREATE TABLE, ALTER TABLE, etc.) cause an implicit commit in MySQL/MariaDB and cannot be rolled back even inside a transaction. When UseTransaction = True and an error occurs, Script.RolledBack is set to True.

Result Properties

Property Type Description
Success Boolean True when execution succeeded
LastError TAppError Structured error; Code = EC_NONE on success
HasResultSet Boolean True when the last statement produced rows
ResultIncomplete Boolean True when the server aborted the fetch early (PHP OOM or MySQL timeout), or when a later result page failed to load into the EDB table; partial result is valid. Reset to False at the start of each Execute call.
TempTableName string Local EDB temp table name; non-empty when HasResultSet = True
RowCount Int64 Total rows in the result set
StatementsExec Integer Number of SQL statements executed
ExecutionTimeSec Double Server-side execution time in seconds
RolledBack Boolean True when a transaction was rolled back by the server
Cancelled Boolean True when Cancel was called during execution
ResultTableDDL string CREATE TABLE DDL used to build the EDB result table
Columns TMMColumnInfoArray Per-column metadata array; indexed parallel to the result fields. Includes SourceName, SourceTable, IsBinary, PrimaryKeyIndex, Scale and all the detailed definition of each column

Assigning Your Own TEDBTable

By default TMMScript creates a TEDBTable internally and exposes it via Table.
To use a specific table instead, assign it before Execute:

Script.Table := MyExistingTable;  // not active: Script opens it after Execute
Script.Execute;
// MyExistingTable is now open with the result rows

If the table is already active when assigned, Execute closes it, re-associates it with the new temp table, and reopens it. TMMScript never frees a table provided by the caller.

Cancellation

Script.Cancel;  // safe to call from any thread

Execute checks the cancellation flag at four points: between upload chunks, after upload, after execution, and between result pages. When cancelled, Script.Success = False and Script.Cancelled = True.

Cancel has no effect on a blocking HTTP call already in progress.

BLOB Size Limits

Two independent limits control how much BLOB/CLOB data is transferred per Execute call.

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

BlobMaxSizeBytes — per-cell truncation cap. Cells larger than this value are truncated server-side before encoding. The cap applied per column is available in Columns[I].TruncCap after Execute.

InlineBlobMaxBytes — out-of-band threshold. Cells whose raw size exceeds this value are stored as server-side temp files and fetched individually, keeping each result page small. Both values are auto-derived from server limits after a successful TestConnection call and do not need to be set manually in most cases.

Inspect Columns[I].ActualMaxBytes after Execute to see the actual maximum raw size seen per column before truncation.

When the server aborted the result fetch before all rows were processed (PHP OOM or MySQL timeout), or when a later page failed to load into the EDB table, ResultIncomplete is set to True; the partial result already in the local EDB table is still valid data.

ResultIncomplete is automatically reset to False at the start of each Execute call, so re-running a query on the same TMMScript instance never inherits a stale flag from a previous failure.

Upload Progress

// Declare in your form or class:
procedure TMyForm.ScriptProgress(ASender: TMMScript; APhase: TMMScriptPhase;
  ACurrent, ATotal: Int64; const AStatus: string);
begin
  if APhase = spUploading then
    ProgressBar1.Position := Round(ACurrent / Max(1, ATotal) * 100)
  else if APhase = spBuilding then
    lblStatus.Caption := Format('Loading rows: %d / %d', [ACurrent, ATotal]);
end;

// Assignment:
Script.OnProgress := ScriptProgress;

APhase indicates which stage is active (spUploading, spExecuting, spFetching, spBuilding). For upload progress, ACurrent is the cumulative bytes uploaded and ATotal is the total script size (UTF-8 bytes). For result building, ACurrent is rows loaded and ATotal is the total row count from the server response.

TMMUpdater — Writing Data

TMMUpdater writes rows from a local EDB buffer to a remote MySQL/MariaDB table.
The workflow is: set properties → call Prepare → fill Table → call Execute.

var Updater: TMMUpdater;
Updater := TMMUpdater.Create(nil);
try
  Updater.Connection  := Conn;
  Updater.TargetTable := 'products';
  Updater.Mode        := umInsert;
  Updater.UpdateColumns := ['name', 'price', 'stock'];  // empty = all columns

  if not Updater.Prepare then
  begin
    ShowMessage('Prepare failed — check OnLog for details');
    Exit;
  end;
  // fill Updater.Table and call Execute (see below)
finally
  FreeAndNil(Updater);  // destructor drops the local EDB buffer table
end;

Prepare fetches the target table schema from the server, validates the column list and mode rules, creates a local EDB temporary table, and opens Table. Returns False on schema fetch failure or validation error.

Filling the Buffer

Use Updater.Table as a regular TEDBTable in append mode:

Updater.Table.Append;
Updater.Table.FieldByName('name').AsString   := 'Widget A';
Updater.Table.FieldByName('price').AsFloat   := 9.99;
Updater.Table.FieldByName('stock').AsInteger := 100;
Updater.Table.Post;
// repeat for more rows...

Do not close or free Updater.Table directly — it is managed by TMMUpdater.

Filling the Buffer with SQL

When source data already exists in a local EDB table, populate the buffer with a single SQL statement. TempTableName exposes the name of the EDB buffer table:

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;

Updater.Table and direct SQL access operate on the same EDB temporary table and can be combined freely.

Executing the Batch

Updater.Execute;
if not Updater.Success then
  ShowMessage('Batch failed: ' + Updater.LastError.Msg)
else
  ShowMessage(Format('%d row(s) sent, %d affected.',
    [Updater.RowsSent, Updater.RowsAffected]));

Execute serialises all rows in chunks (default 512 KB each) and posts each to the server. Result state is set in Success, LastError.Msg, RowsAffected, RowsSent, and ChunksSent after Execute returns.

Write Modes

Mode Server action Where required
umInsert INSERT INTO ... VALUES (...) No
umUpdate UPDATE ... SET ... WHERE <pk> No
umUpsert INSERT ... ON DUPLICATE KEY UPDATE No

For umUpdate, Prepare force-includes PK columns in the buffer even when absent from UpdateColumns, and the PHP backend routes them to the per-row WHERE predicate. Non-PK columns go to SET. You may list PK columns in UpdateColumns for clarity, but it is not required:

Updater.Mode    := umUpdate;
Updater.UpdateColumns := ['id', 'price', 'stock'];
// PHP detects 'id' as PK → WHERE id = ? per row
// 'price' and 'stock' are non-PK → SET price = ?, stock = ?

Use Where only for an additional static filter applied to every row (e.g. 'region = :region' with Params.ParamByName('region').AsString := 'EU'), or when the target table has no PRIMARY KEY.

Reusing the Buffer

Call Clear to empty the local table without recreating it:

Updater.Execute;
Updater.Clear;   // EMPTY TABLE on the EDB buffer; Table stays open
// fill and execute again...

Batch Progress

// Declare in your form or class:
procedure TMyForm.UpdaterProgress(ASender: TMMUpdater; AChunksDone: Integer;
  ARowsDone, ARowsTotal: Int64);
begin
  ProgressBar1.Position := Round(ARowsDone / Max(1, ARowsTotal) * 100);
end;

// Assignment:
Updater.OnProgress := UpdaterProgress;

Foreign Key Checks

Updater.SkipFKChecks := True;  // disables FK checks on the server for this batch

The server restores FK checks after the batch regardless of success or failure.

Prefetch — Loading Existing Rows Before Update

Prefetch loads rows from the remote table into the local EDB buffer before Execute. Use it when you need to read existing server values, modify them locally, and write them back.

Updater.Connection  := Conn;
Updater.TargetTable := 'orders';
Updater.Mode        := umUpdate;
Updater.UpdateColumns := ['id', 'status', 'updated_at'];
Updater.OnLog       := HandleLog;

if Updater.Prepare then
  if Updater.Prefetch('status = ''pending'' AND region = ''EU''') then
  begin
    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;
  end;

Prefetch is available only in umUpdate and umUpsert modes. Pass an empty string to load all rows.

BLOB and CLOB columns — data integrity warning: Prefetch always downloads BLOB and CLOB values in full. Every column in the buffer is later written back to the server by Execute. A BLOB column included in UpdateColumns that was fetched but not explicitly modified would still be sent back on Execute — which is harmless if the value arrived intact, but risks data loss if the download was interrupted or failed silently.

The correct practice when the table has BLOB or CLOB columns that do not need to be updated is to exclude them from UpdateColumns:

// 'image' and 'document' are not in UpdateColumns — they are never fetched
// and never overwritten, regardless of their size on the server.
Updater.UpdateColumns := ['id', 'status', 'updated_at'];

Columns excluded from UpdateColumns are absent from the buffer, absent from Prefetch, and absent from Execute. The server-side values are never touched.

Result Properties

Property Type Description
Success Boolean True when all chunks were accepted
LastError TAppError Structured error; Code = EC_NONE on success
FailedRowPK TArray<TPair<string,string>> PK col=val pairs of the row that caused a server-side failure (PHP v175+); empty on transport failures
RowsInserted Int64 Rows newly inserted (umInsert/umUpsert)
RowsUpdated Int64 Rows updated by ON DUPLICATE KEY (umUpsert)
RowsMatched Int64 Rows matched by WHERE (umUpdate)
RowsAffected Int64 Convenience total: RowsInserted + RowsUpdated + RowsMatched
RowsSent Int64 Rows read from the local EDB buffer and sent
ChunksSent Integer Number of HTTP POST requests made

TAppError is declared in UErrorCodes. Add it to uses when comparing LastError.Code against EC_* constants (e.g. EC_HTTP_TIMEOUT, EC_CLIENT_CANCELLED). The type itself is visible through EDBMMClient without the extra uses entry.

Accessing Temp Tables via SQL

Both TMMScript and TMMUpdater expose the name of their local EDB temporary table via TempTableName. Any TEDBQuery in the same EDB session and database can read from or write to that table using standard EDB SQL. This is often more efficient than iterating Table row by row in Delphi.

Querying helper setup

var Qry: TEDBQuery;
Qry := TEDBQuery.Create(nil);
try
  Qry.SessionName  := Conn.Database.SessionName;
  Qry.DatabaseName := Conn.Database.DatabaseName;
  // Qry.SQL.Text := ...
  // Qry.ExecSQL or Qry.Open
finally
  Qry.Free;
end;

EDB identifiers must be quoted with ". Embed TempTableName directly in the SQL string:

Qry.SQL.Text := 'SELECT COUNT(*) FROM "' + Script.TempTableName + '"';

Copying a result set into a local EDB table

After TMMScript.Execute, the result rows live in the EDB temp table. An INSERT ... SELECT copies them (or a filtered/transformed subset) into a persistent local EDB table for later processing. TempTableName is valid for the lifetime of the TMMScript object, or longer when KeepTempTable = True.

Script.SQL.Text := 'SELECT id, name, amount, status FROM orders';
Script.Execute;

if Script.Success and Script.HasResultSet then
begin
  // Copy only the pending rows into a persistent work table
  Qry.SQL.Text :=
    'INSERT INTO "pending_orders" (id, name, amount) ' +
    'SELECT id, name, amount FROM "' + Script.TempTableName + '" ' +
    'WHERE status = ''pending''';
  Qry.ExecSQL;
  // Script.Table is still open and valid after the INSERT
end;

Loading the TMMUpdater buffer from a local EDB table

Instead of appending rows one by one via Table.Append / Post, bulk-load the buffer with a single INSERT ... SELECT from an existing local EDB table. This applies equally to umInsert, umUpdate, and umUpsert modes.

Updater.TargetTable := 'products';
Updater.Mode        := umUpdate;
Updater.UpdateColumns := ['id', 'price', 'stock'];
Updater.OnLog       := HandleLog;

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

// Bulk-load with a SQL transformation: apply a 10 % price increase
Qry.SQL.Text :=
  'INSERT INTO "' + Updater.TempTableName + '" (id, price, stock) ' +
  'SELECT id, ROUND(price * 1.1, 2), stock ' +
  'FROM "local_products" WHERE active = TRUE';
Qry.ExecSQL;

Updater.Execute;

Table.Append / Post and SQL INSERT operate on the same temp table and can be combined freely: use SQL for bulk loads and Table.Append / Post for rows that need field-level Delphi logic.

Transferring from a TMMScript result into a TMMUpdater buffer

This pattern is useful when the source and destination are different tables, or when the data requires aggregation or a JOIN before being written. When reading from and writing back to the same table, use Prefetch instead – it is designed exactly for that case.

Example: aggregate daily sales by product and update the running total in the products table. TMMUpdater.Prefetch cannot do this because the source (daily_sales) and destination (products) are different tables.

// Step 1: aggregate sales data from MySQL into a local EDB temp table
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;

// Step 2: prepare the updater for the destination table
// PHP detects 'id' as PK of products → WHERE per row (no Where needed)
Updater.TargetTable := 'products';
Updater.Mode        := umUpdate;
Updater.UpdateColumns := ['id', 'total_sold'];
Updater.OnLog       := HandleLog;

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

// Step 3: transfer from script result into 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;

Aggregating or inspecting results without row iteration

Script.Execute;

if Script.Success and Script.HasResultSet then
begin
  Qry.SQL.Text :=
    'SELECT COUNT(*) FROM "' + Script.TempTableName + '" WHERE amount > 1000';
  Qry.Open;
  ShowMessage(Format('%d high-value rows', [Qry.Fields[0].AsInteger]));
  Qry.Close;
  // Script.Table is still open and unaffected by the query above
end;

Avoid DELETE or UPDATE on a TMMScript result table while Script.Table is open and being iterated – this can displace the active cursor. Either complete the iteration first, or close Script.Table before modifying the temp table contents.

Fetching Full BLOB / CLOB Values

Use FetchBlob on a TMMScript instance to retrieve a single BLOB or CLOB column value after Execute, when the value may have been truncated by the budget.

var
  F       : TField;
  ColIdx  : Integer;
  PKParams: TJSONArray;
begin
  F      := Script.Table.FieldByName('photo');
  ColIdx := F.Index;
  PKParams := Script.BuildPKParams(Script.Table, ColIdx, 'products');
  try
    if Script.IsPKComplete('products', PKParams) then
      Script.FetchBlob('products',
                       Script.Columns[ColIdx].SourceName,
                       PKParams, F);
  finally
    PKParams.Free;
  end;
end;

Querying Column Metadata

TMMConnection provides two typed methods that query INFORMATION_SCHEMA.COLUMNS without executing a script. Both parse the server response into TMMColumnInfo records and additionally derive EDBType and IsBinary via the library’s type mapper.

// Single column — parses JSON into a TMMColumnInfo record
var
  Info: TMMColumnInfo;
begin
  if Conn.GetColumnInfo('orders', 'status', Info) then
    ShowMessage(Info.ColumnType + ' / EDB: ' + Info.EDBType);
end;

// All columns — parses JSON into a TMMColumnInfoArray
var
  Cols: TMMColumnInfoArray;
  C   : TMMColumnInfo;
begin
  if Conn.GetTableColumns('orders', Cols) then
    for C in Cols do
      ShowMessage(C.ColumnName + ': ' + C.EDBType);
end;

TMMColumnInfo and TMMColumnInfoArray are declared in EDBMMUtils. They are accessible via uses EDBMMClient (which includes EDBMMUtils in its interface clause), so an explicit uses EDBMMUtils is not required unless your unit uses these types independently.

See TMMColumnInfo for field descriptions.

See Also

(C) 2026 Easygate, Lda