%META:TOPICPARENT{name="ErrorMessagesT"}%

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

%TOC%

==Problem==

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

* {{{DECIMAL (p,s)}}} &#8212; Holds fixed point values with precision {{{p}}}, scale {{{s}}}. {{{p <= 32}}}, {{{s <= p}}}, range {{{10^-130 - 10^124}}}.
* {{{DECIMAL (p)}}} &#8212; 
#* Non-ANSI db &#8212; Holds floating point data with precision {{{p}}}.
#* ANSI db &#8212; {{{DECIMAL (p)}}} interpreted as {{{DECIMAL(p,0)}}}.
* {{{DECIMAL}}} &#8212; 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 &#8212;
{{{
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.