FireDAC.SQLite Sample

From Appmethod Code Examples
Jump to: navigation, search

This sample demonstrates how to use FireDAC to work with SQLite databases.


You can find the SQLite sample project at:


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

  1. Navigate to the location given above and open GettingStarted.dproj.
  2. Select the iOS platform in the Project Manager.
  3. Press F9 or choose Run > Run.
  4. 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.
  5. Interact with the sample.


File in Object Pascal Contains


The project itself.


The main form.


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
  // create temporary connection definition
  with Params do begin

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);
  AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsInteger;

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;with sqlValidate do begin
  Database := 'x:\path_to_database\db.sdb';
  Password := 'p@ssword';
  case rgValActions.ItemIndex of
  C_ANALYZE: Analyze;
    if not CheckOnly then
       Memo1.Lines.Add('Database has problems !')
       Memo1.Lines.Add('Database is valid');
  C_SWEEP: Sweep;
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';


See Also


Personal tools