Re-Introduction to FireDAC Data Access (3) by Embarcadero Japan

[emailx45]

Re-Introduction to FireDAC Data Access (3)
Embarcadero Japan - 16/Jun/2020

Спойлер: Content - 1st part

[SHOWTOGROUPS=4,20]
Exercises for creating an application that updates data
In this blog, I will explain the basic usage of FireDAC for those who have used Delphi / C++Builder to some extent.

The third theme

• FireDAC dataset overview
• Specific example of using data set
• Exercises for creating an application that updates data

Last time , I created a simple application that actually used FireDAC. We used a dataset to display the data. I used this dataset to perform the basic data access functions of querying the database and holding the result set, but I didn't go into the details. So, in this blog, I would like to explain the details of the FireDAC data set.

Datasets are one of the most important items to keep in mind when developing applications that access data.

FireDAC dataset overview
The data set is, in short, the concept of a "container" for holding the records (data) retrieved from the database in memory.

A Delphi/C++Builder dataset maps and holds records from single or multiple tables retrieved from a database into a manageable data format (in Delphi/C++Builder). And that data can all be represented in rows and columns.

However, the dataset is not just a container, it provides processes involving complicated operations such as organizing records (data) and manipulating data as an encapsulated dataset object (class).

The Delphi/C++Builder dataset object (hereafter dataset) has a class named "TDataSet" that has the basic properties, events and methods, which is the base class for all datasets. Become.

Applications that access databases created with Delphi / C++Builder all use datasets derived from TDataSet. This means that whatever data set you use, the method determined by TDataSet can be used, greatly simplifying data access by the data set.

The FireDAC dataset is also derived from the TDataSet class and has been extended to a dataset with FireDAC-specific features.

The FireDAC dataset inheritance diagram is shown below.




The main types of datasets that are frequently used in FireDAC are as follows.

Data set name	Use	Characteristic
TFDTable	Data set dealing with a single table	BDE equivalent to TTable
TFDQuery	Data set that handles SQL statement execution and result set(s)	Execution performance is better than using TFDTable
TFDMemTable	In-memory dataset	Can be used as a local DB like TClientDataSet

*Details of TFDMemTable will be introduced in another part of this blog series.

The general characteristics of FireDAC datasets are that they are highly compatible with BDE datasets and are easy to migrate in terms of code level and functionality.

However, please note that there are some precautions regarding the transition from BDE TTable.

FireDAC provides TFDTable which is compatible with TTable for migration from BDE, but there are many restrictions regarding the use of TFDTable, and extra overhead may occur especially when acquiring data. This can cause performance issues. For details, Для просмотра ссылки Войди или Зарегистрируйся see here.

TFDQuery is recommended as a candidate for a replacement dataset from TTable. TFDQuery has the same properties and methods as BDE's TTable and has better execution performance than TFDTable, so TFDQuery is the best candidate for replacement.

*Tips for improving the execution performance of FireDAC will be introduced in another part of this blog series.

Now let's look at a concrete example of using the FireDAC dataset.

Specific example of using data set
Before we do the exercise of creating an application in the next section, we will explain some basic usages by using the TFDQuery data set as an example.

If you already know how to use datasets, skip this section and do the exercises.

Data acquisition and reference
You can get the data (result set) by executing the Open method of the data set.

Delphi:
FDQuery1.Open('select * from Employee');

　Or
FDQuery1.SQL.Text:='select * from Employee';
FDQuery1.Open();

C++Builder:
FDQuery1->Open("select * from Employee");

　Or
FDQuery1->SQL->Text = "select * from Employee";
FDQuery1->Open();

TFDQuery has SQL properties. If you specify the SQL statement before opening, you can execute the Open method without parameters.

You can also execute a query with parameters. The following is an example.

Delphi:
FDQuery1.SQL.Text:='select * from Employee where EMP_NO = :empno';
FDQuery1.ParamByName('empno').AsInteger:=11;
FDQuery1.Open();

C++Builder:
FDQuery1->SQL->Text="select * from Employee where EMP_NO = :empno";
FDQuery1->ParamByName("empno")->AsInteger=11;
FDQuery1->Open();

Use the :<name> syntax to include the parameter in the SQL text . You can then specify the parameter by assigning a value to TFDQuery's ParamByName( <name> ) method.

Be sure to specify the same (name) character string for <name> .
In the code example above, <name>=empno.

