The FireDAC.TFDLocalSQL MegaFMX sample is divided in three tabs where each tab demonstrates single Local SQL usage case.
Each tab has dedicated SQLite in-memory connection established using the TFDConnection with only single parameter DriverID=SQLite. When no Database parameter value is specified then FireDAC SQLite driver opens an in-memory connection. It will be used as a Local SQL engine.
The TFDLocalSQL component for each page serves as a hub to Local SQL engine, allowing to register datasets with the Local SQL engine. Also there is a TMemo where the Local SQL query is defined before executing it using a TFDQuery component. The TFDLocalSQL.Connection and TFDQuery.Connection properties are set to the same SQLite in-memory TFDConnection.
Visual components such as the TGrid and the TBindNavigator are bind to the TFDQuery using Live Bindings.
Table of Contents
Location
You can find the MegaFMX sample project at:
- Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
- Object Pascal\DataBase\FireDAC\Samples\Comp Layer\TFDLocalSQL\MegaFMX.
- Subversion Repository:
- You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.
How to Use the Sample
- Navigate to the location given above and open:
- Delphi: LSQLMega.dproj
- Press F9 or choose Run > Run.
- Move to the different tabs.
- Click Open to open the connection and see the content on the grid.
Note: You can navigate through the records with the TDBNavigators.
Implementation
The sample is divided in tabs. The TFDMemTables are defined and filled with data by code.
CSV DB (dyna)
The first page represents the Comma-Separated Values (CSV) text file database with SQL query capabilities. The text files are stored in C:\Users\Public\Documents\Embarcadero\Studio\15.0\Samples\Object Pascal\Database\FireDAC\DB\Data folder.
The FDConnection1 provides SQLite in-memory connection. For that DriverName property is set to SQLite. The FDLocalSQL1.Connection and the FDQuery1.Connection are set to the FDConnection1.
Clicking Open, inserts the lines of the Memo1 on the FDQuery1 and executes the Local SQL query:
[crayon-6768f4aca1a08758480623/]Is shows the result on the Grid1 grid.
Heterogeneous Query (dyna)
The second page represents the ability to perform a heterogeneous SQL query, where several joined tables are prefixed by a schema name. There a schema name corresponds to a FireDAC connection definition name. The connection definitions are stored in the C:\Users\Public\Documents\Embarcadero\Studio\FireDAC\FDConnectionsDefs.ini file. So, the tables with different schema names will be obtained from the different (heterogeneous) database connections.
The basic setup is similar to first page. Each schema name is handled by a dedicated TFDLocalSQL component. For that the property TFDLocalSQL.SchemaName is used:
- FDLocalSQL2 = SQLite_Demo
- FDLocalSQL3 = Access_Demo.
The FDLocalSQL2 and FDLocalSQL3 OnGetDataSet event handlers are used to dynamically resolve a reference to an unknown table name and to provide a dataset with the data. For that event handler creates TFDQuery component and sets it ConnectionName to the schema name and SQL property to select * from table query:
[crayon-6768f4aca1a14492413295/]Clicking Open, inserts the lines of the Memo2 on the FDQuery2 and executes the SQL command.
Different DAC’s (stat)
The third page represents the ability to perform a Local SQL query to different data access components in the same SQL query.
Before executing a query the TfrmMain.Button3Click event handler statically registers 3 datasets:
- Data.Win.ADODB.TADOQuery to load region.
- Datasnap.DBClient.TClientDataSet to load employees.
- FireDAC.Comp.Client.TFDMemTable to load orders.
Finally inserts the lines of the Memo3 on the FDQuery3 and opens the connection.
The link below will redirect you to the original post of Embarcadero:
http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.TFDLocalSQL_MegaFMX_Sample