Introduction

AnyDAC for Delphi v 6.0.1 introduces new feature - Local SQL. The Local SQL allows to execute SQL's, where the TDataSet objects are used instead of the DB tables. The Local SQL is based on SQLite virtual table technology and supports most of the SQLite SQL dialect.

If you know or use xQuery component set, then you can consider Local SQL as a native, feature reach, effective and stable xQuery replacement for AnyDAC users.

Reason

So, what for you may need to query an ADQuery, when you can query the DB directly ? You can, when all data is homogenous and located inside of a single database system. When the data is heterogenous, then different approaches may be choosen:

• To use heterogenous data features of a DBMS, like Oracle Heterogenous Services, SQL Server Linked Server, etc.
• To use heterogenous query engines, like BDE Heterogenous Joins or xQuery.
• To export required data and import into a single DB, then query this DB.
• To migrate heterogenous environment to a homogenous one.

AnyDAC offers a simple, efficient and flexible approach. Which does not rely on any external components, does not require additional learning or configuration, and works with the live data.

Applications

Local SQL may be used in a range of applications, including:

• Heterogeneous queries. Queryable datasets have result sets from different DB's.
• In-memory database. TADMemTable's are serving as the datasets.
• Advanced offline mode. Although a DB is offlined, an application can perform the local SQL queries.
• Advanced DataSnap client. The data delivered by DataSnap driver may be queried locally.
• Simplified migration. Use the 3d party TDataSet's and use AnyDAC API to work with these data sources.
• Data sources not directly supported by AnyDAC. For example, the SQL Server Compact Edition has only OLEDB provider and AnyDAC does not support OLEDB. Using dbGo (ADO) components and Local SQL an AnyDAC application may work with SQL CE.

Usage

In-memory database

Lets create a simple in-memory DB. The setup will be similar for other applications. So:

• Drop TADMemTable. Set its name to Brands (or what else). And add (Code ftInteger) and (Name ftString(20)) fields to FieldDefs.
• Drop TADMemTable. Set its name to Models. Add (Brand ftInteger), (Name ftString(20)), (ModelYear ftInteger) and (Price ftCurrency) fields.
• Add the AnyDAC Local SQL engine - drop TADLocalSQL.
• Now we will connect ADMemTable's to Local SQL engine. Set Brands.LocalSQL to ADLocalSQL1, set Models.LocalSQL to ADLocalSQL1.
• Local SQL engine uses AnyDAC SQLite connection to register datasets and to perform SQL queries. So, drop TADConnection, set it DriverName to SQLite.
• The base setup is finished. Now we will do the tasks, standard for AnyDAC. Drop TADQuery and connect it to ADConnection1, drop TADGUIxWaitCursor and TADPhysSQLiteDriverLink.
• Now we can write Local SQL command. Double click on ADQuery1 and type:

select * from Brands B left join Models M on B.Code = M.Brand

• In the simple demo Brands is connected to TDataSource and TDBGrid on first page. Models is similar on the second page. And to open and refresh ADQuery1 we have:

procedure TfrmMain.PageControl1Change(Sender: TObject);
begin
  if PageControl1.ActivePage = TabSheet3 then begin
    ADQuery1.Close;
    ADQuery1.Open();
  end;
end;

Heterogeneous queries

The setup is similar to in-memory DB. But instead of TADMemTable we should use TADQuery, TADTable or TADStoredProc, which will query a remote DB data. In case of a remote table with big data volume, we will recommend to use TADTable with FetchOptions.LiveWindowFastFirst = True. So:

• Drop TADConnection. Set ADConnection1.ConnectionDefName to ASA_Demo (SQL Anywhere) or what else you would like.
• Drop TADQuery. Set it Connection to ADConnection1. Double click on TADQuery and type:

select * from "Orders"

• Drop another TADConnection. Set ADConnection2.ConnectionDefName to Oracle_Demo (Oracle Database) or what else.
• Drop another TADQuery. Set it Connection to ADConnection2. Double click on TADQuery and type:

select * from "Order Details"

• Add the AnyDAC Local SQL engine - drop TADLocalSQL.
• Now we will connect ADQuery's to Local SQL engine. Set ADQuery1.LocalSQL to ADLocalSQL1, set ADQuery2.LocalSQL to ADLocalSQL1.
• Local SQL engine uses AnyDAC SQLite connection to register datasets and to perform SQL queries. So, drop TADConnection, set ADConnection3.DriverName to SQLite.
• The base setup is finished. Now we will do the tasks, standard for AnyDAC. Drop TADQuery and connect it to ADConnection3, drop TADGUIxWaitCursor and TADPhysSQLiteDriverLink.
• Let write Local SQL command. Double click on ADQuery3 and type:

select * from ADQuery1 O left join ADQuery2 OD on O.OrderID = OD.OrderID

Or we can do that right in the code:

// 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;

Access to non-AnyDAC data sources

When an application needs to work with non-AnyDAC datasets, then Local SQL setup must be changed. Non-AnyDAC datasets does not have LocalSQL property, instead they should use TADLocalSQL.DataSets collection. Note, that it supports AnyDAC and non-AnyDAC datasets, and also allows to specifiy a dataset alias.

The following code sample shows how to use ADO queries in Local SQL:

// 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;

Data Editing

Local SQL engine supports INSERT / UPDATE / DELETE SQL commands, as transactions and savepoints. And transforms corresponding SQL commands into the TDataSet API calls. For example to move the data from Microsoft Access to Oracle database you can use the SQL, where ADQuery1 connected to Access and ADQuery2 connected to Oracle:

INSERT INTO ADQuery2 SELECT * FROM ADQuery1 WHERE OrderDate >= :P

To support savepoints, AnyDAC dataset must be in cached updates mode. Non-AnyDAC dataset must provide an adapter, giving access to cached updates like features.

To support transactions, a dataset must implement IProvideSupport interface, and PSStartTransaction and PSEndTransaction methods. This allows to implement distributed transactions. For example:

ADConection1.BeginTransaction;
try
  ADConection1.ExecSQL('INSERT INTO OracleQuery VALUES (...)');
  ADConection1.ExecSQL('INSERT INTO MSSQLQuery VALUES (...)');
  ADConection1.Commit;
except
  ADConection1.Rollback;
  raise;
end;

There either both records will be inserted into Oracle and SQL Server DB's, each in a context of a DB transaction. Either both DB transactions will be rolled back.

Summary

Local SQL allows AnyDAC developers to work with non-standard data sources as with standard DB tables. That gives new cool possibilities right out of the box and does not require any additional libraries.

More to read and see:

• Documentation - http://www.da-soft.com/anydac/docu/Local_SQL.html
• Cross DBMS query demo - AnyDAC\Samples\Comp Layer\TADLocalSQL\xDBMS 
• In-memory database demo - AnyDAC\Samples\Comp Layer\TADLocalSQL\InMemDB