Sql-Server problem with datatype real

[invent]

Hello,

we are using UniDAC with Interbase 2009, Oracle 8.0.5 and MS SQL-Server 2008 RC2. Delphi 7 and UniDAC 3.50.0.13.

I have some tables with Float/Real-fields. So I checked some field-types:

1. Interbase: "double precision"
2. Oracle: "number"
3. Sql-Server: "float"
4. Sql-Server: "real"

When I created some tables with this fields, everything works good. But now I found a problem.

I made a simple Query

select myfield from mytable where myfield = '123'

and want to show this content with ShowMessage:

ShowMessage (MyUniQuery.FieldByName ('myfield').AsString);

I see the following result in my 4 cases:

1.: "1,4"
2.: "1,4"
3.: "1,4"
4.: "1,3999999999"

Why can't I see in the case Sql-Server / Datatype = real the value "1,4"?

Thanks in advance for this answer.

Kind regards,
Gerd Brinkmann
invent GmbH

[AndreyZ]

Hello,

It's a specifity of the real data type in SQL Server. You can check it with ADO. To avoid this problem you should use the float data type instead of the real data type.

[invent]

Hi,

sorry, but this answer is too short and no help. I can look into the table with severval tools:

- Sql Server Management Studio
- MS Access (with ODBC)
- TUniTable and DBGrid, DBEdit or other DB-Components

In all cases I got the correct value 1.4.

Only FieldByName or FieldValues shows the wrong value 1.399999.

I think, this problem can be fixed by Devart.

Kind regards,
Gerd Brinkmann
invent GmbH

[Dimon]

This behaviour is connected with peculiarity of FPU working with the Real type. The length of the Real type in SQL Server is 4 bytes. In Delphi this type corresponds to the Single type, length of which is also 4 bytes. You can use the following code to learn it:

Code: Select all

var
  f: single;
  d: double;
begin
  f := 1.4;
  d := 1.4;
  ShowMessage(FloatToStr(f));
  ShowMessage(FloatToStrF(f, ffGeneral, 7, 0));
  ShowMessage(FloatToStr(d));
end;

To solve the problem you can use the TField.DisplayText property instead of the AsString property, or you can use the following code:

Code: Select all

  Field := TFloatField(MyUniQuery.FieldByName('myfield'));
  ShowMessage(FloatToStrF(Field.AsFloat, ffGeneral,Field.Precision, 0));