FireDAC Components for Zoho CRM

Build 22.0.8462

Batch Processing

Executing Batch Operations to Zoho CRM

FireDAC batch commands drastically improve performance for large numbers of updates. When you execute a FireDAC batch command, the CData FireDAC Components for Zoho CRM utilize the Zoho CRM bulk APIs to post the entire batch to the server in a single request. The server executes all commands in the batch simultaneously.

Batch Processing Flow

Batch processing in FireDAC is accomplished by binding arrays to the columns of a parameterized statement. To execute the batch, you can call the Execute method available in the TFDQuery, TFDCustomCommand, and TFDStoredProc classes. The size of the batch to execute must be specified.

Controlling Batch Size

Instead executing every statement in the batch in bulk, you can use the following overloaded method to execute sub-batches:

procedure Execute(ATimes: Integer, AOffset: Integer);

ATimes defines the number of items to execute in the Params array. ATimes must be equal or less than the ArraySize property of the TFDQuery object.

AOffset defines the index of the row you want to start from. For example, the following calls execute the first two statements:

FDQuery1.Execute(1, 0);
FDQuery1.Execute(2, 1);

Retrieving Affected Rows

You can use the RowsAffected property of the TFDQuery class to retrieve the total number of successful changes.

ShowMessage(IntToStr(FDQuery1.RowsAffected));

Examples

The examples below demonstrate the batch operation process:

Bulk Insert

The following example prepares a single batch that inserts records in bulk.

FDConnection1.ResourceOptions.ServerOutput := True;
FDQuery1.SQL.Text := 'insert into Accounts (AccountName, AccountNumber) values (:AccountName, :AccountNumber )';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= 'MyAccountName1';
FDQuery1.Params[1].AsStrings[0]:= 'MyAccountNumber1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= 'MyAccountName2';
FDQuery1.Params[1].AsStrings[1]:= 'MyAccountNumber2';
...

FDQuery1.Execute(FDQuery1.Params.ArraySize);
ShowMessage(IntToStr(FDQuery1.RowsAffected));
To retrieve the Ids of the new records, query the LastResultInfo#TEMP table:
sName := FDQuery1.Open('SELECT * FROM [LastResultInfo#TEMP]');

Bulk Update

The following example prepares a single batch that inserts records in bulk.

FDQuery1.SQL.Text := 'update Accounts set AccountNumber = :AccountNumber WHERE Id = :Id';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= '4501';
FDQuery1.Params[1].AsStrings[0]:= 'Id1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= '4500';
FDQuery1.Params[1].AsStrings[1]:= 'Id2'; 
...  

FDQuery1.Execute(FDQuery.Params.ArraySize); 
ShowMessage(IntToStr(FDQuery1.RowsAffected));

Bulk Delete

The following example prepares a single batch that inserts records in bulk.

FDQuery1.SQL.Text := 'delete Accounts where Id = :Id';
FDQuery1.Params.ArraySize := 100;
FDQuery1.Params[0].AsStrings[0]:= 'MyId1';

//next statement
FDQuery1.Params[0].AsStrings[1]:= 'MyId2';
...

FDQuery1.Execute(FDQuery.Params.ArraySize); 
ShowMessage(IntToStr(FDQuery1.RowsAffected));

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462