The result set obtained by opening the data set can be referenced using the FieldByName( <field name>) method of TFDQuery.

The following is an example.

Delphi:
FDQuery1.Open('select FIRST_NAME from Employee');
while not FDQuery1.Eof do
begin
// Output the referenced data to a memo
Memo1.Lines.Add(FDQuery1.FieldByName('FIRST_NAME').AsString);
FDQuery1.Next;
end;

C++Builder:
FDQuery1->Open("select FIRST_NAME from EMPLOYEE");
while (!FDQuery1->Eof) {
// Output referenced data to memo
Memo1->Lines->Add(FDQuery1->FieldByName("FIRST_NAME")-> AsString);
FDQuery1->Next();
}

Close the dataset
Call the Close method to close the dataset.

The following is a code example that reopens a dataset.

Delphi:
FDQuery1.Close;
FDQuery1.Open('select * from Employee');

C++Builder:
FDQuery1->Close();
FDQuery1->Open("select * from Employee");

Edit/update data
You can call the Edit method to edit the data in the dataset's current cursor row, and the Post method to update the edited data.

The code example for updating the data is as follows.

Delphi:
FDQuery1.SQL.Text:='select * from EMPLOYEE Where EMP_NO=:empno';
FDQuery1.ParamByName('empno').AsInteger:=11;
FDQuery1.Open;
FDQuery1.Edit;
FDQuery1.FieldByName('FIRST_NAME'). AsString:='Marco';
FDQuery1.FieldByName('LAST_NAME').AsString:='Cantu';
FDQuery1.Post;

C++Builder:
FDQuery1->SQL->Text="select * from EMPLOYEE Where EMP_NO=:empno";
FDQuery1->ParamByName(“empno”)->AsInteger=11;
FDQuery1->Open();
FDQuery1->Edit();
FDQuery1 ->FieldByName("FIRST_NAME")->AsString="Marco";
FDQuery1->FieldByName("LAST_NAME")->AsString="Cantu";
FDQuery1->Post();

When updating data in the FireDAC dataset, the following error may occur.

[FireDAC][DApt]-400. The Update command updated the [x] records, not the [1] records. Possible reasons: update table does not have a primary key or row identifier. Record modified or deleted by another user

In FireDAC, if the table does not have a primary key , multiple [x] rows may be detected in the update target , and the above error occurs.

Please be careful, especially when you move from BDE to FireDAC.

If you encounter this error, try the following settings. (excerpt from docwiki)

Exclude fiMeta from FetchOptions.Items and use one of the following: • Set UpdateOptions.KeyFields to a semicolon (';') separated list of column names. · Include pfInKey in the corresponding TField.ProviderFlags property. For more information, please see the note Для просмотра ссылки Войди или Зарегистрируйся .

SQL statement execution
TFDQuery can directly execute SQL statements to add, update, or delete records.

The following is a code example that executes record update (update statement).

Delphi:
FDQuery1.SQL.Text :='update EMPLOYEE set FIRST_NAME=:NAME where EMP_NO=15';
FDQuery1.ParamByName('NAME').AsString:='Marco';
FDQuery1.ExecSQL;

C++Builder:
FDQuery1->SQL->Text :="update EMPLOYEE set FIRST_NAME=:NAME where EMP_NO=15";
FDQuery1->ParamByName("NAME")->AsString="Marco";
FDQuery1->ExecSQL;

If you want to execute an SQL statement such as insert, update, or delete that does not require result set retrieval, execute the ExecSQL method instead of the Open method.

In FireDAC, you can select the TFDQuery placed on the form and right-click the mouse to start [Query Editor].

In FireDAC's Query Editor, you can enter a SQL statement in [SQL Command] and execute it on the fly.


When the [OK] button is clicked, the SQL statement entered in the SQL command is saved as is in the TFDQuery.SQL property.



[/SHOWTOGROUPS]

 

[emailx45]

Спойлер: Content - 2nd part

[SHOWTOGROUPS=4,20]
Implementation of transaction processing
With FireDAC, there are two ways to manage transactions.

1. Use StartTransaction, Commit, Rollback methods of TFDConnection
2. Use the TFDTransaction component and set the TxOptions option

This section shows a code example of transaction management by TFDConnection.

