Show: Delphi C++
Display Preferences

Mobile Tutorial: Using SQLite (iOS and Android)

From RAD Studio
Jump to: navigation, search

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


Contents

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

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

iOS Android

IOSScreen.png

AndroidScreen.png

Using dbExpress to Connect to the Database

dbExpress is a very fast database access framework, written in Delphi. RAD Studio provides drivers for most major databases, such as InterBase, Oracle, DB2, SQL Server, MySQL, Firebird, SQLite, and ODBC. You can access these different databases using procedures similar to the procedure described here.

  • For the mobile platforms, dbExpress 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 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 in the Windows Environment for Development Purposes

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

Create the Database in the Data Explorer

  1. Go to Data Explorer, right-click the SQLite node and select Add New Connection:
    AddSQLiteConnection.png

  2. Define the name of the connection, such as ShoppingList.
    ShoppingListConnection.png

  3. Specify the location of the database file:
    DatabaseLocationFile.png

  4. Click the Advanced button and open the Advanced Properties dialog box.
  5. Change the FailIfMissing property to False and click OK to close the Advanced Properties dialog box:
    300

    Note: Setting FailIfMissing to False instructs the Data Explorer to create a new database file if the file is not available.
  6. Back on the Modify Connection dialog box, click the Test Connection button. With this operation, the new database file is created if no file existed:
    ConnectionTest.png
    Note: Ensure that sqlite3.dll is present on your development system. If this file is not present, download sqlite3.dll from http://www.sqlite.org/download.html to your system path (such as C:\Windows\SysWOW64 for 64-bit Windows).

Create Table on DataExplorer

  1. On the Data Explorer, double-click the ShoppingList node under the SQLite section, right-click Tables, and then select New Table from context menu.
    CreateNewSQLiteTable.png
  2. Set Data Type for a ShopItem column to TEXT.
    SQLiteDefineShopItemTable.png
  3. Click the Save button and specify a table name (for example, Item.)
    SpecifySQLiteTableNameAsItem.png

Design and Set Up the User Interface

Visible UI components are loaded on the designer

This tutorial uses one TListBox component as the UI element.

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

  1. Create a FireMonkey Mobile application using File > New > FireMonkey Mobile Application - Delphi or File > New > FireMonkey Mobile Application - C++ Builder.
  2. Drop a TToolBar on the Form.
  3. 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 addtoolbuttonbordered.
  4. 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 Text to Delete.
    • Set the Visible to False.
  5. Drop a TLabel on the ToolBar component and set the following properties in the Object Inspector:
  6. Drop a TListBox component on the Form and set the following properties in the Object Inspector:
    • Set the Align property to alClient, so that the ListBox component uses the entire form.

Connecting to the Data

Following are the basic steps to connect to data in a database which is already defined in the Data Explorer:

  1. Select the Item table on the Data Explorer and drag it to the Form Designer.
    DragTableFromDataExplorerToiOSForm.png

    Note: This creates two components (ShoppingList: TSQLConnection and Item: TSQLDataSet) on the Form.
    ShoppingListAndItem.png

  2. Select the ShoppingList component on the Form, and then change the Connected property to True.
  3. Select the Item component on the Form, and then change the Active property to True.
  4. Select View > LiveBindings Designer and the LiveBindings Designer opens.
  5. Select ShopItem in the Item component and drag ShopItem to ListBox1.
    ShopItemToListBox.png

Following these steps connects the app's user interface 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 ListBox1 and define the following event handler for the OnItemClick event.

Delphi:

procedure TForm1.ListBox1ItemClick(const Sender: TCustomListBox;
  const Item: TListBoxItem);
begin
  if ListBox1.Selected <> nil then
    ButtonDelete.Visible := True
  else
    ButtonDelete.Visible := False;
end;

C++:

void __fastcall TForm4::ListBox1ItemClick(const TCustomListBox *Sender,const TListBoxItem *Item)
{
	if (ListBox1->Selected)
		ButtonDelete->Visible = True;
	else
		ButtonDelete->Visible = False;
}

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 TSQLQuery component to the form.
  2. Set the following properties in the Object Inspector:
    • Set the Name property to SQLQueryInsert.
    • Set the SQLConnection property to ShoppingList.
    • 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:
      EditingShopItemParameter.png

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

Delphi:

procedure TForm1.ButtonAddClick(Sender: TObject);
var
  TaskName: String;
begin
  try
    if InputQuery('Enter New Item', 'Name', TaskName) and (TaskName.Trim <> '') then
    begin
      SQLQueryInsert.ParamByName('ShopItem').AsString := TaskName;
      SQLQueryInsert.ExecSQL();
      Item.Refresh;
      LinkFillControlToField1.BindList.FillList;
      if ListBox1.Selected <> nil then
        ButtonDelete.Visible := True
      else
        ButtonDelete.Visible := False;
    end;
  except
    on e: Exception do
    begin
      ShowMessage(e.Message);
    end;
  end;
