ODBC Data Types
The following table shows how KCML data types are mapped to ODBC SQL data types. Note that not all ODBC SQL data types are supported.
| KCML Data Type | SQL datatype | Data dictionary code | ODBC Data Type |
|---|---|---|---|
| Character | VARCHAR | C | SQL_VARCHAR |
| Julian Date | DATE | J | SQL_DATE |
| Packed Date | BCDDATE | L | SQL_DATE |
| Logical | BIT | F | SQL_BIT |
| KCML packed | NUMERIC | P | SQL_NUMERIC |
| IBM packed | DECIMAL | K | SQL_NUMERIC |
| Binary | INTEGER | B | SQL_INTEGER |
Note SQLGetTypeInfo returns ODBC SQL data types. All conversions in Appendix D of the Microsoft ODBC SDK Programmer's Reference are supported for the ODBC SQL data types listed earlier in this topic.
Character type
Character data is mapped to SQL_VARCHAR if up to 255 characters long and SQL_LONGVARCHAR of more than that. Both datatypes are ortherwise identical. When bound to SQL_C_CHAR objects trailing blanks will be removed and the string will be null terminated. This may make blank strings indistinguishable from NULL's.
The Unicode version of the ODBC API, and the SQL_C_WCHAR type, is supported from version 7.09.00.20175 of the ODBC server & driver when querying UTF-8 databases such a KDB type-7, Oracle & PostGreSQL. Data queried from legacy, non UTF-8, systems will still be encoded in the codepage it was entered.
Numeric type
Packed BCD data is mapped to SQL_NUMERIC and preserves its data dictionary scale and precision deduced from the pack image. The default C data type for SQL_C_DEFAULT is SQL_C_CHAR to avoid rounding.
Binary type
Binary columns are always unsigned. They cannot be NULL.
Logical type
The logical type is a synthetic type generated whenever a column is of type C, has width 1 and permits only the values Y, N or blank. A blank value is considered to be NULL. This type can be suppresed using the option to hide BIT type in the connection string or by checking the box in in the options dialog.
Packed date type
A packed date is assummed to be BCD and formatted as either CCYYMMDD or YYMMDD depending on whether the packed width is 4 or 3 bytes.
Nulls
Numeric fields are considered NULL if the first byte is HEX(FF). Character fields are NULL if the first byte is HEX(FF) or HEX(00). Date fields are considered NULL if zero. NULL is not implemented for binary fields.