FireDAC.Transactions Sample

This sample shows how to manage database transactions.

Location

You can find the Transactions sample project at:

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

Description

The Transactions sample shows you how to manage database transactions. This sample demostrates how to use the following properties and methods:

• The isolation property is used for setting up the transaction isolation level for the transactions managed by FireDAC.
• The auto commit property is used to control the automatic transaction management.
• The start transaction method is used to start a new DBMS transaction.
• The commit method is used to permanently store modifications made in the current transaction to the database.
• The rollback method is used to cancel all modifications made in the current transaction to the database.

How to Use the Sample

1. Navigate to the location given above and open Transactions.dproj.
2. Press F9 or choose Run > Run.
3. Click on the Use Connection Definition combo box and select an option.

Files

File in Delphi	Contains
Transactions.dproj Transactions.dpr	The project itself.
fTransactions.pas fTransactions.fmx	The main form.

Implementation

The sample implements the following database transaction features:

Setting up Transaction Isolation Level

The sample sets the Isolation property to xiReadCommitted to allow the reading of committed (permanent) changes made to the database by other simultaneous transactions. This is the default value of the Isolation property. It is set by typing the following code:

 with dmlMainBase.dbMain do begin
   TxOptions.Isolation := xiReadCommitted;

Controlling Auto Commit Mode

The sample turns off the AutoCommit mode by typing:

 TxOptions.AutoCommit := False;

Starting transaction

The sample starts a new DBMS transaction by typing:

 StartTransaction;
 try
   // execute simple command inside transaction
   ...

Committing Transaction

To permanently store modifications made in the current transaction to the database, call the Commit method.

 ...
 FDQuery1.ExecSQL;
 // Commit transaction
 Commit;

Rollbacking Transaction

The sample uses the Rollback method to cancel all the current transaction modifications when an exception is raised.

 StartTransaction;
 try
   // ...
   FDQuery1.ExecSQL;
   // ...
   // Commit transaction
   Commit;
 except    
   // During FDQuery1.ExecSQL it's raised an exception and transaction now rollbacking
   Rollback;
   raise;
 end;

Uses

• Isolation
• AutoCommit
• StartTransaction
• Commit
• Rollback

See Also

• FireDAC
• Transactions
• Managing Transactions (FireDAC)
• Specifying the Transaction Isolation Level

Samples

• FireDAC IFDPhysConnection Transactions sample
• FireDAC Info Report sample
• FireDAC Pooling sample
• FireDAC Get Field Names sample