end;

C++:

void __fastcall TForm4::ButtonAddClick(TObject *Sender) {
     String TaskName;
	try {
		if ((InputQuery("Enter New Item", "Name", TaskName)) &&(!(Trim(TaskName) == ""))) {
			SQLQueryInsert->ParamByName("ShopItem")->AsString = TaskName;
			SQLQueryInsert->ExecSQL();
			Item->Refresh();
			LinkFillControlToField1->BindList->FillList();
			if (ListBox1->Selected)
				ButtonDelete->Visible = True;
			else
				ButtonDelete->Visible = False;
		}
	}
	catch (Exception &e) {
		ShowMessage(e.Message);
	}
}

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

UsingInputQueryOniOS.PNG

EnterItemAndroid.png

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 TSQLQuery component to the form.
  2. Set the following properties in the Object Inspector:
    • Set the Name property to SQLQueryDelete.
    • Set the SQLConnection property to ShoppingList.
    • 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 Form Designer, double-click the DeleteButton component. Add the following code to this event handler.

Delphi:

procedure TForm1.ButtonDeleteClick(Sender: TObject);
var
  TaskName: String;
begin
  TaskName := ListBox1.Selected.Text;
 
  try
    SQLQueryDelete.ParamByName('ShopItem').AsString := TaskName;
    SQLQueryDelete.ExecSQL();
    Item.Refresh;
    LinkFillControlToField1.BindList.FillList;
    if ListBox1.Selected <> nil then
      ButtonDelete.Visible := True
    else
      ButtonDelete.Visible := False;
  except
    on e: Exception do
    begin
      SHowMessage(e.Message);
    end;
  end;
end;

C++:

void __fastcall TForm4::ButtonDeleteClick(TObject *Sender) {
	String TaskName = ListBox1->Selected->Text;
	try {
		SQLQueryDelete->ParamByName("ShopItem")->AsString = TaskName;
		SQLQueryDelete->ExecSQL();
		Item->Refresh();
		LinkFillControlToField1->BindList->FillList();
		if (ListBox1->Selected)
			ButtonDelete->Visible = True;
		else
			ButtonDelete->Visible = False;
	}
	catch (Exception &e) {
		ShowMessage(e.Message);
	}
}

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\RAD Studio\12.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.
  • Change 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. Open the Deployment Manager by selecting Project > Deployment.
  2. Select Add Files, and select the database file (for example, C:\Users\Public\Documents\RAD Studio\12.0\Samples\Data\shoplist.s3db).
    AddingDatabaseFile.png

  3. Select shoplist.s3db, and change Remote Path to StartUp\Documents\ (for iOS platform) or assets\internal\ (for Android platform).
    RemotePath on iOS device platform
    RemotePathiOS.png

    RemotePath on Android platform
    RemotePathAndroid.png

  4. Select the Platforms column (double-click the ellipsis [...] in the row for shoplist.s3db):
    1. Ensure that iOS Simulator and iOS Device or Android are present for shoplist.s3db.
    2. Remove Win32 from the list if it is present (you do not have to copy database files to the Win32 platform).
  5. Select All-Configurations - iOS Device platform or All-Configurations - Android platform and make sure shoplist.s3db is set to be deployed to StartUp\Documents\ or 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 mobile app.

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

The basic features of this application are now implemented. As you worked in the Data Explorer, 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 ShoppingList component.
  2. In the Object Inspector, double-click the BeforeConnect event.
  3. Add the following code to this event handler:

Delphi:

procedure TForm1.SQLConnectionSQLiteBeforeConnect(Sender: TObject);
begin
  {$IF DEFINED(iOS) or DEFINED(ANDROID)}
  ShoppingList.Params.Values['ColumnMetadataSupported'] := 'False';
  ShoppingList.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.

C++:

void __fastcall TForm4::ShoppingListBeforeConnect(TObject *Sender) {
        ShoppingList->Params->Values["ColumnMetadataSupported"] = "False";
	ShoppingList->Params->Values["Database"] = System::Ioutils::TPath::Combine(System::Ioutils::TPath::GetDocumentsPath(), "shoplist.s3db");
}

The TPath record is declared in System.IOUtils library, so you need to add #include <System.IOUtils.hpp> in your header unit.

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 TSQLConnection component connects to the database and before the TSQLDataSet component connects to the table. Use the following steps:

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

Delphi:

procedure TForm1.ShoppingListAfterConnect(Sender: TObject);
begin
  ShoppingList.ExecuteDirect('CREATE TABLE IF NOT EXISTS Item (ShopItem  TEXT NOT NULL)');
end;

C++:

void __fastcall TForm4::ShoppingListAfterConnect(TObject *Sender) {
	ShoppingList->ExecuteDirect("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.
    Targets.png
  2. Choose either of the following commands:
    • Run > Run
    • Run > Run Without Debugging


iOS Android

IOSScreen.png

AndroidScreen.png


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

See Also

Personal tools
In other languages