FireDAC DBMS Identifiers for conditional statement-CodePudding

I'm trying to use the FireDAC DBMS Identifiers for generating a database specific query. I'm connecting to a MySQL-Server currently (DriverID = MySQL). I want to query either from mysql or mssql with a limit / top query. My current statement looks like this:

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}

Of course I'm aware, that the escaping will not be correct for mssql, but that's a different story. When I inspect the FireDAC Monitor the preprocessed query looks like this:

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

Of course this will result in error

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

since it should only add the LIMIT 1 and omit the TOP(1).

Since I only have the Delphi Community Edition 10.4, I don't have access to the MSSQL-Driver. I thought, that could cause this error and I tried with others. But also with {if FIREBIRD} and {if ADS} it was the same.

The constructor looks like this:

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;

My question looks a bit related to this question here How do I use FireDAC DBMS identifiers to conditionally change SQL text

Thanks

CodePudding user response：

I found the solution. If you want to use the conditional escape sequence, you also have to use the FireDAC.Phys.XXX unit for all involved databases. Since I want to use MSSQL, I have to add FireDAC.Phys.MSSQL to the use-clause.

The units are listed here: https://docwiki.embarcadero.com/RADStudio/Sydney/en/Databases_(FireDAC)

Maybe this answer helps others.