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.

Show your parameter definitions. What is getting sent to the database for `YR` has a decimal place which produces the error. Ex on SQL Server `SELECT CAST(1980.0 AS VARCHAR(4))` fails with the error you see. As well be careful of referencing parameters by index - easy for the wrong parameter to be set as things evolve over time. — Brian, May 09 '18 at 19:08
@Brian The parameters are in the question: two ptInput ftInteger ones. Or am I misunderstanding you? I set .AsInteger as well... — Jan Doggen, May 09 '18 at 19:22

Victoria · Accepted Answer · 2018-05-10T12:49:29.320

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))

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

1 Answers1