Tutorial: Connecting to a SQLite Database from a VCL Application
Go Up to Database and LiveBindings Tutorials
This tutorial is a simple VCL Form 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\17.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
- Select File > New > VCL Forms Application - Delphi , or VCL Forms Application - C++Builder.
- Add the following components to the form:
- Two TButton controls; from the Object Inspector, set the Name properties of the buttons to
executeButton
andconnectButton
, and their Caption properties toExecute
andConnect
, 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
.
- Two TButton controls; from the Object Inspector, set the Name properties of the buttons to
- 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();
- Note: Do not forget to declare
- 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,
- Note: Do not forget to declare
- 6. After running the application and clicking the Connect button first, and then the Execute button, the application must look like this:
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
- Data.DB.TDataSet
- Data.DB.TField
- System.Classes.TStringList
- Data.SqlExpr.TSQLConnection
- Vcl.StdCtrls.TButton
- Vcl.StdCtrls.TMemo
- Vcl.Controls.TControl.OnClick