Computer Programming Solutions
TECHNICAL INFORMATION SHEET

Working with Currency and Scaled Numeric Data Types

Abstract:  
I have been working on improving the IBO code for handling of numeric(x,y) values.  In the process I have been working through various issues that arise not just with currency, but also with scaled numerics within the Firebird/IB database engine itself.  What follows is a compilation of that information.

Geoff Worboys
Telesis Computing
(31-Dec-2001 - Updated: 02-Jan-2002)
___________________________________________________________________________

Note

The descriptions concernings IBO's handling of numeric data types below refer to IBO v4.3 and later.  Earlier versions of IBO had some difficulties with large numeric values.

Background

The Firebird NUMERIC and DECIMAL data types provide exact storage of numeric values.  The declarations are

    NUMERIC(p,s)  and  DECIMAL(p,s)

where p is the precision (the total number of numeric digits held by the value) and s is the scale (the number of those digits that are on the right hand side of the decimal point).

In Firebird and Interbase(R) v6+ the mechanism for storing exact values is to store all NUMERIC and DECIMAL values as scaled integers.  Floating point values (such as DOUBLE PRECISION ) are not guaranteed to store a number exactly as it was originally input and, worse, they accumulate rounding errors in subsequent calculations.

The theoretical difference between NUMERIC and DECIMAL is that NUMERIC will store exactly p digits, whereas DECIMAL will store not fewer than p digits.

The actual difference between the two types (in versions available at the time of this writing) is minimal. So from this point forward I will mostly discuss the NUMERIC data type, and to the best of my knowledge the same rules apply to DECIMAL data types.

How Firebird/IB 6.x stores your scaled numerics

*  If the precision (p) is specified as 1 to 4, then the server will implement and store the value
   as a scaled small integer (16bit) value.

*  If the precision (p) is specified as 5 to 9, then the server will implement and store the value
   as a scaled integer (32bit) value.

*  If the precision (p) is specified as 10 to 18, then the server will implement and store the 
   value as a scaled large integer (64bit) value.

InterBase 5 and below

In Interbase v5 or earlier, 15 is the largest p(recision) supported.  Numerics with precision of 10..15 are stored as DOUBLE PRECISION.  If such databases are upgraded to Firebird or IB6 or FB1 using a gbak backup and then restored, those higher-scale numeric fields will remain implemented as DOUBLE PRECISION.  Although they will still appear to be NUMERIC(15,2) or whatever you defined, the storage used continues to be DOUBLE PRECISION.

Server Validation of NUMERIC data types

In Interbase v5 and earlier, the declared precision was not stored.  Now, in Firebird and Interbase 6,  the precision is stored but it is not used for validation.  Although you have declared a precision for a field/value, the server will NOT check that the value fits within that declared specification.  It will only check for overflow of the underlying integer storage type. 

For example, if you have a field declared as

    AFIELD NUMERIC(7,2),

the server will accept the value '1234567.89' despite the fact that this value has 9 digits (including the decimal places).

It will reject the value '123456789' because, after adding two decimal places, this value becomes 11 digits in length and will not fit into the 32bit integer storage that was allocated for NUMERIC(7,2).

NOTE: In the current implementations of Firebird and IB 6, DECIMAL(7,2) will also reject '123456789', regardless of the fact that it is supposed to store "at least" 7 digits.

Getting the server to enforce your declaration

In order to make the server enforce the declaration of a particular precision, you must create a domain with a CHECK constraint to enforce it;  for example

    CREATE DOMAIN NUMERIC_7_2
    AS NUMERIC(7,2)
    CHECK( VALUE BETWEEN -99999.99 AND +99999.99 );

I consider it very important that you do this, otherwise your database may accept values that will later generate exceptions in your client application.

NUMERIC data types at the Client (IBO)

If the scale of a NUMERIC value is 0, then it can be read directly as an integer (of matching/appropriate size) using the AsSmallint, AsInteger and AsInt64 properties of the TIB_Column instance (or the Delphi VCL equivalents, if using TDataset and TField.)  When the scale is 0, IBO will automatically create a derivative of TIB_Column whose Value property matches the integer type required.

When NUMERIC values have a non-zero scale, it becomes a little more difficult to determine the best way to manage the value at the client.  The difficulty arises because Delphi does not provide any native data type that exactly matches the properties of the Firebird NUMERIC data type.

