FireDAC.SQLite Sample

From RAD Studio Code Examples
Jump to: navigation, search

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

Location

You can find the SQLite sample project at:

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

See Also

Samples

Personal tools
Assistance