Casting a parameter value in INSERT query gives arithmetic overflow when BCD mapping rules are active

Question

Consider this table in an MSSQL database:

CREATE TABLE dbo.TESTPAR
(
  ID INTEGER NOT NULL,
  YR VARCHAR(50) NULL
)

I have a TFDQuery with command text:

insert into TESTPAR
(ID,YR)
values(:ID,cast(:YR as varchar(4)))

This has two ftInteger ptInput parameters

Executing it with

procedure TFrmCastAsVarchar.BtnTestInsertClick(Sender: TObject);
begin
   Inc(FLastID);
   FDQuery2.Params[0].AsInteger := FLastID;
   FDQuery2.Params[1].AsInteger := 2018;
   try
      FDQuery2.ExecSQL;
   except
      on E:Exception do ShowMessage(E.Message);
   end;
end;

gives an error EMSSQLNativeException Arithmetic overflow converting numeric to data type varchar when Mapping for dtBCD and dtFmtBCD fields is active:

procedure TDM.SetBCDMapRules;
// For (Fmt)BCD data types. Called from SetOracleMapRules/SetMSSQLMapRules
begin
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // Convert numeric data types with scale=0 and precision<=10 to a 32-bit integer
      PrecMax := 10;
      PrecMin :=  0;
      ScaleMax := 0;
      ScaleMin := 0;
      SourceDataType := dtBCD;
      TargetDataType := dtInt32;
   end;
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // Do the same for those that might return as dtFmtBCD instead of dtBCD
      PrecMax := 10;
      PrecMin :=  0;
      ScaleMax := 0;
      ScaleMin := 0;
      SourceDataType := dtFmtBCD;
      TargetDataType := dtInt32;
   end;
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // Convert numeric data types with scale=0 and precision>10 to a 64-bit integer
      PrecMin := 11;
      ScaleMax := 0;
      ScaleMin := 0;
      SourceDataType := dtBCD;
      TargetDataType := dtInt64;
   end;
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // Idem dtFmtBCD
      PrecMin := 11;
      ScaleMax := 0;
      ScaleMin := 0;
      SourceDataType := dtFmtBCD;
      TargetDataType := dtInt64;
   end;
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // All other dtBCD types (notably scale <> 0) should return as float
      SourceDataType := dtBCD;
      TargetDataType := dtDouble;
   end;
   with FDConnection.FormatOptions.MapRules.Add do
   begin      // Idem dtFmtBCD
      SourceDataType := dtFmtBCD;
      TargetDataType := dtDouble;
   end;
end;

(How) can I change the SQL to fix this?
Alternatively, is there something weird in my mapping rules that could be fixed? I'm surprised this has an influence at all.

• This is of course just a basic example. The real script concatenates other strings to the cast() to arrive at a varchar value to put into the varchar field.
• Not using the BCD mappings will give other issues (e.g. with DECIMAL field types).
• Changing the table structure for the client "is not optimal" ;-)
• I have tested this using a lot of different ODBC/native drivers.
• This is Delphi Tokyo 10.2.3, Win32 app on Win7.

Answer 1

Sure, there is something wrong with your mapping (we've been at this before). For parameters, it is transformation of target into source. The Data Type Mapping topic says this:

In case of a command parameter, the rule defines a transformation of a target data type, specified by an application, into a source data type, supported by a driver.

So, in this case you have instructed FireDAC to transform 32-bit integer into decimal number, which when arrives to DBMS won't be just 4 chars long. If you want to fix this, then (ordered by reliability):

• use proper data type in your table
• stop using mapping rules in general
• use proper parameter data type and pass value as it really is (so as string, not as integer)
• cast the parameter value into integer like e.g. CAST(CAST(:YR AS INTEGER) AS VARCHAR(4))