Subject Re: [IBO] floating point values truncated
Author Helen Borrie
At 11:37 PM 14/02/2005 +0000, you wrote:

>I have a grid connected to a IBOQuery component and when I update a
>value like 10.25 , it becomes 1025 when I post it.
>I have searched the group and found out that I shoud use NUMERIC(9,2)
>or something with a scale. In my table definition the colums are
>defined as NUMERIC(8,2).

On the database side, NUMERIC(8,2) isn't a very useful geometry for a
number. since, theoretically, the biggest number it can store is
999999.99. In fact, the number will be stored as a NUMERIC(9,2), because
of the way the engine handles fixed point decimal numbers. It will store an
integer of precision 9 and a scale, a number which is a power of 10.

On the Delphi side, Delphi doesn't support scaled numerics. They have to
be converted AsExtended or AsFloat. This should happen automatically.

I can't tell precisely what's going on in your case, if "a value like 10.25
...becomes 1025 when I post it". It's impossible to tell whether you are
seeing a DisplayFormat problem in your program or some actual alteration of
scale by your code. You should check what is going on by querying your
test data using IB_SQL, isql or another tool that behaves properly with

>What can I do?

With native IBO you can set the CURRENCY attribute of the ib_column true
and use AsCurrency to have the value of the floating point number rounded
to 2 places of decimal.

Because you are using TIBOQuery, you can instantiate the field object and
set its DataType to be TIBOBCDField. You can set the Currency property to
have the number displayed with 2 decimal places. You can use AsCurrency
and (maybe!) AsBCD to refer to an 8-byte number, i.e. a double precision
float number or Delphi's interpretation of a 64-bit fixed numeric, i.e.

If you are using Dialect 3, you can redefine your money fields in the
database as NUMERIC(18,2). If your current precision and scale are OK for
what you want, keep the current definition.

Also go to the TechInfo page at the IBO website and pick up Geoff Worboys'
TI sheet on handling numerics. There's some very worthwhile stuff there
about choosing your data types for numbers and controlling what numbers
actually get stored.