FireDAC.TFDLocalSQL.xDBMS Sample
This sample demonstrates how to use TFDLocalSQL to implement a SQLite-based Local SQL engine for a cross-DBMS query application (queryable datasets from different databases).
Contents
Location
You can find the xDBMS sample project at:
- Start | Programs | Embarcadero RAD Studio 10 Seattle | Samples and then navigate to:
Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDLocalSQL\xDBMS
- Subversion Repository:
Description
The xDBMS sample shows you how use the TFDLocalSQL class to implement a cross-DBMS query application. To this end, each queryable dataset must be registered with the Local SQL engine using the DataSets collection property.
- Note: The Local SQL feature allows executing SQL commands, where the TDataSet descendants are used instead of the database tables.
How to Use the Sample
- Navigate to the location given above and open
xDBMS.dproj
. - Press F9 or choose Run > Run.
Files
File in Delphi | Contains |
---|---|
|
The project itself. |
|
The main form. |
Implementation
To setup Local SQL engine for a cross-DBMS query application, the sample has to perform the following steps:
- Set up an in-memory SQLite local connection (FDConnection1) as a query source from the Local SQL engine (FDLocalSQL1).
- Perform a local SQL query (FDQuery1).
- Connect the datasets (qOrders and qDetails) to the Local SQL engine.
To this end, the sample implement the following steps:
- Setup connection and query to "Orders" table in SQLite database and link the dataset to the Local SQL engine. To this end, the following components are needed:
- A TFDConnection object named cnSQLite.
- The ConnectionDefName property is set to
SQLite_Demo
.
- The ConnectionDefName property is set to
- A TFDQuery object named qOrders.
- The Connection property is set to
cnSQLite
. - The LocalSQL property is set to
FDLocalSQL1
. - The SQL property is set to
select * from Orders
.
- The Connection property is set to
- A TFDConnection object named cnSQLite.
- Setup connection and query to "Orders Details" table in MSAccess database and link the dataset to the Local SQL engine. To this end, the sample uses the following components:
- A TFDConnection object named cnMSAccess.
- The ConnectionDefName property is set to
Access_Demo
.
- The ConnectionDefName property is set to
- A TFDQuery object named qDetails.
- The Connection property is set to
cnMSAccess
. - The LocalSQL property set to
FDLocalSQL1
. - The SQL property set to
select * from "Order Details"
- The Connection property is set to
- A TFDConnection object named cnMSAccess.
- Setup SQLite in-memory connection. To this end, the sample sets up the following components:
- A TFDConnection object named FDCOnnection1.
- The DriverName property set to
SQLite
.
- The DriverName property set to
- A TFDLocalSQL object named FDLocalSQL1
- The Connection property set to
FDConnection1
. - The Active property set to
True
.
- The Connection property set to
- A TFDConnection object named FDCOnnection1.
- Execute a SELECT query on above datasets using:
- A TFDQuery object named FDQuery1.
- The Connection property is set to
FDConnection1
. - The SQL property set to
SELECT QO.*, QD.* FROM qOrders QO LEFT JOIN qDetails QD ON QO.OrderID = QD.OrderID ORDER BY QO.OrderID
- The Connection property is set to
- A TFDQuery object named FDQuery1.
- Note: All the configuration of these components is done using the Object Inspector at design time.
When you run the application, click on the Open/Close button in order to set the Active property of FDQuery1 to True
. It executes the SELECT query on the datasets registered with this Local SQL engine. Then, the records from the registered datasets are displayed on a TDBGrid component.
Uses
See Also
- FireDAC
- Defining Connection (FireDAC)
- Local SQL engine
- Using SQLite with FireDAC
- Executing Commands (FireDAC)
Samples
- FireDAC Getting Started SQLite sample
- FireDAC TFDLocalSQL in Memory DB sample
- FireDAC TFDLocalSQL MegaFMX sample