FireDAC Data Access Components

FireDAC is a high-performance data access library for developing applications for multiple devices and connected to enterprise databases in Delphi. FireDAC enables native high-speed direct access from Delphi to InterBase, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, DB2, SQL Anywhere, Advantage DB, Firebird, Access, Informix, DataSnap and more, including the NoSQL Database MongoDB. It is preinstalled in latest Delphi versions and introduced from Delphi XE5 version. We can use FireDAC in Android, iOS, Windows, and Mac OS X applications as it supports cross platform. Besides providing access to data in these RDBMSes, it also provides services for creating and restoring backups for engines that support this.

Features

1. Supports cross platform

2. Unicode support

3. Multi Device Support

4. Native Driver Support for all databases

5. Automatic Connection Recovery

6. TFDMemTable – in memory dataset, faster than TClientDataSet

7. Provides high speed direct data access from Delphi to Interbase, SQLite, 

MySQL, SQL Server, Oracle, PostgreSQL, IBM DB2, SQL Anywhere, Access, Firebird, Informix and more.

8. Have local SQL engine for executing SQL queries against datasets

9. One of the fastest in-memory datasets, with sorting, filtering, aggregates, filtered and expression indexes, persistence and more

10. Full support for auto-incrementing fields, including those based on generators and table triggers

11. Cached updates mode with ability to track correlated changes for several datasets with cascading updates

12. Disconnected connection mode, allowing application to continue to work without a physical connection to a database

13. Support for services like backup, restore, security management, and more

14. Mobile database access on Android and iOS for these databases: InterBase – InterBase ToGo and IBLite, SQLite

15. Compatible with old Delphi components functions and features like Append, Edit, Post, Locate, Find, Filter, FieldDefs etc.

Architecture

Components

In this article, we will see how to use FireDac to connect to a Oracle Database, but the techniques discussed are the same for any other database kind.

FireDAC Core Components 

Name	Description
TFDManager	Responsible for connection definitions and connections management.
TFDConnection	Responsible for connection establishment with a DBMS.
TFDTransaction	Responsible for connection transaction management.
TFDCommand	Responsible for executing SQL commands.
TFDTableAdapter	Binds TFDCommand and TFDMemTable.
TFDSchemaAdapter	Binds several datasets into a single in-memory storage and builds a log.
TFDMemTable	Implements an in-memory dataset.
TFDQuery	Implements a dataset that can execute SQL queries
TFDStoredProc	Implements a dataset that can execute server-side stored procedures
TFDTable	Implements a dataset that works with single database tables
TFDUpdateSQL	Applies updates on behalf of queries or stored procedures that cannot post updates directly
TFDBatchMove	Responsible for data movement between different data sources.
TFDScript	Implements a SQL script engine that can execute a series of SQL queries
TFDMetaInfoQuery	Implements a dataset that can execute meta-info queries
TFDEventAlerter	Responsible for handling the database event notifications
TFDLocalSQL	Implements the Local SQL engine, which can execute the SQL queries with TDataSet descendants as data sources

FireDAC Driver Link Components

Most of the applications use one link component per supported database. The FireDAC driver link components are described in the following table.

