Capture mysql_real_query from FireDAC connection

How can I capture just the mysql_real_query on a FireDAC connection?

I have a FireDac connection on Delphi with a TFDMoniCustomClientLink. I need to capture just mysqL_real_query. I tried enabling and disabling all the EventKinds, but I could't find a way to do that. The closer I found was to enable just the ekVendor, but it comes with a lot more information than the mysqL_real_query.

Edit: The mysql_real_query is a section on the log text file generated by TFDMoniCustomClienteLink. This section show the sql executed on the database. The only references I found to this term are here: http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Phys.MySQLWrapper.TMySQLLib.mysql_real_query and here https://dev.mysql.com/doc/refman/5.7/en/mysql-real-query.html.

Code in the OnOutput event from TFDMoniCustomClientLink:

procedure TDmConnX.FDMonitorOutput(ASender: TFDMoniClientLinkBase;
  const AClassName, AObjName, AMessage: string);
var
  lstLog: TStringList;
  sFile: ShortString;
begin
  lstLog := TStringList.Create;
  try
    sFile := 'C:\log.txt';
    if FileExists(sFile) then
      lstLog.LoadFromFile(sFile);
    lstLog.Add(AMessage);
    lstLog.SaveToFile(sFile);
  finally
    lstLog.Free;
  end;
end;

