View Issue Details

IDProjectCategoryView StatusLast Update
0033691FPCDatabasepublic2018-11-24 11:02
ReporterChristian VitrolerAssigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version3.0.4Product Build 
Target Version3.2.0Fixed in Version3.1.1 
Summary0033691: Faulty posting of ftBCD params to PostgreSQL server
DescriptionWhen posting a ftBCD parameter to the PostgreSQL server in TPQConnection.Execute, the parameter value is handle incorrectly. Here is the code:

  ftFloat, ftBCD:
    Str(AParams[i].AsFloat, s);

The code should actually be:

  ftFloat:
    Str(AParams[i].AsFloat, s);
  ftBCD:
    Str(AParams[i].AsCurrency, s);
Steps To ReproduceCREATE TABLE "Prices" ("Price" NUMERIC (10, 2));
INSERT INTO "Prices" VALUES(49.99);

Query.SQL.Text := 'SELECT * FROM "Prices" WHERE "Price" = :Price';
Query.Prepare;
Query.ParamByName(CColNetPrice).AsBCD := 49.99;
Query.Open;

Will not return any row, as the column Price will be actually compared to 49.990000000000002, due to well-known imperfection of IEEE floats.

This will work, but is a real kludge:

Query.ParamByName(CColNetPrice).AsFMTBCD := CurrToBCD(49.99);
Additional Informationodbcconn handles this correctly in TODBCConnection.SetParameters

      ftFloat:
        begin
          FloatVal:=AParams[ParamIndex].AsFloat;
          PVal:=@FloatVal;
          Size:=SizeOf(FloatVal);
          CType:=SQL_C_DOUBLE;
          SqlType:=SQL_DOUBLE;
          ColumnSize:=15;
        end;
      ftCurrency, ftBCD:
        begin
          NumericVal:=CurrToNumericStruct(AParams[ParamIndex].AsCurrency);
          PVal:=@NumericVal;
          Size:=SizeOf(NumericVal);
          CType:=SQL_C_NUMERIC;
          SqlType:=SQL_NUMERIC;
          ColumnSize:=NumericVal.precision;
          DecimalDigits:=NumericVal.scale;
        end;
TagsNo tags attached.
Fixed in Revision38909
FPCOldBugId
FPCTarget
Attached Files

Activities

Michael Van Canneyt

2018-05-05 14:18

administrator   ~0108150

I am not sure that using currency is the correct approach:
if the precision is >4, this will give wrong results.

Christian Vitroler

2018-05-05 15:32

reporter   ~0108151

Last edited: 2018-05-05 15:38

View 2 revisions

Most implementations check for precision. If the precision is equal to or less 4, then Currency is used. If the precision is higher, TBCD is used. It is up to the programmer to know which one to use. When reading columns from the SQL server, this very check is made and TField set up properly.

As I usually know what precision my NUMERIC column has, I can decide myself whether to use AsBCD or AsFMTBCD.

For illustration, here is the code from TPQConnection.TranslateFldType

  Result := ftBCD;
  li := PQfmod(res,Tuple);
  if li = -1 then
   size := 4 // No information about the size available, use the maximum value
  else
  // The precision is the high 16 bits, the scale the
  // low 16 bits with an offset of sizeof(int32).
   begin
   size := (li-VARHDRSZ) and $FFFF;
   if (size > MaxBCDScale) or ((li shr 16)-size > MaxBCDPrecision-MaxBCDScale) then
     Result := ftFmtBCD;
   end;
  end;

And here the code from TPQConnection.LoadField:

else if FieldDef.DataType = ftBCD then
  begin
  cur := 0;
  for i := 0 to NumericRecord^.Digits-1 do
    begin
    cur := cur + beton(pword(CurrBuff)^) * intpower(NBASE, NumericRecord^.weight-i);
    inc(pointer(CurrBuff),2);
    end;
  if NumericRecord^.Sign <> 0 then cur := -cur;
  Move(Cur, Buffer^, sizeof(currency));
  end
else //ftFmtBCD
  begin
  bcd := 0;
  for i := 0 to NumericRecord^.Digits-1 do
    begin
    BCDAdd(bcd, beton(pword(CurrBuff)^) * intpower(NBASE, NumericRecord^.weight-i), bcd);
    inc(pointer(CurrBuff),2);
    end;
  if NumericRecord^.Sign <> 0 then BCDNegate(bcd);
  Move(bcd, Buffer^, sizeof(bcd));
  end;
end;

Similar code can be found in odbcconn. There seems to be a consensus, if it is ftBCD, then it is a Currency, if it is ftFMTBCD, then it is TBCD.

I'd be very happy with that! Just no double for NUMERIC, for gods sake!

Christian Vitroler

2018-05-05 15:45

reporter   ~0108152

Last edited: 2018-05-05 15:46

View 2 revisions

Additional information, the constant MaxBCDScale is defined in FmtBCD as following:

MaxBCDScale = 4; { not used ! }

The "not used" is a bit funny, it seems to apply to FmtBCD only. Obviously, it is used in sqldb.

Anyway, it seems like that to use Currency for ftBCD is justified by this constant!

But if you need another argument, here is the documentation from Embarcadero:

"As implemented by TField, reading AsBCD returns the value of AsCurrency converted to a TBcd value, and setting AsBCD sets the AsCurrency property after converting a supplied TBcd value to the Currency type. Some TField descendants override AsBCD to perform a more direct conversion."

If you like me to, I can make a few other arguments, that Currency is the perfect choice :-)

Michael Van Canneyt

2018-05-05 17:00

administrator   ~0108157

No need for extra arguments, I was not aware that precision>4 is converted to fmtBCD. All is well then, I changed the code as you suggested.

Thanks for the report and suggested solution!

Issue History

Date Modified Username Field Change
2018-05-05 01:26 Christian Vitroler New Issue
2018-05-05 14:18 Michael Van Canneyt Note Added: 0108150
2018-05-05 14:18 Michael Van Canneyt Assigned To => Michael Van Canneyt
2018-05-05 14:18 Michael Van Canneyt Status new => assigned
2018-05-05 15:32 Christian Vitroler Note Added: 0108151
2018-05-05 15:38 Christian Vitroler Note Edited: 0108151 View Revisions
2018-05-05 15:45 Christian Vitroler Note Added: 0108152
2018-05-05 15:46 Christian Vitroler Note Edited: 0108152 View Revisions
2018-05-05 17:00 Michael Van Canneyt Fixed in Revision => 38909
2018-05-05 17:00 Michael Van Canneyt Note Added: 0108157
2018-05-05 17:00 Michael Van Canneyt Status assigned => resolved
2018-05-05 17:00 Michael Van Canneyt Fixed in Version => 3.1.1
2018-05-05 17:00 Michael Van Canneyt Resolution open => fixed
2018-05-05 17:00 Michael Van Canneyt Target Version => 3.2.0
2018-11-24 11:02 Christian Vitroler Status resolved => closed