Name	Description
TFDPhysADSDriverLink	Use TFDPhysADSDriverLink to link the Advantage Database Server driver
TFDPhysASADriverLink	Use TFDPhysASADriverLink to link the Sybase SQL Anywhere driver
TFDPhysDSDriverLink	Use TFDPhysDataSnapDriverLink to link the Multi-Tier DataSnap driver
TFDPhysDB2DriverLink	Use TFDPhysDB2DriverLink to link the IBM DB2 driver
TFDPhysFBDriverLink	Use TFDPhysFBDriverLink to link the Firebird driver .
TFDPhysIBDriverLink	Use TFDPhysIBDriverLink to link the Interbase driver .
TFDPhysInfxDriverLink	Use TFDPhysInfxDriverLink to link the Informix driver
TFDPhysMSAccessDriverLink	Use TFDPhysMSAccessDriverLink to link the Microsoft Access driver
TFDPhysMSSQLDriverLink	Use TFDPhysMSSQLDriverLink to link the Microsoft SQL Server driver.
TFDPhysMySQLDriverLink	Use TFDPhysMySQLDriverLink to link the MySQL Server driver
TFDPhysODBCDriverLink	Use TFDPhysODBCDriverLink to link the ODBC bridge driver to an application
TFDPhysOracleDriverLink	Use TFDPhysOracleDriverLink to link the Oracle Database driver
TFDPhysPgDriverLink	Use TFDPhysPgDriverLink to link the PostgreSQL driver
TFDPhysSQLiteDriverLink	Use TFDPhysSQLiteDriverLink to link the SQLite driver
TFDPhysTDataDriverLink	Use TFDPhysTDataDriverLink to link the Teradata Database driver
TFDPhysTDBXDriverLink	Use TFDPhysTDBXDriverLink to link the dbExpress v 4 bridge driver to an application
TFDPhysMongoDriverLink	Use TFDPhysMongoDriverLink to link the MongoDB driver to an application

FireDAC UI Components

Most of the applications use TFDGUIxWaitCursor and TFDGUIxLoginDialog.

Name	Description
TFDGUIxAsyncExecuteDialog	This dialog shows an SQL query execution progress
TFDGUIxErrorDialog	This dialog displays the FireDAC exceptions
TFDGUIxLoginDialog	This dialog allows the users to enter their DB credentials
TFDGUIxScriptDialog	This dialog shows an SQL script execution progress
TFDGUIxWaitCursor	This component allows the control of the wait cursor.

FireDAC Service Components

An application uses service components to add a specific DBMS facility support.

Name	Description
TFDADSBackup	This class implements the Advantage backup database service.
TFDADSRestore	This class implements the Advantage service, by restoring a database from a backup.
TFDADSUtility	This class implements the Advantage table utilities.
TFDASABackup	This class implements the SQL Anywhere backup database service.
TFDASAValidate	This class implements the SQL Anywhere database validate service.
TFDIBBackup	This class implements the FB/IB backup database service.
TFDIBConfig	This class implements the FB/IB setting database properties service.
TFDIBInfo	This class implements the FB/IB service to query the service manager.
TFDIBRestore	This class implements the FB/IB service, by restoring a database from a backup.
TFDIBSecurity	This class implements the FB/IB database security management service.
TFDIBValidate	This class implements the FB/IB database validate and repair service.
TFDIBSDump	This class implements the InterBase-only database dump service.
TFDFBNBackup	This class implements the Firebird-only backup database service.
TFDFBNRestore	This class implements the Firebird-only restore database service.
TFDFBTrace	This class implements the Firebird database trace service.
TFDMSAccessService	This class implements the Microsoft Access database. It also creates, drops, compacts, and repairs the services.
TFDOracleAdmin	This class implements a service to manage an Oracle database instance.
TFDSQLiteBackup	This class implements SQLite backup. It also restores and copies the database functionality
TFDSQLiteCollation	This class implements the custom SQLite collation
TFDSQLiteFunction	This class implements the custom SQLite function
TFDSQLiteSecurity	This class allows you to manage the SQLite database encryption.
TFDSQLiteValidate	This class implements the SQLite database validate service
TFDSQLiteRTree	This class implements the custom SQLite RTree

FireDAC ETL Components

An application uses the ETL components for data movement between different types of data sources and the data destinations.

Name	Description
TFDBatchMove	This class implements the engine to process the data movement between different data source and destinations.
TFDBatchMoveTextReader	This class defines a data source reader using a text file.
TFDBatchMoveTextWriter	This class defines a data target writer using a text file.
TFDBatchMoveDataSetReader	This class defines a source dataset.
TFDBatchMoveDataSetWriter	This class defines a destination dataset.
TFDBatchMoveSQLReader	This class connects to the database that contains the data to copy.
TFDBatchMoveSQLWriter	This class connects to the database where you want to copy the data.

