Back to Contents

Database Schema Files

Summary:

• What are Database Schema Files?
• Database Schema Extractor
• Column Data types File
• Column Attributes File
• Column Validation File

See also: Forms, Programs, Variables, fgldbsch.

Definition of Database Schema Files

Database Schema Files are used during compilation to indirectly define data types, default values, display attributes and validation rules for form fields and program variables.

In program sources or form specification files, you can specify the database schema file with the SCHEMA instruction.

You must generate database schema files each time the database structure changes, before compiling program modules and form specification files.

The FGLDBPATH environment variable can be used to define a list of directories where the compiler can find database schema files.

Warnings:

1. The data types, display attributes and validation rules are taken from the Database Schema Files during compilation. Make sure that the schema files of the development database correspond to the production database, otherwise, the elements defined in the compiled version of your modules and forms will not match the table structures of the production database.

Database Schema Extractor

The Schema Extractor is the tool provided to generate the database schema files from an existing database.

For more details, see fgldbsch.

Column Data Types File

Purpose

The Column Data Types File contains the data type definition of all columns selected during schema extraction.

Syntax:

Notes:

1. The data type of program variables or form fields used to hold data of a given database column must match the data type of the corresponding column. BDL simplifies the definition of these elements by centralizing the information in external .sch files.
2. Column data types are extracted from the database system tables (typically, syscolumns when using Informix). The native data types can be converted to BDL data types in accordance with the conversion tables, by using the -cv option of the fgldbsch tool.
3. In programs, you can define variables with the data type of a database column by using the LIKE keyword.
4. You can directly specify the table and column name in the field definition in the ATTRIBUTES section of forms.

Warnings:

1. 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 (= BDL DATE), while Oracle stores DATEs as year to second ( = BDL DATETIME YEAR TO SECOND). 

Column Attributes File

Purpose

The Column Attributes File contains the default display attributes of all columns selected during schema extraction.

Syntax:

Notes:

1. Column attributes in .att files are generated from the Informix-specific syscolatt table that can be filled with the Informix upscol utility.
2. This files defines display attributes for form fields like the color or the format.  

Warnings:

1. This feature is supported for compatibility with Informix 4GL only, since other databases do not provide the upscol utility program.

Column Validation File

Purpose

The Column Validation File holds the value definition of all columns selected during schema extraction.

Syntax:

Notes:

1. Column validation information in .val files is generated from the Informix-specific syscolval table that can be filled with the Informix upscol utility.
2. The information found in this file (for a given column):
   • The DEFAULT attribute.
   • The INCLUDE attribute.
   • The AUTONEXT attribute.
   • The COMMENT attribute.
   • The PICTURE attribute.
   • The UPSHIFT / DOWNSHIFT attribute.
   • The VERIFY attribute.
3. In programs, you can validate variable values in accordance with the INCLUDE attribute by using the VALIDATE instruction.
4. In form files, the attributes are taken from the .val file as defaults if the corresponding attribute is not  explicitly specified in the field definition of the ATTRIBUTES section.

Warnings:

1. This feature is supported for compatibility with Informix 4GL only, since databases do not provide the upscol utility program.