Reading integer number from database returns floating point

Question

We've got a table in an Oracle DB defined as: ``` CREATE TABLE AVALUES ( ACODE VARCHAR2(4) NOT NULL, ATYPE NUMBER NOT NULL, ANAME VARCHAR2(50), CREATED DATE DEFAULT SYSDATE ) ``` Within Delphi we have a query in an ADOQuery component akin to this which returns the value to our application: ``` with qryComp do begin Close; SQL.Text := 'SELECT ATYPE FROM AVALUES ORDER BY CREATED'; Open; while not EOF do begin AddComponents('NAME' + FieldByName('ATYPE').AsString); Next; end; Close; end; ``` Deployed on many various client PCs this has worked fine for years, and nothing in our code has changed. On a few client PCs however it's recently started returning, say, 1.999999999969 instead of 2, which causes the application to crash. We've tried looking for the problem but it's very intermittent - connected via remote desktop to the client computer, we can't replicate it at all. Any suggestions for things I can do to investigate this further? As it's intermittent and only happens on a few compute…

Accepted Answer

The only thing I can see possible is differences between the FPU control word on client machines causing the precision to be handled differently, because of inherent rounding issues with dealing with floating point types. (See Set8087CW in the Delphi docs; the link is for XE2's documentation, but there's no significant change recently that I'm aware of, so they should work.)

There are four ways to fix it (one not probable, three fairly easy):

Change the database column to actually be an `integer` type instead of a `NUMBER`

Directly ask for an `integer` value, and convert it yourself

`AddComponent('Name' + IntToStr(FieldByName('ATYPE').AsInteger));`

Change the code that uses the column, either at the point of use:

`AddComponents(Format('NAME%d', [FieldByName('ATYPE').AsInteger]));`

or in `AddComponents` itself:

```
procedure AddComponents(Prefix: string; Value: Integer);
begin
  DoWhateverIDo(Prefix + IntToStr(Value));
end;

// calling code
AddComponents('Name', FieldByName('ATYPE').AsInteger);
```

- Explicitly set the value of the 8087CW before working with the database, and set it back when you're finished. This seems to be the worst option to me; there's an example of doing this at the documentation link I posted above.

Reading integer number from database returns floating point

Solution

Problem