Book Image

Delphi Cookbook, - Third Edition

By : Daniele Spinetti, Daniele Teti
Book Image

Delphi Cookbook, - Third Edition

By: Daniele Spinetti, Daniele Teti

Overview of this book

Delphi is a cross-platform integrated development environment (IDE) that supports rapid application development on different platforms, saving you the pain of wandering amid GUI widget details or having to tackle inter-platform incompatibilities. Delphi Cookbook begins with the basics of Delphi and gets you acquainted with JSON format strings, XSLT transformations, Unicode encodings, and various types of streams. You’ll then move on to more advanced topics such as developing higher-order functions and using enumerators and run-time type information (RTTI). As you make your way through the chapters, you’ll understand Delphi RTL functions, use FireMonkey in a VCL application, and cover topics such as multithreading, using aparallel programming library and deploying Delphi on a server. You’ll take a look at the new feature of WebBroker Apache modules, join the mobile revolution with FireMonkey, and learn to build data-driven mobile user interfaces using the FireDAC database access framework. This book will also show you how to integrate your apps with Internet of Things (IoT). By the end of the book, you will have become proficient in Delphi by exploring its different aspects such as building cross-platforms and mobile applications, designing server-side programs, and integrating these programs with IoT.
Table of Contents (12 chapters)

The amazing TFDTable – indices, aggregations, views, and SQL

Without question, the software industry is a data-driven environment. All of the IT industry runs on data (we are in the big data era, guys!)—customers, orders, purchases, billings; every day, our applications transform in data interactions between them. Undoubtedly, data is the wellspring of all IT businesses, so we must choose the best programs to interact with it, and fortunately with Delphi we are safe, we have FireDAC.

FireDAC is a unique set of Universal Data Access Components for developing multi-device database applications for Delphi and C++Builder. Here are some features that make this framework special:

  • Cross-platform support
  • You can use FireDAC on Windows, macOS, Android, iOS, and Linux applications
  • Drivers for almost every major relational database, both commercial and open source

In the 90s, the catchphrase in software development was developing database applications. Delphi was the master, thanks to the way it was designed (TDataSet interface, Data Module, and ClientDataSet) and its frameworks (Borland Database Engine). In the spring of 2013, Embarcadero acquired AnyDAC and re-branded it as FireDAC. Now Delphi Database Developers have made an unrivaled framework available again.

Getting ready

The TFDTable component implements a dataset that works with a single database table. What makes this component amazing is a set of additional features—filtering, indexing, aggregation, cached updates, and persistence.

In this recipe, we'll see some of these at work: how to configure an TFDTable, how to manage the indexes to sort an associated grid, and how to collect new information via aggregates.

This recipe uses the DELPHICOOKBOOK database, an InterBase DB prepared for the last three recipes of this chapter. To speed up the mechanisms, I suggest adding it to the FireDAC connections in the Data Explorer:

  1. Open Delphi.
  2. Go to the Data Explorer tab.
  3. Open the FireDAC section.
  4. Open the InterBase section.
  5. Right-click on it.
  6. Click Add New Connection.
  7. In the opened window, enter the name, DELPHICOOKBOOK.
  8. Complete the configuration with this data:
    • Username: sysdba
    • Password: masterkey
    • Database: Choose the path of the database in your filesystem (the database is under the data folder)

Follow the same steps to register the EMPLOYEE database; you can find it at C:\Users\Public\Documents\Embarcadero\Studio\19.0\Samples\data\employee.gdb.

How to do it...

Let's look at the following steps:

  1. Create a new VCL application by selecting File | New | VCL Forms Application.
  1. Put a DBNavigator (aligned to the top), a DBGrid (aligned to the client), a DataSource, and a PopUpMenu into the form.
  2. Set the DataSource property of DBGrid1 to DataSource1.
  3. Select the EMPLOYEE connection in the Data Explorer and then drag and drop it on the form to generate the EmployeeConnection.
  4. Put a TFDTable in the form and rename it to SalesTable.
  5. The connection property of SalesTable is automatically set to EmployeeConnection.
  6. Set the DataSet property of DataSource1 to SalesTable.
  7. To choose the Table, you have to expand the Table property combobox and select SALES:
Figure 1.25: SalesTable in the Object Inspector
  1. If you performed all the steps correctly, you should be in this situation:
Figure 1.26: Form at design time
  1. Declare the CreateIndexes procedure under the private section of the form and implement it with the following code:
procedure TMainForm.CreateIndexes;
var
LCustNoIndex: TFDIndex;
begin
LCustNoIndex := SalesTable.Indexes.Add;
LCustNoIndex.Name := 'MyCustNoIdx';
LCustNoIndex.Fields := 'Cust_No';
LCustNoIndex.Active := true;
end;
  1. Declare the CreateAggregates procedure under the private section of the form and implement it with the following code:
procedure TMainForm.CreateAggregates;
begin
with SalesTable.Aggregates.Add do
begin
Name := 'CustomerTotal';
Expression := 'SUM(TOTAL_VALUE)';
GroupingLevel := 1;
Active := true;
IndexName := 'MyCustNoIdx';

end;

