Tutorial: Connecting to a SQLite Database from a VCL Application

From RAD Studio
Jump to: navigation, search

Go Up to Database and LiveBindings Tutorials


This tutorial is a simple Windows VCL application that shows how to establish a connection to an SQLite database and execute a simple query.

In this example, we use a database provided by Embarcadero, but you can use any SQLite database available to you. The database used in this example is located at:

  • Windows: C:\Users\Public\Documents\Embarcadero\Studio\23.0\Samples\Data\Employees.s3db.

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 table Employee.

Steps

  1. Select File > New > Windows VCL Application - Delphi , or Windows VCL Application - C++Builder.
  2. Add the following components to the form:
3. At this point, the form should look like this:
VLCSQLiteTutorialForm.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.
In Delphi:
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;
In C++:
void __fastcall TDemonstration::connectButtonClick(TObject *Sender)
{
 // Use double backslash in Windows Paths as the backslash character (\) must be escaped with another one 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.
In Delphi:
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;
In 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:
In Delphi:
procedure TDemonstration.ShowSelectResults();
var
  names: TStringList;
  i: Integer;
  currentField: TField;
  currentLine: string;
begin
  if not SQLQuery1.IsEmpty then
  begin
    SQLQuery1.First;
    names := TStringList.Create;
    try
      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;
    finally
      names.Free;
    end;
  end;
end;
Note: Do not forget to declare procedure ShowSelectResults();
In C++:
void __fastcall TDemonstration::ShowSelectResults()
{
	TStringList* names;
	TField* currentField;
	String currentLine;

	if (!SQLQuery1->IsEmpty()) {
	  SQLQuery1->First();
	  names = new TStringList
	  __try{
	    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();
	    }
	  }
	}
	  __finally {
	    names->Free();
	  }
}
Note: Do not forget to declare void __fastcall TDemonstration::ShowSelectResults(); in the Unit.h of your project.
6. After running the application and clicking the Connect button first, and then the Execute button, the application must look like this:
VLCSQLiteTutorialApp.png
Note: On Windows, in order to connect to an SQLite database, place the library sqlite3.dll in your system path and verify that sqlite3.dll can be found by your application.
  • On Windows 32-bit: C:\Windows\System32
  • On Windows 64-bit: C:\Windows\SysWOW64

Uses

See Also

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Athens/e/index.php?title=Tutorial:_Connecting_to_a_SQLite_Database_from_a_VCL_Application&oldid=276243"