| Database schema / Structure of database schema files | |
The .sch database schema file contains the data types of database table columns.
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:
| 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:
| 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 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:
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 next 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:
For example, a 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. |
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 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^