17.4.ODBC to Jet Data Type Mapping
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
Table17.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
Table17.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:
Table17.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.
Table17.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.
Table17.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.
Table17.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
|
VCHR16
|
||
| 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
|
||
| OFF |
SQL_DOUBLE
|
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.
Table17.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 |