FireDAC Connections

[Stuart Clennett 15]

Hi,

 

I'm looking at the TMARSFDDataModuleResource and I see that it has protected members `FD` (TMarsFireDAC) and `URL` (TMARSURL).  In my descendant class both of these are `nil` references.  Is there a way to use this?

 

Reason is I'm trying to switch DB connections based on the server's own host URL.  E.g. if it's accessed via localhost or 192.168.x.x then use the LOCAL_DB connection def, otherwise use PROD_DB

 

Edit: Which also begs the question, where is the best place to call `FD.ConnectionDefName := MyConnectionDefName;` ?

 

 

Thanks

Stuart

Edited July 3, 2019 by Stuart Clennett

[Lars Fosdal 1791]

We ended up setting up the connections in code, rather than using connection definitions.

 

procedure TPSDDatabase_FD.CreateFireDACConnections;
const
  OSAuthent = 'No';
begin
  if not Assigned(FConnection)
  then begin
    OnConnectionCreate;
    FConnection := TFDConnection.Create(nil);
    FConnection.DriverName := FireDriverLink.BaseDriverId;

    FConnection.Params.Values[MSSQLParam.Server] := Trim(FHost);
    FConnection.Params.Values[MSSQLParam.Database] := Trim(FDatabaseName);
    FConnection.Params.Values[MSSQLParam.OSAuthent] := OSAuthent;
    FConnection.Params.Values[MSSQLParam.User_Name] := Trim(FUserName);
    FConnection.Params.Values[MSSQLParam.Password] := Trim(FPassword);

    FConnection.Params.MonitorBy := Self.MonitorBy;

    if DisableMARS
     then FConnection.Params.Values[MSSQLParam.MARS] := 'No';

    FConnection.Params.Values[MSSQLParam.ApplicationName] := AppInfo.Summary+';tid=' + IntToStr(GetCurrentThreadId);

    FConnection.LoginPrompt := False;

    FConnection.FormatOptions.OwnMapRules := True;
    with FConnection.FormatOptions.MapRules.Add
    do begin
      SourceDataType := dtDateTimeStamp;
      TargetDataType := dtDateTime;
    end;

    with FConnection.FormatOptions.MapRules.Add
    do begin
      SourceDataType := dtDate;
      TargetDataType := dtDateTime;
    end;

   FConnection.OnLogin := FDConnectionLoginEvent;
   FConnection.OnError := FDConnectionErrorEvent;
   FConnection.OnLost := FDConnectionLostEvent;
   FConnection.OnRestored := FDConnectionRestoredEvent;
   FConnection.OnRecover := FDConnectionRecoverEvent;
  end;
end;

 

[Stuart Clennett 15]

Thanks Lars.

 

I'm making a lot of use of the TMARSFireDAC instance (usually called FD) that gets injected into the resource classes which is great for simple data -> api tasks.  I only rarely use a separate datamodule with an FDConnection component.  When I do, I can just set the FDConnection1.ConnectionDefName to whatever is in the INI file in DataModuleCreate.

 

I can also set FD.ConnectionDefName, but the thing I'm most struggling with is where? 

 

I can't see any way to set the default connection name in BeforeHandleRequest.  The RegisterBeforeInvoke has access to the Application.Parameters (where I think I can set ['FireDAC.ConnectionDefName']) but it's called after the TMARSFireDAC instance is already created and injected. 

 

😕

 

 

Edited July 3, 2019 by Stuart Clennett

[Stuart Clennett 15]

Ah, figured it out.

    FEngine.BeforeHandleRequest :=
        function (const AEngine: TMARSEngine; const AURL: TMARSURL; const ARequest: TWebRequest; const AResponse: TWebResponse; var Handled: Boolean): Boolean
        var
          App: TMARSApplication;
        begin
          Result := True;

          // skip favicon requests (browser) ... [snip]
   
          // Handle CORS and PreFlight ... [snip]

          // Get the default connection name based on the host URL
          if (pos('localhost', AUrl.URL) > 0) or (pos('192.168.', aUrl.URL) > 0) then
            cConnectionDefname := 'LOCAL_DB'
          else
            cConnectionDefName := 'MAIN_DB'; 

          // apply to every application
          for App in AEngine.Applications.Values do
            App.Parameters.Values['FireDAC.ConnectionDefName'] := cConnectionDefName;

        end;

So I get the default connection def name based on the URL.   

 

Then set the correct parameter for each hosted application.  (My previous mistake was setting the `FireDAC.ConnectionDefName` parameter at the Engine level)

 

If anyone can think of a better or more elegant way, please let me know.

 

Thanks

 

 

[Andrea Magni 75]

Hi @Stuart Clennett,

I am happy you found a way to solve by yourself while I was in vacation 🙂

 

Your solution works but has a couple of things to consider:

1) that BeforeHandleRequest code gets executed for each incoming request (performance)

2) manipulating Parameters is not suggested as App.Parameters is like a global object (IIRC).

 

You have an other opportunity through (yet another) undocumented MARS feature:

 

    [Context, Connection('Dynamic_MyConnection', True)] FD: TMARSFireDAC;

 

The Connection attribute specifies the name of the connection definition to be used with the TMARSFireDAC instance injected.

The name supports macros (the second argument enables macro expansion).

 

You can rely on built-in macros (i.e. using some part of the token, params, username or similar things. A list is available here: https://github.com/andrea-magni/MARS/blob/a8d323558bd591589ef667eac326324302d167a9/Source/MARS.Data.FireDAC.pas#L528 ). Last chance of the macro-expander is looking for custom providers.

You can register your own in the initialization of one of your units or in the Server.Ignition.pas file:

 

  TMARSFireDAC.AddContextValueProvider(
    procedure (const AActivation: IMARSActivation;
      const AName: string; const ADesiredType: TFieldType; out AValue: TValue)
    begin
      if SameText(AName, 'Dynamic_MyConnection') then
      begin
        if Odd(SecondOf(Now))then
          AValue := 'OddConnection_' + AActivation.Request.HostName
        else
          AValue := 'EvenConnection_' + AActivation.Request.HostName;
      end;
    end
  );

 

Beware: the connection name has to be this format: [PREFIX]_[SUFFIX](_[OTHER_SUFFIX])

Using 'MyConnection' won't work for instance...

 

Pros of this approach:

1) code is executed only when needed (when the TMARSFireDAC instance gets created to be injected in your resources), all others REST endpoints won't be affected.

2) it seems more isolated to me (but this can be a personal evaluation).

 

If you need to fix some TFDConnection instances on your datamodules, try having a TMARSFireDAC instance injected in the datamodule itself and use the FD.ConnectionDefName (already expanded once injected) to fix the ConnectionDefName of your components (OnCreate event of the datamodule should be fine, or AfterConstruction method as well).

If you encounter troubles doing this, write here again: there's another hidden feature for you to fix this (when registering the datamodule as resource you can specify an anonymous method to initialize it or to call a custom constructor instead of TDatamodule.Create(AOwner)).

 

Sincerely,

Andrea

 

 

 

 

[Stuart Clennett 15]

Hello Andrea,

 

That's excellent, thank you very much for your detailed answer.  I agree with you that the Connection attribute seems a much cleaner implementation and I will have a go at refactoring my project. 

 

Thanks again,

 

Best wishes,

Stuart