SQLite becomes popular for working with light weight embedded, mobile, IoT, and desktop applications. Do you want your Delphi and C++ Builder Applications to connect with SQLite Database? How to start? Don’t worry, FireDAC offers robust components to connect with MySQL Database.
FireDAC.SQLLite Sample app demonstrates how to use FireDAC to work with SQLite Database.
You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.
Components used in the Sample App:
- TFDQuery : To execute SQL queries, browse the result sets, and edit the result set records.
- TFDPhysSQLiteDriverLink: To link the SQLite driver to an application and set it up. In general, it is enough to only include the FireDAC.Phys.SQLite unit into your application uses clause. The TFDPhySQLiteDriverLink component can be used to specify: The VendorHome – the SQLite installation root folder. The VendorLib – the name and the optional path to the SQLite client library.
- TFDConnection : To establish a connection to a DBMS and to manage associated datasets.
- TFDSQLLiteFunction : To register a custom SQLite function. A registered function then may be used in any place in a SQL command, where an expression may be used.
- TFDSQLiteBackup : Add a backup, restore, copy database capability to an application.
- TFDSQLiteSecurity : To add SQLite database security management capabilities to an application.
- TFDSQLiteValidate : Add a database validation capability to an application. This is a programmatic method to invoke the specific SQLite PRAGMA and other commands.
- TFDSQLiteCollation : To register a custom SQLite collation. A registered collation then may be used in any place in a SQL command where a collation may be specified.
- And some of the components, like TDBGrid,TDBComboBox, TFDGUIxWaitCursor1,TFDGUIxLoginDialog1,TFDGUIxErrorDialog1.
Implementation Details:
The simplest to configure connection to SQLite database at run-time is to build a temporary connection definition: In the sample, the temporary definition is created when the <Open database…> item is selected in the Connection combo box. Open the following database: C:\Users\Public\Documents\Embarcadero\Studio\21.0\Samples\data\FDDemo.sdb.
In the demo database, the Categories and Products tables have one-to-many relation by CategoryID field. Mention the query to the qryCategories.SQL property and qryProducts.SQL property.
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:The event occurs when fetching records from the database. It returns the calculated value.Simple queries execution is demonstrated via ExecSQL method of TFDConnection.
Managing the SQLite Database : The sample also 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: Check out the full article in the DocWiki about the FireDAC SQLite Sample.