FireDAC NoSQL Components

An application uses the NoSQL components to work with NoSQL document databases.

Name	Description
TFDMongoDataSet	This class implements aa dataset that can be attached to a MongoDB cursor.
TFDMongoQuery	This class implements a dataset that can execute MongoDB queries.
TFDMongoPipeline	This class allows to execute a MongoDB pipeline.

Connecting to the database with using FireDAC

Now lets see how to connect with a database using FireDAC. FireDAC provides TFDConnection component to start connection with a database. We just need to drop this component on a form and set some properties.

So lets check with a new sample Delphi VCL application. Here I will show you how to connect with a MS-Access DB. And the same process is applied to connect with other DBs also. So create a new Delphi VCL application from File Menu -> New -> VCL Form Application.

Project opens with default form Form1, so save the project and form. Now drop a TFDConnection component and TFDPhysMSAccessDriverLink1 component on Form1 as follow. TFDConnection component is used to establish connection and TFDPhysMSAccessDriverLink1 component is used to include required Units in Delphi application for native connection support. For different DB FireDAC have different Driver Link component and we have put respected Driver Link component on form with FDConnection component.

Then we can use Params property or Connection Editor to set connetion details like DB name, Host, User and Pass etc.

So first lets check with Connection Editor. To open Connection Editor either double click on FDConnection1 or right click on FDConnection then select Connection Editor option.

Then select Driver ID as MSAcc for Access DB. We need to select different Driver for required DB. And once we select a Driver ID then it will show related fields for DB connection. Then we can fill required data and client test to check the connection status.

After filling all required fields now click on Test to check connection status. Then it may show following dialog for login because FDConnection1.LoginPrompt = True. So to avoid this dialog during connection open please set FDConnection1.LoginPrompt = False.

Now click OK in Login Dialog.

If everything is fine then it will show success message else you will get an error message.

It shows successfully message, means all connection parameter as set are correct. Now click OK on Connection Editor dialog and check FDConnection1.Params property. You can see all data are filled automatically that we had entered on Connection Editor dialog. So the same thing we can do from FDConnection1.Params property in manually also.

Now to open connection from Delphi Application set FDConnection1.Connected = True. If everything is fine it will set True else you will get an error message.

So now connection with DB is done successfully. Its very simple in Delphi.

If you want to do the same by writing code then please check following codes. Here I added a Button and on Button click, I am creating a TFDConnection component and setting all required properties for connection establish then opening connection.

Double click on the button to enter it’s OnClick event and add followinfg codes.

procedure TForm1.Button1Click(Sender: TObject);

begin

    // Set Connection parameters //

    conn.Params.DriverID := 'MSAcc';

    conn.Params.Database := 'C:\JITENDRA\TEST-PROJECTS\dbdemos.mdb';

    conn.Params.UserName := 'if any user';

    conn.Params.Password := 'if any pass';

    conn.LoginPrompt := False; // to not show FireDAC login dialog//

    // Connect to the database //

    conn.Connected := True;

    if conn.Connected then begin

      ShowMessage('Connection succeeded.');

    end 
    else begin

      ShowMessage('Connection Failed.');
end;

end;

We can also use FDConnection1.Open to open connection and FDConnection1.Close to close connection. It is same like FDConnection1.Connected = True / False.

Following are some examples of setting FDConnection Params property to establish connection with different DBs.

Connect to Oracle 

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=Ora');

    Add('Database=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = OraSrv)(PORT = 1521))) (CONNECT_DATA = (SERVER =    DEDICATED)(SERVICE_NAME = orcl)))');

    Add('User_Name=ADDemo');

    Add('Password=pass');

  end;

  Open;

