Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Delphi Cookbook

You're reading from   Delphi Cookbook Recipes to master Delphi for IoT integrations, cross-platform, mobile and server-side development

Arrow left icon
Product type Paperback
Published in Jul 2018
Publisher
ISBN-13 9781788621304
Length 668 pages
Edition 3rd Edition
Languages
Tools
Arrow right icon
Authors (2):
Arrow left icon
Daniele Teti Daniele Teti
Author Profile Icon Daniele Teti
Daniele Teti
Daniele Spinetti Daniele Spinetti
Author Profile Icon Daniele Spinetti
Daniele Spinetti
Arrow right icon
View More author details
Toc

Table of Contents (12) Chapters Close

Preface 1. Delphi Basics FREE CHAPTER 2. Becoming a Delphi Language Ninja 3. Knowing Your Friends – The Delphi RTL 4. Going Cross-Platform with FireMonkey 5. The Thousand Faces of Multithreading 6. Putting Delphi on the Server 7. Linux Development 8. Riding the Mobile Revolution with FireMonkey 9. Using specific platform features 10. Delphi and IoT 11. Other Books You May Enjoy

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).
  1. 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.
  2. From DataExplorer, drag and drop onto the form the SALES table from the DELPHICOOKBOOK connection under the InterBase voice.
  3. Now, put on the form one TFDQuery, one TFDLocalSQL, and one TClientDataSet.
  4. It's time to rename components:
Old New
FDQuery1 LocalQuery
DataSource1 dsLocalQuery
ClientDataSet1 CustomersCDS
  1. If you performed all the steps correctly, you should be in this situation:
Figure 1.33: Form layout at design time
  1. Set the FDLocalSQL1 connection to FDConnection1.
  2. Select the DataSets property of FDLocalSQL1 and click the ellipsis button (...) to enter the editor.
  1. Click the Add New button on the editor twice to add two datasets to the DataSets collection.
  2. 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.
  3. 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
  1. 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;
  1. 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;
  1. Generate a FormCreate event handler and put in this code:
procedure TMainForm.FormCreate(Sender: TObject);
begin
PrepareDataSets;
end;
  1. 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;
  1. Run the application by hitting F9 (or by going to Run | Run).
  1. 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

You have been reading a chapter from
Delphi Cookbook - Third Edition
Published in: Jul 2018
Publisher:
ISBN-13: 9781788621304
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image