Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 36 ยท Files and Databases

Databases with FireDAC

Connect Delphi to SQLite or any SQL database with FireDAC, run queries with parameters, walk datasets with First, Next and Eof, and use transactions safely.

Once your data has relationships, needs searching, or is shared by several users, a database beats files. Delphi's standard database layer is FireDAC, a set of components for talking to SQLite, MySQL, PostgreSQL, SQL Server, Oracle, Firebird, InterBase and more with the same code. Free Pascal and Lazarus use a similar but different family (SQLdb), whose ideas map closely.

FireDAC needs a Delphi installation and a database driver, so the Pascal code here is read-only. The SQL statements are real and run in the editors below on the site's SQLite engine. A small C program models the dataset cursor.

The pieces

A FireDAC program uses a few components that you drop on a form or create in code:

Component Role
TFDConnection holds the database connection (driver, file or server, login)
TFDQuery runs a SQL statement and exposes the result rows
TFDTransaction optional explicit transaction control
TFDTable, TFDMemTable whole-table view, in-memory table
TDataSource connects a dataset to visual controls (grids, edits)

TFDQuery and its relatives all descend from TDataSet, the base class of every dataset in Delphi. Learn the dataset methods once and they work everywhere.

Connecting to SQLite

SQLite needs no server, only a file, so it is a good first database:

PASCAL
uses
  FireDAC.Comp.Client, FireDAC.Stan.Def, FireDAC.Stan.Async,
  FireDAC.DApt, FireDAC.Phys.SQLite, FireDAC.Stan.Intf;

var
  Conn: TFDConnection;
begin
  Conn := TFDConnection.Create(nil);
  try
    Conn.DriverName := 'SQLite';
    Conn.Params.Database := 'library.db';   // created if it does not exist
    Conn.Connected := True;
    Conn.ExecSQL('CREATE TABLE IF NOT EXISTS books (' +
                 'id INTEGER PRIMARY KEY, title TEXT NOT NULL, year INTEGER)');
  finally
    Conn.Free;
  end;
end.

The long uses list pulls in the driver and plumbing units; the IDE adds them for you when you drop components on a form. ExecSQL runs a statement that returns no rows (CREATE, INSERT, UPDATE, DELETE).

Running a query and walking the rows

PASCAL
Q := TFDQuery.Create(nil);
try
  Q.Connection := Conn;
  Q.SQL.Text := 'SELECT title, year FROM books ORDER BY year';
  Q.Open;
  while not Q.Eof do
  begin
    WriteLn(Q.FieldByName('title').AsString, ' (',
            Q.FieldByName('year').AsInteger, ')');
    Q.Next;
  end;
finally
  Q.Free;
end;

A dataset has a current row (a cursor). Open runs the query and puts the cursor on the first row. Eof is true once you have moved past the last row, Next moves forward, First and Last jump, and RecordCount tells you how many rows there are (it may need to fetch everything first). FieldByName('x').AsString (or AsInteger, AsFloat, AsDateTime, AsBoolean) reads a column of the current row.

Forgetting Q.Next is the classic infinite loop: Eof never becomes true.

Always use parameters

Never build SQL by gluing user text into a string. It breaks on a quote character and invites SQL injection. Use :name parameters:

PASCAL
Q.SQL.Text := 'SELECT * FROM books WHERE year >= :minYear AND title LIKE :pat';
Q.ParamByName('minYear').AsInteger := 1950;
Q.ParamByName('pat').AsString := '%Pascal%';
Q.Open;

The database receives the statement and the values separately, so a value can never become part of the SQL. The same applies to writes:

PASCAL
Q.SQL.Text := 'INSERT INTO books (title, year) VALUES (:t, :y)';
Q.ParamByName('t').AsString := 'Programming in Modula-2';
Q.ParamByName('y').AsInteger := 1982;
Q.ExecSQL;
WriteLn('Rows affected: ', Q.RowsAffected);

Transactions

A transaction makes several changes all succeed or all fail. Moving money between two accounts must not stop halfway:

PASCAL
Conn.StartTransaction;
try
  Conn.ExecSQL('UPDATE accounts SET balance = balance - 50 WHERE id = 1');
  Conn.ExecSQL('UPDATE accounts SET balance = balance + 50 WHERE id = 2');
  Conn.Commit;
except
  Conn.Rollback;
  raise;
end;

Without an explicit transaction each statement is committed on its own. For bulk inserts, wrapping thousands of rows in one transaction makes SQLite dramatically faster.

The SQL itself

You can run and change these examples right here. They use the same statements the Pascal code above sends.

SQL Create, fill and query the books table

Press Run (or Ctrl + Enter) to see the result here.

SQL Updates inside a transaction

Press Run (or Ctrl + Enter) to see the result here.

Change COMMIT; to ROLLBACK; and run again: the balances return to 100 and 20. That is what Conn.Rollback does in the exception handler.

The dataset cursor in C

The Pascal loop while not Q.Eof do ... Q.Next is a cursor over rows. The program below models it: open positions on the first row, eof is true past the end, and a loop that forgets next would never finish (the program guards against that with a counter so it stops).

C Stand-in: a dataset cursor with First, Eof and Next

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with scanf or fgets), it gets the next line from this box.

Showing data in a form

In a VCL or FMX application you connect TFDQuery to a TDataSource, and the data source to a TDBGrid or TDBEdit. The controls then follow the dataset cursor, and edits go back with Post. Visual database work is covered in VCL forms and components. You can also edit rows in code: Q.Edit; Q.FieldByName('title').AsString := 'New'; Q.Post;, with Append/Insert for new rows and Delete to remove the current one, provided the query is updatable.

Good practice

  • Keep the connection in one place (a data module) instead of scattered over forms.
  • Always use parameters; close queries you no longer need.
  • Do slow queries off the UI thread, or the window freezes.
  • Free the query and the connection in finally.
  • Choose the SQL dialect knowingly: LIMIT is SQLite/MySQL/PostgreSQL, while SQL Server uses TOP and Firebird uses ROWS.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use :parameters instead of building SQL by joining strings?

    Parameters keep user data out of the SQL text, which prevents injection and handles quotes correctly.
  2. 2. Which loop walks every row of an opened query?

    Process the current row, then call Next. Without Next, Eof never becomes true.
  3. 3. What does ExecSQL do?

    Use Open for SELECT and ExecSQL for statements that change data or structure.
  4. 4. What does Rollback do in a failed transaction?

    A rollback restores the state before the transaction began, so the changes are all-or-nothing.

Summary

  • FireDAC provides TFDConnection and TFDQuery (a TDataSet) for many databases with the same code.
  • Open runs a SELECT; walk it with while not Eof do ... Next; ExecSQL runs everything else.
  • Always use :name parameters via ParamByName.
  • Transactions (StartTransaction, Commit, Rollback) make multi-step changes all-or-nothing.
  • Keep database code organized, off the UI thread, and freed in finally.

Next: VCL Forms and Components