When IBO detects a NUMERIC value with a non-zero scale it creates a TIB_Column derivative called TIB_ColumnNumeric, whose Value property is an Extended data type.  This column class also introduces special handling of the AsString, AsInteger, AsCurrency and AsExtended properties to try and match the properties of NUMERIC data as closely as possible.  For interaction with user controls (TIB_Edit etc) the IBO uses the rules described below.  TIB_Column provides a boolean IsCurrencyDataType property that can be used to detect when IBO processing is going to use the Delphi currency type (rather than floating point values).

IBO will automatically round any floating point values assigned to NUMERIC fields according to the scale of the field.

NUMERIC handling by native IBO components

The default processing for native IBO components (TIB_Dataset, TIB_Query, TIB_Cursor etc) uses the following rules...

    If the Scale is 1..4 then the column will implement its AsString and AsInteger handling using the Delphi currency data type (using the AsCurrency property).  In this instance the column IsCurrencyDataType property will return true.

    If the Scale is greater than 4 then the column will implement its AsString and AsInteger handling using the Delphi data type extended (using the AsExtended property).  In this instance the column IsCurrencyDataType property will return false.  It is necessary to use the extended type for scale greater than 4 because the currency data type only supports 4 decimal places, but be aware that the extended data type is not a scaled integer and you will experience rounding inconsistencies at the limits of the range.

NUMERIC handling by TDataset based IBO components

The default processing for TDataset based IBO components (TIBODataset, TIBOQuery, TIBOTable etc) uses the following rules...

*  If you are using Delphi v5 or later and the Scale is 1..4 then the dataset will create 
   TIBOBCDField instances, which use the Delphi currency data type (and read/write the 
   data to the underlying TIB_Column using the AsCurrency property).

*  If you are using Delphi v4 or earlier and the Scale is greater than 4 then the dataset 
   will create TIBOFloatField instances, which use the Delphi double data type (and 
   read/write the data to the underlying TIB_Column using the AsExtended property).  
   It is necessary to use the Extended type for scale greater than 4 because the currency 
   data type supports a maximumn of 4 decimal places.  However, be aware that the 
   double data type is not a scaled integer and you will experience rounding inconsistencies 
   at the limits of the range.

NOBCD Column Attribute

You can alter the default processing rules described above using the NOBCD column attribute.  If you setup a column attribute such as:

    MY_NUMERIC_FIELD=NOBCD

then IBO will use the floating point implementation for MY_NUMERIC_FIELD if its scale is greater than zero.  It will not attempt to use the Delphi currency type.  Thus, the attribute NOBCD will cause the native IBO components to revert to using AsExtended to implement scaled numerics and it will cause the TDataset components to revert to using the TIBOFloatField class to instantiate the persistent field object.

NOTE ::  If you are using persistent field objects with TDataset components and you apply or remove the NOBCD attribute to a field, it will not alter the TField class automatically.  You must bring the field object up in the Fields Editor and manually change the TField class from TIBOBCDField to TIBOFloatField or vice versa.

BCD Column Attribute

This attribute is used by TDataset components only.  It is designed so that you can force any floating point field to use the TIBOBCDField (which implements processing using the Delphi currency data type).  If you set up a column attribute such as:

    MY_FLOAT_FIELD=BCD

then IBO will use the TIBOBCDField class for that field.

Please refer to the NOTE in the previous topic.

Variants and IBO Numbers

Because of problems and inconsistencies with Variant handling in Delphi, IBO converts all non-zero NUMERIC values (and all 64bit integer values) to string when assigning to variant.   In consequence, the TIB_Column AsVariant method will return a varString type of variant from NUMERIC and large integer fields.  This appears to be the only reliable way of retaining the exact value of NUMERIC and large integer values when using variants.

When you assign a variant to the TIB_Column AsVariant method it will accept number variant types (varInteger etc) but the assignment is handled via the AsExtended property of the column.  This introduces the possiblity of rounding problems at the limits of the large value range.  

AsVariant will (of course) also accept varString types, which will be be converted to the underlying data type by means of the AsString method - and so minimise rounding issues at range limits.

Value properties of TIB_Columns

