FireDAC.SQLite Sample
This sample demonstrates how to use FireDAC to work with SQLite databases.
Contents
Location
You can find the SQLite sample project at:
- Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
Object Pascal\Database\FireDAC\Samples\Getting Started\SQLite
- Subversion Repository:
- You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.
Description
The SQLite getting started sample shows how to use FireDAC with SQLite databases. This sample allows the user to do:
- Temporary connection definition at run time.
- Master-details relationship between datasets using TFDQuery.
- Custom SQLite functions using TFDSQLiteFunction.
- Management of SQLite database such as: encrypting and validating a database or creating database backup.
How to Use the Sample
- Navigate to the location given above and open
GettingStarted.dproj
. - Press F9 or choose Run > Run.
- Click on the Connection combo box and select
<Open database…>
. Open the following database: C:\Users\Public\Documents\Embarcadero\Studio\17.0\Samples\data\FDDemo.sdb. - Interact with the sample.
Files
File in Delphi | Contains |
---|---|
|
The project itself. |
|
The main form. |
Implementation
The sample implements the following features.
Creating temporary connection definition
The simplest way to configure connection to SQLite database at run time is to build a temporary connection definition:
with dbMain do begin
Close;
// create temporary connection definition
with Params do begin
Clear;
Add('DriverID=SQLite');
Add('Database=x:\path_to_database\db.sdb');
end;
Open;
end;
In the sample, the temporary definition is created when the <Open database…>
item is selected in the Connection combo box.
Master Details
In the demo database, the Categories and Products tables have one-to-many relation by CategoryID
field. The qryCategories.SQL property is set up as follows:
select * from Categories
In addition, the qryProducts.SQL property is set up as follows:
select *,
StockPrice(UnitPrice, UnitsInStock) as StockCost
from Products
where CategoryId = :CategoryID
Finally, the qryProducts.MasterSource property is set to dsCategories
, while the MasterFields property is set to CategoryID
. This creates a master-details relationship between the datasets.
StockPrice function is a custom function defined using the TFDSQLiteFunction component. The calculation is configured using a OnCalculate event:
sqlFunction.DriverLink := FDPhysSQLiteDriverLink1;
sqlFunction.FunctionName := 'StockPrice';
sqlFunction.ArgumentsCount := 2;
sqlFunction.Active := True;
sqlFunction.OnCalculate := sqlFunctionCalculate;
…
procedure TfrmGettingStarted.sqlFunctionCalculate(AFunc: TSQLiteFunction;
AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsInteger;
end;
The event occurs when fetching records from the database. It returns the calculated value.
Executing queries
Simple queries execution is demonstrated via ExecSQL method of TFDConnection.
Inserting a record providing parameters of various types:
dbMain.ExecSQL('insert into Categories(CategoryName, Description, Picture) ' +
'values(:N, :D, :P)', ['New category', 'New descr', $0334]);
Updating records:
dbMain.ExecSQL('update Products set UnitPrice = UnitPrice * :P1 + :P2 ' +
'where ProductID < 3', [Random(5), Random(3)]);
Deleting a record:
dbMain.ExecSQL('delete from Categories where CategoryName like :N', ['New category']);
Getting a scalar value from the database:
iID := dbMain.ExecSQLScalar('select MAX(CategoryID) from Categories');
Management of SQLite database
The sample shows how to perform several Management of databases such as:
- Database management
- Validating database
- Creating database backup
- Using custom collations and functions
All this Management of databases is done using the following components: TFDSQLiteBackup, TFDSQLiteSecurity, TFDSQLiteValidate, TFDSQLiteCollation and TFDSQLiteFunction.
This demo demonstrates managing database encryption, validating database and creating database backup:
with sqlSecurity do begin
Database := 'x:\path_to_database\db.sdb';
Password := 'p@ssword';
ToPassword := 'p@ssword_changed';
case rgSecActions.ItemIndex of
C_SET_PASSWORD: SetPassword;
C_REMOVE_PASSWORD: RemovePassword;
C_CHANGE_PASSWORD: ChangePassword;
end;
end;
…
with sqlValidate do begin
Database := 'x:\path_to_database\db.sdb';
Password := 'p@ssword';
case rgValActions.ItemIndex of
C_ANALYZE: Analyze;
C_CHECKONLY:
if not CheckOnly then
Memo1.Lines.Add('Database has problems !')
else
Memo1.Lines.Add('Database is valid');
C_SWEEP: Sweep;
end;
end;
…
with sqlBackup do begin
Database := 'x:\path_to_database\db.sdb';
DestDatabase := 'x:\path_to_database\db.backup';
Password := 'p@ssword';
DestPassword := 'dest_p@ssword';
Backup;
end;
Uses
- TFDQuery
- TFDConnection
- TFDSQLiteFunction
- TFDSQLiteBackup
- TFDSQLiteSecurity
- TFDSQLiteValidate
- TFDSQLiteCollation
See Also
- Defining Connection (FireDAC)
- SQLite support in RAD Studio
- Using SQLite with FireDAC
- Connect to SQLite database (FireDAC)
- Master-Detail Relationship (M/D)
- Mobile Tutorial: Using FireDAC and SQLite (iOS and Android)
- Tutorial: Connecting to a SQLite Database Using FireDAC
- SQLite Database Questions (FireDAC)
Samples
- FireDAC InterBase sample
- FireDAC MSAccess sample
- FireDAC MSSQL sample
- FireDAC MySQL sample
- FireDAC Firebird sample