Column Definition File (.sch)

The .sch database schema file contains the data types of database table columns.

Description

The data type of program variables or form fields used to hold data of a given database column must match the data type used in the database. The definition of these elements is simplified by centralizing the information in external .sch files, which contain column data types.

In form files, you can directly specify the table and column name in the field definition in the ATTRIBUTES section of forms.

In programs, you can define variables with the data type of a database column by using the LIKE keyword.

As column data types are extracted from the database system tables, you may get different results with different database servers. For example, Informix^® provides the DATE data type to store simple dates in year, month, and day format (= DATE FGL type), while Oracle stores dates as year to second ( = DATETIME YEAR TO SECOND FGL type).

The table describes the fields you will find in a row of the .sch file:

Table 1. Structure of the .sch file
Pos	Type	Description
1	STRING	Database table name.
2	STRING	Column name.
3	SMALLINT	Coded column data type. If the column is NOT NULL, you must add 256 to the value.
4	SMALLINT	Coded data type length.
5	SMALLINT	Ordinal position of the column in the table.

Next table shows the data types and their corresponding type code that can be present in a .sch schema file:

Table 2. Database Schema file (.sch) data type codes
Data type name	Data type code (field #3)	Data type length (field #4) *
CHAR	0	Maximum number of characters.
SMALLINT	1	Fixed length of 2
INTEGER	2	Fixed length of 4
FLOAT / DOUBLE PRECISION	3	Fixed length of 8
SMALLFLOAT / REAL	4	Fixed length of 4
DECIMAL	5	The length is computed using this formula: length = (precision * 256 ) + scale
SERIAL	6	Fixed length of 4
DATE	7	Fixed length of 4
MONEY	8	Same as DECIMAL
Unused	9	N/A
DATETIME	10	For DATETIME and INTERVAL types, the length is determined using the next formula: length = ( digits * 256 ) + ( qual1 * 16 ) + qual2 where digits is the total number of digits used when displaying the datetime value. For example, a DATETIME YEAR TO MINUTE (YYYY-MM-DD hh:mm) uses 12 digits. The qual1 and qual2 elements identify datetime qualifiers according to this list: 0 = YEAR 2 = MONTH 4 = DAY 6 = HOUR 8 = MINUTE 10 = SECOND 11 = FRACTION(1) 12 = FRACTION(2) 13 = FRACTION(3) 14 = FRACTION(4) 15 = FRACTION(5)
BYTE	11	Length of descriptor
TEXT	12	Length of descriptor
VARCHAR	13	Maximum number of characters. If the length is positive: length = ( min_space * 256 ) + max_size If length is negative: length + 65536 = ( min_space * 256 ) + max_size
INTERVAL	14	Same as DATETIME
NCHAR	15	Same as CHAR
NVARCHAR	16	Same as VARCHAR
INT8	17	Fixed length of 10 (size of int8 structure) In programs, will be converted to a `BIGINT` type.
SERIAL8	18	Fixed length of 10 (size of int8 structure) In programs, will be converted to `BIGINT` type.
BOOLEAN (SQLBOOL)	45	Boolean type, in the meaning of Informix front-end SQLBOOL (sqltype.h)
BIGINT	52	Fixed length of 8 (bytes)
BIGSERIAL	53	Fixed length of 8 (bytes)
VARCHAR2	201	Maximum number of characters In programs, will be converted to a `VARCHAR` type.
NVARCHAR2	202	Maximum number of characters In programs, will be converted to a `VARCHAR` type.

* data type length (field #4) is a SMALLINT value encoding the length or composite length of the type. For character string types, the unit of the length used to define character program variables and form fields depends on the length semantics.

Informix SERIAL types

When the database schema defines SERIAL, BIGSERIAL or SERIAL8 types, form fields referencing the serial column with get the NOENTRY attribute automatically, except if defined with the TYPE LIKE syntax.

Informix DISTINCT types

Informix IDS version 9.x and higher allow you to define DISTINCT types from a base types with the CREATE DISTINCT TYPE instruction. In the syscolumns table, Informix identifies distinct types in the coltype column by adding the 0x0800 bit (2048) to the base type code. For example, a distinct type defined with the VARCHAR built-in type (i.e. code 13) will be identified with the code 2061 (13 + 2048). Informix sets additional bits when the distinct type is based on the LVARCHAR or BOOLEAN opaque types: If the base type is an LVARCHAR, the type code used in coltype gets the 0x2000 bit set (8192) and when the base type is BOOLEAN, the type code gets the 0x4000 bit (16384).

When extracting a schema from an Informix database defining columns with DISTINCT types, the schema extractor will keep the original type code of the distinct type in the .sch file for columns using distinct types based on built-in types (with the 0x0800 bit set). Regarding the exception of opaque types, BOOLEAN-based distinct types get the code 45 ( + 256 if NOT NULL), and LVARCHAR-based distinct types are mapped to the code 201 (+ 256 if NOT NULL) if the -cv option enables conversion from LVARCHAR to VARCHAR2.

The fglcomp and fglform compilers understand the distinct type code bit 0x0800, so you can define program variables with a DEFINE LIKE instruction based on a column that was created with a distinct Informix type.

customer^customer_num^258^4^1^
customer^customer_name^256^50^2^
customer^customer_address^0^100^3^
order^order_num^258^4^1^
order^order_custnum^258^4^2^
order^order_date^263^4^3^
order^order_total^261^1538^4^