Data integration made easy – TFDLocalSQL

As Wikipedia says:

"Data integration involves combining data residing in different sources and providing users with a unified view of them."

Traditionally, information must be stored in a single database with a single schema, but many organizations store information on multiple databases, so they need a way to retrieve data from different sources and assemble it in a unified way.

FireDAC provides a component that permits you to execute SQL statements against any dataset: TFDLocalSQL.

Getting ready

Let's imagine that a company wants to gain some business intelligence on their data. The marketing department, to allow special customers to take advantage of a special promotion, wants a list of customers who have spent at least a certain sum in at least one order.

The problem is that customers are provided in XML format and sales are stored in a database table. We want to achieve the aim of executing heterogeneous queries—XML and database tables. Let's go!

Ensure you have followed the instructions in the The Amazing FDTable recipe on database preparation. If you haven't, go to it and set up your environment.

How to do it...

Let's look at the following steps:

1. Create a new VCL application by selecting File | New | VCL Forms Application.
2. Put a TFDConnection on the form and set its DriverName to SQLite (because SQL Local uses SQLLite in its engine).

3. Place on the form a DBEdit (aligned to the top), a TButton (aligned to the top), a DBNavigator (aligned to the top), a DBGrid (aligned to the client), and a DataSource. Set the DataSource property of DBNavigator1 and DBGrid1 to DataSource1.
4. From DataExplorer, drag and drop onto the form the SALES table from the DELPHICOOKBOOK connection under the InterBase voice.
5. Now, put on the form one TFDQuery, one TFDLocalSQL, and one TClientDataSet.
6. It's time to rename components:

Old	New
FDQuery1	LocalQuery
DataSource1	dsLocalQuery
ClientDataSet1	CustomersCDS

7. If you performed all the steps correctly, you should be in this situation:

Figure 1.33: Form layout at design time

8. Set the FDLocalSQL1 connection to FDConnection1.
9. Select the DataSets property of FDLocalSQL1 and click the ellipsis button (...) to enter the editor.

10. Click the Add New button on the editor twice to add two datasets to the DataSets collection.
11. Select the first dataset in the collection and set the DataSet property to SalesTable; set the Name property to Sales in order to use the Sales identifier in SQL to refer to this dataset.
12. Select the second dataset in the collection and set the DataSet property to CustomersCDS; set the Name property to Customers in order to use the customers identifier in SQL to refer to this dataset:

Fig 1.34: FDLocalSQL DataSets editor collection

13. In the private section of the form, declare a procedure named OpenDataSets and put in the following code:

procedure TMainForm.OpenDataSets;
begin
  SalesTable.Open();
  CustomersCDS.Active := True;
end;

14. In the private section of the form, declare a procedure named PrepareDataSets and put in the following code:
    

procedure TMainForm.PrepareDataSets;
begin
  CustomersCDS.FileName :=
    'C:\Users\Public\Documents\Embarcadero\Studio\19.0\Samples\Data\customer.xml';
  LocalQuery.SQL.Text := 'select distinct c.* from Customers c ' +
    ' JOIN Sales s on cast (s.CUST_NO as integer) = c.CustNo ' +
    ' where s.total_value > :v order by c.CustNo ';
end;

15. Generate a FormCreate event handler and put in this code:

procedure TMainForm.FormCreate(Sender: TObject);
begin
  PrepareDataSets;
end;

16. We have almost finished; now, we need to put everything together. Generate the Button1 Click event handler and put in this code:

procedure TMainForm.btnExecuteClick(Sender: TObject);
var
  LAmount: Integer;
begin

  // ensure amount is an integer
  if not TryStrToInt(Edit1.Text, LAmount) then
  begin
    ShowMessage('Amount must be integer...');
    exit;
  end;

  LocalQuery.Close;
  OpenDataSets;
  // apply user data
  LocalQuery.ParamByName('v').AsInteger := LAmount;
  // Execute the query through eterogeneous sources
  LocalQuery.Open;
end;

17. Run the application by hitting F9 (or by going to Run | Run).

18. Try different amounts to filter the different customers:

Figure 1.35: Data integration in action

Following image is an example showing different amounts to filter the different customers:

Figure 1.36: Another example of Data integration in action

How it works...

The code of this recipe is quite simple but I want to explain it anyway.

Our data is stored in two different dataset—SalesTable, which refers to a database table, and CustomerCDS, which refers to an XML file. By setting the FDConnection1, FDLocalSQL1, and LocalQuery components as explained in the previous How to do it... section, it is possible to have an FDQuery component (LocalQuery) where we write the query using different heterogeneous sources:

select distinct c.* from Customers c
JOIN Sales s on cast (s.CUST_NO as integer) = c.CustNo
where s.total_value > :v order by c.CustNo

When you click on the Execute button, preliminary checks are carried out on the validity of the data entered, then the query in LocalQuery is performed and the LocalQuery dataset is populated with data... from heterogeneous sources! This is a really great feature!

There's more...

The Local SQL is based on the SQLite database and supports most of the SQLite SQL dialect.

All the read and write operations are performed through the TDataSet API with some extensions, which means that FireDAC performs the operations by converting SQL into dataset calls. This is the reason why you can execute SQL statements against any dataset—FDQuery, IBQuery, ClientDataSet, third-party components, and so on.

The possible applications of Local SQL are (from Embarcadero DocWiki):

• Heterogeneous queries: Queryable datasets have result sets from different DBs
• In-memory database: TFDMemTables serve the datasets
• Advanced offline mode: In this case, although the main DB is not accessible, an application is still able to perform 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 can use a FireDAC API to work with these data sources

Here some important notes (from Embarcadero DocWiki):

• 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 intoTDataSet API calls.
• The Local SQL engine supports INSERT/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/REPLACE INTO tab (<field list>), the fields that are not specified get null values on updating.
• The Local SQL engine uses the 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.

See also

• For more information, take a look at http://docwiki.embarcadero.com/RADStudio/en/Local_SQL_(FireDAC)