end;

Connect to MS-SQL

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=MSSQL');

    Add('Server=(local)');

    Add('Database=FDDemo');

  end;

  Open;

end;

Connect to PostgresSQL

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=PG');

    Add('Server=pgsrv');

    Add('Port=5433');

    Add('Database=addemo');

    Add('CharacterSet=utf8');

    Add('MetaDefSchema=MySchema');

    Add('ExtendedMetadata=True');

  end;

  Open;

end;

Connect to My-SQL

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

     Clear;

     Add('DriverID=MySQL');

     Add('Server=127.0.0.1');

     Add('Port=3306');        

     Add('Database=FDDemo');

  end;

  Open;

end;

Connect to IB

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=IB');

    Add('Server=localhost');

    Add('Database=x:\path_to_db\db.fdb');

    Add('User_Name=sysdba');

    Add('Password=masterkey');

  end;

  Open;

end;

Connect to Sybase SQL Anywhere

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=ASA');

    Add('Server=ASASrv');

    Add('Database=ADDemo');

    Add('User_Name=dba');

    Add('Password=sql');

    Add('MetaDefSchema=dba');

  end;

  Open;

end;

Connect to MS-Access DB

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=MSAcc');

    Add('Database=x:\path_to_db\db.fdb');

  end;

  Open;

end;

Connect to SQL-Lite

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=SQLite');

    Add('Database=x:\path_to_database\db.sdb');

  end;

  Open;

end;

Connect to FireBird

with dbMain do begin

  Close;

  // create temporary connection definition

  with Params do begin

    Clear;

    Add('DriverID=FB');

    Add('Server=localhost');

    Add('Database=x:\path_to_db\db.fdb');

    Add('User_Name=sysdba');

    Add('Password=masterkey');

  end;

  Open;

end;

Getting data from Database

Now that we have a connection to our database, lets query some data from it. Here I have created a sample db that having a table Employee which stores Employee data. So we will get all Employee data and show on a DBGrid using FireDAC.

For this we can use TFDTable component or TFDQuery component. TFDTable component is used to get data from single table only. TFDQuery is used to get data from single or mulitple tables by writing SQL.

So lets check with TFDTable first. So drop a TFDTable component on form and set Connection, TableName properties and set Active = True. If no error then data is fetched from database to TFDTable.

FDTable1.Connection := FDConnection1;
FDTable1.TableName := ‘employee’;
FDTable1.Active := True; //We can use Open also//

Now we will show data in a grid. So put a Tdatasource component and TDBGrid component on Form1. Then set following properties to set link between FDTable1, Datasource1 and DBGrid1.

Datasource1.Dataset := FDTable1;
DBGrid1.DataSource := DataSource1;

And you can see the fetched data of TFDTable1

Now lets use TFDQuery to get data. So drop a TFDQuery component on Form1 and set following properties

FDQuery1.Connection := FDConnection1;
FDQuery1.SQL.Text := ‘ Select * from employee’; // we can use join for multiple table//
FDQuery1.Active := True;         //We can use Open also//

And again to show FDQuery1 data in grid do the same setting as like above but please change as follow.

Datasource1.Dataset := FDQuery1;

DBGrid1.DataSource := DataSource1;

And you can see the fetched data of FDQuery1

Using Parameters

With using TFDQuery component we can use parameters in SQL to get some specific data. Here I am using parameter EmpID to get a specific Employee data.

FDQuery1.Connection := FDConnection1;
FDQuery1.SQL.Text := ‘ Select * from employee where EmpNo = :EmpID’;
FDQuery1.ParamByName('EmpID').Value := 5; // will get data for EmpNo = 5//
FDQuery1.Active := True;         //We can use Open also//

Executing a DDL or DML SQL command

To execute any DDL or DML SQL command we can TFDCommand

Lets use TFDCommand. So drop a TFDCommand component on Form1 and set following properties to work.

    FDCommand1.Connection := FDConnection1;

    FDCommand1.CommandText.Text := 'delete from employee where empno = 5';

    FDCommand1.Execute();

Just like delete SQL we can use insert, update, create, alter and drop statements also. Note that FDCommand will not fetch any data from DB so its better not to use for Select statement.

Executing Stored Procedures and Functions

To execute Stored Procedures and Functions we can use TFDStoredProc. Here I have used MS-Access db so I have not used TFDStoredProc. But I can show you how to use with other DB like MS-Sql, Oracle, PostgresSql, My-Sql etc.

Put a TFDStoredProc component on Form1 and set following properties to work.

Stored Procedure

    FDStoredProc1.Connection := FDConnection1;

    FDStoredProc1.StoredProcName := 'ModifyEmployeeCountry';

    FDStoredProc1.ParamByName('param1').Value := 1; // if any parameter//

    FDStoredProc1.ExecProc;

Function

    FDStoredProc1.Connection := FDConnection1;

    FDStoredProc1.StoredProcName := 'getEmployelistToPromote';

    FDStoredProc1.ParamByName('param1').Value := 1; // if any parameter//

    FDStoredProc1.ExecFunc;

Editing data in TFDTable and TFDQuery

Obviously, FireDAC not only supports displaying but also updating of data in the dataset. In fact, the UpdateOptions property of TFDQuery offers several options to control which operations (insert/update/delete) are allowed

After active a TFDTable and TFDQuery we can add, edit and delete data exists. For that FireDAC provides some procedures as follow

FDTable1.Append;      //add a new data//

FDTable1.Edit;            //edit current existing data//

FDTable1.Post;           //saves changes into DB//

FDTable1.Delete;        //deletes data from component and DB//

FDQuery1.Append;      //add a new data//

FDQuery1.Edit;           //edit current existing data//

FDQuery1.Post;          //saves changes into DB//

FDQuery1.Delete;       //deletes data from component and DB//

Sorting data in TFDTable and TFDQuery

FireDAC does not provide any SORT property like ADO to sort data. So we have to use Index for that. We can use IndexFieldNames property.

FDTable1.IndexFieldNames := 'Name ASC';

FDQuery1.IndexFieldNames := 'Name ASC; Contact DESC';

  

Filtering data in TFDTable and TFDQuery

To filter some existing data in TFDTable and TFDQuery component we can use Filter and Filtered property.

FDTable1.Filtered := False;

FDTable1.Filter := 'EmpNo = 10';

FDTable1.Filtered := True;

FDQuery1.Filtered := False;

FDQuery1.Filter := 'EmpNo = 10';

FDQuery1.Filtered := True;

Import/Export

Besides simple data access, FireDAC offers many utilities. One of this is the TFDDataMove component. It can be used to move data between 2 datasets, or between a CSV file and a dataset. All that needs to be done is set source and destination for the data. This can be used to quickly load data into a database, or to duimp data from the database. For instance, the list of pupils can be easily exported. To do this, we drop a TFDDataMove component on the form, name if DMPupils and set its Source property to QPupils. The DestinationKind is set to skText. To let the user choose a filename for the dump, we add a TSaveFileDialog component (SDDump). The actual dump can then be coded as follows:

procedure TForm1.ADumpPupilsExecute(Sender: TObject);

Var

  FN : String;

begin

  if not SDDump.execute then

    exit

  else

    FN:=SDDump.FileName;

    FADGUIxSilentMode:=True;

    With DMPupils do

    begin

      TextFileName:=FN;

      Execute;

    end;

end;

The code is quite straightforward. Setting TextFileName and calling Execute is all that is needed to create the CSV file.

Migrate BDE to FireDAC

Migrate Interbase Express (IBExpress) to FireDAC

Migrate DbExpress to FireDAC

Migrate DBGo(ADO) to FireDAC

Migrate AnyDAC to FireDAC