KCML Datatypes
This table shows the mapping between SQL data types that can be used in a CREATE TABLE statement and the old Rev7 .dd dictionary codes. Note that the data type specifies the TYPE, LENGTH, PIMAGE dictionary fields in combination with an optional SIGNED or UNSIGNED attributes. Packed numbers are to be assumed to be SIGNED however this can be overridden by the explicit attribute. Integers are assumed UNSIGNED unless the SIGNED attribute
The precision prec defines the number of digits in total and the scale scale determines how many are after the decimal.
| Rev7 dictionary code | KDB SQL type | FLD type | Purpose |
|---|---|---|---|
| C | CHAR(len) | CHAR(len) | Fixed size strings of any size. |
| V | VARCHAR(len) | CHAR(len) | Variable length strings of any size. NULL if blank |
| P | NUMERIC(prec[, scale]) | NUM(prec[, scale]) | KCML packed numbers. |
| B | INTEGER[(size)] | INT(size) UINT(size) | Integers 1-4 bytes. Represented in Oracle by NUMBER(n) where n is 3,5,9 or 11 respectively. |
| J | DATE | DATE | A date represented as a Julian date in an unsigned integer of 3 bytes. A zero value is considered to be NULL |
| H | HEX(len) | HEX(len) | A binary datatype. |
| F | BIT | The logical type is a synthetic type generated whenever a column is of type VARCHAR, has width 1 and permits only the values Y, N or blank. A blank value is considered to be NULL. | |
| G | BOOL | BOOL | BOOLEAN 'Y', '1' or N' '0' held as a single byte |
| L | BCDDATE | A 3 byte YYMMDD packed date or a 4 byte CCYYMMDD packed date | |
| K | DECIMAL(prec[, scale]) | IBM packed numbers | |
| T | TIME | TIME | Time since midnight local time in seconds as an unsigned integer in 3 bytes. |
| M | TIMESTAMP | TIMESTAMP | Time in milliseconds since 00:00 GMT year 0000, as an unsigned integer in 6 bytes. |
| Q | BLOB | BID | BLOB - Binary Large OBject |
| O | CLOB | Character BLOB |
Variable length VARCHAR strings are only supported for SQL databases where the length is interpreted as a maximum. Such strings are space filled when read from the database and trailing blanks are clipped before writing to the database. A blank variable length string is held in the database as a NULL. Variable length strings are only allowed in index columns if the column is declared as NOT NULL in the CREATE TABLE thus disallowing blank values. VARCHAR can be used in word indexed columns without this restriction.
VARCHAR2 is a synonym for VARCHAR. The native KDB database considers CHAR and VARCHAR to be the same fixed length string datatype.
CHAR and VARCHAR types must be UTF-8 encoded. Oracle databases must be created with UTF-8 encoding.