APPENDIX E How to interpret syscolumns (in English)


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.