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 10 Seattle | Samples and then navigate to:
Object Pascal\Database\FireDAC\Samples\Getting Started\SQLite
- Subversion Repository:
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 (FireDAC)
- 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