条件语句的FireDAC DBMS标识符

Question

我正在尝试使用FireDAC DBMS标识符来生成特定于数据库的查询。我当前正在连接到MySQL服务器(DriverID=MySQL(。我想通过limit/top查询从mysql或mssql进行查询。我目前的声明如下：

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}

当然，我知道mssql的转义是不正确的，但那是另一回事。当我检查FireDAC监视器时，经过预处理的查询如下所示：

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

因为它应该只添加CCD_ 4而省略CCD_。

由于我只有Delphi Community Edition 10.4，所以我无法访问MSSQL驱动程序。我想，这可能会导致这个错误，我尝试了其他人。但CCD_ 6和CCD_。

构造函数如下所示：

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;

我的问题看起来和这个问题有点关系如何使用FireDAC DBMS标识符有条件地更改SQL文本

感谢

Answer 1

我找到了解决方案。如果要使用条件转义序列，还必须为所有涉及的数据库use(FireDAC.Phys.XXX单元(。由于我想使用MSSQL，我必须将FireDAC.Phys.MSSQL添加到use-子句中。

单位如下所示：https://docwiki.embarcadero.com/RADStudio/Sydney/en/Databases_(FireDAC(

也许这个答案对其他人有帮助。