Error Message: The decimal field precision is too small to accept the numeric you attempted to add.

Problem

Informix's DECIMAL type can hold both fixed-point and floating-point values.

• DECIMAL (p,s) — Holds fixed point values with precision p, scale s. p <= 32, s <= p, range 10^-130 - 10^124.
  
• DECIMAL (p) —
  
  • Non-ANSI db — Holds floating point data with precision p.
    
  • ANSI db — DECIMAL (p) interpreted as DECIMAL(p,0).
    
• DECIMAL — Interpreted as DECIMAL(16). Holds floating point data with precision 16.

The problem occurs with columns defined using the second and third forms above, i.e., when a DECIMAL column holds floating-point data. When Informix detects such a column, it reports the column's scale as 255. The maximum scale of a DECIMAL column holding fixed-point data is 32, so 255 indicates floating-point data. Our agent was detecting when scale = 255 and adjusting the scale to a more meaningful value for ODBC.

The issue was that the driver always mapped an Informix DECIMAL to the ODBC SQL type SQL_DECIMAL, and when scale=255 was detected, the driver adjusted the scale to match the column's precision. However the adjusted scale value was not always appropriate, which caused Access to balk. A column's scale can be retrieved through several ODBC functions: SQLDescribeCol(), SQLColAttributes(), SQLColumns(), and this fix applies to all of these calls.

Fix

The driver now maps:

• When the jetfix option is off:
  
  • An Informix DECIMAL holding fixed-point data to the ODBC SQL type SQL_DECIMAL. The reported scale is that reported by Informix.
    
  • An Informix DECIMAL holding floating-point data to ODBC SQL_FLOAT. The reported scale is NULL (SQLColumns()) or 0 (SQLDescribeCol() and SQLColAttribute()). (This is how the Microsoft's driver for SQL Server handles a FLOAT column.)
    
• When the jetfix option is on:
  
  • An Informix DECIMAL column is mapped to the ODBC SQL_DOUBLE type, irrespective of whether it holds fixed-point or floating-point data. Because the maximum precision of a SQL_DOUBLE is 15, rounding may occur when fetching from DECIMAL, DECIMAL(p), or DECIMAL(p,s) columns where p>15.

Testing

Create and populate a test table —

CREATE TABLE bug5552 
   ( id INT, 
     d1 DECIMAL(5,2), 
     d2 DECIMAL(6), 
     d3 DECIMAL
   ) ;

INSERT INTO bug5552 
   VALUES (1, 321.12,    654.321,    654321.0987654321 ) 
   VALUES (2, 654.32,    65432.1,     6543210987654321 ) 
   VALUES (3,  92.37,  6.54321e6,  6.543210987654321e2 )
   VALUES (4, 987.65, 6.54321e41, 6.543210987654321e49 ) ;

These values were chosen to use the full precision of each column.

All of these values should be retrieved correctly from a linked table in Microsoft Access whether jetfix is on or off. Note that Microsoft Access fetches these columns into NUMBER columns, the precision of which varies depending on the underlying Field Size property; consequently, some values may be rounded.