Most properties of TIB_Column use virtual functions, providing consistent support through all derivatives subclassed from TIB_Column.  This means that the following code is consistent, and will work as you would expect...

    var
        tmpStr: string;
    begin
        tmpStr := IB_Query1.FieldByName( 'MYFIELD' ).AsString;

tmpStr will be assigned the string value of 'MYFIELD', and that string value will have been obtained from the code of that particular TIB_Column class derivative, the special AsString handling used by the TIB_ColumnNumeric class, for example.

This is NOT true of the Value property!

The Value property cannot use virtual methods because the data type depends on the particular TIB_Column class.  The following code may not have the effect that you would expect...

    var
        tmpExt: extended;
    begin
        tmpExt := IB_Query1.FieldByName( 'MY_NUMERIC_FIELD' ).Value;

You know that 'MY_NUMERIC_FIELD' will have been created as TIB_ColumnNumeric, and you know the Value property of that class returns an Extended data type - so you might expect the code above to return an Extended value directly.  In fact, it will return a Variant of type varString (see above) and, if the scale was 1..4, that string would have been generated using the AsCurrency method.

This happens because the column reference returned by FieldByName() is a generic TIB_Column class.  The Value property of the TIB_Column class returns a Variant (using the AsVariant property), and because the Value access methods are not virtual, the result returns directly from the TIB_Column class and not a specialised derivative.

Either of the following is more appropriate...

    var
        tmpExt: extended;
    begin
        tmpExt := IB_Query1.FieldByName( 'MY_NUMERIC_FIELD' ).AsExtended;

    var
        tmpExt: extended;
    begin
        tmpExt := (IB_Query1.FieldByName( 'MY_NUMERIC_FIELD' ) as TIB_ColumnNumeric).Value;

Value and AsExtended properties of TIB_ColumnNumeric

If you use the Value property (see notes about Value properties above) or the AsExtended property of a TIB_ColumnNumeric instance, you will be working with the Extended data type, an 80-bit floating point value.  Extended provides more significance than Double, which means that working with the Extended data type is usually more reliable, especially when working with NUMERIC columns whose precisions are 9 or less.  But there are still some possible inconsistencies to consider when using NUMERIC fields.

The TDataset components do not support fields of extended data type.  TFloatField (and therefore TIBOFloatField) use the Delphi double type (which is only a 64bit floating point value).  Because of this,  values shown by the native components using AsExtended, may not exactly match values displayed using TDataset components.

When working with precision of 10 or greater, it is possible continue to experience rounding difficulties at the limits of the 64bit integer range, because the limits of the 64bit integer are very similar to the limits of Extended.   You need to be aware that, if you need to manipulate large NUMERIC values using functions with parameter or return types of Extended, the results may not be entirely predictable.  

If at all possible when using NUMERICs with a scale of 1..4, use functions that use the Delphi currency data type to ensure that you minimise rounding inconsistencies.

The Delphi currency data type

The TIB_ColumnNumeric method AsCurrency, uses the Delphi currency data type to access a column value. Currency is a scaled 64bit integer type native to Delphi/BCB (versions 4 or later).  Its purpose is to minimise rounding errors.  However, because currency is not quite the same thing as the Firebird NUMERIC data type, you need to understand the differences if you intend to use the currency type.

The currency data type is an 64bit integer implicitly scaled (by the compiler) to 4 decimal places.  This scaling is fixed, so it cannot be matched to the scale of a NUMERIC that you defined in the database.  The closest match occurs between a Delphi currency value and a Firebird NUMERIC(18,4).  In other words...

  currency supports:
      14 digits** to the left of the decimal place
      4 digits to the right of the decimal

Conversion of database numerics to currency will cause an exception data exists that exceeds these fixed limits**.

** Actually currency will support up to 15 digits to the left of the decimal place, but only within the range:
  -922337203685477.5808..922337203685477.5807
so in terms of the database declared numeric data type, 14 digits is all that can be relied on.

People often implement a database field using a data specification like...
  AMONEY NUMERIC(18,2)
because they only need two decimal places.  

The trap with using such a specification with the Delphi currency data type is that NUMERIC(18,2) is capable of supporting 16 digits to the left of the decimal point, but a value such as that would overflow the currency limits and cause an exception.

Avoiding overflow with Currency

If you only want two decimal places and plan to use the Delphi currency data type, you need to set up a domain similar to...

    CREATE DOMAIN CURRENCY_D
    AS NUMERIC(16,2)
    CHECK( VALUE BETWEEN -99999999999999.99 AND +99999999999999.99 );

