The system catalogue table SysColumns documents the type of the column in encoded form using values from 2 columns, ColType and ColLength, both of type SMALLINT. The ColType column contains two pieces of information: * The basic type * The NOT NULL indication 1. Basic types The basic types are documented in the ESQL/C header file sqltypes.h. The values in version 6.00 are: CHAR 0 SMALLINT 1 INTEGER 2 FLOAT 3 SMALLFLOAT 4 DECIMAL 5 SERIAL 6 DATE 7 MONEY 8 DATETIME 10 BYTE 11 TEXT 12 VARCHAR 13 INTERVAL 14 NCHAR 15 NVARCHAR 16 Note that type 9 is not used in the system catalogue. If the column does not accept nulls (it was defined with NOT NULL), then 256 is added to the value listed above. The ColLength column contains qualifying information about the type. For some types (CHAR, NCHAR, INTEGER, SMALLINT, SERIAL, DATE, FLOAT, SMALLFLOAT), the value is simply the length of the column in bytes. For CHAR and NCHAR, that is the value specified in parentheses; for the other types, it is simply a constant value (INTEGER = 4, SMALLINT = 2, SERIAL = 4, DATE = 4, FLOAT = 8, SMALLFLOAT = 4). The value stored for the blob types BYTE and TEXT is 56; this is the size of the descriptor entry which tells OnLine where the actual blob data is stored. It bears no resemblance to the actual size of the blob. For all the other types, the information is encoded and has to be dissected carefully to be comprehensible. 2. VARCHAR and NVARCHAR The macros VCMAX and VCMIN are defined in the ESQL/C header varchar.h. If the column is declared as VARCHAR(255), then VCMIN is 0 and VCMAX is 255. If the column is declared as VARCHAR(100, 20), then VCMIN is 20 and VCMAX is 100; the value is encoded as VCMIN * 256 + VCMAX. 3. DECIMAL and MONEY The macros PRECTOT and PRECDEC are defined in the ESQL/C header decimal.h. PRECTOT indicates the number of digits in total in the decimal; this includes the digits after the decimal point. PRECDEC indicates the number of digits after the decimal point; it can take any value from 0 through PRECTOT, or it can have the value 0xFF (255), which indicates that the decimal is a floating point, not a fixed point, decimal. If the decimal type is DECIMAL(16,4), then the value in ColLength is 16 * 256 + 4; if the type is DECIMAL(10), then the value stored in ColLength is 10 * 256 + 255. The actual space used on disk to store a decimal is given by the DECLEN macro. If the decimal is declared as DECIMAL(16,5), then the space used on disk is (16 + ((5 + 1) / 2) * 2 + 1) / 2 + 1 = 12 bytes. The first two 1s in the calculation are there to ensure rounding up; the last 1 accounts for the exponent and sign byte; the divisions are integer (truncating) divisions; and the 2s are all due to the fact that 2 decimal digits can be stored in a single byte. MONEY is just a special case of DECIMAL with different default values from DECIMAL; internally, it is stored identically to a DECIMAL. 4. DATETIME and INTERVAL The macros for analyzing DATETIME and INTERVAL types are defined in the ESQL/C header datetime.h. They are by far the most complex types to deal with, and they are subtly different from each other, as well. The type information is encoded in 3 sub-fields of the ColLength. The declaration of a DATETIME type can be regarded as: DATETIME dt_from TO dt_to; the equivalent for INTERVAL is INTERVAL iv_from(iv_len) TO iv_to. The start and end qualifiers are encoded from the values: YEAR 0 MONTH 2 DAY 4 HOUR 6 MINUTE 8 SECOND 10 FRACTION 12 -- For DATETIME FRACTION TO FRACTION(5), etc. FRACTION(1) 11 FRACTION(2) 12 FRACTION(3) 13 FRACTION(4) 14 FRACTION(5) 15 The end qualifier is encoded in the least significant 4 bits of ColLength; the start qualifier is encoded in the next 4 bits of ColLength. The remaining 8 bits (the high byte) contain the total number of digits in the type. The macros TU_START and TU_END give you the start and end qualifiers; the macro TU_LEN gives the total length. The macro TU_FLEN tells you how many digits there are in the first component of an interval (also of a datetime, but the lengths of these are fixed, whereas in an interval they are not). The macro TU_DTENCODE encodes a DATETIME into the value for ColLength given the start and end qualifiers; the macro TU_IENCODE encodes an INTERVAL given the number of digits (iv_len) and the start and end qualifiers.