The decimal and money type mapping

Microsoft™ Visual Basic and ADO have limitations when handling floating point numbers with a scale greater than 30 and decimals with an undefined scale. Therefore, some ADO consumers (for example, Microsoft Visual Basic 6) might encounter problems representing HCL Informix® DECIMAL or MONEY values.

ADO allows you to specify DBPROP_INIT_PROVIDERSTRING parameters as part of the connection string. Some tools (for example, Microsoft Visual Basic 6) allow you to set DBPROP_INIT_PROVIDERSTRING parameters as Extended Properties. The parameters are case-sensitive.

To allow these consumers to correctly handle decimal values, HCL Informix OLE DB Provider sets the advanced connection option Describe Decimal as Real/Double, so that decimal values with no scale are returned as the type DBTYPE_R8.

To avoid the problem of floating point numbers with a scale greater than 30, HCL Informix OLE DB Provider supplies the provider string option decasr8=R8, which you specify by setting the DBPROP_INIT_PROVIDERSTRING initialization property. This parameter instructs HCL Informix OLE DB Provider to map DECIMAL and MONEY values to the standard Windows™ DBTYPE_R8 data type. This option also resolves the decimals-with-no-scale problem, but can lead to unnecessary truncation of digits.

Starting with version 3.00, when the connection option (decasr8=R8) is not used for columns with DECIMAL data type and no scale is specified, the precision and scale are evaluated by the OLE DB Provider with the following formula for the non-ANSI databases:

DECIMAL(p) = DECIMAL(MIN(2 * p, 32),(p < 16) ? p : 12 + ((32 - p) / 4))

For best results, always specify a scale for DECIMAL data types.