Purpose of database schemas

Database schemas hold the definition of the database tables and columns. The schema files contain the column data types, validation rules, form item types, and display attributes for columns.

The schema files are used to centralize column data types to define program variables, as well as display attributes which are normally specified in the form specification file.

The database schema files are generated with the fgldbsch tool from the system tables of an existing database.

It is strongly recommended that you regenerate the schema files when upgrading to a new compiler version. Bug fixes and new data type support can required schema file changes. If the schema file holds data type codes that are unknown to the current version, the compilers will raise the error -6634.

For some type of databases, the table owner is mandatory to extract schema information. If you do not specify the -ow option in the comment line, fgldbsch will take the -un user name as default. If you do not use the -un/-up options because you are using indirect database connection with FGLPROFILE settings to identify the database user, or if the database user is authenticated by the operating system, the fgldbsch tool will try to identify the current database user after connection and use this name as table owner to extract the schema.

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

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

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.

Program variables can be defined with the LIKE keyword to get the data type defined in the schema files:

SCHEMA stores 
MAIN
   DEFINE custrec RECORD LIKE customer.*
   DEFINE name LIKE customer.cust_name 
   ...
END MAIN

Form fields defined with the FIELD item type can get the form item type from the schema files:

SCHEMA stores 
LAYOUT
GRID
{
  [f001         ]
}
TABLES
  customer 
END
ATTRIBUTES
FIELD f001 = customer.cust_name;
END

Note: For handling uppercase characters in the database name you must quote the name: SCHEMA "myDatabase"