Tutorial: Connecting to a SQLite Database Using FireDAC

Go Up to Database and LiveBindings Tutorials

This tutorial is a simple Delphi multi-device application that shows how to establish a connection to a SQLite database and execute a simple query.

The database used in this example is located at C:\Users\Public\Documents\Embarcadero\Studio\23.0\Samples\Data\Employees.s3db and has one table named Employee.

Steps

1. Select either:
• File > New > Multi-Device Application - Delphi
• File > New > Multi-Device Application - C++Builder
2. Add the following components to the form:
   1. Two TButton controls;
      • From the Object Inspector, set the Name properties of the buttons to executeButton and connectButton, and their Text properties to Execute and Connect, respectively.
      • Set the Enabled property of the executeButton to False.
   2. A TFDConnection component;
   3. A TMemo control;
      • Set the Name property to outputMemo.
   4. A TFDPhysSQLiteDriverLink component, to link the SQLite driver to the application.

   At this point, the form should look like this:

   

3. Add the following code to the OnClick event handler for the connectButton.
   • In Delphi:

   procedure TForm3.connectButtonClick(Sender: TObject);
   begin
     outputMemo.Text := '';
     // Set the path of your database file.
     // Replace 'C:\Users\Public\Documents\...\Employees.s3db ' with the absolute path 
     // to your SQLite database file.
     FDConnection1.DriverName := 'SQLITE';
     FDConnection1.Params.Values['Database'] :=
       'C:\Users\Public\Documents\Embarcadero\Studio\23.0\Samples\Data\Employees.s3db';
     try
       // Establish the connection.
       FDConnection1.Open;
       executeButton.Enabled := True;
       outputMemo.Lines.Add('Connection established!');
     except
       on E: EDatabaseError do
         outputMemo.Lines.Add('Exception raised with message' + E.Message);
     end;
   end;
   

   • In C++Builder:

   void __fastcall TForm1::connectButtonClick(TObject *Sender) {
     outputMemo->Text = "";
     // Set the path of your database file.
     // Replace 'C:\Users\Public\Documents\...\Employees.s3db ' with the absolute path 
     // to your SQLite database file.
     FDConnection1->DriverName = "SQLite";
     FDConnection1->Params->Values["DataBase"] = "C:\\Users\\Public\\Documents\\Embarcadero\\Studio\\14.0\\Samples\\Data\\Employees.s3db";
     try {
       // Establish the connection.
       FDConnection1->Open();
       executeButton->Enabled = true;
       outputMemo->Lines->Add("Connection established!");
     }
     catch (Exception& E) {
       outputMemo->Text = "Exception raised with message" + E.Message;
     }
   
   }
   

4. Add the following code to the OnClick event handler for the executeButton.
   • In Delphi:

   procedure TForm1.executeButtonClick(Sender: TObject);
   var
     query: TFDQuery;
   begin
   
     query := TFDQuery.Create(nil);
     
     try
       // Define the SQL Query
       query.Connection := FDConnection1;
       query.SQL.Text := 'SELECT * FROM Employee';
       query.Open();
       outputMemo.Text := '';
       // Add the field names from the table.
       outputMemo.Lines.Add(String.Format('|%8s|%25s|%25s|', [' ID ', ' NAME ',
         ' DEPARTMENT ']));
       // Add one line to the memo for each record in the table.   
       while not query.Eof do
       begin
         outputMemo.Lines.Add(String.Format('|%8d|%-25s|%-25s|',
           [query.FieldByName('ID').AsInteger, query.FieldByName('Name').AsString,
           query.FieldByName('Department').AsString]));
         query.Next;
       end;
   
     finally
       query.Close;
       query.DisposeOf;
     end;
   
   end;
   

   Note: You need to add the FireDAC.DApt unit.
   • In C++Builder:

   void __fastcall TForm1::executeButtonClick(TObject *Sender) {
     TFDQuery *query;
     query = new TFDQuery(NULL);
   
     __try {
       query->Connection = FDConnection1;
       // Define the SQL Query
       query->SQL->Text = "SELECT * FROM Employee";
       query->Open();
       outputMemo->Text = "";
       // Add the field names from the table.
       TVarRec args[3] = {"ID", "NAME", "DEPARTMENT"};
       outputMemo->Lines->Add(System::UnicodeString::Format("|%8s |%25s |%25s |", args, ARRAYSIZE(args) - 1));
       // Add one line to the memo for each record in the table.   
       while (!query->Eof) {
         TVarRec arguments[3] = {query->FieldByName("ID")->AsInteger, query->FieldByName("Name")->AsString, query->FieldByName("Department")->AsString};
         outputMemo->Lines->Add(System::UnicodeString::Format("|%8d |%-25s |%-25s |", arguments, ARRAYSIZE(arguments) - 1));
         query->Next();
       }
     }
     __finally {
       query->Close();
       query->DisposeOf();
     }
   
   }
   

   Note: You need to add #include <FireDAC.DApt.hpp>.
5. Choose either of the following commands:
   • Run > Run
   • Run > Run Without Debugging

   

See Also

• Tutorial: Connecting to a SQLite Database from a VCL Application

• TFDPhysSQLiteDriverLink
• TDataSet.Eof
• System.SysUtils.Format
• FireDAC.Comp.Client.TFDConnection
• SQLite support in RAD Studio
• Tutorial: TSQLMonitor Support for SQLite Databases
• Object Inspector

Samples

• FireDAC SQLite sample