FireDAC DBMS标识符条件语句失效问题求助

场景说明 #

使用Delphi 10.4社区版，通过FireDAC连接MySQL（DriverID=MySQL），编写适配MySQL和MSSQL的分页查询语句（分别用LIMIT/TOP），但条件宏未正确过滤无效代码，生成的SQL同时包含TOP(1)和LIMIT 1，触发语法错误。

原始SQL语句 #

SELECT 
  {IF MSSQL} TOP(1) {fi} `tr`.`TaxRate_Primkey` 
FROM 
  `tbl_taxrates` AS `tr` 
WHERE 
  `tr`.`TaxRate_TaxCodeId` = `tc`.`TaxCode_Primkey` 
AND 
  `tr`.`TaxRate_ValidSince` <= :DATE 
ORDER BY `tr`.`TaxRate_ValidSince` DESC 
{IF MySQL} LIMIT 1 {fi}

FireDAC预处理后的错误SQL #

通过FireDAC Monitor查看，预处理后的语句同时保留了TOP(1)和LIMIT 1：

SELECT 
   TOP(1)  `tr`.`TaxRate_Primkey` 
FROM 
  `tbl_taxrates` AS `tr` 
WHERE 
  `tr`.`TaxRate_TaxCodeId` = `tc`.`TaxCode_Primkey` 
AND 
  `tr`.`TaxRate_ValidSince` <= ? 
ORDER BY 
  `tr`.`TaxRate_ValidSince` DESC 
 LIMIT 1

触发的语法错误 #

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`TaxRate_Primkey` FROM `tbl_taxrates` AS `tr` WHERE `tr`.`TaxRate_TaxCodeId` ' at line 1 [errno=1064, sqlstate="42000"]

连接初始化代码 #

constructor TFireDACTenantRepository.Create(
  ADBName: string;
  ADBServer: string;
  APort: Integer;
  AUserName: string;
  APassword: string;
  ALogger: TLogger
);
var
  oParams: TStrings;
begin
  inherited Create;

  FLogger := ALogger;
  Self.MonitorLink := nil;
  Self.MonitorBy := mbRemote;
  Self.Tracing := True;

  FConnection := TFDConnection.Create(nil);

  oParams := TStringList.Create;
  try
    oParams.Add('Server=' + ADBServer);
    oParams.Add('Port=' + IntToStr(APort));
    oParams.Add('Database=' + ADBName);
    oParams.Add('User_Name=' + AUserName);
    oParams.Add('Password=' + APassword);
    oParams.Add('OSAuthent=No');
    FDManager.AddConnectionDef('MySQLConnectionTenant', 'MySQL', oParams);
    FConnection.Params.MonitorBy := Self.MonitorBy;
    FConnection.ConnectionDefName := 'MySQLConnectionTenant';
    FConnection.ResourceOptions.ParamCreate := True;
    FConnection.ResourceOptions.MacroCreate := True;
    FConnection.ResourceOptions.ParamExpand := True;
    FConnection.ResourceOptions.MacroExpand := True;
    FConnection.ResourceOptions.PreprocessCmdText := True;
    FConnection.ResourceOptions.EscapeExpand := True;
  finally
    oParams.Free;
  end;

  FConnection.AfterConnect := DoAfterConnect;
  FConnection.AfterDisconnect := DoAfterDisconnect;
end;

解决方法 #

1. 修正DBMS宏语法
   FireDAC的DBMS条件宏必须使用{IF DBMS=<DBMS_ID>}格式，而非直接写{IF MSSQL}。修改后的SQL语句如下：

SELECT 
  {IF DBMS=MSSQL} TOP(1) {fi} `tr`.`TaxRate_Primkey` 
FROM 
  `tbl_taxrates` AS `tr` 
WHERE 
  `tr`.`TaxRate_TaxCodeId` = `tc`.`TaxCode_Primkey` 
AND 
  `tr`.`TaxRate_ValidSince` <= :DATE 
ORDER BY `tr`.`TaxRate_ValidSince` DESC 
{IF DBMS=MySQL} LIMIT 1 {fi}

2. 确保FireDAC能识别目标DBMS
   即使不连接MSSQL，也需要在项目中引用对应DBMS的FireDAC物理驱动单元（如FireDAC.Phys.MSSQL），否则FireDAC无法识别MSSQL这个DBMS标识符，会跳过宏判断。

3. 手动验证宏预处理结果
   可以通过TFDSQLPreprocessor类手动测试SQL预处理结果，确认宏是否生效：

var
  LPreprocessor: TFDSQLPreprocessor;
  LProcessedSQL: string;
begin
  LPreprocessor := TFDSQLPreprocessor.Create(nil);
  try
    LPreprocessor.DBMS := 'MySQL'; // 模拟当前连接的DBMS
    LProcessedSQL := LPreprocessor.Process(你的原始SQL语句);
    // 输出LProcessedSQL查看是否只保留了LIMIT 1
  finally
    LPreprocessor.Free;
  end;
end;

内容的提问来源于stack exchange，提问作者Raphi1