Tutorial: Connecting to a SQLite Database Using FireDAC

From RAD Studio
Jump to: navigation, search

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\17.0\Samples\Data\Employees.s3db and has one table named Employee.

Steps

  1. Select either:
  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:

    Design.png
  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\17.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|%-25|%-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
    Runtime.png

See Also

Samples