menu
  Home  ==>  papers  ==>  db  ==>  firedac  ==>  delphi_firedac_connection   

Delphi FireDac Connection - Felix John COLIBRI.


1 - Connection Using FireDac

Firedac can use several techniques to connect to a database.

This article presents

  • a first example using code only technique
  • the FdDrivers.Ini and FdConnectionDefs.Ini files
  • the UML class diagram of the involved components


2 - FireDac connection using code

2.1 - The required information

Firedac requires two sets of information
  • driver information
  • database specific information


2.2 - tFdPhysIbDriverLink

The driver information is contained in the tFdPys_xxx_DriverLink Class.

There is such a driver link for each supported Sql Engine:

firedac_physical_driver_links



For Interbase / Firebird, the fields required are:

  • BaseDriverId is a predefined ReadOnly name, 'IB' for the FireBird tFdIbDriverLink
  • DriverId is an identifier which will be used by the tFdConnection to find its driver information
  • VendorHome and VendorLib specify the location and name of the FireBird client .DLL


So in our case:
  • the client driver is in the FireBird intallation folder

    firebird_client_dll

  • and initialisation by code looks like

    With ADPhysIBDriverLink1 Do
    Begin
      // BaseDriverId:= 'IB'; ReadOnly
      DriverId:= 'FB25';
      VendorHome := 'C:\Program Files\Firebird\Firebird_2_5'
      VendorLib := 'fbclient.dll';
    End// with ADPhysIBDriverLink1



Please note
  • the VendorHome is the path the Firebird's home, not to the BIN\ where the FbClient.Dll is stored. Specifying the bin\ path works also
  • we can also specify the full file name in VendorLib

    With ADPhysIBDriverLink1 Do
    Begin
      DriverId:= 'FB25';
      VendorLib:= 'C:\Program Files\Firebird\Firebird_2_5\bin\fbclient.dll';
    End// with ADPhysIBDriverLink1

We also used a 64 bit Windows version (Windows Server 2012 R2), and
  • there are two "Program Files" folders:

    x86

  • Firebird was installed in the default (64 bit) folder, but the Firebird install contains a Wow64 bit folder where the 32 bit FbClient.Dll lives:

    firebird_client_dll_wow64



2.3 - The tFdConnection

We can use either DriverId and Params or ConnectionDefName.

2.3.1 - DriverId and Params

All connection information can be specified with the DriverId and Params properties:
  • DriverId is the name of a tFdPhysIbDriverLink.DriverId. In our case this is 'FB25'
  • Params contains the other parameters:
    • Server is the Ip address of the Sql Server
    • Database is the path and file name of the database file
    • User_Name and Password are the login parameters


Here is our example:

With AdConnection1 Do
Begin
  Close;
  With Params Do
  Begin
    Add('Server=localhost');

    Add('Database= C:\prog\_data\_fb_25\MASTAPP.FDB');

    Add('User_Name=SYSDBA');
    Add('Password=masterkey');
  End// with Params do

  LoginPrompt:= False;

  Open;
End// with AdConnection1



Please note that

  • DriverId has the same value as tFdPhysIbDriverLink.DriverId
  • this name can also be specified in the Params property. In fact, specifying DriverId adds the Params value 'DriverId=FB25' to the Params list
  • if hardcoding those values bothers you, you still can
    • stuff the parameters in some .Txt file
    • use Params.LoadFromFile to load them


2.4 - Using the Object Inspector

Of course we could specify all the tPhysIbDriverLink and tFdConnection properties we detailed in the previous section in the Object Inspector.




3 - Using FdDrivers.Ini and FdConnectionDefs.Ini

3.1 - The .INI files

Instead of initializing the detailes driver and database properties, we can also use two .Ini files which
  • contain the same information
  • are stored outside of the program, avoiding to hardcode the information in the code or the .DFM


We can use two files :
  • fdDrivers.INI which specifies drivers information
  • fdConnectionDefs.INI which specifies database informations
