Learn How To Execute Batch Queries With This Array DML For FireDAC In Delphi

The Batch sample shows you how to use Array DML to execute multiple SQL INSERT commands in a single step to optimize database population performance. In this demo the “batch” and “Array DML” terms are interchangeable.

Location

You can find the Batch sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Comp Layer\TFDQuery\ExecSQL\Batch
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

How to Use the Sample

1. Navigate to the location given above and open Batch.dproj.
2. Press F9 or choose Run > Run.

Files

File in Delphi	Contains
Batch.dproj Batch.dpr	The project itself.
fBatch.pas fBatch.fmx	The main form.

Implementation

Before running the sample, the main components are configured at design time using the Object Inspector as follows:

• Two TFDQuery objects named qrySelect and qryBacth. These components implement a dataset capable of executing SQL queries: qryBacth is responsible for the INSERT query of data into the database while qrySelect is responsible for the SELECT query of data from the database in order to display the datasets. To this end, the sample configures the following properties of both objects:
  • The Connection property is configured to specify the FireDAC connection object that is used to connect to a DBMS.
  • The SQL property of qrySelect is set to select * from {id FDQA_Batch_test}.
  • The SQL property of qryInsert is set to insert into {id FDQA_Batch_test}(tint, tstring, tblob) values(:f1, :f2, :f3).

Note: You can change the SQL property of qryInsert at run time by checking the insert blob checkbox.

• A TDataSource object named DataSource1. This component provides an interface between a dataset component and data-aware controls on a form. In this sample, it is used to provide communication between the dataset and the grid where the dataset is displayed. To this end, the sample sets the following properties:

• The DataSet property of DataSource is set to qryExecSQL.
• The DataSource property of DBGrid1 is set to DataSource1.

When you run the application, you see the following components on the form:

• A combo box labeled as Use Connection Definition.
• A label named Array size. The preset value is 10000.
• Two check box labeled as insert blob (unchecked) and batch executing (checked).
• Two buttons labeled as ExecSQL and Disconnect. Both buttons are disabled.

To define a connection to a database, click on the Use Connection Definition combo box and select an option. When you select an item of the combo box, the sample enables the ExecSQL and Disconnect buttons and unchecks the insert blob check box. Then, if you click on the ExecSQL button, the sample executes the SQL statement of qryBatch. The execution of the SQL statement varies depending on the state of the check boxes and the value of the Arraysize value:

• The qryBatch SQL statement depends on the state of the insert blob check box:
  • If it is unchecked, the qryBatch SQL statement is set to 'insert into {id FDQA_Batch_test}(tint, tstring) values(:f1, :f2)'.
  • If it is checked, the SQL statement is set to 'insert into {id FDQA_Batch_test}(tint, tstring, tblob) values(:f1, :f2, :f3)'.
• When clicking on the ExecSQL button with batch executing checked, the sample uses the Execute method in Array DML mode, where the value of the Array size label specifies the size of each parameter array. Change the Array size value to change the length of the parameters array. On the other hand, if batch executing is unchecked, the sample uses the ExecSQL method to execute the SQL statement.

If you want to visit the original post, please follow the link below:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.TFDQuery.Batch_Sample