![1262883intenseCircuitCanvasLandscape | Embarcadero RAD Studio Delphi C++Builder Blogs](https://i0.wp.com/blogs.embarcadero.com/wp-content/uploads/2020/08/1262883_intenseCircuit_Canvas_Landscape.jpg?resize=1140%2C675&ssl=1)
The Batch sample shows you how to use the Array DML execution technique to execute multiple SQL INSERT
commands in a single step to optimize database population performance. To this end, the sample uses the IFDPhysCommand interface to set an array of values for each parameter and the Execute method in Array DML mode to populate the database in a single step.Note: In this demo the “batch” and “Array DML” terms are interchangeable.
Table of Contents
Location
You can find the Batch sample project at:
- Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
Object PascalDatabaseFireDACSamplesPhys LayerIFDPhysCommandBatch
- 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
- Navigate to the location given above and open
IFDPhys_Batch.dproj
. - Press F9 or choose Run > Run.
![Batch | Embarcadero RAD Studio Delphi C++Builder Blogs](https://i0.wp.com/blogs.embarcadero.com/wp-content/uploads/2020/10/Batch.png?resize=750%2C446&ssl=1)
Files
File in Delphi | Contains |
---|---|
IFDPhys_Batch.dproj IFDPhys_Batch.dpr | The project itself. |
fBatch.pas fBatch.fmx | The main form. |
Implementation
When you run the application, you see the following components on the form:
- A TCheckBox object labeled as Insert blobs. It is unchecked by default.This sample executes multiple SQL
INSERT
commands. First, the sample executes the SQLINSERT
commands using an ordinary execution mode. Then, the sample executes the SQL statements using an Array DML execution mode. The state of the Insert blobs check box changes the SQL statement:- If Insert blobs is unchecked, the SQL statement is set to
'insert into {id FDQA_Batch_test}(tint, tstring) values(:f1, :f2)'
. - If Insert blobs is checked, the SQL statement is set to
'insert into {id FDQA_Batch_test}(tint, tstring, tblob) values(:f1, :f2, :f3)'
.
- If Insert blobs is unchecked, the SQL statement is set to
- A TLabeledEdit object named Records to insert.This label represents the number of SQL
INSERT
commands that are executed in a single step. The preset value is10000
. - A TComboBox object labeled as Use Connection Definition.When you click the Use Connection Definition combo box, the menu shows all the persistent connections defined on the file
C:UsersPublicDocumentsEmbarcaderoStudioFireDACFDConnectionDefs.ini
. Select an option in order to define a connection to a database. When the connection is defined, the sample uses the CreateCommand method of IFDPhysConnection to create a command interface. Then, the sample uses this command interface to execute multiple SQLINSERT
commands in run time. The sample implements the execution of the multiple SQLINSERT
commands using two different execution modes: the ordinary execution and the batch execution.- Ordinary executionThe sample calls 10000 times the Execute method to execute the 10000 SQL
INSERT
statement. - Batch executionThe sample calls the Execute method in Array DML mode to execute the 10000 SQL
INSERT
statement in a single step. The Execute method is called only once as follows:Execute(iBatchSize, 0)
, whereiBatchSize
is the length of the DML array. In this sample,iBatchSize
is set with the value of the Records to insert label.
- Ordinary executionThe sample calls 10000 times the Execute method to execute the 10000 SQL
- A TMemo object.The sample uses this object to report the execution time of both execution methods.
For more information and more external links for other samples, please go to the next link:
http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.IFDPhysCommand.Batch_Sample
Head over and check out the full source code for the Array DML bulk insert sample for Delphi.
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition