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
Responsible for connection definitions and connections management.
Responsible for connection establishment with a DBMS.
Responsible for connection transaction management.
Responsible for executing SQL commands.
Binds TFDCommand and TFDMemTable.
Binds several datasets into a single in-memory storage and builds a log.
Implements an in-memory dataset.
Implements a dataset that can execute SQL queries
Implements a dataset that can execute server-side stored procedures
Implements a dataset that works with single database tables
Applies updates on behalf of queries or stored procedures that cannot post updates directly
Responsible for data movement between different data sources.
Implements a SQL script engine that can execute a series of SQL queries
Implements a dataset that can execute meta-info queries
Responsible for handling the database event notifications
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
Use TFDPhysADSDriverLink to link the Advantage Database Server driver  
Use TFDPhysASADriverLink to link the Sybase SQL Anywhere driver  
Use TFDPhysDataSnapDriverLink to link the Multi-Tier DataSnap driver  
Use TFDPhysDB2DriverLink to link the IBM DB2 driver  
Use TFDPhysFBDriverLink to link the Firebird driver .
Use TFDPhysIBDriverLink to link the Interbase driver .
Use TFDPhysInfxDriverLink to link the Informix driver  
Use TFDPhysMSAccessDriverLink to link the Microsoft Access driver  
Use TFDPhysMSSQLDriverLink to link the Microsoft SQL Server driver.
Use TFDPhysMySQLDriverLink to link the MySQL Server driver
Use TFDPhysODBCDriverLink to link the ODBC bridge driver to an application
Use TFDPhysOracleDriverLink to link the Oracle Database driver
Use TFDPhysPgDriverLink to link the PostgreSQL driver
Use TFDPhysSQLiteDriverLink to link the SQLite driver
Use TFDPhysTDataDriverLink to link the Teradata Database driver
Use TFDPhysTDBXDriverLink to link the dbExpress v 4 bridge driver to an application
Use TFDPhysMongoDriverLink to link the MongoDB driver to an application

FireDAC UI Components
Most of the applications use TFDGUIxWaitCursor and TFDGUIxLoginDialog.


Name
Description
This dialog shows an SQL query execution progress
This dialog displays the FireDAC exceptions
This dialog allows the users to enter their DB credentials
This dialog shows an SQL script execution progress
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
This class implements the Advantage backup database service.
This class implements the Advantage service, by restoring a database from a backup.
This class implements the Advantage table utilities.
This class implements the SQL Anywhere backup database service.
This class implements the SQL Anywhere database validate service.
This class implements the FB/IB backup database service.
This class implements the FB/IB setting database properties service.
This class implements the FB/IB service to query the service manager.
This class implements the FB/IB service, by restoring a database from a backup.
This class implements the FB/IB database security management service.
This class implements the FB/IB database validate and repair service.
This class implements the InterBase-only database dump service.
This class implements the Firebird-only backup database service.
This class implements the Firebird-only restore database service.
This class implements the Firebird database trace service.
This class implements the Microsoft Access database. It also creates, drops, compacts, and repairs the services.
This class implements a service to manage an Oracle database instance.
This class implements SQLite backup. It also restores and copies the database functionality
This class implements the custom SQLite collation
This class implements the custom SQLite function
This class allows you to manage the SQLite database encryption.
This class implements the SQLite database validate service
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
This class implements the engine to process the data movement between different data source and destinations.
This class defines a data source reader using a text file.
This class defines a data target writer using a text file.
This class defines a source dataset.
This class defines a destination dataset.
This class connects to the database that contains the data to copy.
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
This class implements aa dataset that can be attached to a MongoDB cursor.
This class implements a dataset that can execute MongoDB queries.
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

Comments

  1. Hello, I have a great development with IBX, many years of work and maintenance, I installed delphi XE10 and I cannot get it to work with the version of IBX that it brings. I am analyzing the options ... Keep trying to make it work, for example obtaining previous versions of IBX, which I still cannot get, or migrate everything to FireDac ... time that I do not have ... What you propose is interesting, although you have heard talk about reFind.exe? Have you tried using it? It is a embarcadero tool, there are instructions to migrate from other components but not from IBX, have you analyzed it? What is your opinion?

    ReplyDelete
  2. Sorry, the last comment is not for this post... I send it on: Migrating from Interbase Express (IBExpress) to FireDAC

    ReplyDelete

Post a Comment

Popular posts from this blog

ShellExecute in Delphi

Drawing Shapes in Delphi

MS Excel Automation in Delphi