17.4.1. Overview

When linking to a remote table, Microsoft Jet calls the ODBC SQLColumns function to gather information on the fields in the table. The information returned by SQLColumns determines how the data type of each field will be mapped to a Microsoft Jet data type.

Jet uses the fSqlType, lPrecision, and wScale values to determine an appropriate local data type, on a field-by-field basis, and uses that data type to represent the remote data in a linked table. Each time Microsoft Jet executes an action or parameter query against the remote data source, the information stored in the linked table is used to ensure that ODBC calls are made with a valid ODBC data type.

The Jet Engine's mapping of the ODBC SQL_DECIMAL and SQL_NUMERIC types is detailed below. The mapping of these column types is problematic when the precision of a column is greater than 15. Under these circumstances Jet maps the column to the Jet Text type. This is done so as not to lose precision, since the maximum precision of the Jet numeric types is 15. An unfortunate consequence of mapping to the Text type is that the data is no longer recognised as being numeric.

To overcome this limitation, the Jetfix switch (+jetfix) changes the ODBC type returned by SQLColumns for native database column types which would normally be reported as SQL_DECIMAL or SQL_NUMERIC. These might, for example, be MONEY, DECIMAL or NUMERIC columns in the remote table. With Jetfix enabled, these columns are typically reported as SQL_DOUBLE; which Jet always maps to Number (Double). As a result, Jet recognises the data as numeric, albeit with a possible loss of precision, depending on the actual data values.

17.4.2. ODBC to Jet Data Type Mapping

Table 17.14. ODBC to Jet Data Type Mapping performed by Jet Engine

ODBC Data Type Jet Data Type Notes
Type Precision p Scale s

SQL_DECIMAL

SQL_NUMERIC

p <= 4 s = 0 Number (Integer)  
p <= 9 s = 0 Number (Long Integer)  
p <= 15 0 <= s <=4 Number (Double)  
p <= 15? s > 4 Number (Double) "ODBC to Jet Data Type Mapping" document unclear here.
p = 19 s = 4 Currency MS SQL Server only
p = 10 s = 4 Currency MS SQL Server only
p > 15 s irrelevant Text Not documented. Based on testing.

The Jet Data Type is a property setting available in the table Design view.

17.4.3. Jet Data Type Ranges

Table 17.15. Jet Data Type Ranges

Jet Data Type Range
Integer – 32,768 … 32,767
Long Integer – 2,147,483,648 … 2,147,483,647
Double -1.8E+308 … -1.8E-308, 1.8E-308 … 1.8E+308
Currency – 922,337,203,685,477.5808 … 922,337,203,685,477.5807

17.4.4. References

See the following articles from Microsoft:

  • Knowledge base article KB214854, "Improved ODBC DataType Mappings with Jet 4.0" on the Microsoft Support Website .

  • See Chapter 9 "Developing Client/Server Application" of Microsoft Jet Database Engine Programmer's Guide, Second Edition. Redmond, WA: Microsoft Press, 1997.

17.4.5. Informix Jet Support

Informix Jetfix Testing

To verify the effect of the +jetfix switch, two tables were created and then linked to from Access, once with +jetfix enabled, and once with +jetfix disabled. The first table contained five MONEY columns of various precisions and scales. The second table was identical except that it used DECIMAL instead of MONEY columns. The resulting Jet data type mappings as shown in the 'Table Design' view are detailed below:

Table 17.16. Informix ODBC to Jet Data Type Mapping

Informix table definition:

create table CBTEST (<column defs>)

Jet Type Mappings  
Jetfix off Jetfix on
col1 {MONEY| DECIMAL} (14, 4) Number (Double) Number (Double)
col2 {MONEY| DECIMAL} (14, 5) Number (Double) Number (Double)
col3 {MONEY| DECIMAL} (16, 0) Text Number (Double)
col4 {MONEY| DECIMAL} (16, 4) Text Number (Double)
col5 {MONEY| DECIMAL} (16, 5) Text Number (Double)

OpenLink Informix Agent – Numeric Data type Mappings

The following table details how the Informix agent fetches numeric data from Informix. The SQL type reported for a SELECT column varies depending on the native Informix type of the column, and its precision and scale. The OpenLink C type used to fetch the data is also shown, together with the effect of the JetFix command line switch.

Changes to the type mappings required to implement the +jetfix option are shown in italics.

Table 17.17. Informix Agent – Numeric Data type Mappings

Informix Type Scale (s)

Decimal Precision (p)

Range (r)

JetFix SQL Type

OpenLink Fetch Type

CTYPE_xxx

FLOAT [(n)] {1}

Synonyms:

DOUBLE PRECISION

NA

p <= 16

r = 1.7E308 (15 digits precision)

NA SQL_DOUBLE FLT64

DECIMAL

DECIMAL (p)

DECIMAL (p,s)

Synonyms:

NUMERIC

NA

NA

p = 16

p <= 32

p <= 32

r = 1E+124 … 1E-130 ..

OFF SQL_DECIMAL VCHR
ON SQL_DOUBLE FLT64 {2}

INTEGER

Synonymns:

INT

s = 0

p <= 9

