VCL and data access

How a VCL application with a database is built: forms and .dfm files, datasets and fields, the ADO and FireDAC libraries with stored procedure calls, and background threads that do not freeze the UI. These questions check whether a candidate understands the mechanism rather than just dropping components on a form.

Before this topic: Object Pascal.

Junior+

How do VCL forms and components work?

Answer

What it is

Form — a TForm1 = class(TForm) class in a .pas unit plus a .dfm file with the same name.
.dfm — the form’s components and their property values, usually as text.
Event — a property that references a method: OnClick calls Button1Click on a click.

Form lifecycle

OnCreate — components have been loaded from the .dfm.
OnShow — the form is appearing on screen.
OnClose — the Action parameter decides the form’s fate: caHide — hide it, caFree — free it, caNone — do not close.
OnDestroy — the form is being destroyed.

ShowModal waits until the form closes and returns ModalResult, while Show returns control immediately.

Common mistake

Calling Free on a form inside its own event handler: the handler has not finished yet and will touch freed memory. That is what Release and Action := caFree are for.

Takeaway: a form is a class plus a .dfm, and event handlers define its behavior.

How it works

The .dfm stores published properties whose values differ from the one given in the default directive.
The form constructor finds the .dfm resource (linked by {$R *.dfm}) and reads it.
For each object a component of the right class is created, properties are assigned via RTTI, and the published form field with the same name starts pointing to it.
A line like OnClick = btnOkClick is bound to a published form method by name. Rename the method only in code and the form will not load.

Owner and Parent

	Owner	Parent
Responsible for	freeing and the `Components` list	display and position on screen
How to set	`Create(AOwner)`	the `Parent` property
Who can be one	any `TComponent`	only a `TWinControl`

A Parent also frees its child controls, but its main role is visual.

Pitfalls

A control created in code without a Parent is not visible.
Create(nil) without Free is a leak. Create(Self) for a dialog created on every call piles up objects until the owner closes.
Free on a form from its own handler — use Release: it waits until the form’s handlers and messages are done.

Rule of thumb

A modal dialog: Create(nil) and ShowModal inside try..finally Free. A modeless window: Action := caFree in OnClose.

Takeaway: the .dfm restores objects via RTTI, the Owner handles memory, the Parent handles display.

Under the hood

TReader creates components, assigns properties and, after the whole form is read, calls Loaded on every component.
References between components (DataSource = dsOrders) are resolved after all objects are read, so their order in the .dfm does not matter.
The creation order of non-visual components does matter: a saved Connected = True or Active = True opens the connection and queries right when the form is created.
A descendant form stores only its differences from the ancestor in the .dfm (inherited), and a frame on a form stores only its differences too (inline).

Cost

Application.CreateForm for every form in the .dpr: all .dfm files are read at startup, and the forms with their open queries live until the program exits.
A global Form1 variable encourages “one form reads another form’s fields” coupling. Such code is hard to test and to move.
A binary .dfm cannot be diffed in Git; a text one can be read and merged.

Trade-offs

A frame is a reusable piece of UI with code. Changes to the frame show up on every form, except properties overridden on a particular form.
Form inheritance gives a shared skeleton: buttons, validation, error handling. A deep hierarchy is fragile: changing the ancestor breaks descendants.
A data module (TDataModule) separates connections and queries from the form, so they can be used without UI.

How to check in production

Leaked forms and components show up with ReportMemoryLeaksOnShutdown := True or FastMM in FullDebugMode.
A binary .dfm starts with the TPF0 signature — an easy way to find files worth converting to text.

Takeaway: a form is a serialized object graph; manage its lifetime explicitly instead of relying on auto-creation.

Example

procedure TMainForm.btnEditOrderClick(Sender: TObject);
var
  Dlg: TOrderDialog;
begin
  Dlg := TOrderDialog.Create(nil);   // no owner: we free it ourselves
  try
    Dlg.OrderId := 42;
    if Dlg.ShowModal = mrOk then     // waits for closing and returns ModalResult
      RefreshOrders;
  finally
    Dlg.Free;
  end;
end;

procedure TMainForm.FormCreate(Sender: TObject);
var
  Btn: TButton;
begin
  Btn := TButton.Create(Self);       // Owner = the form, it will free the button
  Btn.Parent := pnlTools;            // without a Parent the button is invisible
  Btn.Caption := 'Refresh';
end;

procedure TReportForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;                  // a modeless form frees itself
end;

