VarType 271, 272, 273 Supported by Oracle but not by TValue.FromVariant

I am using XE5 FireDac with Oracle 11g. When a number(19) is returned by oracle its variant type is FMTBcd which is a VarType of 271, Additionally 272 is SqlTimeStampVariantType and 273 is SqlTimeStampOffsetVariantType.

These types are not handled by the TValue.FromVariant method but rather an exception is thrown when they are passed to this method. Additionally when a number is greater than a double there is a bug in Delphi that causes the FMTBCD to lose precision when converted from it's variant type to an Int64 Delphi Type. We have reported this bug (QC 117696) and finally Embarcadero has fixed it in XE6, but all previous versions of Delphi will have this issue. Try converting the following number from fmtBCDVariantType to Int64 to see the bug 999999999999999999. You will notice that the int64 result will be 100000000000000000, hence a loss of precision occurs.

I notice you have a util method in Spring.Persistence.Core.Utils for handling VarType 273 which you comment on as FMTBCDVariantType, I am seeing this type though come back as 271 and 272,273 as TimeStamps as stated above. Also the current code just casts the fmtbcd variant type to Double and as previously stated this will loose precision on high values. I have done the coding to resolve these issues and would gladly provide a pull request if you can provide instructions for doing this.

Here is my version of the method.

class function TUtils.FromVariant(const AValue: Variant): TValue;
var
  bStream: TMemoryStream;
  ptr: Pointer;
  iDim: Integer;
  V : Variant;
  S : String;
begin
  if VarIsArray(AValue) then
  begin
    //make stream from variant
    bStream := TMemoryStream.Create;
    iDim := VarArrayDimCount(AValue);
    ptr := VarArrayLock(AValue);
    try
      bStream.Write(ptr^, VarArrayHighBound(AValue, iDim) + 1);
    finally
      VarArrayUnlock(AValue);
    end;
    Result := bStream;
  end
  else
  begin
    case VarType(AValue) of
      {Oracle specific User VariantTypes}
      272 {SQLTimeStampVariantType}, 273 {SQLTimeStampOffsetVariantType}:
      begin
        V := Double(AValue);
      end;
      271 : {FMTBcdVariantType}
      begin
        {
          OldCode: Result := Double(AValue)
          TFlora - BCD Type Precision is greater than double type. so this looses precision and changes the value in some cases
          For instance: 999999999999999999 becomes 100000000000000000
          Also it does not match the Actual Datatype of Int64 so the resulting assignment of this
          type to an Int64 fails with invalid type cast exception later on.

          Therefore we need to do the following:
          Convert Variant to String first so that the BCD to Double conversion bug does not occur.
          BTW this bug is finally fixed in XE6. Then convert it to an Int64.
        }
        S := VarToStr(AValue);
        V := StrToInt64(S);
      end;
      else
        V := AValue;
    end;
    Result := TValue.FromVariant(V);
  end;
end;

Finally anywhere in the Code Base that TValue.FromVariant is called, this method should probably be called instead.

This precision bug has haunted us as we use a Stored procedure to generate GUID like ID values and they are many times above the Double Precision Barrier.

Comments (10)