Mobile Tutorial: Using FireDAC and SQLite (iOS and Android)

Go Up to Mobile Tutorials: Mobile Application Development (iOS and Android)

Before starting this tutorial, you should read and perform the following tutorial session:

• Mobile Tutorial: Using LiveBindings to Populate a ListView (iOS and Android)

This tutorial describes the basic steps to use SQLite as a local data storage on your mobile device through the FireDAC framework.

iOS	Android

Using FireDAC to Connect to the Database

FireDAC is a unique set of Universal Data Access Components for developing cross-platform database applications for Delphi and C++Builder. With its powerful common architecture, FireDAC enables native high-speed direct access from Delphi to InterBase, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, IBM DB2, SQL Anywhere, Access, Firebird, Informix, and more.

• For the mobile platforms, FireDAC supports InterBase ToGo as well as SQLite. These database products can run on iOS and Android devices.

• For other databases, such as Oracle, you need to have at least a client library. On Windows platforms, the client library is provided as a DLL to connect to. Therefore, you need to develop applications using middle-tier technologies such as DataSnap REST to connect to these database products from a mobile device.

Another tutorial discusses how to connect to Enterprise Database without using a client library on a mobile device; see Mobile Tutorial: Connecting to an Enterprise Database from a Mobile Client (iOS and Android).

Creating the Database using FireDAC framework

First, you need to create a SQLite database file on your Windows development platform. Use the following steps, so that you can use the Form Designer to design the user interface of your Mobile App.

Note: In the Form Designer, activate the Master view for this tutorial.

1. To create an Multi-Device Application, select:
   • For Delphi: File > New > Multi-Device Application - Delphi > Blank Application
   • For C++: File > New > Multi-Device Application - C++Builder > Blank Application
2. On the Tool Palette, double-click the TFDConnection component.

   

3. Right click the TFDConnection component and choose Connection Editor.
4. In the FireDAC Connection Editor, set the following parameters of the TFDConnection:
   • Set the Driver ID property to SQLite.
   • Set the Database parameter to:

     C:\Users\Public\Documents\Embarcadero\Studio\18.0\Samples\Data\shoplist.s3db (location of the database)

     and click Open in the File Open dialog box.

     Note: You can set the above Database parameter even if shoplist.s3db does not exist, RAD Studio creates it automatically. (To display Employees.s3db or other *.s3db files in the Open dialog, set the All Files (*.*) option.)

   • Set the LockingMode parameter to normal.

     
     
     

   • Click the Test button to test the connection.
   • Click OK to close the Connection Editor.
5. In the Object Inspector, set the following properties of TFDConnection:
   • Set the LoginPrompt property to False, so that the user is not prompted for a login.
   • Set the Connected property to True.
6. On the Tool Palette, double-click the TFDQuery component.
7. In the Object Inspector, set the following properties of TFDQuery:
   • Set the Name property to FDQueryCreateTable.
   • Set the SQL property as follows:

     CREATE TABLE IF NOT EXISTS Item (ShopItem TEXT NOT NULL)

8. Right click the FDQueryCreateTable and choose Execute.

Design and Set Up the User Interface

Visible UI components are loaded on the designer

This tutorial uses one TListView component as the UI element.

To set up a ListView component and other UI elements, use the following steps:

1. Drop a TToolBar on the form.
2. Drop a TButton on the ToolBar component and set the following properties in the Object Inspector:
   • Set the Name property to ButtonAdd.
   • Set the StyleLookup to addtoolbutton.
   • Set the Align to Right.
3. Drop a TButton on the ToolBar component and set the following properties in the Object Inspector:
   • Set the Name property to ButtonDelete.
   • Set the StyleLookup to deletetoolbutton.
   • Set the Align to Left.
   • Set the Text to Delete.
   • Set the Visible property to False.
4. Drop a TLabel on the ToolBar component and set the following properties in the Object Inspector:
   • Set the Align to Client.
   • Set the StyleLookup to toollabel.
   • Set the Text to Shopping List.
   • Expand the TTextSettings node and set the HorzAlign property to Center.
5. Drop a TListView component on the form and set the following properties in the Object Inspector:
   • Set the Align property to Client, so that the ListView component uses the entire form.

Using the LiveBindings Wizard

This tutorial uses the LiveBindings Wizard to add the LiveBindings components (TBindSourceDB, TBindingsList), and the TFDQuery component.

Add the LiveBinding components

1. Select View > LiveBindings Designer and the LiveBindings Designer opens.
2. Select LiveBindings Wizard.

   
   
   

