The Local SQL feature allows to execute 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 a table name it uses name of a TDataSet (Name property value) or alternatively specified name. Each queryable dataset must be registered with TADLocalSQL - the Local SQL engine. 

All read and write operations are performing through the TDataSet API with some extensions. The engine supports as AnyDAC, as non-AnyDAC datasets. Local SQL is more effective with AnyDAC datasets. For non-AnyDAC datasets optionally may be implemented the IADPhysLocalQueryAdapter interface as an adapter class and registered it with the Local SQL engine. 

The Local SQL possible applications are:

• Heterogeneous queries. Where queryable datasets have result sets from different DB's.
• In-memory database. There TADMemTable's are serving as the datasets.
• Advanced offline mode. There although the main DB is not accessible, an application still can perform the SQL queries.
• Advanced DataSnap client. There the data delivered by DataSnap driver to the client may be queried locally.
• Simplified migration. A developer may use the 3d party TDataSet objects in application and use AnyDAC API to work with these data sources.

AnyDAC uses SQLite driver as the Local SQL engine. So first, application should have an SQLite "local" connection. That may be as a file-based read-write connection, as an in-memory connection. We recommend to use in-memory connection. To setup it:

• drop TADPhysSQLiteDriverLink component on a form;
• drop TADConnection;
• set it DriverName to SQLite.

Then application should connect the datasets to the Local SQL engine. For that:

• drop TADLocalSQL component on a form;
• set it Connection property to a local SQLite connection object;
• verify / assign value to a dataset Name property. Required step for the datasets created dynamically in the code;
• for an AnyDAC dataset set TADAdaptedDataSet.LocalSQL property to the TADLocalSQL instance;
• or for an AnyDAC and non-AnyDAC dataset use TADLocalSQL.DataSets collection to register TDataSet and optional adapter with Local SQL engine. Also DataSets collection allow to specify alternative dataset name.

Finally activate objects:

• activate local SQLite connection;
• activate Local SQL engine by setting TADLocalSQL.Active to True.

Note, if application is using base datasets and local datasets connected to the same SQLite connection, then the connection must be activated explicitly before any dataset on this connection will be opened / executed / prepared. Otherwise exception will be raised. 

Alternatively datasets may be submitted to Local SQL engine using TADLocalSQL.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 to reopen something, and it will be registered automatically. When a dataset is registered, then it will be opened. A TADMemTable must have a defined structure before it will be opened. When application performs a massive datasets registration, it is recommended to deactivate Local SQL engine before and activate it after the task. 

The application may setup TADLocalSQL.SchemaName to a schema prefix, which will be used in the SQL queries. Application may use multiple TADLocalSQL's connected to the same connection with or without schema name specified. But each TADLocalSQL.SchemaName in a single SQLite connection must be unique. A schema name allows to organize datasets into the logical groups. When a schema name is specified then dataset in a SQL command may be referenced as <dataset> or <schema>.<dataset>. 

The application may optionally set TADLocalSQL.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 AnyDAC clones AnyDAC dataset using TADMemTable. And copies a non-AnyDAC dataset to a TADMemTable. When programmer is sure, that a dataset will be used only a single time in each moment of the time, then MultipleCursors may be set to False to improve performance. We do not recommend to set MultipleCursors to False, when there are unidirectional datasets. 

To perform a Local SQL query, application should use TADQuery / TADCommand. And set Connection / ConnectionName to the local SQLite connection, which is set also for the TADLocalSQL.Connection. Now application can execute SQL queries, the same as with non-Local SQL connections. 

While a query is processing a queryable dataset is disabled by DisableControls, and enabled at end of processing. To keep a dataset enabled, set TADLocalSQL.DisableControls to False. Before a dataset will be read the TADLocalSQL.OnReadDataSet event handler will be called. 

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 will be unlinked from Local SQL engine, then it will be dropped automatically (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.

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

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

The Local SQL engine uses TDataSet API with some extensions provided by IADPhysLocalQueryAdapter interface. AnyDAC datasets implement this interface. For non-AnyDAC datasets a developer optionally may create a class implementing the interface, and assign it instance to the TADLocalSQL.DataSets[..].Adapter property. 

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

Using SQLite with AnyDAC, Executing Command, TADLocalSQL

Heterogeneous query using SQL Anywhere and Oracle tables and AnyDAC TADQuery's: 

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

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

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

// execute SELECT query on above datasets
ADQuery3.Connection := ADConnection3;
ADQuery3.SQL.Text := 'SELECT * FROM ADQuery1 LEFT JOIN ADQuery2 ON ADQuery1.OrderID = ADQuery2.OrderID';
ADQuery3.Active := True;

Heterogeneous query using ADO TADOQuery's and AnyDAC TADQuery's: 

// setup connection and query to "Orders" table
ADOQuery1.SQL.Text := 'select * from Orders';
// link dataset to Local SQL engine
with ADLocalSQL1.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 ADLocalSQL1.DataSets.Add do begin
  DataSet := ADOQuery2;
  Name := 'Order Details';
end;

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

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

See demos: 

- Cross DBMS query - AnyDAC\Samples\Comp Layer\TADLocalSQL\xDBMS 

- In-memory database - AnyDAC\Samples\Comp Layer\TADLocalSQL\InMemDB