Local SQL (FireDAC)

From Appmethod Topics
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)

General

The Local SQL feature allows executing SQL commands, where the TDataSet descendants are used instead of the DB tables. The Local SQL is based on the SQLite database and supports most of the SQLite SQL dialect. As table name, it uses the name of a TDataSet (Name property value) or, alternatively, a specified name. Each queryable dataset must be registered with TFDLocalSQL, the Local SQL engine.

All the read and write operations are performed through the TDataSet API with some extensions. The engine supports as FireDAC, as non-FireDAC datasets. Local SQL is more effective with FireDAC datasets. Optionally, for non-FireDAC datasets, the IFDPhysLocalQueryAdapter interface can be implemented as an adapter class and registered with the Local SQL engine.

The possible applications of Local SQL are:

  • Heterogeneous queries (queryable datasets have result sets from different DBs).
  • In-memory database (TFDMemTables are serving as the datasets).
  • Advanced offline mode. In this case, although the main DB is not accessible, an application is still able to perform the SQL queries.
  • Advanced DataSnap client (the data delivered by the DataSnap driver to the client can be queried locally).
  • Simplified migration. A developer can use the third-party TDataSet objects in an application, and use FireDAC API to work with these data sources.

Configuring

FireDAC uses the SQLite driver as the Local SQL engine. So first, the application should have an SQLite "local" connection. This can be a file-based read-write connection, such as an in-memory connection. We recommend using an in-memory connection. To set it up, execute the following steps:

The application should connect the datasets to the Local SQL engine. For that, perform the following operations:

  • Drop a TFDLocalSQL component on the form.
  • Set its Connection property to a local SQLite connection object.
  • Verify/assign a value to the dataset Name property. This step is required for datasets created dynamically in the code.
  • For a FireDAC dataset, set the TFDAdaptedDataSet.LocalSQL property to the TFDLocalSQL instance.
  • Or, for FireDAC and non-FireDAC datasets, use the TFDLocalSQL.DataSets collection to register the TDataSet and optional adapter with the Local SQL engine. Also, the DataSets collection allow specifying alternative dataset name.

Finally, activate the following objects:

  • Enable the local SQLite connection.
  • Enable the Local SQL engine by setting TFDLocalSQL.Active to True.
Note: If the application is using base datasets and local datasets connected to the same SQLite connection, the connection must be enabled explicitly before any dataset on this connection is opened/executed/prepared. Otherwise, an exception is raised.

Alternatively, datasets can be submitted to the Local SQL engine by using the TFDLocalSQL.OnGetDataSet event handler.

All associated datasets will be registered with the engine when both local connection and Local SQL engine are active. Associating a new dataset does not require reopening something, and it will be registered automatically. After its registration, a dataset is opened. A TFDMemTable must have a defined structure before it is opened. When the application performs a massive datasets registration, we recommended to deactivate the Local SQL engine before, and activate it after the task.

The application can set up TFDLocalSQL.SchemaName to a schema prefix, which will be used in the SQL queries. The application can use multiple TFDLocalSQLs connected to the same connection with or without schema name specified. But each TFDLocalSQL.SchemaName in a SQLite connection must be unique. A schema name allows organizing datasets into logical groups. When a schema name is specified, the dataset in a SQL command can be referenced as <dataset> or <schema>.<dataset>.

Optionally, the application sets TFDLocalSQL.MultipleCursors to False to avoid dataset cloning/copying. SQLite creates a cursor to execute a SQL command referring to a dataset. TDataSet API can keep only a single position. To overcome that, FireDAC clones FireDAC dataset by using TFDMemTable and copies a non-FireDAC dataset to a TFDMemTable. When the programmer is sure that a dataset will be used only a single time in each second, MultipleCursors can be set to False to improve the performance. We do not recommend seting MultipleCursors to False, when there are unidirectional datasets.

Querying

To perform a Local SQL query, the application should use TFDQuery/TFDCommand, and set Connection/ConnectionName to the local SQLite connection, that is also set for the TFDLocalSQL.Connection. Now, the application can execute SQL queries just as with non-Local SQL connections.

While a query is processing, a queryable dataset is disabled by DisableControls, and enabled when the processing is complete. To keep a dataset enabled, set TFDLocalSQL.DisableControls to False. Before a dataset is read, the TFDLocalSQL.OnOpenDataSet event handler is called.

The Local SQL engine supports SQLite SQL dialect with some limitations. The following SQL commands are not supported for the TDataSet data sources:

  • ALTER TABLE ... ADD COLUMN. Instead, change the dataset structure.
  • DROP TABLE. When a dataset is unlinked from the Local SQL engine, it is automatically dropped (not freed).
  • CREATE INDEX / DROP INDEX. Instead of the SQL indexes, use the dataset indexes.
  • CREATE TRIGGER / DROP TRIGGER. Instead of the triggers, use the dataset events.

The Local SQL engine does not support datasets with multiple result sets.

