ODBC: Data Type Mapping

ODBC defines certain generic data types, which every data source driver maps to its own internal types. Each driver needs to be queried about which types it supports, since different drivers support different types.

The types that Acu4GL for ODBC uses, if they exist in the driver, are:

 SQL_CHAR
 SQL_VARCHAR
 SQL_DECIMAL
 SQL_NUMERIC
 SQL_SMALLINT
 SQL_INTEGER
 SQL_REAL
 SQL_FLOAT
 SQL_DOUBLE
 SQL_TINYINT
 SQL_BIGINT
 SQL_BINARY
 SQL_VARBINARY
 SQL_LONGVARBINARY
 SQL_DATE
 SQL_TIMESTAMP

For example, Informix maps these types like this:

 SQL_CHAR                 CHAR
 SQL_VARCHAR              VARCHAR
 SQL_DECIMAL              DECIMAL or MONEY
 SQL_NUMERIC              no such type
 SQL_SMALLINT             SMALLINT
 SQL_INTEGER              INTEGER or SERIAL
 SQL_REAL                 REAL
 SQL_FLOAT                no such type
 SQL_DOUBLE               FLOAT
 SQL_TINYINT              no such type
 SQL_BIGINT               no such type
 SQL_BINARY               no such type
 SQL_VARBINARY            no such type
 SQL_LONGVARBINARY        BYTE
 SQL_DATE                 DATE
 SQL_TIMESTAMP            DATETIME YEAR TO
                          FRACTION(5)

Informix also defines a type which ODBC calls SQL_LONGVARCHAR, and Informix calls TEXT, which the Acu4GL for ODBC interface doesn't use.

Notice that Informix has two types which match the ODBC SQL_DECIMAL type, and two that match the SQL_INTEGER type. The Acu4GL interface will usually use the first type that it finds that matches an ODBC type, unless there are restrictions on that type. For example, the SERIAL type is limited in that a table can have only one such column, while a typical table may have more than one column of integer data.

So the Acu4GL for ODBC interface will use the Informix INTEGER type, instead of the SERIAL type for integer data.

For another example, Access maps these types like this:

 SQL_CHAR               CHAR
 SQL_VARCHAR            TEXT
 SQL_DECIMAL            no such type
 SQL_NUMERIC            CURRENCY
 SQL_SMALLINT           SHORT
 SQL_INTEGER            LONG or COUNTER
 SQL_REAL               SINGLE
 SQL_FLOAT              no such type
 SQL_DOUBLE             DOUBLE
 SQL_TINYINT            BYTE
 SQL_BIGINT             no such type
 SQL_BINARY             BINARY
 SQL_VARBINARY          VARBINARY
 SQL_LONGVARBINARY      LONGBINARY
 SQL_DATE               no such type
 SQL_TIMESTAMP          DATETIME

Access also defines types, SQL_BIT = BIT and SQL_LONGVARCHAR = LONGTEXT which the Acu4GL for ODBC interface doesn't use.