Delphi:
FDConnection1.StartTransaction; // start transaction
try
　FDQuery1.SQL.Text :='update EMPLOYEE set FIRST_NAME=:NAME where EMP_NO=15';
　FDQuery1.ParamByName('NAME').AsString:='Marco';
　FDQuery1.ExecSQL;
　FDConnection1.Commit; //If the SQL statement is executed normally, commit
except
　FDConnection1.Rollback; // rollback if an exception occurs
..
end;

C++Builder:
FDConnection1->StartTransaction(); // start transaction
try
{
FDQuery1->SQL->Text :="update EMPLOYEE set FIRST_NAME=:NAME where EMP_NO=15";
FDQuery1->ParamByName("NAME")->AsString="Marco";
FDQuery1->ExecSQL;
FDConnection1->Commit(); //If the SQL statement is executed normally, commit
}
catch(Exception &e)
{
FDConnection1->Rollback(); // rollback if an exception occurs
..
}

Cache update
Cache update is simply explained as follows. Instead of posting it to the database every time you update the data, save all the update information of the dataset in the local cache and "post it all together" It is a function . Cache updates were available in BDE, but FireDAC will continue to take advantage of this feature.

The following settings are required for each dataset to enable cache-up mode.

• Set the CachedUpdates property to True
• Execute ApplyUpdates method

Cache update will be explained in the next section with specific implementation code.

Exercises for creating an application that updates data
In this section, as an improved version of the application creation by FireDAC performed in Part 2, this time, we added the implementation code for data acquisition and data update (transaction) processing, and conducted an exercise closer to actual application development. I will.

The practice procedure is as follows.

1. Set up and establish a connection to the database
2. Placement of FireDAC components required for execution
3. Bind the dataset to a grid control
4. Implement data acquisition and data update (transaction) processing
5. Run the application and view and update data

This blog also uses InterBase 2020 as the database to connect to.

(1) Creating a VCL Form Application project

From the Delphi/C++Builder menu, select [File]-[New]-[Windows VCL Forms Application].



(2) Save the project

Select [File]-[Save All] from the menu to save all files. The project can be saved in any folder.



(3) Start InterBase server

Start "Server Manager" by selecting "Embarcadero InterBase 2020 [instance=gds_db]"-"InterBase Server Manager" from the Windows start menu.

Click the Start button on the InterBase Server Manager screen to start the InterBase process.

Make sure that the server status is "Running".


(4) Place FireDAC components on the form

From the [FireDAC] category of the tool palette
・TFDConnection
・TFDQuery

In order to connect to the database from FireDAC, at least one TFDConnection component must be placed.

From the [FireDAC Links] category of the tool palette
・TFDPhysIBDriverLink
Place each at any position on the form.

TFDPhysXXXDriverLink is required for each database to connect. For example, when connecting to InterBase, use TFDPhysIBDriverLink (TFDPhysXXXDriverLink is not mandatory and optional for Delphi/C++Builder XE6 or later). However, if you need to specify your own library file, you need to explicitly set the VenderHome and VenderLib properties, so you need to place TFDPhysXXXXDriverLink as before.

For this blog, I will explicitly place TFDPhysIBDriverLink.

(5) Place other components on the form

From the Data Access category of the tool palette
・TDataSource
From the Data Controls category of the tool palette
・TDBGrid
From the [Standard] category of the tool palette
・TButton
Two
Place it at any position on the form (see the placement example in the figure below).

Посмотреть вложение 2376


(6) Display FireDAC connection editor

Select FDConnection1 on the form and right-click the mouse to display the popup menu. Select [Connection Editor] from the menu to display the FireDAC connection editor.



(7) Setting TFDConnection connection parameters

Select "IB" from the list of driver IDs in the FireDAC connection editor.

When you select the driver ID, a list of parameters required to set up the database is displayed.


Change the blank part of FireDAC connection editor (above figure) to the following parameters.

Parameter name	value
Driver id	IB
Database	C:\ProgramData\Embarcadero\InterBase\gds_db\examples\database\employee.gdb
User_Name	SYSDBA
Password	masterkey

(8) Test the connection to the database

After setting the parameters required to connect to the database, press the [Test] button in the FireDAC connection editor.


When you press the test button, the login screen for the database is displayed.

Click the [OK] button.


“The connection was established successfully.”

Message is displayed, the connection to the database has been completed successfully.

After confirming the connection to the database, select FDConnection1 on the form and change the following properties from the screen of the Object Inspector.

FDConnection1

Property name	value
LoginPrompt	False

If the FDConnection1.LoginPrompt property is True, the connection screen to the database will be displayed every time the application is started, so change it to False here.