r = -2,147,483,648 to +2,147,483,647

NA SQL_INTEGER SGN32

MONEY

MONEY(p)

MONEY (p,s)

s = 2

s = 2

p = 16

p <= 32

p <=32

r = 1E(p-s) - 1E(-s)

OFF SQL_DECIMAL VCHR
ON SQL_DOUBLE FLT64 {2}

SMALLFLOAT

Synonyms:

REAL

NA r =3.4E38 NA SQL_REAL FLT32
SMALLINT s = 0

p = 5

r = -32,768 to +32,767

NA SQL_SMALLINT SGN16

  • {1} Informix SQL ignores the specified float precision.

  • {2} Possible loss of precision.

Precision of OpenLink C Types

The choice of OpenLink C type used to fetch a numeric value is determined by the maximum precision supported by the type. If a numeric type exists which can represent the data to be fetched, without loss of precision, then this is used as the preferred fetch type. If a numeric type capable of supporting the required precision does not exist, then the data is fetched in character form, unless the JetFix switch is on. In this case, the data is fetched using the CTYPE_FLT64 fetch type. This type has sufficient range to store the maximum numeric values supported by Informix, but its use may result in a loss of precision, if the precision of the data to be fetched exceeds 15 decimal digits.

Table 17.18. Precision of OpenLink C Types

OpenLink C Type CTYPE_xxx Equivalent built in C type (32bit processor) Range Can hold all numbers of precision <= p decimal digits
SGN8 signed char -128…127 p = 2
SGN16 short -32768…32767 p = 4
FLT32 float 3.4E38 (6 digits precision) p = 6
SGN32 long -2,147,483,648…2,147,483,647 p = 9
FLT64 double 1.7 E308 (15 digits precision) p = 15

17.4.6. Oracle Jet Support

OpenLink Oracle Agent – Numeric Data type Mappings

The following table details how the Oracle agent fetches numeric data from Oracle. The SQL type reported for a SELECT column varies depending on the native Oracle type of the column, and its precision and scale. The OpenLink C type used to fetch the data is also shown, together with the effect of the JetFix command line switch.

Changes to the type mappings are shown in the table as italics.

Table 17.19. Informix ODBC to Jet Data Type Mapping

Oracle Type Scale (s) Decimal Precision (p) JetFix SQL Type OpenLink Fetch Type CTYPE_xxx
FLOAT (b) NA

p <= 15

(b <= 50) {1}

NA SQL_DOUBLE FLT64

15 < p < 38

(50 < b < 126)

ON SQL_DOUBLE FLT64 {2}
OFF SQL_DECIMAL VCHR16
FLOAT {3} , NUMBER {4} NA p = 38 ON SQL_DOUBLE FLT64 {2}
OFF SQL_DECIMAL VCHR16
NUMBER (p,s) s <> 0 p <= 15 ON SQL_DOUBLE FLT64
OFF SQL_DECIMAL VCHR16
p > 15 ON

SQL_DECIMAL

SQL_DOUBLE

VCHR16

FLT64 {2}?

OFF SQL_DECIMAL VCHR16
Calculated field s = 0 p = 0 {5} ON SQL_DOUBLE FLT64
OFF SQL_DECIMAL VCHR16
NUMBER (p) s = 0 0 < p <= 2 NA SQL_TINYINT SGN8
2 < p <= 4 NA SQL_SMALLINT SGN16
4 < p <= 9 NA SQL_INTEGER SGN32
9 < p <= 15 ON SQL_DOUBLE

VCHR16

FLT64

OFF

SQL_DOUBLE

SQL_DECIMAL

VCHR16
15 < p <= 38 {6} ON SQL_DOUBLE FLT64 {2}
OFF SQL_DECIMAL VCHR16

{Notes}:

  • FLOAT(b) specifies a floating point number with binary precision b. The precision can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103.

  • Possible loss of precision

  • FLOAT specifies a floating point number with decimal precision 38, or a binary precision of 126.

  • NUMBER specifies a floating point number with decimal precision = 38.

  • Oracle appears to return s=0, p=0 for a calculated field

  • Oracle permits a maximum precision of 38.

Precision of OpenLink C Types

The choice of OpenLink C type used to fetch a numeric value is determined by the maximum precision supported by the type. If a numeric type exists which can represent the data to be fetched, without loss of precision, then this is used as the preferred fetch type. If a numeric type capable of supporting the required precision does not exist, then the data is fetched in character form, unless the JetFix switch is on. In this case, the data is fetched using the CTYPE_FLT64 fetch type. This type has sufficient range to store the maximum numeric values supported by Oracle, but its use may result in a loss of precision, if the precision of the data to be fetched exceeds 15 decimal digits.

Table 17.20. Precision of OpenLink Oracle C Types

OpenLink C Type CTYPE_xxx Equivalent built in C type (32 bit processor) Range Can hold all numbers of precision <= p decimal digits
SGN8 signed char -128…127 p = 2
SGN16 short -32768…32767 p = 4
SGN32 long -2,147,483,648…2,147,483,647 p = 9
FLT64 double 1.7 E308 (15 digits precision) p = 15