Database Schema Wizard

Documentation home

The Database Schema Wizard is accessible from Tools on the Designer Main Menu, the Database Resource Editor and the Stored Procedure Resource Editor. It reads schema information from the database and uses this to create or replace Database Resources and/or Stored Procedure Resources. When the wizard is invoked from the designer menu, any number of Database Resources and Stored Procedure Resources can be created or replaced. When invoked from the Database Resource Editor or the Stored Procedure Resource Editor, only the resource being edited can be changed.

The wizard allows you to navigate to the table(s), view(s) and/or stored procedure(s) you want to access and then extracts the relevant schema information about these objects. Tables and views can be accessed via Database Resources and stored procedures can be accessed by Stored Procedure Resources. In all cases, the wizard will only create a resource to access a single table, view or stored procedure. If you want to create SQL joining several tables, you should create or edit a Database Resource manually.

All columns are imported as resource fields:

• Resource field name, type, length and number of decimal digits are taken from the column metadata
• Additionally for tables and views:
• Any auto-increment fields are marked as read only
• Any primary key fields are marked as unique key

The following pages are displayed by the wizard:

1. Introduction Page

The text displayed depends on which editor the wizard has been invoked from.

2. Database selection page

This page is shown when the database system supports separately named databases (referred to as catalogs) as a sub-division. The term “database” shown at the top may be replaced with the term used within a particular database system. The initial selection is set with the database name specified on the JDBC URL string for the Database Connection.

3. Database schema selection page

This page is shown when the database system supports separately named schemas as a sub-division. The term “owner” shown at the top may be replaced with the term used within a particular database system. The initial selection is usually set with the user name specified on the JDBC URL string for the Database Connection.

4. Item Selection Page

Each item selected will be imported. Tables and Views are imported as Database Resources, stored procedures as Stored Procedure Resources. The tabs shown on this page depend on which wizard has been selected. If the wizard is invoked from the designer menu any number of items can be selected and imported; otherwise only one item can be selected.

5. Import Details Page

This page shows a summary of the selected items to be imported.

Import as Resource Name: when invoked from the Database Connection Editor, the resource name to be created/replaced can be edited by clicking in this field.

Tables Only: this checkbox determines how a Database Resource will be used:

• Checked: the resource can only be mapped to a table, read operations are expected to return multiple records, records are fetched and updated using FPL script commands fetchtable/updatetable or API methods Table.fetchtable/Table.updatetable().
• Not checked: resource fields are mapped to individual form fields, read operations are expected to return one record, FPL script commands fetch, update, insert, delete or API DatabaseResource methods fetch(), update(), insert(), delete() are used.

Click here for further details.

Columns for Where Clause: when Tables Only is checked, the columns to be added to the where clause of the Database Resource are shown here, and can be changed by clicking on the column names: a selection of candidate columns is displayed – this will include all columns identified as unique key columns.

Click Finish to import the selected items.