Location of FireDAC files. How can I have POOLED = true?

[Bimmer]

Starting to build our first UniGui project I face myself in a situation I need an answer to.

With a standard unigui login form. I would like to be able to have multiple databases available.
Not for selection - but via the users login, through our user-api that handles auth.

The database alias (an entry in firedac connections file - ConnectionDefName) is returned from our api and I now need to set the name on the TFDConnection.

function TUniMainModule.ChooseDatabaseConnection(aDatabaseConnectionDefName: string): boolean;
begin
  if FDManager.IsConnectionDef(aDatabaseConnectionDefName) then
  begin
    DBConn.Close;
    DBConn.ConnectionDefName := aDatabaseConnectionDefName;
  end;
end;

This works - but only if the setting - POOLED is set to false in my connections file.

I'm loading FDDrivers.ini (specific different vendor files) in the Creation of the servermodule.

And the Connections.ini (databases) is loaded in mainmodule creation.

Should I do anything specific to be able to use pooled=true ?

 

 

[Sherzod]

31 minutes ago, Bimmer said:

This works - but only if the setting - POOLED is set to false in my connections file.

Hello,

If set to True, what error are you getting, or what is not working?

[AlexM123]

Hello

IMHO Firedac is very cunning

Here is an explanation

https://github.com/antoniojmsjr/MultithreadingFireDAC

[Bimmer]

Hi Sherzod - and thanks for joining my quest 🙂

 

Trying to use a connections.ini file with the setting - pooled=true - I get the following error when doing my login and connecting to the database.

Project SkyPOS.exe raised exception class EFDException with message '[FireDAC][Comp][Clnt]-507. 
Connection [DBConn: TFDConnection] cannot be pooled. 
Possible reason: connection definition is not in the FDManager.ConnectionDefs list or 
TFDConnection.Params has additional parameters'.

And I now that this indicates that the setup i wrong somehow. But I'm only setting the ConnectionDefName of the TFDConnection. And by doing that I should have what is defined as a persistent connection definiton

link to info here -> https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Defining_Connection_(FireDAC)

The connection is in the ConnectionDefs list - I know that because I've tried listing all connections on the login form. And all is correctly listed.

 

[Bimmer]

14 minutes ago, AlexM123 said:

Hello

IMHO Firedac is very cunning

Here is an explanation

https://github.com/antoniojmsjr/MultithreadingFireDAC

Hi AlexM123. I will check if it can explain my situation. Thanks.

[Bimmer]

I found a demo app - "DBLookupComboBox - Custom Remote Query" 

It uses pooled connection. But is doing it with a private setup. 

Let me try and modify the sample app to use a persistent setup. And see if it fails like my app. I will report back later.

[Sherzod]

Ok.

[AlexM123]

in the lib

 

type
  TConnectionDefDriverParams = record
    DriverDefName: string;
    VendorLib: string;
  end;

  TConnectionDefParams = record
    ConnectionDefName: string;
    Server: string;
    Database: string;
    UserName: string;
    Password: string;
    LocalConnection: Boolean;
    CharacterSet: string;
  end;

  TConnectionDefPoolParams = record
    Pooled: Boolean;
    PoolMaximumItems: Integer;
    PoolCleanupTimeout: Integer;
    PoolExpireTimeout: Integer;
  end;

 

 

procedure ConfigFDManagerConnectionFirebird(
  const pConnectionDefDriverParams: TConnectionDefDriverParams;
  const pConnectionDefParams: TConnectionDefParams;
  const pConnectionDefPoolParams: TConnectionDefPoolParams);
var
  lConnection: TFDCustomConnection;
  lFBConnectionDefParams: TFDPhysFBConnectionDefParams; // FIREBIRD CONNECTION PARAMS
  lFDStanConnectionDef: IFDStanConnectionDef;
  lFDStanDefinition: IFDStanDefinition;
begin
  //PARA CRIAR OU ALTERAR Й NECESSБRIO FECHAR A O FDMANGER REFERENTE A ConnectionDefName
  FDManager.CloseConnectionDef(pConnectionDefParams.ConnectionDefName);

  FDManager.ActiveStoredUsage := [auRunTime];
  FDManager.ConnectionDefFileAutoLoad := False;
  FDManager.DriverDefFileAutoLoad := False;
  FDManager.SilentMode := True; //DESATIVA O CICLO DE MENSAGEM COM O WINDOWS PARA APRESENTAR A AMPULHETA DE PROCESSANDO.