3. Select Create a data source binding task.
4. Click the Next button.
5. Select FireDAC class name.
6. Click the Next button.
7. Change the Command Type to Query.
8. Set the Command Text property to select ShopItem from Item.

   
   
   

9. Click the Test Command button.
10. Click the Next button.
11. Click the Finish button.

At this point, TBindSourceDB and TFDQuery components were added to your form.

Connecting to the Data

1. Reopen the LiveBindings Wizard.
2. Select Link a control with a field binding task.

   
   
   

3. Click the Next button.
4. Select the Existing Control tab.
5. Select the ListView1 component.
6. Click the Next button.
7. Select BindSourceDB1.
8. Click the Next button.
9. Select ShopItem Field Name.

   
   
   

10. Click the Next button.
11. Click the Finish button to close the wizard.
    

Note: These last steps are not mandatory for this tutorial since there is only one field in BindSourceDB1. These steps are useful to link with the selected value if we are managing several fields of a database.

Displaying ShopItem in the ListView

The following step displays the text of ShopItem in the TListView component.

1. In the LiveBindings Designer select ShopItem in the BindSourceDB1 component and drag ShopItem to Item.Text in ListView1.

   

Following these steps connects the user interface of the app with data on a SQLite database. If you used a table with existing data for this tutorial, now you should see actual data within the Form Designer.

Creating the Event Handler to Make the Delete Button Visible When the User Selects an Item from the List

The Visible property for the Delete button is set to False. Therefore, by default, the end user does not see this button. You can make it visible when the user selects an item on the list, as follows:

• Select ListView1 and define the following event handler for the OnItemClick event.

Delphi:

procedure TForm1.ListView1ItemClick(const Sender: TObject;
  const AItem: TListViewItem);
begin
  ButtonDelete.Visible := ListView1.Selected <> nil;
end;

• For C++:

void __fastcall TForm1::ListView1ItemClick(const TObject *Sender,
    const TListViewItem *AItem) {
    ButtonDelete->Visible = (ListView1->Selected != NULL);
}

Creating the Event Handler for the Add Button to Add an Entry to the List

Database connections are also configured

The next step is adding a feature to this application for adding an item to the shopping list.

1. Drop a TFDQuery component on the form.
2. Set the following properties in the Object Inspector:
   • Set the Name property to FDQueryInsert.
   • Set the SQL property as follows:

     INSERT INTO ITEM (ShopItem) VALUES (:ShopItem)

   • Select the Expand (...) button on the Params property.
   • Select the ShopItem parameter and set DataType to ftString:

     
     
     

3. In the Structure View, right-click the ButtonAdd component and select Control > Bring to Front. This brings the button to the visual front of the active form.

• For Delphi:

• Declare the following procedure in the private section:

private
  procedure OnInputQuery_Close(const AResult: TModalResult; const AValues: array of string);

• Add the following procedure :

procedure TForm1.OnInputQuery_Close(const AResult: TModalResult; const AValues: array of string);
var
  TaskName: String;
begin
 TaskName := string.Empty;
  if AResult <> mrOk then
    Exit;
  TaskName := AValues[0];
  try
    if (TaskName.Trim <> '')
    then
    begin
      FDQueryInsert.ParamByName('ShopItem').AsString := TaskName;
      FDQueryInsert.ExecSQL();
      FDQuery1.Close();
      FDQuery1.Open;
      ButtonDelete.Visible := ListView1.Selected <> nil;
    end;
  except
    on e: Exception do
    begin
      ShowMessage(e.Message);
    end;
  end;
end;

• In the Form Designer, double-click the ButtonAdd component. Add the following code to this event handler:

procedure TForm1.ButtonAddClick(Sender: TObject);
begin
  TDialogServiceAsync.InputQuery('Enter New Item', ['Name'], [''], Self.OnInputQuery_Close)
end;

• For C++:

To replicate the same functionality in C++, additional steps are required.

• Add the following definition after the TForm1 definition (in the .h file of your unit):

typedef void __fastcall(__closure * TInputCloseQueryProcEvent)
    (const System::Uitypes::TModalResult AResult,
    System::UnicodeString const *AValues, const int AValues_High);

• Add the following class definition (in the .h file of your unit, after the previously defined type):

class InputQueryMethod : public TCppInterfacedObject<TInputCloseQueryProc>
{
private:
    TInputCloseQueryProcEvent Event;

public:
    InputQueryMethod(TInputCloseQueryProcEvent _Event) {
        Event = _Event;
    }

