Show: Object Pascal C++
Display Preferences

Tutorial: Connecting to a SQLite Database from a FireMonkey Desktop Application

From Appmethod Topics
Jump to: navigation, search

Go Up to Tutorials, SQLite and SQLMonitor


This tutorial is a simple FireMonkey Desktop application that shows how to establish a connection to a SQLite database and execute a simple query.

This tutorial uses a database provided by Embarcadero, but you can use any SQLite database available to you. The database used in this tutorial is located at:

  • Windows: C:\Users\Public\Documents\Embarcadero\Studio\14.0\Samples\Data\Employees.s3db.
  • Mac OS X: You can copy the database provided by Embarcadero to your Mac. Before deploying your application, remember to select OS X as your target platform, and check your Connection Profile.
Note: The SQLite program comes with Mac OS X from version 10.4 onwards and it is placed in /usr/bin. To create a database, you can do it just by going to Applications > Utilities > Terminal. Open a terminal window and type sqlite3<database_name>.

The database has one table named Employee and several tuples with fields. We are going to connect to the database and show all the tuples in the Employee table.

Steps

  1. Create a FireMonkey desktop application for either Object Pascal or C++:
    • File > New > FireMonkey Desktop Application - Object Pascal > HD FireMonkey Application
    • File > New > FireMonkey Desktop Application - C++ > HD FireMonkey Application.
  2. Activate a target platform on the Project Manager. FireMonkey desktop applications support the following platforms:
    • 32-bit Windows
    • 64-bit Windows
    • OS X
    Note: If the target platform that you want is missing from the Project Manager, right-click the Target Platform node to add this target platform to your project. For more information, see Activating and Configuring the Target Platform.
  3. Add the following components to your form:
    • 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.
    • A TSQLConnection control; from the Object Inspector, set the Driver property to Sqlite.
    • A TMemo control; from the Object Inspector, set the Name to outputMemo.
    • A TSQLQuery control; from the Object Inspector, set the SQLConnection property to SQLConnection1.
At this point, your form should look like this:
FMXSQLiteTutorialForm.png
4. Add the following code to the OnClick event handler for the connectButton.
Note: Replace "full_path_to_your_database_file" with the absolute path of your database file.
Object Pascal:
procedure TDemonstration.connectButtonClick(Sender: TObject);
begin
  SQLConnection1.Params.Add('Database=full_path_to_your_database_file');
  try
    // Establish the connection.
    SQLConnection1.Connected := true;
    executeButton.Enabled := true;
    outputMemo.Text := 'Connection established!';
  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;
end;
C++:
void __fastcall TDemonstration::connectButtonClick(TObject *Sender)
{
 // Use double backslash in Windows paths, as the backslash character (\) must be escaped with another backslash to be treated literally.
	SQLConnection1->Params->Add("Database=full_path_to_your_database_file");
	try
	{
		// Establish the connection.
		SQLConnection1->Connected = true;
		executeButton->Enabled = true;
		outputMemo->Text = "Connection established!";
	}
	catch (EDatabaseError& E)
	{
	   ShowMessage("Exception raised with message" + E.Message);
	}

}
5. Add the following code to the OnClick event handler for the executeButton.
Object Pascal:
procedure TDemonstration.executeButtonClick(Sender: TObject);
var
  query: String;
begin
  outputMemo.ClearSelection;
  // A random query
  query := 'SELECT * FROM Employee;';

  try
    // Assign the query to the object SQLQuery1.
    SQLQuery1.SQL.Text := query;
    SQLQuery1.Active := true;
  except
    on E: Exception do
      outputMemo.Text := 'Exception raised with message: ' + E.Message;
  end;
  // Show the results of the query in a TMemo control.
  ShowSelectResults();
end;
C++:
void __fastcall TDemonstration::executeButtonClick(TObject *Sender)
{
	String query;

	outputMemo->ClearSelection();
	// A random query
	query = "SELECT * FROM Employee;";
	try
	{
		// Assign the query to the object SQLQuery1.
		 SQLQuery1->SQL->Text = query;
		 SQLQuery1->Active = true;
	}
	catch (Exception& E)
	{
		outputMemo->Text = "Exception raised with message" + E.Message;
	 }
	// Show the results of the query in a TMemo control.
	 ShowSelectResults();
}
Here is the code for the ShowSelectResults procedure:
Object Pascal:
procedure TDemonstration.ShowSelectResults();
var
  names: TStringList;
  i: Integer;
  currentField: TField;
  currentLine: string;
begin
  if not SQLQuery1.IsEmpty then
  begin
    SQLQuery1.First;
    names := TStringList.Create;
    SQLQuery1.GetFieldNames(names);
    while not SQLQuery1.Eof do
    begin
      currentLine := '';
      for i := 0 to names.Count - 1 do
      begin
        currentField := SQLQuery1.FieldByName(names[i]);
        currentLine := currentLine + ' ' + currentField.AsString;
      end;
      outputMemo.Lines.Add(currentLine);
      SQLQuery1.Next;
    end;
  end;
end;
Note: Do not forget to declare procedure ShowSelectResults(); in the interface part of your source file.
C++:
void __fastcall TDemonstration::ShowSelectResults()
{
	TStringList* names;
	TField* currentField;
	String currentLine;

	if (!SQLQuery1->IsEmpty()) {
	SQLQuery1->First();
	names = new TStringList;
	SQLQuery1->GetFieldNames(names);
	while (!SQLQuery1->Eof)
		{
		currentLine = "";
		for (int i = 0; i < names->Count; ++i)
		{
			currentField = SQLQuery1->FieldByName(names->Strings[i]);
			currentLine = currentLine + " " + currentField->AsString;
		 }
		 outputMemo->Lines->Add(currentLine);
		 SQLQuery1->Next();
		}

	}

}
Note: Do not forget to declare void __fastcall TDemonstration::ShowSelectResults(); in the Unit.h file of your project.

After running your application and clicking the Connect button first, and then the Execute button, your application must look like this:

FMXSQLiteTutorialApp.png

Note: On Windows, in order to connect to a SQLite database, place the library sqlite3.dll in your system path and verify that sqlite3.dll can be found by your application.

  • On 32-bit Windows: C:\Windows\System32
  • On 64-bit Windows: C:\Windows\SysWOW64

Uses

See Also

Personal tools
In other languages