This declares the type which is appropriate to the support provided by the currency datatype, and implements a check to ensure the database will not accept any values larger than the 16 digit precision specifies.

You can also avoid the limitation by defining the NOBCD column attribute for the particular field or domain.  However this will revert client processing to using the Extended data type (or double if using TDataset components), with the consequent rounding problems near the limits of the range - see above.

Calculations with Currency and NUMERIC

Even after declaring an appropriately constrained domain on the server, there are still some other differences to note between NUMERIC and currency...

*  Because NUMERIC is a true scaled integer, division will truncate (not round) any remainder.
   Currency is implemented partly as a real/floating-point value, and the compiler implements
   rounding to the nearest even value.

*  Because of the part-real implementation of currency, you can still get rounding errors at the 
   extremes of the currency range.  The following code was run under Delphi 5:

 procedure TForm1.TestCalcs(Sender: TObject);
 var
   c, m: currency;
 begin
   m := 100;
   c := 12345678901234.0000;
   c := c / m;
   c := c * m;
   Label1.Caption := FormatCurr('#,#0.0000', c);
 end;

   The result displayed in Label1.Caption was:
   12,345,678,901,233.9984

   This was caused by declaring m as currency.  If the code was implemented as simply...
    c := 12345678901234.0000;
    c := c / 100;
    c := c * 100;

  then the rounding problem did not appear. 

The exact mechanism that produces these results is part of "compiler magic" and I dont pretend to understand it all. However it is important to realise that such problems exist if you are manipulating large currency values within code.

NOTE :: Changes to IBO (v.4.3 and beyond) will avoid this problem when altering the scale of currency values to match the NUMERIC declaration, by casting first to Int64 and then adjusting the scale appropriately.  For consistency with the Delphi currency type, the IBO implementation will still perform rounding where necessary - but the IBO rounding is to the nearest value, not the nearest even value.

Stored Procedures and Triggers

In the course of discussing the manipulation of large numeric/currency values, it is appropriate to highlight potential problems with stored procedures and triggers.  

Code such as this...

  DECLARE VARIABLE C NUMERIC(18,4);
  DECLARE VARIABLE M NUMERIC(18,4);
BEGIN
  C = 12345678901234.0000;
  M = 100;
  C = C / M;
  C = C * M;
 
will fail with an arithmetic overflow.  This is because the server will follow strict mathematical rules with the division and attempt to place the result of 'C/M' into an interim variable declared as NUMERIC(18,8).  Of course, this has only 10 digits to the left of the decimal place because 18 digits is the maximum for the data type.

The following code avoids the problem:

  DECLARE VARIABLE C NUMERIC(18,4);
BEGIN
  C = 12345678901234.0000;
  C = C / 100;
  C = C * 100;

because 100 has a scale of 0, so the interim value becomes NUMERIC(18,4) which is acceptable.

It can be useful NOT to declare the full available resolution of the data type, so that there is some room left for interim calculation values.  

For example the following code is OK:

  DECLARE VARIABLE C NUMERIC(16,2);
  DECLARE VARIABLE M NUMERIC(16,2);
BEGIN
  C = 12345678901234.0000;
  M = 100;
  C = C / M;
  C = C * M;

BEWARE of performing compound calculations using numeric values.  For example:
  C = (C / M) * M;

because the interim values used will result in attempting to use values NUMERIC(18,6) and result in an overflow.  If you must use compound calculations, you probably need to cast each part of the result to keep within range...
  C = CAST( (C/M) AS NUMERIC(16,2)) * M;

Alternatives

It is not essential to use numeric data types (although when the is scale is 0 or the values stored are small you are reasonably safe).
Instead, you can implement your monetary values using double precision.  This has a significance of only 15, but that is often sufficient.

However, if you use double precision you will probably need to implement a UDF library containing rounding functions, so that you can set up your triggers and stored procedures to force the stored value to be rounded as required.

___________________________________________________________________________
As always, if you have any comments or questions feel free to contact the public list server dedicated to IBO.

Jason Wharton
http://www.ibobjects.com
mailto:jwharton@ibobjects.com
Copyright December 2001 Geoff Worboys and Computer Programming Solutions - Mesa AZ