.dfm fragment

object OrderDialog: TOrderDialog
  Caption = 'Order'
  OnCreate = FormCreate
  object btnOk: TButton
    Caption = 'OK'
    ModalResult = 1
  end
end

ModalResult = 1 is mrOk: clicking the button closes the form, and ShowModal returns mrOk.

Likely follow-ups

How does Owner differ from Parent? — The Owner frees the component and keeps it in Components; the Parent is the windowed control the control is drawn inside.
What does Application.CreateForm do? — Creates a form owned by Application; the first form created this way becomes the main form.
Why can’t you call Free in the form’s own handler? — The handler is still running; Release defers destruction until message processing is done.
How does a frame differ from form inheritance? — A frame is an embeddable piece of UI; inheritance is a shared ancestor for the whole form.
What happens if the .dfm references a handler that no longer exists in code? — The form will not load: the binding goes by the published method name.

Sources

Middle+

How does working with data through TDataSet work?

Answer

What it is

TDataSet is a set of rows with a cursor: at any moment one record is current. TADOQuery, TFDQuery, TClientDataSet and other datasets inherit from it.

How the components connect

A query (TFDQuery or TADOQuery) fetches rows from the database.
TDataSource links the dataset to controls.
A TDBGrid or TDBEdit with its DataSource and DataField properties displays and edits the data.

How to use

Navigation: First, Next, Prior, Last; end of data is Eof.
Reading: FieldByName('Amount').AsCurrency.
Changing: Edit, assign fields, Post. A new record: Append or Insert.

Common mistake

Assigning a field value without Edit: the dataset is in dsBrowse, and you get “Dataset not in edit or insert mode”. Another classic is forgetting Next in a while not Eof loop and getting an infinite loop.

Takeaway: TDataSet is a cursor over rows, and TDataSource connects controls to it.

How it works

Open moves the dataset from dsInactive to dsBrowse.
Edit gives dsEdit, Insert or Append gives dsInsert. A new record fires OnNewRecord — the place for default values.
Post writes the changes. BeforePost is the place for validation: an exception there cancels the post. Cancel discards the buffer.
Moving fires BeforeScroll and AfterScroll. If the record was being edited, moving first performs an implicit Post.

Kinds of fields

Kind	Where the value comes from
Dynamic	created on `Open` from the result columns
Persistent	defined in the Fields Editor and stored in the .dfm
Calculated	the `OnCalcFields` handler
Lookup	another dataset by key (`LookupDataSet`)

Pitfalls

If a dataset has persistent fields, dynamic ones are not created by default: a new query column cannot be found via FieldByName.
A persistent field’s type must match the column type: after the type changes in the database, the dataset will not open.
DisableControls without try..finally: after an exception the grid stays frozen. In a master-detail setup it also stops the detail dataset from refreshing.
Do not modify the dataset in OnCalcFields: with AutoCalcFields = True that is infinite recursion.

Rule of thumb

Validation goes in BeforePost. Bulk loops go between DisableControls and EnableControls in try..finally. Heavy work does not belong in AfterScroll.

Takeaway: a dataset is a state machine with events, and most bugs are a wrong state or an extra event.

Under the hood

The dataset keeps a record buffer the size of the grid’s visible part, while the full result is held by the specific library: the ADO client cursor or FireDAC’s internal storage.
Controls are linked to the dataset through TDataSource and TDataLink objects. The dataset broadcasts record and state change notifications to them, and DisableControls increments a counter that silences this broadcast.
A field’s OldValue and CurValue are read through the temporary states dsOldValue and dsCurValue — conflict resolution is built on them.

Cached updates

Library	How to enable and apply
FireDAC	`CachedUpdates := True`, then `ApplyUpdates` and `CommitUpdates`
ADO	`LockType := ltBatchOptimistic`, then `UpdateBatch`
`TClientDataSet`	the `Delta` log, `ApplyUpdates` through a provider

FireDAC’s ApplyUpdates does not raise an exception; it returns the error count and does not start a transaction itself. So it is wrapped in StartTransaction and Commit.

Cost

An AfterScroll handler with a server query means dozens of queries while scrolling the grid.
A lookup field searches the lookup table for every record. For a large lookup table a JOIN on the server is cheaper.

Trade-offs

Cached updates reduce locking and round trips, but the data gets stale: between reading and ApplyUpdates another user may have changed it. The defense is a WHERE condition on the key or on all fields (UpdateOptions.UpdateMode in FireDAC).