(9) Change each property

Change the following properties of each component from the screen of the Object Inspector.



FDQuery1

Property name	value
Connection	FDConnection1
CachedUpdates	True

FireDAC requires the CachedUpdate property to be set to True in order to update the dataset using the ApplyUpdate method. If you do not set the CachedUpdate property of FDQuery1 to True and execute ApplyUpdate, the exception shown above will occur.

DataSource1

Property name	value
DataSet	FDQuery1

DBGrid1

Property name	value
DataSource	DataSource1

Button1

Property name	value
Caption	Open dataset

Button2

Property name	value
Caption	Execute Apply Update

(10) Implement the process to open the dataset

When you double-click Button1 on the design screen, the OnClick event handler for Button1 is generated. Add a process to open the dataset in the event handler.

Delphi:
procedure TForm1.Button1Click(Sender: TObject);
begin
FDQuery1.Close;
FDQuery1.Open('select * from Employee');
end;

C++Builder:
void __fastcall TForm1::Button1Click(TObject *Sender)
{
FDQuery1->Close();
FDQuery1->Open("select * from Employee");
}

FireDAC allows you to specify a Select statement as an argument of the Open method. However, insert/update/delete statements that do not get the result set cannot be specified.

(11) Implement the process to execute ApplyUpdate

Double-clicking Button2 on the design surface will generate an OnClick event handler for Button2. Add the process that executes ApplyUpdate in the event handler.

Delphi:
procedure TForm1.Button2Click(Sender: TObject);
var
ErrorCount: Integer;
begin
FDConnection1.TxOptions.AutoCommit:=False; // Auto commit mode OFF
FDConnection1.StartTransaction; // explicit transaction start

ErrorCount := FDQuery1.ApplyUpdates(0);

if ErrorCount = 0 then
FDConnection1.Commit // commit if there are no errors
else
FDConnection1.Rollback; // rollback if there is an error
end;

C++Builder:
void __fastcall TForm1::Button2Click(TObject *Sender)
{
FDConnection1->TxOptions->AutoCommit=False; // Auto commit mode OFF
FDConnection1->StartTransaction(); // start explicit transaction

int ErrorCount=FDQuery1->ApplyUpdates(0);
if( ErrorCount==0 ){
FDConnection1->Commit(); // commit if there are no errors
}else{
FDConnection1->Rollback(); // rollback if there is an error
}
}

ApplyUpdates does not raise an exception, but instead returns the number of exception errors it encountered. Therefore, instead of trying to catch exceptions with try-catch, judge by the number of exception errors and decide whether to execute commit/rollback.

Call the StartTransaction method of TFDConnection to start an explicit transaction. However, if the TFDTxOptions.AutoCommit property is True, the auto-commit mode will be enabled, so let's set this property to False.

(12) Save the project

Select [File]-[Save All] from the menu to save all files.

(13) Run the application


Press the execute button on the toolbar (above figure) or the [F9] button on the keyboard.

(14) Open dataset

After running the application, press the Open Dataset button and the DBGrid component will display the data from the Employee table.

(15) Change any data in the grid

Change any data displayed in DBGrid.

For example,

“KJ” in FIRST_NAME field is “Marco”
“Weston” in LAST_NAME field is “Cantu”

Let's change each to.

The following figure is an example of changing the record of EMP_NO=11.

Посмотреть вложение 2377


(16) Execute Apply Update

No matter how much you change the data in the DBGrid, it does not reflect the changed data in the actual InterBase database.

This is because the result set obtained with the Open method of FDQuery1 is cached locally, so no matter how much you change the data in DBGrid, you are only changing the cached data.

Therefore, if you click the [Open Data Set] button, the changed data will be restored. To reflect the changes to the actual database, execute the ApplyUpdate method of FDQuery1.
Click the [Apply Update] button.

Then, press the [Open Data Set] button and reload the data set.

What is the result? If the changed part is changed, the ApplyUpdate method is executed correctly.


This concludes the exercises in this section.

As expected, it is not possible to create an application without writing any code at all, but as explained so far, arrange components by drag and drop and implement the minimum necessary code in addition to the operation of changing properties. You can just build a real application. In this exercise, we used VCL application as an example, but you can use the same method to create mobile application and server-side application.

Next time, I will explain how to use the data module as a method to further improve the efficiency of development projects.

[/SHOWTOGROUPS]