How to use the FireDAC Monitor classes

[Lars Fosdal 1730]

How to do FireDAC monitoring or logging to file.

 

Here is a rough outline of what you need to do. The code is not ready to use as is and serves only to give you the general gist.

 

Add

  FireDAC.Moni.Base, FireDAC.Moni.RemoteClient, FireDAC.Moni.FlatFile

to your uses clause

 

Somewhere appropriate, create the following - I keep them in my DatabasePool class.

    FTracing: Boolean  // init to False

    FMonitorBy: TFDMonitorBy;  // init to mbRemote
    FMonitorLink: TFDMoniClientLinkBase; // init to nil

 

// Here is where the actual establishing of the tracing happens depending on the trace model.

procedure TDBPool.SetMonitorBy(const Value: TFDMonitorBy);
begin
  Lock;
  try
    if (FMonitorBy <> Value) and Assigned(MonitorLink)
     then FreeAndNil(FMonitorLink);
    FMonitorBy := Value;
    // mbNone or mbCustom disables the monitoring

    if MonitorBy = mbRemote
    then begin
      MonitorLink := TFDMoniRemoteClientLink.Create(nil);
    end
    else if MonitorBy = mbFlatFile
    then begin
      MonitorLink := TFDMoniFlatFileClientLink.Create(nil);
      TFDMoniFlatFileClientLink(MonitorLink).FileName := ParamStr(0)+'.'+FormatDateTime('yyyymmdd-hhnnss', Now)+'.FireDAC.log';
      TFDMoniFlatFileClientLink(MonitorLink).FileAppend := False;
    end;
    if Assigned(MonitorLink)
     then MonitorLink.Tracing := Self.Tracing;
  finally
    Unlock;
  end;
end;

// Use Tracing to enable / disable the tracing

function TDBPool.GetTracing: Boolean;
begin
  Result := FTracing;
end;

procedure TDBPool.SetTracing(const Value: Boolean);
begin
  FTracing := Value;
  if not Assigned(MonitorLink)
   then SetMonitorBy(FMonitorBy)
    else MonitorLink.Tracing := Self.Tracing;
end;

procedure TDBPool.SetMonitorLink(
  const Value: TFDMoniClientLinkBase);
begin
  FMonitorLink := Value;
end;

// When the DB connection is created - it should assign the MonitorBy value

procedure TDB_FD.CreateFireDACConnections;
const
  OSAuthent = 'No';
begin
  if not Assigned(FConnection)
  then begin
    OnConnectionCreate;
    FConnection := TFDConnection.Create(nil);
    FConnection.DriverName := DBPool.DataBaseDriverName;  // that you have selected - This example assumes an MSSQL driver - see other code block below

    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 := DBPool.MonitorBy;

  ...
end;

 

BTW - Selecting the right DatabaseDriverName can have a significant impact on performance.

Here is how I currently pick my preferred MSSQL driver.
 

Why are those constants are not in the interface section of FireDAC.Phys.MSSQL, @Dmitry Arefiev?

 

class function TDBPoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_2018_ODBC = 'ODBC DRIVER 18 FOR SQL SERVER';
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER';
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER';
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER';
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS';
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0';
{$ENDIF}
var
  DriverList : TStringList;
  WantedList : TArray<String>;
begin
  Result := ''; // Blank = Default
  WantedList := {$IFDEF MSWINDOWS}
                  {$IFDEF SQLNative}  // defined if supposed to prioritize the old SQLNCLI11
                    [C_2012_NC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC]
                  {$ELSE}
                    [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC]
                 {$ENDIF}
               {$ENDIF}
               {$IFDEF POSIX}
                 [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS]
               {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);
    for var Wanted in WantedList
     do for var Driver in DriverList
      do begin
        // DebugOut('ODBC: "' + Driver + '"');
        if CompareText(Wanted , Driver) = 0
         then Exit(Wanted);
      end;
  finally
    DriverList.Free;
  end;
end;