How to check in production

How many queries a form generates is visible in SQL Server Profiler or Extended Events, and for FireDAC in the FDMonitor trace.

Takeaway: TDataSet hides queries behind events, and cached updates postpone conflicts rather than remove them.

Example

procedure TOrdersForm.RecalcTotal;
var
  Mark: TBookmark;
  Total: Currency;
begin
  Total := 0;
  Mark := qryOrders.Bookmark;        // remember the current record
  qryOrders.DisableControls;         // the grid does not repaint on every row
  try
    qryOrders.First;
    while not qryOrders.Eof do
    begin
      Total := Total + qryOrdersAmount.AsCurrency; // persistent field from the .dfm
      qryOrders.Next;
    end;
    qryOrders.Bookmark := Mark;      // go back to the user's record
  finally
    qryOrders.EnableControls;        // counter back to zero — controls refresh
  end;
  lblTotal.Caption := CurrToStr(Total);
end;

procedure TOrdersForm.qryOrdersBeforePost(DataSet: TDataSet);
begin
  if qryOrdersAmount.AsCurrency <= 0 then
    raise Exception.Create('Amount must be positive'); // Post cancelled, record stays in dsEdit
end;

Likely follow-ups

How does TDataSource differ from TDataSet? — The dataset holds data; TDataSource only links it to controls and can disable them via Enabled.
What happens on Next in the dsEdit state? — The dataset implicitly calls Post if the record was modified, or Cancel if not.
How does a calculated field differ from a lookup field? — A calculated field is computed by your code in OnCalcFields; a lookup field takes its value from another dataset by key.
Why DisableControls if there is BlockReadSize? — BlockReadSize also skips control updates but keeps refreshing detail datasets.
How do you inspect errors after FireDAC’s ApplyUpdates? — Check the returned error count, then use FilterChanges with rtHasErrors and RowError, or an OnReconcileError handler.

Sources

Middle+

ADO, dbExpress and FireDAC: how do they differ and how do you call stored procedures?

Answer

What it is

Library	How it works
ADO (dbGo)	`TADO*` components on top of Microsoft ADO (COM)
dbExpress	lightweight drivers, forward-only datasets; deprecated
FireDAC	`TFD*` components, one API for many DBMSs

How to call a procedure

Put a connection (TADOConnection or TFDConnection) and a procedure component on the form.
Set the name: ProcedureName in ADO, StoredProcName in FireDAC.
Get the parameter list: Parameters.Refresh in ADO, Prepare in FireDAC.
Fill in the input parameters and call ExecProc, or Open if the procedure returns rows.
After execution, read the output parameters.

Common mistake

Calling Open for a procedure that returns no rows, or ExecProc for one that does. FireDAC reports an error in both cases.

Takeaway: to a form all three libraries are a TDataSet, and a procedure is called through its name, parameters and ExecProc or Open.

How it works

ADO — TADOConnection with a connection string to an OLE DB provider; datasets TADOQuery, TADOStoredProc, TADOCommand. The default cursor is client-side (CursorLocation = clUseClient): the whole result comes to the client.
dbExpress — TSQLConnection, TSQLQuery. Datasets are unidirectional and not editable; a grid needs a TDataSetProvider plus TClientDataSet pair.
FireDAC — TFDConnection with DriverID=MSSQL. It reaches SQL Server through the ODBC Driver for SQL Server; the docs recommend version 18.

What FireDAC adds

Fetching. FetchOptions.Mode: the default fmOnDemand pulls rows in chunks of RowsetSize (50), fmAll fetches everything on Open.
Array DML. One command with parameter arrays instead of a loop: Params.ArraySize and Execute.
Macros. &Name or !Name substitute into the SQL text what cannot be a parameter, such as a table name.
Monitoring. TFDMoniRemoteClientLink and MonitorBy=Remote show every command and its timing in the FDMonitor tool.

Pitfalls

SQL Server parameters in FireDAC are named with @ (@CustomerId). ResourceOptions.UnifyParams removes the prefix.
FireDAC does not support procedure parameters with default values: fill them in explicitly.
A macro is text substitution, so user input in it is SQL injection.

Rule of thumb

New code on FireDAC, values only through parameters, bulk writes through Array DML.

Takeaway: all three libraries give you a TDataSet, but only FireDAC is still developed, and only it controls fetching, batching and tracing.

Under the hood