    void __fastcall Invoke(const System::Uitypes::TModalResult AResult,
        System::UnicodeString const *AValues, const int AValues_High) {
        Event(AResult, AValues, AValues_High);
    }
};

• Add the following declaration under the private section of the form (in the .h file of your unit):

private: //User declarations
    void __fastcall OnInputQuery_Close
        (const System::Uitypes::TModalResult AResult,
        System::UnicodeString const *AValues, const int AValues_High);

• Add the following code (in the .cpp file of your unit):

void __fastcall TForm1::OnInputQuery_Close(const System::Uitypes::TModalResult AResult,
        System::UnicodeString const *AValues, const int AValues_High) {
    String TaskName;
    TaskName = "";
    if (AResult != mrOk)
        return;
    TaskName = AValues[0];
try {
        if  (!(Trim(TaskName) == "")) {
            FDQueryInsert->ParamByName("ShopItem")->AsString = TaskName;
            FDQueryInsert->ExecSQL();
            FDQuery1->Close();
            FDQuery1->Open();
            ButtonDelete->Visible = (ListView1->Selected != NULL);
        }
    }
    catch (Exception &e) {
        ShowMessage(e.Message);
    }
}

• In the Form Designer, double-click the ButtonAdd component. Add the following code to this event handler:

void __fastcall TForm1::ButtonAddClick(TObject *Sender) {
    String caption = "Enter New Item";
    String Prompts[1];
    Prompts[0] = "Name:";
    String Defaults[1];
    Defaults[0] = "";
    _di_TInputCloseQueryProc Met = new InputQueryMethod(&OnInputQuery_Close);
    TDialogServiceAsync::InputQuery(
        caption, Prompts, 0, Defaults, 0, (TInputCloseQueryProc *)Met);
}

The InputQuery function shows a dialog box asking the end user to enter text. This function returns True when the user selects OK, so that you can add data to the database only when the user selects OK and the text contains some data.

iOS	Android

Creating the Event Handler for the Delete Button to Remove an Entry from the List

The next step is adding a feature to this application to remove an item from the shopping list:

1. Drop a TFDQuery component on the form.
2. Set the following properties in the Object Inspector:
   • Set the Name property to FDQueryDelete.
   • Set the SQL property as follows:

     delete from Item where ShopItem = :ShopItem

   • Select the Expand (...) button on the Params property.
   • Select the ShopItem parameter and set DataType to ftString.
3. In the Structure View, right-click the ButtonDelete component and select Control > Bring to Front. This brings the button to the visual front of the active form.
4. In the Form Designer, double-click the ButtonDelete component. Add the following code to this event handler.

• For Delphi:

procedure TForm1.ButtonDeleteClick(Sender: TObject);
var
  TaskName: String;
begin
  TaskName := TListViewItem(ListView1.Selected).Text;

  try
    FDQueryDelete.ParamByName('ShopItem').AsString := TaskName;
    FDQueryDelete.ExecSQL();
    FDQuery1.Close;
    FDQuery1.Open;
    ButtonDelete.Visible := ListView1.Selected <> nil;
  except
    on e: Exception do
    begin
      SHowMessage(e.Message);
    end;
  end;
end;

• For C++:

void __fastcall TForm1::ButtonDeleteClick(TObject *Sender) {
    String TaskName = ((TListViewItem*)(ListView1->Selected))->Text;
    try {
        FDQueryDelete->ParamByName("ShopItem")->AsString = TaskName;
        FDQueryDelete->ExecSQL();
        FDQuery1->Close();
        FDQuery1->Open();
        ButtonDelete->Visible = (ListView1->Selected != NULL);
    }
    catch (Exception &e) {
        ShowMessage(e.Message);
    }
}

Preparing Your Application for Run Time

FireDAC has a loosely-coupled multilayered architecture, where layers provide services. A service API is defined as a COM interface that other layers can request using the interface factory.

To properly operate FireDAC, you must link the implementation of the IFDGUIxWaitCursor and IFDPhysDriver interfaces to your application.

For this, drop the TFDGUIxWaitCursor and TFDPhysSQLiteDriverLink components on the form.

Setting Up Your Database Deployment for mobile

Up to this point, you have used SQLite on your desktop. This means that the actual database is located on your local hard disk drive (for example, C:\Users\Public\Documents\Embarcadero\Studio\18.0\Samples\Data\shoplist.s3db). On the mobile Device, applications are sand-boxed, and typically you can only read and write data that is located in the Documents folder (for iOS device) and internal storage (for Android device) under your application folder.

To connect to a local database on mobile, you need to perform the following actions:

• Deploy the database to the mobile device.
• Check the configuration (to connect to the database file) to a local file under the Documents folder (for iOS device) or internal storage (for Android device).

Add and Configure Your Database File in the Deployment Manager

Before you can run your application on mobile, you need to set up the deployment for your database file (shoplist.s3db).

1. You can add the database to your project with one of the following two methods:
   • Right-click the project name in the Project Manager and select Add… from the context menu (or Project > Add to Project) to display the Add to Project dialog box. Navigate to the database location C:\Users\Public\Documents\Embarcadero\Studio\18.0\Samples\Data, select the database shoplist.s3db and click Open.
   • Navigate to the database location C:\Users\Public\Documents\Embarcadero\Studio\18.0\Samples\Data and drag and drop the database shoplist.s3db to the project in the Project Manager. Click Yes to confirm that you want to add the file to your project.
2. After adding the database file, the Featured Files window displays, click Cancel to close it.
3. Open the Deployment Manager by selecting Project > Deployment.
4. Select Debug configuration - iOS Device - 32 bit platform, Debug configuration - iOS Device - 64 bit platform or Debug configuration - Android platform from the drop-down list of target platforms at the top of the Deployment Manager and see that the database shoplist.s3db has been added to the platforms.
5. See how the Remote Path of shoplist.s3db has been set for iOS and Android platforms:
   • Remote Path on iOS Device platform: StartUp\Documents\

   
   
   

   • Remote Path on Android platform: assets\internal\

   
   
   

As you just configured, when you run the app on the mobile device, the database file (shoplist.s3db) is set to be deployed to the Documents folder (for iOS platform) or internal storage (for Android platform) in the sandbox area of your multi-device application.

Modifying Your Code to Connect to a Local Database File on mobile

The basic features of this application are now implemented. Following the steps from this tutorial, you created a database file on Windows. The database file is not available on your mobile device unless you copy it to the mobile device or create it on the fly.

You can create a SQLite Database and Table with the following steps:

Specifying the Location of the SQLite Database on the Mobile Device

1. In the Form Designer, select the FDConnection1 component.
2. In the Object Inspector, double-click the BeforeConnect event.
3. Add the following code to this event handler:

• For Delphi:

procedure TForm1.FDConnection1BeforeConnect(Sender: TObject);
begin
  {$IF DEFINED(iOS) or DEFINED(ANDROID)}
  FDConnection1.Params.Values['Database'] :=
      TPath.Combine(TPath.GetDocumentsPath, 'shoplist.s3db');
  {$ENDIF}
end;

The TPath record is declared in System.IOUtils unit, so you need to add System.IOUtils in the uses clause of your unit.

implementation

{$R *.fmx}
uses  System.IOUtils;

• For C++:

void __fastcall TForm1::FDConnection1BeforeConnect(TObject *Sender) {
#if defined(_PLAT_IOS) || defined(_PLAT_ANDROID)
    FDConnection1->Params->Values["Database"] =
        System::Ioutils::TPath::Combine(System::Ioutils::TPath::GetDocumentsPath
        (), "shoplist.s3db");
#endif
}

You need to add #include <System.IOUtils.hpp>.

Creating a Table if None Exists

With SQLite you can create a table when no table exists, by using the CREATE TABLE IF NOT EXISTS statement. You can create a table after the TFDConnection component connects to the database and before the TFDQuery component connects to the table. Use the following steps:

1. In the Form Designer, select the FDConnection1 component.
2. In the Object Inspector, double-click the AfterConnect event.
3. Add the following code to this event handler:

• For Delphi:

procedure TForm1.FDConnection1AfterConnect(Sender: TObject);
begin
  FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Item (ShopItem  TEXT NOT NULL)');
end;

• For C++:

void __fastcall TForm1::FDConnection1AfterConnect(TObject *Sender) {
    FDConnection1->ExecSQL("CREATE TABLE IF NOT EXISTS Item (ShopItem  TEXT NOT NULL)");
}

Running Your Application on a Simulator or on a Mobile Device

Now your application is ready to run on either a simulator or your connected mobile device.
To run your application

1. In Project Manager, select your target platform.
   
2. Choose either of the following commands:
   • Run > Run
   • Run > Run Without Debugging

iOS	Android

Note: If you have an issue with running the application, follow the steps given in Troubleshooting.

See Also

• Mobile Tutorial: Using InterBase ToGo with FireDAC (iOS and Android)

• Mobile Tutorial: Connecting to an Enterprise Database from a Mobile Client (iOS and Android)

• SQLite support in RAD Studio
• Android Mobile Application Development
• iOS Mobile Application Development