Connecting to Databases (Data-Aware)

Modern desktop applications must interact with persistent data stored in databases (e.g., SQLite, PostgreSQL, MySQL). Object Pascal frameworks use a layered approach based on non-visual components to manage this interaction cleanly.

1. The Data Access Layer (DAL)

The connection to a database is handled by a set of non-visual components that manage the communication, queries, and buffering of results. This process is generally divided into three parts:

1. Connection: Manages the physical link to the database file or server.
2. Dataset: Manages the actual data retrieval and modification using SQL.
3. Data Source: Acts as a bridge between the Dataset and the visual Data-Aware controls.

A. The Connection Component (e.g., TSQLConnection)

This component holds the connection parameters necessary to link to the database engine.

// Example properties for a connection component in the IDE:
// SQLConnection1.DatabaseName := '/home/user/appdata/mydatabase.sqlite';
// SQLConnection1.Params.Add('User=dbuser');
// SQLConnection1.ConnectorType := 'SQLite3Connection';

// Code to activate the connection:
SQLConnection1.Connected := True;

B. The Dataset Component (e.g., TSQLQuery or TADOTable)

The Dataset executes SQL commands and retrieves the results. The resulting data is structured like a two-dimensional table and is buffered in memory.

• TSQLQuery: Used when you need to execute custom SQL statements (e.g., SELECT, UPDATE, INSERT).
• TSQLTable: Used when you simply want to view or edit the entire contents of a single database table.

// Example using a TSQLQuery component:
SQLQuery1.SQL.Text := 'SELECT FirstName, LastName FROM Customers WHERE City = ''London''';
SQLQuery1.Active := True; // Execute the query and load results

C. The Data Source Component (TDataSource)

This non-visual component links a Dataset (where the data is) to the Data-Aware Controls (where the data is shown).

• It acts as a cursor manager, pointing to the current record in the dataset.
• It automatically handles the passing of changes from the visual control back to the dataset.

2. Data-Aware Controls (Visual Components)

Data-Aware controls are specialized visual components designed to read and write data automatically when linked to a TDataSource.

A. Linking the Controls (Design-Time)

The entire connection is typically set up visually in the IDE’s Object Inspector:

1. Place the TSQLConnection, TSQLQuery, and TDataSource components on the form.
2. Set DataSource1.DataSet to point to SQLQuery1.
3. Place a TDBGrid on the form.
4. Set DBGrid1.DataSource to point to DataSource1.
5. Set SQLQuery1.Active to True (often done at runtime).

3. Record Navigation and Manipulation

When using Datasets, the programmer doesn’t manually loop through rows; methods manage the cursor’s position.

Example: Programmatic Record Iteration

// The TDBNavigator component automates this, but you can do it manually:
begin
  SQLQuery1.First;
  while not SQLQuery1.Eof do
  begin
    Writeln('Customer: ', SQLQuery1.FieldByName('LastName').AsString);
    SQLQuery1.Next;
  end;
end;

4. Updating Data

To change data, you must place the dataset into Edit Mode before modifying any field values:

procedure TForm1.ButtonUpdateClick(Sender: TObject);
begin
  // Assume the cursor is already on the record to be changed
  
  SQLQuery1.Edit; // Start editing mode
  
  // Modify a field's value
  SQLQuery1.FieldByName('Notes').AsString := 'Updated by application.';
  
  SQLQuery1.Post; // Save the change back to the database
end;