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

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, please

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 = mpno';
FDQuery1.ParamByName('empno').AsInteger:=11;
FDQuery1.Open();

C++Builder:
FDQuery1->SQL->Text="select * from Employee where EMP_NO = mpno";
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&gt 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=mpno';
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=mpno";
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 here .

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

Скрытая информация :: Авторизуйтесь для просмотра »


(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:



utton1Click(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:



utton2Click(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.



(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.