The Local SQL engine supports the INSERT/UPDATE/DELETE SQL commands as transactions and savepoints. Also, it transforms the corresponding SQL commands into TDataSet API calls.

The Local SQL engine supports INSERT OR REPLACE, but uses only primary key fields to find a record to replace, when a primary or unique key constraint is violated. Additionally, when only several fields are specified in INSERT OR REPLACE INTO tab (<field list>), the fields that are not specified get NULL values on updating.

Compatibility

The Local SQL engine uses TDataSet API with some extensions provided by the IFDPhysLocalQueryAdapter interface. FireDAC datasets implement this interface. Optionally, for non-FireDAC datasets, a developer can create a class implementing the interface, and assign its instance to the TFDLocalSQL.DataSets[..].Adapter property.

Also, to perform the Local SQL operations, a dataset must conform to the following requirements:

Operation Requirements
INSERT Mandatory:
  • The Append method.
  • The Post method.

Optionally:

  • PSGetKeyFields must return primary key fields.
UPDATE Mandatory:
  • The Edit method.
  • The Post method.
  • The Locate method.

Optionally:

  • PSGetKeyFields must return primary key fields.
DELETE Mandatory:
  • The Delete method.
  • The Locate method.

Optionally:

  • PSGetKeyFields must return primary key fields.
Sorting / ORDER BY Optionally:
  • If afIndexFieldNames in IFDPhysLocalQueryAdapter.Features, the IFDPhysLocalQueryAdapter.IndexFieldNames property is used for sorting the dataset.
Filtering / WHERE Optionally:
  • If afRanges in IFDPhysLocalQueryAdapter.Features, the IFDPhysLocalQueryAdapter.SetRange method is used to filter the dataset.
  • If afFilters in IFDPhysLocalQueryAdapter.Features, the Filter property is used, too.
Savepoints Mandatory:
  • afCachedUpdates, afSavePoints in IFDPhysLocalQueryAdapter.Features, the IFDPhysLocalQueryAdapter.Savepoint property is used.
Transactions Mandatory:
  • The PSStartTransaction method.
  • The PSEndTransaction method.


Example 1

The following is a heterogeneous query using SQL Anywhere and Oracle tables and FireDAC TFDQuerys:

// setup connection and query to "Orders" table in SQL Anywhere DB
FDConnection1.ConnectionDefName := 'ASA_Demo';
FDConnection1.Connected := True;
FDQuery1.Connection := FDConnection1;
FDQuery1.SQL.Text := 'select * from Orders';
// link dataset to Local SQL engine
FDQuery1.LocalSQL := FDLocalSQL1;

// setup connection and query to "Order Details" table in Oracle DB
FDConnection2.ConnectionDefName := 'Oracle_Demo';
FDConnection2.Connected := True;
FDQuery2.Connection := FDConnection2;
FDQuery2.SQL.Text := 'select * from "Order Details"';
// link dataset to Local SQL engine
FDQuery2.LocalSQL := FDLocalSQL1;

// setup SQLite in-memory connection
FDConnection3.DriverName := 'SQLite';
FDConnection3.Connected := True;
// link Local SQL to SQLite connection
FDLocalSQL1.Connection := FDConnection3;
FDLocalSQL1.Active := True;

// execute SELECT query on above datasets
FDQuery3.Connection := FDConnection3;
FDQuery3.SQL.Text := 'SELECT * FROM FDQuery1 LEFT JOIN FDQuery2 ON FDQuery1.OrderID = FDQuery2.OrderID';
FDQuery3.Active := True;


Example 2

The following is a heterogeneous query using ADO TADOQuerys and FireDAC TFDQuerys:

// setup connection and query to "Orders" table
ADOQuery1.SQL.Text := 'select * from Orders';
// link dataset to Local SQL engine
with FDLocalSQL1.DataSets.Add do begin
  DataSet := ADOQuery1;
  Name := 'Orders';
end;

// setup connection and query to "Order Details" table
ADOQuery2.SQL.Text := 'select * from "Order Details"';
// link dataset to Local SQL engine
with FDLocalSQL1.DataSets.Add do begin
  DataSet := ADOQuery2;
  Name := 'Order Details';
end;

// setup SQLite in-memory connection
FDConnection1.DriverName := 'SQLite';
FDConnection1.Connected := True;
// link Local SQL to SQLite connection
FDLocalSQL1.Connection := FDConnection1;
FDLocalSQL1.Active := True;

// execute SELECT query on above datasets
FDQuery1.Connection := FDConnection1;
FDQuery1.SQL.Text := 'SELECT * FROM Orders o LEFT JOIN "Order Details" od ON o.OrderID = od.OrderID';
FDQuery1.Active := True;

Example 3

See the following demos:

  • Cross-DBMS query: FireDAC\Samples\Comp Layer\TFDLocalSQL\xDBMS
  • In-memory database: FireDAC\Samples\Comp Layer\TFDLocalSQL\InMemDB

See Also