TFDStoredProc.Prepare with fiMeta in FetchOptions.Items asks the server for the parameter description — a separate round trip. ADO does the same in Parameters.Refresh. So the component is prepared once, and in hot code parameters are described manually.
On SQL Server fmOnDemand uses a server-side static cursor, while fmAll uses a fast forward-only cursor and frees the connection for the next command right away.
MARS is enabled by default in the MSSQL driver, and the docs warn about a possible performance hit.
Array DML on SQL Server runs natively, and errors are collected for all rows of the array.

Trade-offs

Concern	ADO	FireDAC
Dependencies	COM and an OLE DB provider	the SQL Server ODBC driver
Bulk writes	a loop of calls or a batch on a client cursor	Array DML
Diagnostics	server-side tracing	FDMonitor on the client plus server-side tracing

Cost of switching

Replacing ADO with FireDAC is not renaming components: parameter names and types, field type mapping and cursors all differ. It is a separate task, covered in Delphi 6 → 12/13 migration.

How to check in production

FDMonitor or TFDMoniFlatFileClientLink record the text, parameters and timing of every command.
On the server, Extended Events or Query Store show which procedures are called most often and run longest.

Takeaway: data access speed is decided by metadata, fetch mode and batching, not by the choice of component.

Example

// FireDAC: SQL Server parameter names start with @
FDStoredProc1.StoredProcName := 'dbo.OrderCreate';
FDStoredProc1.Prepare;                          // asks the server for the parameter list
FDStoredProc1.ParamByName('@CustomerId').AsInteger := 42;
FDStoredProc1.ExecProc;                         // a procedure without a result set
NewOrderId := FDStoredProc1.ParamByName('@OrderId').AsInteger; // OUTPUT parameter

// ADO: the same
ADOStoredProc1.ProcedureName := 'dbo.OrderCreate';
ADOStoredProc1.Parameters.Refresh;              // also a metadata round trip
ADOStoredProc1.Parameters.ParamByName('@CustomerId').Value := 42;
ADOStoredProc1.ExecProc;
NewOrderId := ADOStoredProc1.Parameters.ParamByName('@OrderId').Value;

// Array DML: the whole array in one command instead of an ExecSQL loop
FDQuery1.SQL.Text := 'insert into dbo.ItemTag (ItemId, Tag) values (:ItemId, :Tag)';
FDQuery1.Params.ArraySize := Length(Tags);
for I := 0 to High(Tags) do
begin
  FDQuery1.Params[0].AsIntegers[I] := Tags[I].ItemId;
  FDQuery1.Params[1].AsStrings[I] := Tags[I].Name;
end;
FDQuery1.Execute(FDQuery1.Params.ArraySize, 0);

Likely follow-ups

Why can’t a dbExpress dataset be edited in a grid? — It is unidirectional and does not buffer rows; you need a TClientDataSet via TDataSetProvider.
How do you get a procedure’s RETURN value? — A parameter with ParamType = ptResult; in SQL Server it is named @RETURN_VALUE.
How does a macro differ from a parameter? — A parameter sends the value separately from the text; a macro changes the SQL text itself before it is sent.
When fmAll and when fmOnDemand? — fmAll for small results and to free the connection immediately; fmOnDemand for large lists that are scrolled in parts.
The procedure returns several result sets — how do you read them? — NextRecordSet in FireDAC, NextRecordset in ADO.

Sources

Senior+

How do you work with threads in a VCL application without breaking the UI?

Answer

What it is

The main thread creates windows and processes their messages: clicks, key presses, repaints.
A background thread is a TThread or a TTask. It does long work without stopping the message loop.

Why

A 10-second query in a button handler freezes the window for 10 seconds. Windows shows “Not responding”, and the user clicks again.

How to use

Start the work in the background: TThread.CreateAnonymousThread(...).Start or TTask.Run(...).
To show the result, pass code to TThread.Synchronize or TThread.Queue — it runs on the main thread.
While the work is running, disable the start button.

Common mistake

Changing Label1.Caption or ProgressBar1.Position directly from a background thread. Sometimes it “works”, and then the application hangs or crashes in a random place.

Takeaway: UI on the main thread, long work in the background, results through Synchronize or Queue.

How it works

	`Synchronize`	`Queue`
Background thread	waits until the main thread runs the code	continues immediately
Good for	an answer from the UI, such as a dialog choice	progress, logs, notifications

Called from the main thread, Queue runs the code immediately; ForceQueue always queues it.

