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 computers it's difficult to debug. I think it might be a problem with the Oracle client, but I'm not sure how we can actually verify that.

Thanks for any help.

Answer 1

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.

Answer 2

I encountered a similar problem with the data type Currency (Blog article: "Why 1.99 suddenly no longer equals 1.99"). It looks like some DLLs modify a FPU (processor) control word. This explains why the problem is machine-dependent. I also found that Delphi contains a SafeLoadLibrary function, which restores the control word. But it is not a big help, because after loading the DLL, still any call of a DLL function could mess up the control word again.