Log file generated:

 . mysql_get_client_info [Ver="5.1.37"]
 . mysql_init
 . mysql_options [option=1, arg=0]
 . mysql_real_connect [host="127.0.0.1", user="user", passwd="***", db="banco", port=3306, clientflag=198158]
 . mysql_get_server_info [Ver="5.1.73-community"]
 . mysql_real_query [q="SET SQL_AUTO_IS_NULL = 0
 . mysql_insert_id [res=0]
 . mysql_real_query [q="SHOW VARIABLES LIKE 'lower_case_table_names'
 . mysql_store_result
 . mysql_fetch_row [res=$06530BE8]
 . mysql_fetch_lengths [res=$06530BE8]
 . mysql_free_result [res=$06530BE8]
 . mysql_get_server_info [Ver="5.1.73-community"]
 . mysql_get_client_info [Ver="5.1.37"]
 . mysql_character_set_name [res="latin1"]
 . mysql_get_host_info [res="127.0.0.1 via TCP/IP"]
 . mysql_get_server_info [Ver="5.1.73-community"]
 . mysql_get_client_info [Ver="5.1.37"]
 . mysql_character_set_name [res="latin1"]

I need to capture just the SQL, eliminating event the " . mysql_real_query [q=""

I was hoping that there was some configuration that I can change to just export the real SQL, without the sections, so I would not need to check for patterns in the string.

Solution

What you want to do is not possible with the shipped trace monitor. It is because FireDAC's tracing is not developed for custom entries and as you correctly identified, calls of mysql_real_query API function are categorized by the ekVendor event kind so there is no way to distinguish between the other messages of this kind except parsing the message itself, which is ugly way. So, let's try to go in a different way.

1. Read the SQL command passed to the DBMS

It's not clear from the question, but you've confirmed in your comment that you actually want to log just SQL commands that are passed to the DBMS. If you lose the possibility of using trace monitor, you can read the SQL command, after it's prepared, from the Text property (that's actually covered in the Checking the SQL Command Text chapter).

In case when EFDDBEngineException exception is raised, you can read the SQL command from its SQL property (that's also covered in the mentioned chapter).

2. Intercept specific DBMS API function

If you wanted to stay by the idea of monitoring specific API function calls without altering FireDAC source, you could write handler for the driver's OnDriverCreated event, intercept there a function of your interest, storing the original pointer and doing what you need (including calling the original stored function) in you intercept function body. For example, for the mysql_real_query function it could be like this:

uses
  FireDAC.Phys.MySQLWrapper, FireDAC.Phys.MySQLCli;

var
  OrigRealQuery: TPrcmysql_real_query;

function MyRealQueryIntercept(mysql: PMYSQL; const q: my_pchar; length: my_ulong): Integer;
  {$IFDEF MSWINDOWS} stdcall {$ELSE} cdecl {$ENDIF};
begin
  { ← do whatever you need before the original function call }
  Result := OrigRealQuery(mysql, q, length); { ← call the original function }
  { ← do whatever you need after the original function call }
end;

procedure TForm1.FDPhysMySQLDriverLink1DriverCreated(Sender: TObject);
var
  CliLib: TMySQLLib;
begin
  CliLib := TMySQLLib(TFDPhysMySQLDriverLink(Sender).DriverIntf.CliObj);

  OrigRealQuery := CliLib.mysql_real_query; { ← store the original function }
  CliLib.mysql_real_query := MyRealQueryIntercept; { ← replace current with intercept }
end;

But that way is very specific and costs you an extra function call overhead.

3. Write your own trace monitor

Trace monitor is not as flexible as might have been, but there is still the way to write your own and receive information passed to the Notify method instead of concatenated messages (but of course, you must know the meaning of the trace notification arguments).

Here is one example I've crafted by the TFDMoniCustomClientLink class (but it's no good for the used RTTI, but you can tune it up by yourself):

unit FireDAC.Moni.Extended;

interface

uses
  System.SysUtils, System.Classes, System.Rtti, FireDAC.Stan.Intf, FireDAC.Moni.Base;

type
  IFDMoniClientNotifyHandler = interface(IFDMoniClientOutputHandler)
    ['{32F21585-F9CC-4C41-A7DF-10B8C1B98006}']
    procedure HandleNotify(AKind: TFDMoniEventKind; AStep: TFDMoniEventStep;
      ASender: TObject; const AMsg: string; const AArgs: TArray<TValue>);
  end;

  TFDMoniExtendedClient = class(TFDMoniClientBase, IFDMoniCustomClient)
  private
    FSynchronize: Boolean;
    function GetSynchronize: Boolean;
    procedure SetSynchronize(AValue: Boolean);
  protected
    procedure Notify(AKind: TFDMoniEventKind; AStep: TFDMoniEventStep;
      ASender: TObject; const AMsg: string; const AArgs: array of const); override;
  public
    destructor Destroy; override;
  end;

  TFDMoniNotifyEvent = procedure(ASender: TObject; AKind: TFDMoniEventKind;
    AStep: TFDMoniEventStep; const AMsg: string; const AArgs: TArray<TValue>) of object;

  TFDMoniExtendedClientLink = class(TFDMoniClientLinkBase, IFDMoniClientNotifyHandler)
  private
    FOnNotify: TFDMoniNotifyEvent;
    FExClient: IFDMoniCustomClient;
    function GetSynchronize: Boolean;
    procedure SetSynchronize(AValue: Boolean);
    procedure SetOnNotify(AValue: TFDMoniNotifyEvent);
  protected
    function GetMoniClient: IFDMoniClient; override;
    procedure HandleNotify(AKind: TFDMoniEventKind; AStep: TFDMoniEventStep;
      ASender: TObject; const AMsg: string; const AArgs: TArray<TValue>); virtual;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    property ExClient: IFDMoniCustomClient read FExClient;
  published
    property Tracing;
    property Synchronize: Boolean read GetSynchronize write SetSynchronize default False;
    property OnNotify: TFDMoniNotifyEvent read FOnNotify write SetOnNotify;
  end;

implementation

uses
  FireDAC.Stan.Factory;

type
  TFDMoniExtendedClientMsg = class
  private
    FMsg: string;
    FArgs: TArray<TValue>;
    FKind: TFDMoniEventKind;
    FStep: TFDMoniEventStep;
    FSender: TObject;
    FClient: IFDMoniCustomClient;
  protected
    procedure DoNotify; virtual;
  public
    constructor Create(const AClient: IFDMoniCustomClient; ASender: TObject;
      AKind: TFDMoniEventKind; AStep: TFDMoniEventStep; const AMsg: string;
      const AArgs: TArray<TValue>);
  end;

{ TFDMoniExtendedClientMsg }

constructor TFDMoniExtendedClientMsg.Create(const AClient: IFDMoniCustomClient;
  ASender: TObject; AKind: TFDMoniEventKind; AStep: TFDMoniEventStep;
  const AMsg: string; const AArgs: TArray<TValue>);
var
  I: Integer;
begin
  inherited Create;
  FMsg := AMsg;
  SetLength(FArgs, Length(AArgs));
  for I := Low(FArgs) to High(FArgs) do
    FArgs[I] := AArgs[I];
  FKind := AKind;
  FStep := AStep;
  FSender := ASender;
  FClient := AClient;
end;

procedure TFDMoniExtendedClientMsg.DoNotify;
var
  Handler: IFDMoniClientNotifyHandler;
begin
  if Supports(FClient.OutputHandler, IFDMoniClientNotifyHandler, Handler) then
    Handler.HandleNotify(FKind, FStep, FSender, FMsg, FArgs);
  Destroy;
end;

{ TFDMoniExtendedClient }

destructor TFDMoniExtendedClient.Destroy;
begin
  SetTracing(False);
  inherited;
end;

function TFDMoniExtendedClient.GetSynchronize: Boolean;
begin
  Result := FSynchronize;
end;

procedure TFDMoniExtendedClient.SetSynchronize(AValue: Boolean);
begin
  FSynchronize := AValue;
end;

procedure TFDMoniExtendedClient.Notify(AKind: TFDMoniEventKind; AStep: TFDMoniEventStep;
  ASender: TObject; const AMsg: string; const AArgs: array of const);
var
  InArray: TArray<TValue>;
  Payload: TFDMoniExtendedClientMsg;
  Handler: IFDMoniClientNotifyHandler;
begin
  if Supports(GetOutputHandler, IFDMoniClientNotifyHandler, Handler) then
  begin
    InArray := ArrayOfConstToTValueArray(AArgs);
    if TThread.CurrentThread.ThreadID = MainThreadID then
      Handler.HandleNotify(AKind, AStep, ASender, AMsg, InArray)
    else
    begin
      Payload := TFDMoniExtendedClientMsg.Create(Self, ASender, AKind, AStep, AMsg, InArray);
      TThread.Queue(nil, Payload.DoNotify);
    end;
  end;
  inherited;
end;

{ TFDMoniExtendedClientLink }

constructor TFDMoniExtendedClientLink.Create(AOwner: TComponent);
begin
  inherited;
  FExClient := MoniClient as IFDMoniCustomClient;
end;

destructor TFDMoniExtendedClientLink.Destroy;
begin
  FExClient := nil;
  inherited;
end;

function TFDMoniExtendedClientLink.GetSynchronize: Boolean;
begin
  Result := FExClient.Synchronize;
end;

procedure TFDMoniExtendedClientLink.SetSynchronize(AValue: Boolean);
begin
  FExClient.Synchronize := AValue;
end;

procedure TFDMoniExtendedClientLink.SetOnNotify(AValue: TFDMoniNotifyEvent);
begin
  if (TMethod(FOnNotify).Code <> TMethod(AValue).Code) or
     (TMethod(FOnNotify).Data <> TMethod(AValue).Data) then
  begin
    if Assigned(AValue) then
      MoniClient.OutputHandler := Self as IFDMoniClientNotifyHandler
    else
      MoniClient.OutputHandler := nil;
    FOnNotify := AValue;
  end;
end;

function TFDMoniExtendedClientLink.GetMoniClient: IFDMoniClient;
var
  Client: IFDMoniCustomClient;
begin
  FDCreateInterface(IFDMoniCustomClient, Client);
  Result := Client as IFDMoniClient;
end;

procedure TFDMoniExtendedClientLink.HandleNotify(AKind: TFDMoniEventKind;
  AStep: TFDMoniEventStep; ASender: TObject; const AMsg: string; const AArgs: TArray<TValue>);
begin
  if Assigned(FOnNotify) and not (csDestroying in ComponentState) then
    FOnNotify(Self, AKind, AStep, AMsg, AArgs);
end;

var
  Factory: TFDFactory;

initialization
  Factory := TFDSingletonFactory.Create(TFDMoniExtendedClient, IFDMoniCustomClient);

finalization
  FDReleaseFactory(Factory);

end.

Important, when using class like this you must not include FireDAC.Moni.Custom module in your project otherwise the IFDMoniCustomClient interface will be registered for a different class (that's because a trace monitor for the mbCustom kind of MonitorBy connection parameter is created by the class registered for the IFDMoniCustomClient interface; this is what is done in the initialization block of the above unit).

Simplified example of use:

uses
  System.Rtti, FireDAC.Moni.Extended;

type
  TForm1 = class(TForm)
    FDPhysMySQLDriverLink1: TFDPhysMySQLDriverLink;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FDPhysMySQLDriverLink1DriverCreated(Sender: TObject);
  private
    FMonitor: TFDMoniExtendedClientLink;
    procedure MonitorNotify(ASender: TObject; AKind: TFDMoniEventKind;
      AStep: TFDMoniEventStep; const AMsg: string; const AArgs: TArray<TValue>);
  end;

implementation

procedure TForm1.FormCreate(Sender: TObject);
begin
  FMonitor := TFDMoniExtendedClientLink.Create(nil);
  FMonitor.OnNotify := MonitorNotify;
  FMonitor.EventKinds := [ekVendor];
  FMonitor.Tracing := True;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FMonitor.Free;
end;

procedure TForm1.MonitorNotify(ASender: TObject; AKind: TFDMoniEventKind;
  AStep: TFDMoniEventStep; const AMsg: string; const AArgs: TArray<TValue>);
begin
  if (AKind = ekVendor) and (AStep = esProgress) and (AMsg = 'mysql_real_query') and
    (Length(AArgs) >= 1) and (AArgs[1].IsType<string>)
  then
    ShowMessage(AArgs[1].AsType<string>);
end;

This way is also very specific for your needs and costs you extra overhead of new RTTI, but that's what you can optimize.