Ways to start

A TThread descendant with an Execute method — full control: Terminate, WaitFor, and OnTerminate, which runs on the main thread.
TThread.CreateAnonymousThread — for one-off work. The thread frees itself (FreeOnTerminate), so do not touch the reference after Start.
The PPL (System.Threading): TTask.Run for a task on the thread pool, TTask.Future<T> for a task with a result, TParallel.For for a parallel loop.

Pitfalls

ProcessMessages. While it processes the queue, the user can click the button again or close the form, and the handler carries on with a freed form.
Shared connection. Two threads on one TFDConnection give an AV or the SQL Server error “Connection is busy with results for another command”.
ADO in a thread is COM: Execute must start with CoInitialize(nil) and end with CoUninitialize.
Variable capture. An anonymous method captures the variable, not its value: a Queue inside a loop may show the counter’s next value.

Rule of thumb

UI only from the main thread, a background thread has its own connection, progress goes through Queue.

Takeaway: Synchronize waits for the main thread, Queue does not, and each thread has its own database connection.

Under the hood

Synchronize and Queue put the call into a shared queue and wake the main thread. It runs the queue in CheckSynchronize when it returns to the message loop, and until then a thread in Synchronize is stuck.
TTask runs on the thread pool. ITask.Cancel only marks the task; the code itself calls TTask.CurrentTask.CheckCanceled, which raises EOperationCancelled.
TTask.WaitForAll and reading IFuture<T>.Value on the main thread block it just like a synchronous query.

Cost

A connection per thread means sessions on the server. The FireDAC pool (Pooled=True in a connection definition, POOL_MaximumItems defaulting to 50) reuses physical connections. Set FDManager.Active := True before starting threads.
A Queue per row floods the main thread’s queue. Update progress every N rows or on a timer.

Trade-offs

Cancellation. Terminated is checked only between commands. To abort a command that is already running, FireDAC offers AbortJob on its connection.
Deadlock. The main thread waits on a critical section or an event, while the background thread holding it calls Synchronize — each waits for the other.
Closing the form. In OnCloseQuery threads are stopped with Terminate and WaitFor, and pending calls are removed with TThread.RemoveQueuedEvents. Otherwise a late Queue touches a freed form.

How to check in production

madExcept and EurekaLog can detect a frozen main thread and save the stacks of all threads.
Log TThread.Current.ThreadID to see which thread the code ran on.

Takeaway: a thread alone does not make an app responsive: you need its own connection, cancellation, waiting on close and no UI outside the main thread.

Example

procedure TStockWorker.Execute;
var
  Conn: TFDConnection;
  Proc: TFDStoredProc;
  Batch: Integer;
begin
  Conn := TFDConnection.Create(nil);         // the thread has its own connection
  Proc := TFDStoredProc.Create(nil);
  try
    Conn.ConnectionDefName := 'Shop_Pooled'; // a definition with Pooled=True
    Proc.Connection := Conn;
    Proc.StoredProcName := 'dbo.RecalcStock';
    Proc.Prepare;
    for Batch := 1 to 100 do
    begin
      if Terminated then Exit;               // cooperative cancellation, finally frees everything
      Proc.ParamByName('@BatchNo').AsInteger := Batch;
      Proc.ExecProc;
      ReportProgress(Batch);
    end;
  finally
    Proc.Free;
    Conn.Free;
  end;
end;

procedure TStockWorker.ReportProgress(Value: Integer);
begin
  // Value is a parameter: each call has its own copy, not a shared loop counter
  TThread.Queue(Self, procedure
    begin
      MainForm.ProgressBar1.Position := Value; // runs on the main thread
    end);
end;

On close, the form calls Worker.Terminate and Worker.WaitFor, then TThread.RemoveQueuedEvents(Worker) so that no calls referring to it remain in the queue.

Likely follow-ups

How does Synchronize differ from Queue? — The first waits for the code to run on the main thread; the second queues it and does not wait.
Why is Application.ProcessMessages dangerous? — Reentrancy: other handlers run in the middle of your code, and the form may get closed.
Can one TFDConnection be used from two threads? — Only strictly one at a time; in practice, one connection per thread plus a pool.
How do you cancel a query that is already running? — AbortJob on the FireDAC connection; the Terminated flag is checked only between commands.
What does ADO need in a background thread? — CoInitialize at the start of Execute and CoUninitialize at the end, because ADO is COM.

Sources