• Topic
  • Discussion
    • UdaWikiWeb.ODBCToJetDataTypeMapping(Last) -- Owiki? , 2016-08-19 15:00:37 Edit
    [TXT]
    [PLAIN]
    [DOCBOOK]
    owiki 2016-08-19 15:00:37

    Additional Reading

    ODBC to Jet Data Type Mapping

    Overview

    When linking to a remote table, Microsoft Jet uses the ODBC SQLColumns() API call 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 to preserve precision, since the maximum precision of the Jet numeric types is 15. Unfortunately, once mapped to the Text type, the data is no longer recognized as being numeric.

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

    ODBC to Jet Data Type Mapping performed by Jet Engine







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

    SQL_DECIMAL

    or

    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 irrelevant Text Not documented. Based on testing.


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

    Jet Data Type Ranges

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

    References

    Referenced by...