//  FDManager.Open;

  //DRIVER
  lFDStanDefinition := FDManager.DriverDefs.FindDefinition(pConnectionDefDriverParams.DriverDefName);
  if not Assigned(FDManager.DriverDefs.FindDefinition(pConnectionDefDriverParams.DriverDefName)) then
  begin
    lFDStanDefinition := FDManager.DriverDefs.Add;
    lFDStanDefinition.Name := pConnectionDefDriverParams.DriverDefName;
  end;
  lFDStanDefinition.AsString['BaseDriverID'] := 'FB'; //DRIVER BASE
  if not pConnectionDefDriverParams.VendorLib.Trim.IsEmpty then
    lFDStanDefinition.AsString['VendorLib'] := pConnectionDefDriverParams.VendorLib; //DEFINE O CAMINHO DA DLL CLIENT DO FIREBIRD.

  //CONNECTION
  lFDStanConnectionDef := FDManager.ConnectionDefs.FindConnectionDef(pConnectionDefParams.ConnectionDefName);
  if not Assigned(FDManager.ConnectionDefs.FindConnectionDef(pConnectionDefParams.ConnectionDefName)) then
  begin
    lFDStanConnectionDef := FDManager.ConnectionDefs.AddConnectionDef;
    lFDStanConnectionDef.Name := pConnectionDefParams.ConnectionDefName;
  end;

  //DEFINIЗГO DE CONEXГO: PRIVADO :: https://docwiki.embarcadero.com/RADStudio/Sydney/en/Defining_Connection_(FireDAC)
  lFBConnectionDefParams := TFDPhysFBConnectionDefParams(lFDStanConnectionDef.Params);
  lFBConnectionDefParams.DriverID := pConnectionDefDriverParams.DriverDefName;
  lFBConnectionDefParams.Database := pConnectionDefParams.Database;
  lFBConnectionDefParams.CharacterSet := csUTF8; // <<<---------------- NEGLECTED
  lFBConnectionDefParams.UserName := pConnectionDefParams.UserName;
  lFBConnectionDefParams.Password := pConnectionDefParams.Password;
  lFBConnectionDefParams.Server := pConnectionDefParams.Server;
  lFBConnectionDefParams.Protocol := TIBProtocol.ipLocal;
  if not pConnectionDefParams.LocalConnection then
    lFBConnectionDefParams.Protocol := TIBProtocol.ipTCPIP;

  lFBConnectionDefParams.Pooled := pConnectionDefPoolParams.Pooled;
  lFBConnectionDefParams.PoolMaximumItems := pConnectionDefPoolParams.PoolMaximumItems;
  lFBConnectionDefParams.PoolCleanupTimeout := pConnectionDefPoolParams.PoolCleanupTimeout;
  lFBConnectionDefParams.PoolExpireTimeout := pConnectionDefPoolParams.PoolExpireTimeout;

  //WriteOptions
  lConnection := TFDCustomConnection.Create(nil);
  try
    lConnection.FetchOptions.Mode := TFDFetchMode.fmAll; //fmAll
    lConnection.ResourceOptions.AutoConnect := False;
//    lConnection.ResourceOptions.AutoReconnect := True;  //PERDA DE PERFORMANCE COM THREAD

    with lConnection.FormatOptions.MapRules.Add do
    begin
      SourceDataType := dtDateTime; { TFDParam.DataType }
      TargetDataType := dtDateTimeStamp; { Firebird TIMESTAMP }
    end;

    lFDStanConnectionDef.WriteOptions(lConnection.FormatOptions,
                                      lConnection.UpdateOptions,
                                      lConnection.FetchOptions,
                                      lConnection.ResourceOptions);
  finally
    lConnection.Free;
  end;

  if (FDManager.State <> TFDPhysManagerState.dmsActive) then
    FDManager.Open;
end;

 

[Bimmer]

This problem - is not a problem.

This happens because I did not clear params of my connection.

Having a blank setup page like this. 

is not the same as - "there are no params"!

I made the error of reusing an earlier FDConnection component ... and that cost me some hours of debugging 😕 

And that will give the error - that the connection cannot be pooled.

Thanks all for your help in trying to help me along. And I hope this information can help others as well.