Both files are by default in the Firedac\db\ folder:

vista_32_firedac_pathes

As usual, it is a good idea to make a safety copy of those files before starting to modify them.



3.2 - Location of the .INI files

The .INI files can be placed
  • for use at designe time, in the \Delphi\BIN\ folder
  • for use at run time
    • in the folder of the application's .EXE
    • in the folder specified by the Registry

      firedac_connectiondef_registry_entry

    • in a folder specified by the FdManager.DriverDefFileName and FdMaager.ConnectionDefFileName
The initial files are stored in the Firedac\db\ folder when Firedac is installed. The other copies (the Delphi\Bin\ version for use at design time, the .EXE version, the specific location version, the distributed version etc) must be copied manually.



For the 64 bit Windows, the FireDac components were installed using the Xe3 .ZIP, and the default XE3 folder was chosen to nest those components

  • here are the FireDac .INI files

    win64_firedac_ini_files

  • and the registry entries are

    win64_firedac_registry_entries



The important point is that you usually have TWO copies of the .INI files, one in the \Delphi\BIN\ folder and one in the .EXE (registry, etc) folder. And a copy which must be distributed with the application.



3.3 - fdDrivers.INI

This file contains a section for each Sql Engine. By default this file is empty, but the fdDrivers_sample.txt contains some examples :

 
[ADDrivers.ini]
Encoding=UTF8

[Ora920]
BaseDriverID=Ora
VendorHome=OraHome815

[OraXE]
BaseDriverID=Ora
VendorHome=XE

[MySQL327]
BaseDriverID=MySQL
VendorLib=e:\MySQL\3-23\Bin\LIBMYSQL.DLL

[MySQL559]
BaseDriverID=MySQL
VendorLibWin32=e:\MySQL\5-5-9\Lib\LIBMYSQL.DLL
VendorLibWin64=e:\MySQL\5-5-9\Lib\LIBMYSQL.DLL

[MySQL510_Embedded]
BaseDriverID=MySQL
VendorLib=e:\MySQL\5-1-24\Lib\LIBMYSQLD.DLL
EmbeddedArgs=--datadir=./data;--language=./;--skip-innodb;--skip-networking

[MSSQL_2000]
BaseDriverID=MSSQL
ODBCDriver=SQL SERVER

[MSSQL_2005]
BaseDriverID=MSSQL
ODBCDriver=SQL NATIVE CLIENT

[IB2007]
BaseDriverID=IB
VendorLib=e:\ib\ib2007\bin\gds32.dll

[IB2009]
BaseDriverID=IB
VendorLib=e:\ib\ib2009\bin\gds32.dll

[FB25]
BaseDriverID=IB
VendorLibWin32=e:\ib\fb25\bin\fbclient.dll
VendorLibWin64=e:\ib\fb25_x64\bin\fbclient.dll

[FB25_Embedded]
BaseDriverID=IB
VendorLib=e:\ib\fb25_embedded\fbembed.dll

[PG90]
BaseDriverID=PG
VendorLibWin32=C:\Program Files\PostgreSQL\9.0\bin\libpq.dll
VendorLibWin64=C:\Program Files\PostgreSQL\9.0_x64\bin\libpq.dll

The file used for this article, which is in the downloadable .ZIP is

 
[ADDrivers.ini]
Encoding=UTF8

[FB25]
BaseDriverID=IB
VendorLibWin32=C:\Program Files\Firebird\Firebird_2_5\bin\fbclient.dll

[FB25_64_BIT_PC_32_BIT_TARGET]
BaseDriverID=IB
VendorLibWin32=C:\Program Files\Firebird\Firebird_2_5\WOW64\bin\fbclient.dll

where

  • FB25 is used on a 32 bit Vista PC
  • FB25_64_BIT_PC_32_BIT_TARGET is for 32 bit .EXE compiled on a 64 bit Windows


