FireDAC.SQLite Sample

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

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

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

Files

File in Delphi	Contains
GettingStarted.dproj GettingStarted.dpr	The project itself.
fGettingStarted.pas fGettingStarted.fmx	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