delphi - 从 FireDAC 连接捕获 mysql_real_query

Question

如何仅捕获 FireDAC 连接上的 mysql_real_query？

我在 Delphi 上有一个带有 TFDMoniCustomClientLink 的 FireDac 连接。我只需要捕获 mysql_real_query。我尝试启用和禁用所有 EventKinds，但我找不到方法来做到这一点。我发现更接近的是仅启用 ekVendor，但它提供的信息比 mysqL_real_query 更多。

编辑:mysql_real_query 是 TFDMoniCustomClienteLink 生成的日志文本文件的一部分。此部分显示在数据库上执行的 sql。我找到的对该术语的唯一引用如下:http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Phys.MySQLWrapper.TMySQLLib.mysql_real_query在这里 https://dev.mysql.com/doc/refman/5.7/en/mysql-real-query.html .

来自 TFDMoniCustomClientLink 的 OnOutput 事件中的代码:

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;

生成的日志文件:

 . 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"]

我只需要捕获 SQL，消除 " 事件。mysql_real_query [q=""

我希望有一些配置可以更改为仅导出真正的 SQL，而不包含这些部分，这样我就不需要检查字符串中的模式。

Answer 1

使用附带的跟踪监视器无法实现您想要做的事情。这是因为 FireDAC 的跟踪不是为自定义条目开发的，正如您正确识别的那样，调用 mysql_real_query API函数按ekVendor分类事件类型，因此除了解析消息本身之外，没有办法区分其他此类消息，这是一种丑陋的方式。所以，让我们尝试换一种方式。

1。读取传递给 DBMS 的 SQL 命令

问题并不清楚，但您已在评论中确认您实际上只想记录传递到 DBMS 的 SQL 命令。如果您失去了使用跟踪监视器的可能性，您可以在准备好 SQL 命令后，从 Text 中读取该命令。属性(实际上已在 Checking the SQL Command Text 章节中介绍)。

如果EFDDBEngineException引发异常，您可以从其SQL读取SQL命令属性(也在上述章节中介绍)。

2。拦截特定的DBMS API函数

如果您想坚持监视特定 API 函数调用而不更改 FireDAC 源的想法，您可以为驱动程序的 OnDriverCreated 编写处理程序事件，拦截您感兴趣的函数，存储原始指针并在拦截函数体中执行您需要的操作(包括调用原始存储的函数)。例如，对于 mysql_real_query函数可能是这样的:

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;

但这种方式非常具体，并且会花费额外的函数调用开销。

3。编写自己的跟踪监视器

跟踪监视器并不像以前那样灵活，但仍然可以编写自己的监视器并接收传递到 Notify 的信息。方法而不是串联的消息(但是当然，您必须知道跟踪通知参数的含义)。

这是我由 TFDMoniCustomClientLink 制作的一个示例class(但是对于使用的RTTI来说没有什么好处，不过可以自己调一下):

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.

重要，使用此类时，您必须不得在项目中包含 FireDAC.Moni.Custom 模块，否则 IFDMoniCustomClient 接口(interface)将注册到不同的类(这是因为 mbCustom 类型的 MonitorBy 连接参数的跟踪监视器是由为 IFDMoniCustomClient 接口(interface)注册的类创建的；这是上述单元的初始化 block 中做了什么)。

简化的使用示例:

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;

这种方式也非常适合您的需求，并且会花费您额外的新 RTTI 开销，但这正是您可以优化的。