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.

The .sch file can be produced by the fgldbsch tool, from the system views of the target database.

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.

This table shows the data types and their corresponding type code that can be present in a 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 or bytes (see note)
`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	If the decimal is defined with a precision and scale, the length is computed using this formula: `length = (precision * 256) + scale` If the decimal is defined as a floating point decimal (i.e. with no scale), the length is computed as follows: `length = (precision * 256) + 255`
`SERIAL`	6	Fixed length of 4
`DATE`	7	Fixed length of 4
`MONEY`	8	The length is computed using this formula: `length = (precision * 256) + scale` A `MONEY` cannot be defined with a floating point, is has always a scale.
Unused	9	N/A
`DATETIME`	10	For `DATETIME` types, the length is determined using the 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 using the following codes: `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)` For example, a DATETIME YEAR TO MINUTE size length is computed as follows: `(12 * 256) + (0 * 16) + 8 = 3080`
`BYTE`	11	Length of descriptor
`TEXT`	12	Length of descriptor
`VARCHAR`	13	Maximum number of characters or bytes (see note) If the length is positive: `length = ( min_space * 256 ) + max_size` If length is negative: `length + 65536 = ( min_space * 256 ) + max_size`
`INTERVAL`	14	For `INTERVAL` types, the length is determined using the following formula: `length = ( digits * 256 ) + ( qual1 * 16 ) + qual2` where `digits` is the total number of digits used when displaying the interval value. For example, a `INTERVAL HOUR(5) TO FRACTION(3)` (hhhhh:mm:ss.fff) 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)` For example, an `INTERVAL HOUR(5) TO FRACTION(3)` size length is computed as follows: `(12 * 256) + (6 * 16) + 13 = 3181`
`NCHAR`	15	Maximum number of characters or bytes (see note)
`NVARCHAR`	16	Maximum number of characters or bytes (see note)
`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.

Note: 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 will get the NOENTRY attribute automatically, except if defined with the TYPE LIKE syntax.

Informix CHAR/VARCHAR types and SQL_LOGICAL_CHAR

The Informix SQL_LOCICAL_CHARS onconfig parameter is taken into account when extracting CHAR/VARCHAR column types, to convert the size in bytes from syscolumns.collength to a number of characters for the .sch file.

It is then possible to use FGL_LENGTH_SEMANTICS=CHAR when the application locale is UTF-8.

Informix DISTINCT types

Informix IDS version 9.x and higher allow you to define DISTINCT types from a base type 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 (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.

Example

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^