Please note
  • the first section is a section defining the text encoding
  • if you use a file with only one section

     
    [FB25]
    BaseDriverID=IB
    VendorLibWin32=C:\Program Files\Firebird\Firebird_2_5\bin\fbclient.dll

    when you connect the database, you will get an error

    firedac_ini_without_encoding_section

    Adding any section, even a bogus one, will open the connection without any problem

     
    [bogus]

    [FB25]
    BaseDriverID=IB
    VendorLibWin32=C:\Program Files\Firebird\Firebird_2_5\bin\fbclient.dll




3.4 - FdConnectionDefs.INI

This file contains the parameters of some databases. It usually contains informations about
  • the Tcp / Ip address
  • the database path and file name
  • the User / Password login


Here is the content of the FdConnectionDefs.Ini which was installed with Delphi Xe3:

 
[SQLite_Demo]
DriverID=SQLite
Database=$(ADHOME)\DB\Data\ADDemo.sdb

[IB_Demo]
DriverID=FB25
Server=localhost
Database=C:\prog\db\_firedac\_data\fb_25_northwind.fdb
User_Name=SYSDBA
Password=masterkey

[SQLite_Demo_Pooled]
DriverID=SQLite
Database=$(ADHOME)\DB\Data\ADDemo.sdb
Pooled=True

...

The ConnectionDefs_sample.INI contains a couple of other definitions (Sql Server, Oracle, ASA, Postgres etc)



And here is our own FdConnectionDefs.Ini:

 
[ADConnectionDefs.ini]
Encoding=UTF8

[DBDEMOS]
DriverID=FB25
Server=localhost
Database=C:\_colibri\_data\MASTAPP_D6_REGENERATED.FDB
User_Name=SYSDBA
Password=E3h6&!*@

[DBDEMOS_NO_BIN]
DriverID=FB25_NO_BIN
Server=localhost
Database=C:\_colibri\_data\MASTAPP_D6_REGENERATED.FDB
User_Name=SYSDBA_Y
Password=masterkey

[IB_Demo]
DriverID=IB
Server=localhost
Database=C:\prog\_data\_fb_25\employee_205.fdb
User_Name=SYSDBA
Password=masterkey



3.4.1 - Using ConnectionDefs

To specify which database we want to use, we simply enter the name of the FdConnectionDefs.Ini :
   drop a tFdConnection on the form
   set its COnnectionDefName to Ib_Demo
   uncheck LoginPrompt
   check Connected


Note that
  • the DriverId Params route and the ConnectionDefName route are exclusive: specifying DriverId clears ConnectionDefName and vice versa


Note that
  • there is no direct tFdConnection property specifying the FdDriverLink
  • it turns out that
    • our Ib_demo section specifies
      • the Sql engine : BaseDrvierId is IB
      • the DriverId, FB25
    • to find the FireBird client .DLL, FireDac
      • uses the FdManager singleton
      • the FdManager contains the name of the FdDrivers.Ini
      • this .Ini specifies where the FireBird client .Dll is located
      • when the connection is opened, the driver is loaded using LoadLibrary


3.4.2 - tFdConnection.ConnectionDefName

Instead of specifying the FdConnection.ConnectionDefName in the Object Inspector, we can specify this property using code

With AdConnection1 Do
Begin
  Close;

  ConnectionDefName:= 'DBDEMOS';

  LoginPrompt:= False;

  Open;
End// with AdConnection1




4 - The FdConnection component editor

Like many other database access layers, Firedac has a component editor. If we double click on FdConnection1, this editor is displayed:

firedac_fdconnection_component_editor



We can then

  • either select the ConnectionDef

    fdconnection_component_editor_connection_def

    and we can test the connection

  • or the DriverId

    fdconnection_component_editor_connection_id

    and we have to manually fill the required values

Closing the component editor fill the Object Inspector properties




5 - The Firedac Connection Demo

Firedac has a specific sample to demonstrate several connection possibilities located in:
    samples\Phys Layer\IADPhysConnection\CreateConnection\CreateConnection.dpf

