Delphi DAC for Firebird 4: Using the new INT128, DECFLOAT, and large NUMERIC types

This article describes how to work with Firebird 4 in Delphi data access components. The latest version of IBDAC and UniDAC supports Firebird 4 and the new data types: INT128, DECFLOAT, and NUMERIC.

INT128 is a 128-bit integer data type that isn’t specified in the SQL standard. It’s worth mentioning that unsigned integer data types aren’t supported by Firebird.

DECFLOAT is a data type based on the SQL standard that stores decimal floating values with precision. It should be used if you need to precisely calculate and store numbers. Thus, the DECFLOAT is a great option for financial applications, for example, where the precision of the calculations is a key requirement.

According to IEEE standards, Firebird implements 16-digit and 34-digit DECFLOAT types’ encoding. But, to perform any interim calculations, only 34-digit values are used.

NUMERIC is a 16-, 32-, 64-, or 128-bite decimal point number, depending on the precision. NUMERIC limits are the following:

• precision must be from 1 to 38; it defines a possible number of digits to store;

• the scale must be from 0 to 38; it sets the digits’ number after the decimal point.

For scale, there is an important condition – it must equal the precision or be less.

The new data types work as follows. By default, INT128, DECFLOAT, and NUMERIC with PRECISION 19 or more are returned as a string and are represented in the DataSet as a TStringField since TFMTBCDField fields are not created if the EnableFMTBCD option is not enabled. The goal is to maintain backward compatibility with the previous versions of Firebird and with UniDAC and IBDAC based applications that are being migrated to the new version of the components.

To represent INT128, DECFLOAT, and NUMERIC with PRECISION 19 as a TFMTBCDField in DataSet and to have parameters with these types described as ftFMTBCD, you need to set the following connection option:

for IBDAC:

IBCConnection.Options.EnableFMTBCD := True; 

for UniDAC:

UniConnection.Options.EnableFMTBCD := True;

Also, you can use the data mapping feature to fine-tune the mapping. For example, if you want to represent INT128 as ftLargeInt, DECFLOAT as Double, and Numeric with PRECISION over 19 as ftFMTBCD, set the following mapping rules:

for IBDAC:

// INT128 
IBCConnection.DataTypeMap.AddDBTypeRule(ibcInt128, ftLargeint); 
// DECFLOAT 
IBCConnection.DataTypeMap.AddDBTypeRule(ibcDecFloat16, ftFloat); 
IBCConnection.DataTypeMap.AddDBTypeRule(ibcDecFloat34, ftFloat); 
// DECIMAL and NUMERIC with PRECISION 19 and more 
IBCConnection.DataTypeMap.AddDBTypeRule(ibcDecimal, 19, 38, ftFMTBcd); 
IBCConnection.DataTypeMap.AddDBTypeRule(ibcNumeric, 19, 38, ftFMTBcd);

for UniDAC:

// INT128 
UniConnection.DataTypeMap.AddDBTypeRule(ibcInt128, ftLargeint); 
// DECFLOAT 
UniConnection.DataTypeMap.AddDBTypeRule(ibcDecFloat16, ftFloat); 
UniConnection.DataTypeMap.AddDBTypeRule(ibcDecFloat34, ftFloat); 
// DECIMAL and NUMERIC with PRECISION 19 and more 
UniConnection.DataTypeMap.AddDBTypeRule(ibcDecimal, 19, 38, ftFMTBcd); 
UniConnection.DataTypeMap.AddDBTypeRule(ibcNumeric, 19, 38, ftFMTBcd); 

For more information about the data type mapping, see Data Type Mapping in Delphi DAC.

The data mapping rules can also be set up in design time.