with SalesTable.Aggregates.Add do
begin
Name := 'CustomerMax';
Expression := 'MAX(TOTAL_VALUE)';
GroupingLevel := 1;
Active := true;
IndexName := 'MyCustNoIdx';

end;

with SalesTable.Aggregates.Add do
begin
Name := 'CustomerLastDate';
Expression := 'MAX(ORDER_DATE)';
GroupingLevel := 1;
Active := true;
IndexName := 'MyCustNoIdx';

end;
end;
  1. Now, we are able to set up the SalesTable component. So, implement the OnCreate event handler for the form and include this code:
procedure TMainForm.FormCreate(Sender: TObject);
begin
SalesTable.Active := false;
CreateIndexes;
CreateAggregates;
SalesTable.IndexName := 'MyCustNoIdx';
// index activated
SalesTable.IndexesActive := true;
// aggregates activated
SalesTable.AggregatesActive := true;
SalesTable.Active := true;
end;
  1. Now, we have to implement DBGrid1TitleClick to perform the right sorting method when the user clicks on a specific title:
procedure TMainForm.DBGrid1TitleClick(Column: TColumn);
begin

// if reset the column caption of LastColumnClickIndex, because index could be change...
if FLastColumnClickIndex > 0 then
DBGrid1.Columns[FLastColumnClickIndex].Title.Caption :=
DBGrid1.Columns[FLastColumnClickIndex].FieldName;

// if the order is descending set the IndexFieldNames to ''.
if SalesTable.IndexFieldNames = (Column.Field.FieldName + ':D') then
begin
Column.Title.Caption := Column.Field.FieldName;
SalesTable.IndexFieldNames := '';
end
// if the order is ascending set it to descending
else if SalesTable.IndexFieldNames = Column.Field.FieldName then
begin
SalesTable.IndexFieldNames := Column.Field.FieldName + ':D';
Column.Title.Caption := Column.Field.FieldName + ' ▼';
end
// if no order is specified I'll use ascending one
else
begin
SalesTable.IndexFieldNames := Column.Field.FieldName;
Column.Title.Caption := Column.Field.FieldName + ' ▲';
end;

// set last column index
FLastColumnClickIndex := Column.Index;

end;
  1. It's time to insert the aggregates. The goal is to show some aggregated information through a simple ShowMessage procedure. Add a new menu item to PopupMenu1, rename it to Customer Info, and implement the OnClick event with the following code:
procedure TMainForm.CustomerInfoClick(Sender: TObject);
var
LOldIndexFieldNames: string;
begin

// i use LOldIndexFieldNames to reset the index to last user choice
LOldIndexFieldNames := SalesTable.IndexFieldNames;
DBGrid1.Visible := false;
// the right index for aggregate
SalesTable.IndexName := 'MyCustNoIdx';

// show some customer info
ShowMessageFmt('The total value of order of this customer is %m. ' +
'The max value order of this customer is %m. ' + 'Last order on %s ',
[StrToFloat(SalesTable.Aggregates[0].Value),
StrToFloat(SalesTable.Aggregates[1].Value),
DateTimeToStr(SalesTable.Aggregates[2].Value)]);

SalesTable.IndexFieldNames := LOldIndexFieldNames;
DBGrid1.Visible := true;
end;
  1. Run the application by hitting F9 (or by going to Run | Run):
Figure 1.27: Amazing FDTable at startup

  1. Click on the Total Value column twice in the descending order:
Figure 1.28: Descending order on total_value field
  1. Right-click on the first record to bring up the pop-up menu, then click on Customer Info:
Figure 1.29: Aggregates in action

How it works...

The core concepts of this recipe are enclosed in the DBGrid1TitleClick and CustomerInfoClick functions.

In the first procedure, we used the IndexFieldNames property to generate a temporary index to perform sorting based on a field related to the DBGrid column clicked, and also applying a graphical change to the column to better understand the ordering.

A temporary index accepts more than one field, so if you want to sort data by several fields you can do it by separating fields, with a semicolon.
In addition, you can also specify the sort order, such as ascending or descending, adding the suffixes :A for ascending and :D for descending.

In the second procedure, we used Aggregate to report some customer info:

  • Total Value: This represents the total amount of all orders
  • Max Value: This represents the order with the maximum amount
  • Last Order: This represents the last date order

Aggregate are created in the CreateAggregates procedure. Here is some more information about the properties used:

  • Expression property: This defines the expression to be used to calculate the aggregate.
    • GroupingLevel property: This defines the number of indexed fields to use for grouping. By default, its value is set to 0 (no fields and no grouping; all records in a dataset).
  • IndexName property: This defines the name of the index to use for grouping. If none is specified, it will use the IndexName property of DataSet.

There's more...

The expression engine provided by FireDAC is a powerful engine used for filtering, indexing, and calculated fields. For more information on how to write powerful expressions, go here: http://docwiki.embarcadero.com/RADStudio/en/Writing_Expressions_(FireDAC).

More information about aggregate and calculated fields can be found here: http://docwiki.embarcadero.com/RADStudio/en/Calculated_and_Aggregated_Fields_(FireDAC).