A couple of features are worth mentionning:

  • the systematic use of Interfaces to get the connection

    Var l_i_fd_physical_connectionIADPhysConnection;

      ADPhysManager.ConnectionDefs.Storage.FileName := '$(ADHOME)\DB\ADConnectionDefs.ini';
      ADPhysManager.Open;
      // -- create the physical connection using one of the ConnectionDef
      ADPhysManager.CreateConnection('DbDemos'l_i_fd_physical_connection);

      l_i_fd_physical_connection.Open;

      l_i_fd_physical_connection.Close;
      l_i_fd_physical_connection := Nil;




Note that
  • the average FireDac application usually imports many FireDac units:

    Uses Windows ...
      uADGUIxIntfuADGUIxFormsWaituADStanIntfuADCompGUIx,
      uADPhysIBuADStanOptionuADStanErroruADPhysIntfuADStanDef,
      uADStanPooluADStanAsyncuADPhysManageruADCompClientuADStanParam,
      uADDatSManageruADDAptIntfuADDAptManageruADCompDataSet,
      uADGUIxFormsfLoginuADGUIxFormsfError

  • in this example, we tried to add as few units as possible
    • we first imported uAdPhysIntf
    • the code compiled, but trying to open the connection triggered an exception about a missing standard definition factory :

      firedac_standef_factory_missing

    • fianlly, the physical IB driver factory was missing:

      firedac_factory_missing

    In the end, we only had to import 3 FireDac units

    Uses ...
        , uAdPhysIntf
        , uAdStanDef
        , uADPhysIB
        ;

    This is of no concern to the application developper, but is a good indication of the degree of information hiding achieved by the FireDac library.




6 - The FireDac Connection UML Class diagrams

6.1 - The FdConnection Classes

firedac_connection_class_diagram



6.2 - The Physical Driver Classes

We can develop the driver part

physical_driver_link



Please note that

  • there is a PhysManagerObj global variable which is a singleton (it is even in the Interface)
  • in the tPhysDriverLink.Create constructor, each physical driver link registers itself in the physical manager list
  • whenever a client library must be loaded, the singleton is used to locate the relevant physical driver link and load the .DLL
We can also mention that the tFdPhysManager is the real connection workhorse, the tFdManger acting as a wrapper to present many of the tFdPhysManager methods and properties.




7 - Download the Sources

Here are the source code files: The .ZIP file(s) contain:
  • the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
  • any .TXT for parameters, samples, test data
  • all units (.PAS) for units
Those .ZIP
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • for Delphi 6 projects, can be used from any folder (the pathes are RELATIVE)
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
To use the .ZIP:
  • create or select any folder of your choice
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

The Pascal code uses the Alsacian notation, which prefixes identifier by program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lass etc. This notation is presented in the Alsacian Notation paper.
The .ZIP file(s) contain:

  • the main program (.DPROJ, .DPR, .RES), the main form (.PAS, .ASPX), and any other auxiliary form or files
  • any .TXT for parameters, samples, test data
  • all units (.PAS .ASPX and other) for units
Those .ZIP
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path outside from the container path creation etc).
To use the .ZIP:
  • create or select any folder of your choice.
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

The Pascal code uses the Alsacian notation, which prefixes identifier by program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lass etc. This notation is presented in the Alsacian Notation paper.



Please note

  • you can download the .ZIP containting the Mastapp and Employee database
  • you must adjust the pathes and database file names to the pathes of the databases you want to connect to
  • those changes must be performed
    • in the code connection for the first .ZIP
    • in both the FireDac installation and the .EXE folder .INIs. Our .INI work for a database folder at the same level as the project folders


As usual:
  • please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :
     

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.



8 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: dec-15. Last updated: dec-15 - 99 articles, 220 .ZIP sources, 1068 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2016. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
        – first_firedac_app
        – delphi_firedac_connection
      – bde_unidac_migration
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
    + vcl
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog