Faulty posting of ftBCD params to PostgreSQL server
Original Reporter info from Mantis: chrisv5
-
Reporter name: Christian Vitroler
Original Reporter info from Mantis: chrisv5
- Reporter name: Christian Vitroler
Description:
When 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 reproduce:
CREATE 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 information:
odbcconn 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;
Mantis conversion info:
- Mantis ID: 33691
- Version: 3.0.4
- Fixed in version: 3.1.1
- Fixed in revision: 38909 (#f9e790aa)
- Target version: 3.2.0