[Firedac] Connection Pooling

[Alberto Fornés 22]

Hi, I'm trying to establish a connection to Firebird, using the connection pool. In my project I have several datamodules, which I create and destroy continuously. The first datamodule that is created reads the connection configuration, connects (this does it well) and then saves the parameters of this connection in the connection definition (I use the folowing code):
 Connect is a TFDConnection component

    var oDef: IFDStanConnectionDef: = FDManager.ConnectionDefs.AddConnectionDef;     
    oDef.Name:= defConn;     
    oDef.Params.Pooled: = true;     
    oDef.Params.DriverID: = 'FB';     
    oDef.Params.Database: = Connect.Params.Values ['Database'];     
    oDef.Params.Password: = Connect.Params.Values ['Password'];     
    oDef.Params.UserName: = Connect.Params.Values ['UserName'];     
    oDef.Params.Values ['ExtendedMetadata']: = 'true';     
    oDef.Params.Values ['CharacterSet']: = 'UTF8';

Then, when I open other datamodules, the first thing I do is assign the definition of the connection, and then I connect, but this gives an error it seems that it does not assign the user and password correctly:
 

Connect is the TFDConnection component of the new created datamodule  

Connect.ConnectionDefName: = defConn; 
Connect.Connected: = true; (this gives connection error)

Any suggestions of things to review and / or change?, thanks

Note: The datamodule where I set the connection parameters and establish the connection definition, is also destroyed after read and apply this settings.

Edited August 24, 2019 by Alberto Fornés

[Hans J. Ellingsgaard 21]

You could have just one FDConnection, and let all the other datamodules use that connection. You would then have just one place to set up all your connection logic,

 

And by the way... Some SQL servers have licens restrictions on how many simultanious connections you can have, and if your are using one connection for each datamodule, you could easily end up being unable to connect to the SQL server.

[Uwe Raabe 2057]

Perhaps you are missing this line after the call to AddConnectionDef?

FDManager.Active := true;

 

[Guest]

Interesting, i know my library, IBO, also has this. But i never used it.

 

My REST-like servers pools the datamodules. Say one for search, one for "category lists", one for userhandling and so on. Since i manage datamodules i can close the respective connection on each module when load is low so that the RDBMs garbage collection and transaction handling do not get overused. I design so that different requests means different SQL parameter values (avoid building the SQL whenever possible) so the next request can use several database queries and process results from them without the need for a re-opening. I'm quite happy with this solution.

 

I never do php-ish things like; create query, execute and free. If those are many, well one would gain from pooling, but the DB needs to start all over for each little tidbit of info.

 

My MVVM-delphi like servers (consumed by an delphi application) creates a datamodule per user so the gain is not that extreme.

[Uwe Raabe 2057]

18 hours ago, Hans J. Ellingsgaard said:

You could have just one FDConnection, and let all the other datamodules use that connection.

While this may suit a single threaded application, the pooling approach is the recommended way for multi-threaded DB access. 

 

http://docwiki.embarcadero.com/RADStudio/Rio/en/Multithreading_(FireDAC)#Connection_Pooling

[Alberto Fornés 22]

18 hours ago, Uwe Raabe said:

Perhaps you are missing this line after the call to AddConnectionDef?

FDManager.Active := true;

 

 Well and the end I call this method

FDManager.Open;

 I think do the same (not sure). Anyway after trying a lot of changes, I can do the job creating the connection by code and assigning the connection definition before connect:

 

 Connect:= TFDConnection.Create(nil); 
 Connect.Params.DriverID:= driverDB; 
 Connect.ConnectionDefName:= defConn;

also I've changed how to set the username and password:

 

//before I use this:
Connect.Params.Values['Database']:= TServerConfig.DataPath('DB') + TServerConfig.DBName;
Connect.Params.Values['Password']:= PwdFirebird;
Connect.Params.Values['UserName']:= UserDB;

//and change to this:
Connect.Params.Database:= TServerConfig.DataPath('DB') + TServerConfig.DBName;
Connect.Params.Password:= PwdFirebird;
Connect.Params.UserName:= UserDB;

With this changes it works.

[Hans J. Ellingsgaard 21]

1 hour ago, Uwe Raabe said:

While this may suit a single threaded application, the pooling approach is the recommended way for multi-threaded DB access. 

 

http://docwiki.embarcadero.com/RADStudio/Rio/en/Multithreading_(FireDAC)#Connection_Pooling

Yes you'r right. I was thinking about a single user client with multiple datamodules.