Getting Started with Database Builder

  • Starting Database Builder

  • The Main Elements

  • Selecting a Workspace


Starting Database Builder

There are several ways you may choose to launch Database Builder:

  1. Starting Database Builder from the Studio: Usually you will want to launch the Database Builder while you are developing applications inside the Visual DataFlex Studio. You can do this simply by clicking the Database Builder button on the Studio’s toolbar, or you can choose Database Builder from the Database menu.

  2. Starting Database Builder from the Windows Start Menu: Click the Start button on the Windows Task bar and select Programs. From the Programs menu select Visual DataFlex xx.x | Development Tools | Database Builder.

Database Builder will open using the current workspace. The current workspace is always the last-selected workspace. The current workspace may be changed using the Select Workspace dialog. The name of the current workspace is always displayed in Database Builder’s title bar.


The Main Elements

  • The Menu Bar

  • The Tool Bar

  • The Work Area

  • The Status Bar


The Menu Bar

Database Builder’s menu bar contains eight menus; File, Edit, Report, Maintenance, Filelist, Database, Window, and Help. To find out more about the items contained in each menu, click the items of interest in the menus shown below.

File Menu

The File menu generally contains items that create, open, save, and close tables.

Edit Menu

The Edit menu generally contains items for editing text.

Text Menu

The Text menu is used to perform operations in the code editor (methods page of the data dictionary).

Report Menu

The Report menu contains items that document the structure of your database, its rules (data-dictionary), and its correctness.

Maintenance Menu

The Maintenance menu contains items that perform maintenance on the data and indexes when using the embedded database. It also has options for creating empty Data Dictionaries and for copying rows from one database to another.

Filelist Menu

The Filelist menu contains items for maintaining the internal list of database tables for the current workspace. In Visual DataFlex, this is known as the file list

Database Menu

The Database menu contains items concerned with connecting to and using SQL databases such as Microsoft SQL Server, Pervasive.SQL or IBM DB2.

Workspace Menu

The Workspace menu contains items for configuring the database settings of the current workspace.

Tools Menu

The tools menu contains Database Builder configuration options and various options for launching external applications.

Window Menu

The Window menu contains items for navigating and arranging the display panels for each open table.

Help Menu

The Help menu contains items for getting help about Database Builder.


The Tool Bar

The Database Builder tool bar contains shortcuts to various common operations.

Table Selector

The table selector combo form displays all the tables in the file list of the current workspace. Selecting a table from this list will cause it to be opened.

New Table

The New Table button is used to start creating a new database table.

Open Table

The Open button pops up a dialog that allows you to open multiple tables simultaneously.

Open Table As

The Open As button is used to open a table that is not listed in the file list of the current workspace.

Save Table

The Save button saves all changes to the table being edited.

Delete Table

The Delete Table button is used to delete a selected table (or tables) from the database. Each table's data dictionary is also deleted.

Sort

If you are using the embedded database, the Sort button pops-up a dialog for rebuilding the indexes of your tables.

Set Attributes

The Set Attributes button pops-up a dialog for changing some common table attributes.

Undo

Undo will undo the last typed changes in the data dictionary's methods code editor.

Redo

Redo will redo the last undone changes in the data dictionary's methods code editor.

Undo All

The Undo all button will undo all changes made to the structure and dictionary of the current table since it was last opened in Database Builder.

Cut, Copy, Paste

Used for cutting, copying and pasting text to and from the clipboard.

Database Explorer

The Database Explorer button launches the Database Explorer tool for the currently selected open table. The table must be open in non-exclusive mode before Database Explorer can be launched.


The Work Area

The area below the menu bar and the tool bar is called the work area. Each dialog the Database Builder displays will be bound by this area. For example, when you open a table to change its structure or dictionary, a dialog is created and displayed in the work area.

Database Builder allows you to work on multiple tables simultaneously within the work area.


The Status Bar

The status bar is shown along the bottom of Database Builder and displays status information and user-input prompts about the current action.


Selecting a Workspace

The folders in which you develop your applications are organized into a structure called a workspace. A workspace is a set of Windows registry keys that identify the folders in which the project is to find its data tables, data-dictionary files, source-code files, bitmap files, help files, and your program files. Most Visual DataFlex development sites have multiple workspaces at any given time. Workspaces are an integral part of the Visual DataFlex Studio.

To select a workspace, choose Select Workspace… from the File menu. The Workspace Selector dialog pops up displaying a list of all available workspaces.

Choose the workspace you want to switch to by clicking its item in the list, then click the Select button. The dialog disappears and the name of the selected workspace is shown in the title bar.

Recently Used Workspaces

Database Builder maintains a recently used list of workspaces. To select a recent workspace from this list, choose Recent workspaces from the File menu. Now, select the workspace you wish to work in.

Special Note

Database Builder respects switches to another workspace made in other Visual DataFlex tools. When the workspace has been switched in, say, the Studio while Database Builder is active, Database Builder will detect the switch when it regains the focus. Database Builder will try to switch to the newly selected workspace automatically.

There are three possible results of this attempt:

  1. If there are no open tables in Database Builder, Database Builder will switch to the new workspace.

  2. If there are open tables, but none has been changed, Database Builder will show a warning that it will close all open tables to switch. After the user presses OK for this warning, Database Builder will close all open tables and switch to the new workspace.

  3. If there are open tables and one (or more) has changes, Database Builder will show an error message that the workspace has been changed but cannot switch to it because there are changes. After the user presses OK for this message, Database Builder opens in the workspace it was previously in (no switching occurs).

See Also

New Workspace

Workspace Properties


Building Tables and Data Dictionaries

  • Creating a New Table and Dictionary

  • Opening a Table Definition and Dictionary

  • Changing Table Name and Description

  • Creating Columns

  • Creating Indexes

  • Parameters

  • Data Dictionary Column Settings

  • Data Dictionary Methods

  • Data Dictionary Options

  • Data Dictionary Structures

  • Other Topics


Creating a New Table and Dictionary

To create a table and dictionary, select New from the File menu or click the New Table button in the tool bar. The New Table dialog is activated up and you are prompted to enter the following information:

Table Name

Enter the name of your new table. This name should conform to your database's table name rules and should be unique for the current database.

Advanced

Click the Advanced to edit more options for creating your new table. These options are normally defaulted to the database connection settings you have chosen for this database, however they may be customized for each new table that you create.

For more information, refer to Configuring a Default Database Connection.

OK

Click the OK button to proceed. Database Builder will create a new table and activate the Table Editing dialog to allow you to set up columns, relationships indexes and data dictionary settings for the new table.

Cancel

Click the Cancel button to close this dialog without creating a new table.

Advanced Options

Table Number

DataFlex’s file list uses a table number to address each table. Each data table in your application must have a unique table number within the supported range.

You are prompted with the lowest unused table number. You can accept the default or choose a different number. The table number determines the locking order for any group of tables that need to be locked. This ensures that tables are always locked in the same order.

Clicking the search icon on the right will increment the table number to the next highest unused table number.

File Name

If you are using the embedded database, the File Name refers to the directory name of the new table (excluding the file extension). Normally, the table name this file name will be the same. The File Name must conform to operating-system filename rules.

Enter the File Name of the table you are creating. In certain cases, you may wish to enter a drive letter and path (up to a maximum of 40 characters in total). If you do not enter a drive and path, then the workspace Data path will be used to specify the table's location. Normally, you will not specify a drive letter or path.

If you are not using the embedded database, then the filename refers to the name of the file that records a description of the table to the database driver. Special modifiers can be added to the filename to specify the type of driver. Normally, you will not edit this field as it is better to accept the defaults that are entered for you in this dialog. Refer to your database driver documentation for specific information.

Recnum Support

Check Recnum Support if you want the new table to use DataFlex's legacy recnum system for maintaining a unique row identifier.

If you are not using the embedded database, then you should only use this option when the table will be used in a legacy DataFlex application which requires recnum support in each table. In all other cases you will simply define a unique RowID using the primary key columns of your new table.

Embedded database tables must use Recnum support.

Database Type

This setting identifies the database system of the table. The default is determined by the default database connection settings for the current workspace. The combo list displays all installed database drivers. Choose one of the available database types.

Changing the Database Type will also the text in the File Name and Connection String fields as this information is automatically adjusted to conform to the selected database type.

To ensure that your desired database type is loaded for use in Database Builder, use the Configure Drivers option from the Tools menu.

To change the default database type, use the Configure Database Connection option from the Workspace menu.

Connection String

The Connection String is a set of property=value pairs that is used to establish a connection to your desired database. The particular set of property=value pairs is determined by the particular database that you are connecting to. Refer to your database driver documentation for specific details.

The Embedded database does not use connection strings.

If your Pervasive database is not configured as a secured PSQL database, then you will not use a connection string.

You may click the icon to the right of this field to activate a special dialog to help you to configure the connection string to your particular database. This is the recommended method of editing your connection string.

See Also

Configuring a Database Connection

Configuring Database Drivers

Pervasive.SQL Connection Properties

MSSQL Connection Properties

ODBC Connection Properties

DB2 Connection Properties


Pervasive.SQL Connection Properties

This dialog is used to help form a connection string to a secured Pervasive database.

Table

The table in the database to open.

Server Name

Enter the server name, or host, where the PSQL database is located.

User Name

Enter the user name to use for authentication when you log on to the PSQL database.

Password

Enter the password for your user name.

Database Name

Enter the name of the database that needs to be accessed.

Test Connection

Click the Test Connection button to test the connection properties that you have entered. Database Builder will attempt to connect to the database using these settings. If a connection is established, then a 'success' message will be displayed. If the connection fails, then an error is displayed showing the full connection string that was used.

Make Default

Click the Make Default button to make the connection properties in this dialog the new default connection settings for the current workspace.

OK

Click OK to use the entered connection properties to form the connection string for the new table that you are creating.

Cancel

Click Cancel to close this dialog without using the entered connection properties.

Note

For more information about Pervasive connection properties refer to the Pervasive SQL Secured Database document.

See Also

Configuring a Database Connection

Configuring Database Drivers

Creating a New Table and Dictionary


MSSQL Connection Properties

This dialog is used to help form a connection string to a MSSQL database.

Server Name

Select a server name from the drop-down list, or type the location of the server where the MSSQL database you want to access is located.

User Name

Enter the user name to use for authentication when you log on to the MSSQL database.

Password

Enter the password for your user name.

Use NT Authentication

Click Use NT Authentication to specify that the provider request a secure (or trusted) connection to a the MSSQL Server. When selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The database administrator must have associated your Windows network ID with a SQL Server login ID.

Database Name

Enter the name of the database you are connecting to. Use the combo list to select from a list of databases belonging to the specified server. Note: You must have entered a valid server name, user name and password so that the server can be queried to provide this list.

Test Connection

Click the Test Connection button to test the connection properties that you have entered. Database Builder will attempt to connect to the database using these settings. If a connection is established, then a 'success' message will be displayed. If the connection fails, then an error is displayed showing the full connection string that was used.

Make Default

Click the Make Default button to make the connection settings in this dialog the new default connection settings for the current workspace.

OK

Click OK to use the entered connection properties to form the connection string for the new table that you are creating.

Cancel

Click Cancel to close this dialog without using the entered connection properties.

Note

For more information about MSSQL connection properties refer to the MSSQL Users Guide.

See Also

Configuring a Database Connection

Configuring Database Drivers

Creating a New Table and Dictionary


ODBC Connection Properties

This dialog is used to help form a connection string to a database via ODBC.

Data Source

Enter the data source names (DSN) of the data source you are connecting to. The combo list displays all the available data sources on your machine according to the filters specified below

Show User Data Sources

Filters the data source combo list to data sources that are local to a computer and accessible only by the current user.

Show System Data Sources

Filters the data source combo list to system data sources.

Select File DSN

Activates the open file dialog to select a data source that is specified inside a file (.dsn).

Login ID

Enter the user name to use for authentication when you log on to the database.

Password

Enter the password for your user name.

ODBC Administrator

Click the ODBC Administrator button to launch the ODBC Administrator application.

Test Connection

Click the Test Connection button to test the connection properties that you have entered. Database Builder will attempt to connect to the database using these settings. If a connection is established, then a 'success' message will be displayed. If the connection fails, then an error is displayed showing the full connection string that was used.

Make Default

Click the Make Default button to make the connection settings in this dialog the new default connection settings for the current workspace.

OK

Click OK to use the entered connection properties to form the connection string for the new table that you are creating.

Cancel

Click Cancel to close this dialog without using the entered connection properties.

Note

For more information about ODBC connection properties refer to the ODBC Users Guide.

See Also

Configuring a Database Connection

Configuring Database Drivers

Creating a New Table and Dictionary


DB2 Connection Properties

This dialog is used to help form a connection string to a DB2 database.

Data Source

Enter the data source names (DSN) of the DB2 data source you are connecting to. The combo list displays all the available DB2 data sources.

Login ID

Enter the login id to use for authentication when you log on to the DB2 database.

Password

Enter the password for your user name.

Test Connection

Click the Test Connection button to test the connection properties that you have entered. Database Builder will attempt to connect to the database using these settings. If a connection is established, then a 'success' message will be displayed. If the connection fails, then an error is displayed showing the full connection string that was used.

Make Default

Click the Make Default button to make the connection settings in this dialog the new default connection settings for the current workspace.

OK

Click OK to use the entered connection properties to form the connection string for the new table that you are creating.

Cancel

Click Cancel to close this dialog without using the entered connection properties.

Note

For more information about DB2 connection properties refer to the DB2 Users Guide.

See Also

Configuring a Database Connection

Configuring Database Drivers

Creating a New Table and Dictionary


Opening a Table and Dictionary

Database Builder provides several different ways for you to open a table and its dictionary. You can quickly select a table from the file list, you can select multiple tables to be opened simultaneously, and you can open a table that is not registered on the current workspace’s file list. The following pages discuss each method in further detail.

See Also

Using the File List

Using the Recent Tables List

Opening Multiple Tables

Opening Multiple Related Tables

Opening Tables by Directory Name

Open Modes


Using the File List

To open a single table that is registered in the current workspace's file list, click the Filelist combo form in the tool bar. Now, select the table you wish to be opened.

Note

The table will be opened according to the Default Open Mode setting in the Database Builder properties.

See Also

Opening a Table and Dictionary

Using the Recent Tables List

Opening Multiple Tables

Opening Multiple Related Tables

Opening Tables by Directory Name

Open Modes


Using the Recent Tables List

Database Builder maintains a list of recently opened tables for each workspace.

To open a recent table from this list, choose Recent tables from the File menu. Now, select the table you wish to be opened.

Note

The table will be opened according to the Default Open Mode setting in the Database Builder properties.

See Also

Opening a Table and Dictionary

Using the File List

Opening Multiple Tables

Opening Multiple Related Tables

Opening Tables by Directory Name

Open Modes


Opening Multiple Tables

To open one or more tables from the file list, choose Open… from the File menu or click the Open Tables button on the toolbar. This will activate the Select Tables to Open dialog, which allows you to select and open tables in various ways.

Click the checkbox of each table in the list that you wish to open. The list may be sorted in any order by clicking the appropriate column title. You may also use some of the table selection buttons (described below) for marking tables to be opened.

The following describes the function of each of the buttons to the right of the list of tables.

Open

Clicking this button or pressing F2 opens the selected tables in the non-exclusive (shareable) mode. In this mode, you can view the column and index structures but you cannot change them; however, you can make changes to the data-dictionary settings.

Once the table is open, you can switch between non-exclusive and exclusive mode by simply clicking a button.

Open Exclusive

Clicking this button opens the selected tables in the exclusive mode. In this mode, you can edit the column and index structures as well as change the data-dictionary settings. However, while the table is open in exclusive mode, no other application or user can access that table.

If you try to open a table in exclusive mode that is already open in some other application, then the non-exclusive (shareable) mode will be used instead. Once you have the table open, you can switch between non-exclusive and exclusive mode by simply clicking a button. Switching to exclusive mode will continue to be affected by whether another program has opened it.

Cancel

Clicking this button closes this panel and opens no tables, regardless of whether any are selected.

Select All

Clicking this button selects every table in the list. This is often used as a preliminary to further selection or de-selection of tables.

Invert Selection

Clicking this button deselects all tables that were selected, and selects all tables that were not selected.

Select None

Clicking this button clears any selection of tables that may currently exist.

Tag Bad Entries

Clicking this button selects all tables that are registered in the file list for which DataFlex cannot find the physical files. This can come about from the files’ having been deleted or moved. All other selected tables, if any, are deselected.

Tag Child Tables

Clicking this button selects all child tables that relate to the table whose name the cursor is on. All other selected tables, if any, are deselected.

Tag Parent Tables

Clicking this button selects all parent tables to which the table whose name the cursor is on relates. All other selected tables, if any, are deselected.

Create ASCII File

Clicking this button makes it possible to output Filelist information to an ASCII file (table number, file name, display name, and DataFlex name).

When you press this button, there is a further dialog for specifying options such as line-delimited or comma-delimited format and checkboxes for selecting the information to be output.

See Also

Opening a Table and Dictionary

Using the File List

Using the Recent Tables List

Opening Multiple Related Tables

Opening Tables by Directory Name

Open Modes


Opening Multiple Related Tables

To open one or more tables and all of their related parents, choose Open related… from the File menu. This will activate the Select Files to Open Recursively dialog, which enables you to select and open tables in various ways.

Click the checkbox of each table in the list that you wish to open recursively.

Refer to Opening Multiple Tables for more information.

See Also

Opening a Table and Dictionary

Using the File List

Using the Recent Tables List

Opening Multiple Tables

Opening Tables by Directory Name

Open Modes


Opening Files by Directory Name

Opening Tables by Directory Name

To open a table that is not registered in the file list of the current workspace, select Open as… from the File menu or click the Open As… button on the toolbar. This activates the Open As dialog and you are prompted to enter the following information:

Table Number

Enter a table number that is not being used by any other table. The table number you assign will not be added to the file list when you exit, but will appear in the file list while you have the table open. You are prompted with the lowest unused table number.

The table number you choose must be within the supported range.

Table Name

Enter the drive, path and file name of the table you are opening.

Example

Table Name = C:\Projects\Order\Data\Codemast

In this example, we are attempting to open the Codemast table in the \Projects\Order\Data folder on drive C:

The prompt button in the File Name form will allow you to browse your folders to select a table to be opened. The selected table’s drive, path and file name will be entered.

Note

SQL database drivers may require that you enter additional information in the File Name form.

Once you have entered the above information, click on the OK button to open the table in the non-exclusive mode, or click the Open Exclusive button to open the table in the exclusive mode.

See Also

Connecting to SQL Databases

Opening a Table and Dictionary

Using the File List

Using the Recent Tables List

Opening Multiple Tables

Opening Multiple Related Tables

Open Modes


Open Modes

Database Builder supports opening tables in two different modes:

  • Non-Exclusive: You will be able to change the data dictionary but you will not be able to change the table structure or indexes. Other programs and users can read and write to the table while you have it open in this mode.

  • Exclusive: You will be able to change the table structure and indexes as well as the data-dictionary. While you have the table definition open in this mode, no other program or user will be able to access the table.

When you open a table in Database Builder, it will use the non-exclusive mode unless you explicitly specify that it should be opened exclusive. The reason for this is to allow the table to be shared by other applications and users while you are inspecting it. It is expected that most of the time you will be opening a table to check its structure or change the data-dictionary. You can do both of these things in non-exclusive mode without first having to exit any other applications that also have the table open. This means that you can concurrently use the table in the Visual DataFlex Studio and edit its data dictionary with Database Builder.

Editing the structure or indexes of a table requires that it is opened in exclusive mode. Before opening a table in exclusive mode, you must ensure that no other application has the table opened at the same time. Once the table is opened in exclusive mode, then any other DataFlex program that tries to open it will receive a Can’t Open .DAT file error.

It is recommended that unless you are editing the structure of a table or its index, the table should remain in default (non-exclusive) mode. This is especially important in a team development environment.

Switching Open Modes

Once a table has been opened, you can toggle its mode. There is a special button on the table-definition dialog for this purpose (see below).

Database Builder will always prompt you to save any pending changes before it switches the mode of the table.

If you try to switch a table to exclusive mode that is currently open in some other application, then you will receive a warning and the open will revert to the non-exclusive mode.

See Also

Opening a Table and Dictionary

Using the File List

Using the Recent Tables List

Opening Multiple Tables

Opening Multiple Related Tables

Opening Tables by Directory Name


Changing Table Name and Description

Each table displays its name and a description in the top (header) section of the table definition dialog.

The following information is displayed:

Table Name

The table name can be up to eight characters long and must start with a letter. The table name is used to reference the table when writing source code or when setting certain properties in the Studio.

Description

The description is displayed in table-selection menus by DataFlex tools. Enter a good, descriptive name which would be meaningful, even to occasional operators. You may use upper and lower case as you wish, and up to 32 characters.

Open-Mode Button

This button is used to switch between open modes. The graphic is displayed on the button in a different color depending on the current open mode.

The table is currently in exclusive mode and will be switched to non-exclusive (shared) mode on clicking the button.

The table is currently in non-exclusive (shared) mode and will be switched to exclusive mode on clicking the button.

See Also

Alias Tables

Open Modes

Maintaining File Lists


Creating Columns

The Columns tab page of the table definition dialog displays a list of the table’s columns. All of the information pertaining to the columns’ structure can be viewed and edited from this tab page. In addition, you can create new columns, delete existing columns, save a column as a template or load a column template to create a new column.

You must have the table open in exclusive mode to change the column structure. See "Opening a Table and Dictionary " and "Switching Open Modes " for more information.

Column Data

The following information is maintained for each column in the column list:

Number

This displays the column's number. This number determines the order that the columns are positioned in the table. You cannot change the column number.

If you are editing an SQL table then primary key columns will be indicated here with the primary key icon.

Name

This is the column name. For embedded database tables, the maximum length is 15 characters. Valid characters in column names are: 'A...Z', '0...9', '@', '_', and '#'. The first character must be alphabetic (A...Z). Invalid characters will be replaced by underscores (_). If a database driver other than Embedded is loaded, these specifications and behaviors may be different.

Type

The type you select defines what sort of data will be allowed in the column: ASCII, Numeric, Date, Overlap, Text, or Binary. To select a column type, press one of the initial letters (A, N, D, O, T or B). If you prefer, you can click the combo button to see the list of allowable column types.

Type

Description

ASCII

ASCII columns may contain any characters and are used when numeric formatting will not be required.

Numeric

Numeric columns may contain only the numbers 0 through 9, a decimal separator, and a plus or minus sign.

Date

Dates are input and output as they appear (month/day/year, etc.), but in some database systems, such as the embedded database, they are stored as Julian numbers representing the number of days since the first day of Year 1.

Overlap

Note: Overlap columns are provided to support legacy applications. It is recommended that you do not use overlap-type columns in your database.

Logical columns defined in terms of other physical columns, which must be contiguous. They do not take up space in the table, as they are "virtual" columns.

For Overlap columns, you must specify the start and end position of the column from the Length column.

Text

Text columns (often called "memo" columns) are for storing large amounts of text in ASCII format.

Note that for the Embedded database driver, the usable size of the column will be two bytes less than the declared length. Thus, if you declare a text column with a size of 256 bytes, you will be able to store up to 254 characters in this column. The amount of space occupied on the disk is the actual length of the contents, if any, plus 2 bytes, unless compression is not turned on. In that event, the amount of space occupied will be the declared length of the column, regardless of whether the column has any content.

Binary

Binary columns are used to store any characters, but in a binary format. Binary columns are used primarily for storing graphics as bit images. As with text columns, in the embedded database the usable size of a binary column is two bytes less than the declared length.

Length

If you are defining new columns, you must enter a length for each one in accordance with the guidelines below. Zero-length columns are permitted, mostly for purposes of compatibility with legacy applications.

Type

Length

ASCII

For embedded database ASCII columns, enter a number between 0 and 255. Other database types may permit or require different entries.

Numeric

For embedded database Numeric columns, enter two values separated by a decimal separator character. For example "6.2".

The first value must be greater than 0 less than 15. This defines the number of digits stored to the left of the decimal separator for the numeric column.

The second value must be greater or equal to 0 and less than 9. This defines the number of digits stored to the right of the decimal separator for the numeric column.

The number of bytes required to store the column data (shown in the offset status information) is half the total number of digits specified.

Only even digits are allowed, so, for example, 1 will be changed to 2 and 3 will be changed to 4. This is because the embedded database stores numbers in the BCD (Binary Coded Decimal) format. Other database types may permit or require different entries.

The decimal separator used to divide these two numbers is defined in the International settings of the Windows control panel.

Date

For embedded database Date columns, you may not make an entry. Dates are always stored as three-byte numbers. This will be configured for you automatically. Other database types may permit or require different entries.

Overlap

For Overlap columns, press F4 (or click the Prompt button) when the selection cursor is in the Length column to activate a dialog into which you may enter the start position and end position of the overlap column.

Text

For embedded database Text columns, enter a number between 0 and 16384. If the number entered is not divisible by 16, it will be increased to the next multiple of 16. Other database types may permit or require different entries.

Binary

For embedded database Binary columns, enter a number between 0 and 16384. If the number entered is not divisible by 16, it will be increased to the next multiple of 16. Other database types may permit or require different entries.

Index

When a column appears in more than one index, only one of those indexes can be used when finding from that column. This is identified as the column's Main Index.

In Database Builder, the main index for a column defaults to the first index that column appears in, and that index's number is shown here.

If you want to change to a different main index, press F4 (or click the prompt button) and select that index from the pop-up dialog.

SQL Databases

If you are editing an SQL database then extra information is maintained for each column in the list. The actual information displayed varies depending on the type of database you are maintaining and the revision number of the DataFlex driver for that database:

Null

Check this option if the column allows null values.

Read only

This selects the column's read only status: Select the desired status from the combo list that accompanies this option. The default status is 'No'.

Default

Enter a default value for the column. This will be the value committed to the column value when no value is present.

Column Templates

Database Builder supports the concept of column templates. A column template is a record of the structure and data-dictionary rules for a single column.

Each template is stored in a template file (.FLT) in your workspace's data-dictionary source path (this is the same folder that you data-dictionary (.DD) source code is stored).

The templates store information about the column's name, type, length, index, options, validation rules, lookup objects, mask, appearance and other data dictionary attributes.

Columns Toolbar

The Columns tab contains a toolbar which allows you to perform common column creation operations.

Add Column

Click the Add Column button to begin creating a new column at the bottom of the column list.

Delete Column

Click the Delete Column button to delete the currently highlighted column from the table's definition.

Keyboard shortcut: Shift+F2

Insert Column

Click the Insert Column button to insert a new column above the currently highlighted column.

Keyboard shortcut: Shift+F10

Insert Template

Click the Insert Template button to create a new column using a template. You should highlight the column will follow the new column that you are creating. Or move the cursor below the last column to add the new column to the end of the list.

Save as Template

Click Save as Template to save a column as a template. You should highlight the column that you wish to save.

You must assign a name to the template. The default name of the template file is the same as the column name. Template file names must be unique within the current workspace.

Column Statistics

Column statistics are displayed for the currently highlighted column in the Table Definition dialog's status bar. The information shown is displayed in the following format:

Column <column-number> of <total-columns>, offset <start> of <row-size>

Where:

  • <column-number> is the column number of the highlighted column.

  • <total-columns> is the total number of columns defined for the table.

  • <start> is the start byte offset for the highlighted column.

  • <row-size> is the total number of bytes for all columns defined for the table.

See Also

Define Overlap Column


Define Overlap Column

Columns (other than Overlap) in the embedded database tables occupy contiguous lengths of bytes. Overlap columns can overlap these columns starting and ending at any byte position.

Start Offset

Start offset is the byte-offset marking the first byte of the overlap column. This is counted from the beginning position of the table's first column.

End Offset

End offset is the byte-offsets marking the last byte of the overlap column. This is counted from the beginning position of the table's first column.

If you have already entered a length for the column, the defaults will be 1 for the start offset and 1 plus the length for the end offset. If you had previously entered offsets for the column, the default start position will remain as before, and the default end position will be calculated to yield the length implied by your entries.

You may not specify an end offset that is less than the start offset.

Start Column

You can calculate the start offset by selecting a column in the Start Column form. When you select a start column, its start offset is placed into the Start Offset for the overlap.

End Column

You can calculate the end offset by selecting a column in the End Column form. When you select an end column, its end offset is placed into the End Offset for the overlap.

OK

Click OK to apply the entered offset definition.

Cancel

Click Cancel to close this dialog without applying the entered offset definition.

Note

Overlap columns are provided to support legacy applications. It is recommended that you do not use overlap-type columns in your database. SQL databases may not support overlap columns.

See Also

Creating Columns


Creating Relationships

The Relationships tab page of the File Definition dialog displays the table’s relationships to parent tables.

You must open a table in exclusive mode before you can make any changes to the relationship structure. See "Opening a Table" and "Switching Open Modes" for more information.

This tab page is divided into two panels:

  1. Relationships Overview (left panel): This is a treeview structure that displays all current relationships from the table to its parents.

  2. Relationship Details (right panel): This panel displays details of the currently selected relationship in the overview panel.

A splitter between these two panels allows you to adjust the relative widths of each panel.

Relationships Overview

This treeview displays all the relationships from the current table. Below is a guide to the icon symbols used to represent each relationship.

This node is used to indicate a relationship. To the right of this symbol is the name of the parent table being related to. The list of foreign key to primary key columns that participate in the relationship are listed as child nodes. You can expand or collapse the relationship details.

This node is used to indicate a foreign key/primary key column pair. Each relationship is composed of one or more of these column pairs.

Relationship Details

To view the details of a relationship first you should select it in the Relationships Overview. The relationship details panel will display the following information:

From Table

This form displays the name of the table you are editing. This is the child table in the displayed relationship.

To Table

This form displays the parent table of the relationship. You cannot change this value once a relationship has been established. However, you can delete the entire relationship.

If you are creating a new relationship, then the combo list can be used to select a parent table from the list of tables in the database.

From Column/To Column

This is a list of child table foreign key to parent table primary key column pairs. These pairs define the columns that participate in the relationship.

Each foreign key/primary key column pair must have matching column type and length. See Creating Columns.

The following operations can be performed in this list:

  • You can change the 'from column' by selecting a column from the combo list. If a 'to column' has already been selected for this row, then the 'from columns' that are displayed in the list are filtered to columns that match the type and length of the 'to column'.

  • You can change the to column by selecting a column from the combo list. If a 'from column' has already been selected for this row, then the to columns' that are displayed in the list are filtered to columns that match the type and length of the 'from column'.

  • You can add a new column pair to the relationship. To add a new column pair, click the 'Add Column Pair' button on the toolbar or simply go to the bottom row of the list and begin selecting columns.

  • You can remove a column pair from the relationship. To remove a column pair, highlight the column pair in the list and click the 'Delete Column Pair' button on the toolbar.

Attempt to Synchronize Required Parent & Child Structures

Check this item if you want database builder to synchronize the data dictionary parent/child structures that are affected by these relationships. See Data Dictionary Structures Wizard for more information.

Relationships Toolbar

The relationships tab contains a toolbar which allows you to perform common relationship maintenance operations.

Relationship Wizard

Click this button to activate the Relationships Wizard. This wizard is the fastest and most convenient way to define a new relationship.

For more information see Relationship Wizard.

Add Relationship

Click this button to start creating a new relationship manually (i.e. without using the Relationship Wizard).

Delete Relationship

Click this button to delete the currently selected relationship.

Add Column Pair

Click this button to add a new child foreign key/parent primary key column pair to the currently selected relationship.

Delete Column Pair

Click this button to delete the currently selected child foreign key/parent primary key column pair from the currently selected relationship.

See Also

Data Dictionary Structures Wizard

Relationship Wizard


Data Dictionary Structures Wizard

Database builder includes a "Structures Wizard". This wizard performs the following data dictionary operations in the background while you are creating or editing relationships:

  • When a new relationship is added, the parent table is added to the child's 'Required Parents' structure and the child is added to the parent's 'Required Children' structure.

  • When a relationship is deleted, the parent table is removed from the child's 'Required Parents' structure and the child is removed from the parent's 'Required Children' structure.

  • When a relationship from a table is changed from one table to another, all the necessary adjustments to all three tables in the Child and Parent structures are correctly made.

The wizard also correctly accounts for multiple child columns relating to the same parent, and child or parent lists that already contain the relationship information.

There may be circumstances where you do not wish the Required Parent and Child table structures to be updated as a result of a relationship change, so you can enable or disable the Structures Wizard via the "Attempt to synchronize required child & required parent structures" checkbox.

Restrictions

The Structures Wizard is useful for maintaining required parent/required child structures but there are circumstances that will still cause the structures to get out of sync with the table relationships. This is normal and expected. It is not a bug, and in some cases the developer will want these structures to differ.

Under the following circumstances the Wizard cannot perform proper synchronization:

  • If a relationship is made to a table that is opened exclusively by another application.

  • If the parent table does not yet have a data dictionary.

  • If the parent table is open while the wizard operates, and the user elects not to save the changes that the wizard made to the parent table.

Run the Check Relationships report to verify whether the Relationship Structure matches the Required Child and Requires Parent structures in the data dictionaries.

See Also

Creating Relationships

Relationship Wizard


Relationship Wizard

The Relationship Wizard provides the most effective means for creating new relationships. To use this wizard:

  1. Open the table that will be the child table (from table) in the new relationship. You must open the table in exclusive mode to edit relationships. See Open Modes for more information.

  2. Go to the Relationships Tab page of the table definition dialog.

  3. Click the Relationship Wizard toolbar button. This activates the Relationship Wizard dialog.

  4. Follow the wizard prompts and enter the following information:

Parent Table

On the 'Parent Table' wizard page, select the parent table (to table) for the relationship. The wizard displays a list of tables in the database. You can order the list by table number, table name, or description by clicking on the appropriate column heading.

Parent's Primary Key

On the 'Parent Primary Key' wizard page, select the primary key index from the parent table. This will define the primary key columns from the parent table that will be used in the relationship.

Foreign Key Matching Options

Next you must match the parent table's primary key columns with foreign key columns in the child table. There are three different options you can use to match these columns. Tese are described below:

Select Columns that Match Primary Key Size & Type

If you check this option, then you will be prompted to select columns from the child table whose size and data type match the columns from the parent's primary key.

Select Columns, Then Force a Match

If you check this option, then you will select columns from the child table to pair with the parent's primary key columns. If the child table's columns are not the same type and length, then the wizard will make the necessary adjustments to these columns to ensure that the type and length do match.

Warning: This could result in a loss of data from the child table's columns that are changed. This will depend on the type of changes that the wizard makes. For example, if the wizard needs to reduce the size of an ASCII column, then some values in this column could be truncated. If a data column needs to be changed into a number column, then the data in that column may no longer be valid, etc.

Create New Columns That Match the Primary Key

If you check this option, the wizard will create and use new column(s) in the child table that match the signature of the parent's primary key. The wizard will try to assign the same names to the new columns that were used in the primary key columns. If these names are not unique in the child table, then the wizard will generate unique column names.

This option is the most productive way of defining a relationship and foreign key columns in one single process. The wizard ensures that column names are consistent in parent and child tables and the relationship's column pairs will match size and type correctly.

Finish

When you have correctly entered all the wizard values the final page will display a summary of the new relationship that will be created. Click the Finish button to build this relationship and return to the Relationships tab of the Table Definition dialog. Your new relationship will be displayed in the Relationships Summary panel.

See Also

Creating Relationships


Creating Indexes

The Index tab page of the File Definition dialog displays the table’s indexes (sort orders).

You must open a table in exclusive mode before you can make any changes to the indexes. See "Opening a Table" and "Switching Open Modes" for more information.

This tab page is divided into two panels:

  1. Indexes Overview (left panel): This is a treeview structure that displays all indexes currently defined for the table.

  2. Index Details (right panel): This panel displays details of the currently selected index in the overview panel.

A splitter between these two panels allows you to adjust the relative widths of each panel.

Indexes Overview

This treeview displays all the indexes from the current table. Below is a guide to the icon symbols used to represent each index.

This node is used to indicate an index. To the right of this symbol is the index number. If you are editing an SQL table, then the index name may also be displayed. The list of index segment columns are listed as child nodes. You can expand or collapse the index details.

This node is used to indicate an index segment column. Each index is composed of one or more of these columns.

Index Details

To view the details of an index first you should select it in the Indexes Overview. The Index Details panel will display the following information:

Index Number

The index number is used in you VDF source code to refer to the index you want to use when you are finding or sorting rows.

You can change the index's number by typing the new index number then tabbing out of this field. You must enter an unused index number. After changing the index number, you will see the index definition move to the appropriate position in the Indexes Overview treeview.

Warning: Changing an index's number may affect your applications' ability to find or sort rows from this table. If your application uses hard-coded index numbers, then these references may need to be adjusted.

Key Length / Levels

The aggregate length of the index (Key Length) and the number of index levels needed by the ISAM process (Index Levels) are calculated as you enter and edit segments for an index. You cannot edit these parts of the display—they are there for your reference only.

Generally, the larger the number of index levels needed by an index, the slower the performance of add, delete, edit operations on the table.

For embedded database tables, the total length of all the segments used in an index cannot exceed 256 bytes. Other database types may support different parameters.

Batch Index

By default each index you create will be maintained online. Online indexes are updated every time a change is made to any of the columns which make up the index in any row in the table.

The Embedded database driver supports the concept of Batch indexes. Batch indexes are not updated during data entry. They are updated only when specifically processed with Reindex. Batch indexes are for situations where an index does not have to be constantly up-to-date, for example when it is used only for occasional or periodic reports.

If you wish a newly created index to have batch status, you must set it to that status by checking the Batch box.

If you wish to change a Batch index to online, Database Builder automatically presents the option of sorting the data to ensure that the index is current.

Primary Index

Some database drivers support the specification of a Primary Index (the Embedded driver does not). Primary indexes are used when performing key column searches.

Check the Index is the Primary Index box to make the current index the primary index.

Other Index Settings

If you are editing an SQL database, then there may be other index settings displayed in this panel. Refer to your database driver user's guide for more information.

Segments

This is a list of index segment columns. These columns define the sort order for the currently highlighted index.

The ordering of the columns is important. The data is sorted by the first column, then second column, third column etc. i.e. all rows with the same value in the first index segment column, will then be sorted by the value of the second index segment column, then third, etc.

The following operations can be performed in this list:

  • You can change the column name for the currently highlighted segment. To do this, you can drop down the combo list and select the table column you would like to use in this segment. Or, you can simply enter the column name. As you type the column name, Database Builder will select the first column that matches the name that you have typed so far.

  • You can add a new segment to the index. To add a new segment, click the 'Add Segment' button on the toolbar or simply go to the bottom row of the list and begin selecting columns.

  • You can remove a segment from the index. To remove a segment, highlight the segment in the list and click the 'Delete Segment' button on the toolbar.

  • You can adjust the position of a segment in the list. To move a segment up or down, highlight the segment that you want to move, then click the 'Promote Segment' or 'Demote Segment' toolbar buttons. Not: changing the position of an index segment affects the ordering of rows for that index.

A given column can be used in more than one index, but for each column, there can be only one Main Index. See the discussion under "Main Index".

Ignore Case

This option can be checked for ASCII index segment columns only. If you check Ignore Case all letters ('a'-'z') in column values will be treated in the index as being capital letters.

For example: If one column contains the data "Smith, Robert", it will be the exact equivalent (for indexing purposes only) of a column containing "SMITH, ROBERT". This eliminates the sort of confusion users experience when capitalization of letters in the data affects the order in which rows are listed, or found.

Descending

If you check descending, the segment will rank from high to low, rather than the default low-to-high. For example: the ASCII value "Pears" would rank before "Apples".

This is useful, for example where you want to list customers in a customer table with the largest sales value first. Here you would check descending for the Sales_Value segment of the index.

Index Toolbar

The Index tab contains a toolbar which allows you to perform common index maintenance operations.

Add Index

Click this button to create a new index for the table. Embedded database tables may define up to 16 indexes. Other database types may support more indexes.

Delete Index

Click this button to delete the currently highlighted index in the Indexes Overview panel.

Add Segment

Click this button to add a new segment to the currently highlighted index's segment list. Embedded database tables support up to 16 segments per index. Other database types may support more or less segments.

Delete Segment

Click this button to delete the currently highlighted segment from the segment list of the current index.

Promote Segment

Click this button to move the currently highlighted segment up in the index's segment list.

Demote Segment

Click this button to move the currently highlighted segment down in the index's segment list.

Unique Indexes

Each time a row is saved in a table, DataFlex updates the online indexes with information from the new row. If any of the index values matches that of any existing row in the table, then the save will not be permitted. For example: If you have a Customer table and Index #1 for this table is the Customer_Number column, then DataFlex will not allow two rows to be saved with the same customer number.

In the case of the customer number, this is what you would want, but what if you also have Index #2 that sorts the data by Customer_City? Does this mean that you cannot store two rows with the same city? The answer is you must by add another segment to the Customer_City index so that the combined segments are unique to their row. For example, you could add the Customer_Number column to the Customer_City index. This guarantees that Index #2 is unique for each row regardless of matching city names, because Customer_Number is always unique.


Parameters

The Parameters tab page of the table definition dialog displays various parameters that can be used to fine-tune the performance and behaviors of a table.

You must open a table in exclusive mode before you can make any changes to the parameters. See "Opening a Table" and "Switching Open Modes" for more information.

The following information is maintained in the Parameters tab page:

Table Statistics

Max Records

The value you enter here is the maximum number of rows to be stored in the database for this table. A default of 10,000 will assigned for new tables. The maximum number of rows that can be in a embedded database table is 16.7 million, disk space permitting.

DataFlex uses the Max Rows parameter for organizing its indexes efficiently. Lower Max Rows value will mean faster index performance, and vice versa.

You should set a value for each table that is sufficiently large to accommodate the expected growth of rows in the table. You should never allow the number of rows in a table to exceed the Max Rows; this can result in corruption of one or more indexes.

Each time you save a change to the Max Rows parameter, Database Builder must rebuild the table’s indexes, and will do so automatically.

When you set Max Rows to 1, Visual DataFlex will treat the table as a system table and automatically place a check in the System File checkbox (see below).

Related Topics: Percentage Filled Report

Record Length

Record Length is the number of bytes between the beginning of one row to the beginning of the next row. If you specify a row length too short to accommodate the total length of the columns in the table definition, your choice will be overridden automatically in favor of the smallest row length sufficient to accommodate all the columns.

If you want a particular row length, you may enter it. The default length for embedded database tables is 8 bytes, so this is most-often used for shorter row lengths. If you do not know the row length, the row length will automatically be increased for you and you will see a message indicating that this has happened. Once a row length has been increased automatically, it will not be decreased automatically if you delete a column or reduce its size, but you can do this manually if you wish.

The Embedded database driver can set row lengths to any length from 8 to 26 bytes, 28, 30, 32, 34, 36, 39, 42, 46, 51, 56, 64, 73, 85, 102, 128, 170, 256, and upward in multiples of 128 bytes, to a maximum of 16 kilobytes. These values may change for other database drivers.

Records Used

This statistic, which cannot be changed here, reports the actual number of rows in the table.

RecLen Used

This statistic, which cannot be changed here, reports the total length, in bytes, of the columns in this table. This can be used as a comparison with the actual row length to gauge the amount of unused space in each row.

Table Revision

This value, which you cannot change here, is the DataFlex revision number of the database system under which the table was created. This parameter is not available for all database systems, but the Embedded database driver supports it.

VDF supports 3 Embedded database revisions, 2.3b, 3.0 and 4.0 format. 3.0 format was introduced with DataFlex 3.0 in the early 1990’s. It added capabilities to the Embedded database like more indexes, text and binary column support.

Once a table has been converted from 2.3 format to 3.0 format it cannot be used from a 2.3 runtime anymore. 4.0 format was introduced with Visual DataFlex 7 and DataFlex 3.2 for tables with relationships to other tables with a filelist number above 250.

At the same time as the 4.0 format was introduced, the DataFlex filelist was expanded to support 4095 tables, as opposed to the 255 tables of the older revisions.

When a new table is created in Visual DataFlex, it is created as 3.0 format, unless it contains relationships to tables above 250, then it will be created as 4.0 format.

Table Settings

Multi-User Reread

If rows of the table may be edited by more than one user concurrently, you should make sure there is a check in the Multi-User Reread checkbox. In multi-tasking environments, this box should always be checked, as DataFlex regards concurrent tasks as concurrent users.

This entry affects the Lock Type column of definition tables. When Multi-User Reread is checked, the Lock Type will become set to FILE, while an empty checkbox makes the Lock Type value NONE. Additional Lock Type values are supported for SQL database tables.

Reuse Deleted Space

For most applications, you will want DataFlex to reassign the disk space released when a row is deleted from a table. However, in applications where you want to use the row number as an audit trail or as a transaction number, this would not be desirable. To allow the reuse of deleted space, make sure there is a check in this checkbox; otherwise, clear the checkbox.

Header-Integrity Checking

Embedded database tables store structural information about their columns, indexes, and other information in special portion of the table called the header. It is possible for a header to become corrupted, either by an operating system crash or a program that illegally writes to the header.

If a table's header-integrity checking is turned on, the DataFlex runtime and tools will prohibit access to the table whenever its header is corrupted. Each time the table is saved, information is written to an .HDR file (in the same directory as the .DAT file) which enables header inspection, and repair if it becomes necessary. If header-integrity checking is turned off, the table is not checked and no .HDR file is written.

For new tables, the default for header-integrity checking is on (a check in the checkbox) so if you want it turned off, clear the checkbox. For existing tables, header-integrity checking is off until you turn it on.

Turning on header-integrity-checking causes only a very slight performance impact when the table is first opened. If header-integrity checking is on, then repair of corrupted tables can be done in Database Builder.

Related Topics: Cleanup

System Table

A system table is a table that contains only one row and also has its Max Rows parameter set to one. DataFlex will automatically find and load this row upon opening the table.

Place a check in this checkbox to set this table as a system table. If the database driver supports the Max Records setting, then this will automatically be forced to 1 (see above).

Compression

For the embedded database, data compression can be used to reduce the disk space that is occupied by your tables. You can define a table as having no compression, or as having fast, standard, or custom compression. The default is no compression.

Compression is effective if your tables contain large Text or Binary columns that are usually not filled with data. For example, you may have a 2-Kilobyte Text column that is only half full in most rows.

When a compression method is specified, DataFlex places the compressed data in a table with the same filename as the table, but with the extension .VLD (for variable-length data). The table with the extension .DAT changes to contain pointers to the data and other information necessary to the compression process.

Type

Description

None

The table has fixed-length rows only.

Fast

Fast compression uses the run-length compression technique, in which any series of two or more similar characters are stored as the binary representation of the number of occurrences, followed by the character. For example, one hundred spaces would be compressed to the binary symbol for 100, followed by a space.

Standard

Standard compression is a symbolic replacement based on the frequency that characters are used in a typical table.

Custom

Custom compression is a symbolic replacement based on an analysis that Database Builder performs on the data in the table.

Files that use custom compression should occasionally be re-compressed as the data in the table changes.

Related Topics: Re-Compressing Data

Transaction Type

Transaction processing is when a group of database operations must all complete or fail together to maintain database integrity. Refer to the Visual DataFlex Database Guide manual for a full description of transaction processing.

There are three types of transaction processing: client-atomic, server-atomic, and server-logged. Not all database drivers offer all three types. Embedded databases (as opposed to server-based ones) offer only client-atomic transactions. Other database drivers may be supplied with information on how they handle transaction processing.

Type | Description
None | You should not use this option unless the table in question will only be used in programs in a very limited way, without using data dictionaries. If the table uses data dictionaries, then you must utilize at least client-atomic transactions.
Client-Atomic | Client-atomic transactions can be aborted under program control and, under some circumstances (e.g., deadlock) they will be aborted automatically by DataFlex. Client-atomic transactions do not provide protection from workstation crashes. If the workstation (whether from program failure or system failure) crashes while a client-atomic transaction is outstanding, part of the transaction may have already been written to the database and cannot be automatically backed out.
Server-Atomic | Server-atomic transactions offer the same benefits as client-atomic transactions and, in addition, offer protection from workstation crashes. If a program (or the system) crashes while a server-atomic transaction is outstanding, the full transaction can be automatically backed out.
Server-Logged | Server-logged transaction control offers the same benefits as server-atomic transaction control and, in addition, offer a rollback capability. System rollback restores the system to its status as of a specified point in time. For example, if your company payroll processing was started at 1 p.m. and then something went badly awry later in the afternoon, you could use this functionality to roll back the state of your system to the way it was at 1 p.m., so you could start again. Note that all transaction processing done on that server after 1 p.m. would be affected, not just the payroll.

Related Topics: DF_FILE_TRANSACTION

Lock Type

This setting specifies how this table shall be locked when a lock, reread, or begin_transaction command is executed on it.

Type | Description
None | The table will not be locked. This setting improves performance of the system overall, and may be used on tables to which data entry by more than one user at any time is otherwise prohibited.
File | The table will be locked. This should be the normal setting for embedded database tables into which data entry is enabled in a multi-user setting.
Record | Row locking can greatly improve performance in active, multi-user, multi-table settings. It is supported by the DataFlex Server and by some non-Embedded database drivers. The DataFlex client (as distinguished from the Server) does not support this lock type.

Connection Settings

File Name

If you are using the embedded database, the File Name refers to the directory name files used to store the table's data (excluding the file extension). Normally, the table name and this file name will be the same. The File Name must conform to operating-system filename rules.

In certain cases, a file name may contain a drive letter and path (up to a maximum of 40 characters in total). If it does not contain a drive and path, then the workspace Data path will be used to specify the table's location.

If you are not using the embedded database, then the filename refers to the name of the file that records a description of the table to the database driver. Special modifiers to the filename specify the type of driver. Refer to your database driver documentation for specific information.

Login

Some database drivers (such as the ODBC driver, but not the Embedded driver) require the table-creation login to be set in order to modify a new table. This is how the driver will know on which server to put the new table.

Enter the table-creation login for the table (this is not used for Embedded database tables).

The value that you enter for the login will be used as the connection string when connecting to this table. Often, all tables in your database will use the same connection string, in which case you do not need to edit this value.

Special Note: This form sets the DF_FILE_LOGIN attribute for the table.

For more information, refer to: Configuring a Database Connection, Creating a Table

Other Parameters

If you are editing an SQL database table there may be further parameters displayed on this tab. Refer to the appropriate driver's users guide for relevant information.


Data Dictionary Column Settings

The Column Settings tab page is used to maintain the column-based data dictionary properties. There are many column-based properties, so this information is further divided into sub-pages. The sub-pages display the column-based information for only one column at a time.

The list to the left of the Column Settings tab page is used to select the current column.

You can change the current column at any time by clicking on a column from the list. If you are editing column information in one of the sub-pages, you can use the Ctrl+UpArrow or Ctrl+DownArrow keys to change the current column.

See Also

Column Options

Validation and Lookup

Validation Tables

Column Masks

Column Appearance

Other Column Settings


Column Options

The Options tab page allows you to quickly set a number of useful business rules for each column. These simple rules control such things as forcing data entry to upper case, automatic finding of data, etc.

These options are maintained in the Field_Options property of the DataDictionary class.

Auto Find

This option executes an automatic find eq (equals) by the Main Index of the column when the user navigates out of a data-entry form for the column. This option will not work on a column that does not have a main index.

If a row is found whose value in the main index of the column exactly matches the values in the data-entry form and any other items whose columns participate in the index, it is displayed to the screen. If no such row is found, the cursor exits the form normally and no row is moved into the buffer. No error will be declared.

You would usually set this option on every key column in your database.

Special Note

  • Where you wish to find by a non-unique index, use Auto Find GE (below).

  • Where you are auto finding by a multi-segment index, place the Auto Find option on the last column that participates in the index.

  • If used together with Find Required (below) Auto Find will require users to enter a valid existing key before continuing.

Auto Find GE

This option performs a find ge (greater than or equal to) by the main index of the column when the user navigates out of a data-entry form for the column. This option will not work on a column that does not have a main index. If a row is found whose value in the main index of the column exactly matches the values in the column’s data-entry form and any other forms whose columns participate in the index, it is displayed to the screen. If no such row is found, the row whose index value next follows that in the form(s) is displayed.

Auto Find GE is useful for finding rows on partial entries to items or, in multi-segment indexes, by keys for only the first segment or segments (rather than all of them). All non-unique indexes, for example, are multi-segment (being composed of at least one column plus a row ID column).

You may use Auto Find GE with any indexed column regardless of whether the column’s main index is unique or non-unique.

If you are using Auto Find GE by a multi-segment index, place the Auto Find GE option on the last column in the index for which you wish to use an entered key value. If any column which comes before the one with the Auto Find GE option on it in the index lacks an entered key value in its form, the value of that column will be considered empty for purposes of the find. The found row will be the one whose value for the first such column is the lowest in the index.

If you want to execute an automatic find eq (equals) command for an exact match with the entered data, use the Auto Find option instead (above).

Auto Find GE will always find a row, unless the table has no rows in it (is empty).

Capslock

This option automatically converts any lower-case characters entered into a data-entry form associated with the column, to upper case (capital letters). It has no effect on non-alphabetic characters. This assures that all input to the column through data-entry forms will be saved with letters in upper case.

Capitalization (what character is the uppercase of which other character, and vice versa) is set in the collate.cfg configuration file.

Related Topics

Uppercase Command

Display Only

This option causes the cursor to skip the data-entry form associated with this column and prevents any data in the form from being changed. The form will display the column data shadowed.

Display only is the equivalent of a combination of the No Enter and No Put options (see below).

Find Required

This option prevents the cursor from going to the next data-entry form until a successful find on the form connected to this column places an active row in the view. An unsuccessful find causes Error 90 (Please enter a valid record ID).

Find required has no effect if an active row for the column’s table is already in the view.

You would normally apply Find Required only as a foreign-column option in a parent table to ensure that a related parent row is found before a save occurs in the child. This forms part of the orphan-prevention rules in the child table.

Special Note: An Auto Find option on the column with a Find Required will attempt the required find automatically when a user attempts to move the cursor out of the form. If the automatic find is successful, the cursor will proceed. If it is not, Error 90, Please enter a valid record ID, is declared.

A Find Required with an Auto Find GE is unnecessary. Auto Find GE always finds a row in a non-empty table.

If a find is not possible or convenient from a data-entry form with find required, users may use the Previous Item accelerator key to move the cursor to a previous form whose column is indexed, find through that form, and then proceed through the form with the find required on it.

If a find is not possible or convenient from a data-entry form with find required, users may use the Clear accelerator key to clear all forms, and "start over" again.

Related Topics

Validate_Save

Foreign Field Options

Force Put

This option forces the contents of data-entry forms connected to the column to be put to the table’s row buffer when the cursor exits such a form even if no keyboard changes were made to the form.

Special Note

  • Normally the contents of a data-entry form are put to the buffer only if they have been changed from what was originally displayed from the disk.

Key Field

This option marks a column as the primary key column for the table. The effect of setting this option is to prevent changes to the data stored in the column. This is known as key-column protection. Key-column protection allows data to be entered into the key column only when the row is first created. Once the row is created, the key-column data cannot be changed.

You would normally set this option on ID columns such as the Order Number column in an Order Header table or the Customer ID column in the Customer table. This offers orphan prevention by preventing the ID columns used in parent-child relationships from being changed.

Special Note

  • Key columns are only protected in a table if the Protect Key Fields attribute is also set in the data-dictionary.

Related Topics

Key_Field_State

Protect Key Fields

No Enter

This option prevents data entry through any form associated with the column.

Special Note

  • The Skip Found option (below) has a similar effect, except that it allows the cursor to go to the item only if no row is found. No Enter skips the form regardless of whether a row is found.

  • The Display Only option (above) is a combination of No Enter and No Put.

  • Although data cannot be entered from the keyboard to a form controlled by No Enter, data can still be moved to the form under program control. Such data changes will be moved to the row buffer and saved, unlike with the Display Only or No Put column options.

No Put

This option stops data being moved from the data-entry form back to the row buffer for the column, even if new data is entered from the keyboard. No Put is useful for allowing data to be entered into a form for finding, where you do not wish to allow the column to be changed.

Special Note

  • Use the No Put column option when you want to prevent users from changing data.

  • The Display Only column option is a combination of the No Put and No Enter column options.

Retain

This option prevents data-entry forms associated with the column from clearing when the user initiates an operation that would normally clear all the forms in a view.

Special Note

  • If Retain is combined with the Display Only or No Enter options, then it will function like the Retain All option (below).

  • If the user initiates a clear-all operation, then even retained data-entry forms will be cleared.

Retain All

This option prevents data-entry forms associated with the column from ever being cleared.

Special Note

  • Even though users cannot clear the data-entry form by pressing the Clear key, they still may erase it manually (character-by-character).

  • If you want to be able to clear the data-entry form by pressing the Clear All key, use the Retain column option.

Required

This option checks whether data has been entered to the data-entry form associated with the column and keeps the cursor in the form until either data has been entered, or the view is closed. If the user tries to leave the data-entry form without entering data, an Error 13 (An entry is required on this form) will be posted.

This option can be used to ensure that data is entered into a column.

Special Note

  • Any character or characters entered in the data-entry form will allow the cursor to move on to the next form.

  • You cannot trap Error 13 (An entry is required on this form).

Skip Found

This option prevents data entry to the column’s data-entry form if the buffer for the table has an active row in it.

If the buffer for table is clear (as after the Clear Panel accelerator key has been pressed), the cursor will stop at the form and allow data entry. A subsequent save will create a new row.

Special Note

  • No Enter is a similar column option. However, it skips the form in all cases, regardless of whether an active row is in the buffer.

Zero Suppress

This option blanks the display of the column’s data-entry form if it is a numeric column with value of zero (0).

Special Note

  • Zero Suppress will appear not to work where a small absolute value is put to a form. For example, if a form specifies integer-only display and a column value less than 1 is put to it (say, 0.5), a zero will display in the form. Only a value that is truly zero will be displayed as blanks.


Column Options (Character-Mode)

These three column options are ignored by the Visual DataFlex runtime because they are not valid in a GUI environment; however they are valid in a character-mode DataFlex application. Since data-dictionary files can be shared between Visual DataFlex and character-mode DataFlex, these options are available for this purpose.

Auto Back

This option causes the cursor to move from the first position in the data-entry window to the last position in the previous window when the left arrow or Backspace key is pressed.

Auto Back can be useful for simulating a single, large data-entry window by combining several smaller windows when used in conjunction with the Auto Return option (below).

Auto Back only functions in character-mode DataFlex and is ignored by Visual DataFlex.

Auto Clear

This option clears existing data from the data entry form each time users begin entering characters into the form.

Special Note

  • If a user presses the Tab key to pass through an Auto Clear window, its contents are not affected.

  • If a user presses the right arrow key before typing any characters in an Auto Clear form, the form behaves as though the Auto Clear were not applied to it.

  • Auto Clear only functions in character-mode DataFlex and is ignored by Visual DataFlex.

Auto Return

As a user is entering data, this option causes the cursor to move from the last position in the data-entry window associated with the column to the first position in the next data-entry window.

If Auto Return is in force on the last data-entry window in a view and data entry continues after that window is full, the cursor proceeds as though the user pressed Tab to advance to the next window.

Character-mode DataFlex Configuration offers a global Auto Return option. To apply Auto Return selectively, this option must not be in force from Configuration.

Auto Return can be useful for simulating a single large data-entry window by combining several smaller windows when used in conjunction with the Auto Back option (above).

Auto Return only functions in character-mode DataFlex and is ignored by Visual DataFlex.


Validation and Lookup

The Validation and Lookup tab page allows you to set up some simple rules for validating data entered into each column. The Lookup section allows you to connect columns to a lookup object to search the table.

Validation Method

This form allows you to specify the name of an Integer function that can be used to validate data that is entered into the column. If the function returns false (0), then the entered data has passed validation. If it returns true (not equal to 0), then the validation has failed.

The validation function will be executed after the user has entered data into the column’s data-entry form. If the validation fails, then the user is prohibited from leaving the form. (The user may still exit the form by clicking elsewhere on the screen with the mouse).

The validation function is also executed when a row is saved. If the validation fails, the save is aborted.

Validation methods provide an flexible way of validating column values since the functions can perform any test that can be programmed.

The validation function should be a member function of the table’s data-dictionary. The prompt button can be used to pop up a list of all the data-dictionary’s member functions. The list only shows methods that are defined in this table’s data-dictionary, excluding methods that are pre-defined in the standard DataDictionary class.

You may type in a method name or use the selection list to choose one of the existing methods. The Methods tab page is used to define member functions for the table’s data dictionary. If you enter the function name yourself, then you must prefix the function name with get_. (When you select a function from the list, then this is added for you).

For example, if you have the following member function…

Function ValidateCreditLimit Integer iField Number nValue Returns Boolean

Boolean bErr

If (nValue > 5000) Begin

Error 200 "Credit Limit may not exceed $5000"

Move True To bErr

End

Function_Return bErr

End_Function

To use this function on the Credit Limit column of a table, you would set the column’s validate method to get_ValidateCreditLimit.

Special Note

  • The declaration prototype for a validation method takes the following general form.

function function_name integer iField type currentValue returns integer

Where

iField is the column number of the column that sent function_name. The column number can be used to retrieve any information about the column.

Type and currentValue are the data type and current value of the column that sent function_name.

  • This allows you to write generalized validation-method functions that can be re-used for other columns and tables.

  • Although it is rare to do so, a procedure may also be used as a validate method. In this case, you must not use the get_ prefix.

  • This form sets the field_validate_msg property in the data-dictionary.

Simple Validation

The Simple Validation form provides a convenient way for you to define a list of valid entries for the column. If the value entered into the column’s data-entry form does not belong to the list, then validation will fail.

The validation is performed after the user has entered data into the column’s data-entry form. If the validation fails, then the user is prohibited from leaving the form. (The user may still exit the form by clicking elsewhere on the screen with the mouse).

The validation is also performed when a row is saved. If the validation fails, the save is aborted.

Enter a list of values. Each value should be delimited from the others by the vertical-bar character "|".

Example

"FL|GA|AL|MS|LA|TX"

In the above example, data entry for the column would be limited to one of 6 southern state prefixes.

Simple Validation lists affect data-entry views in a number of useful ways:

  • If the column’s data-entry form is based on the dbForm class, then a prompt button will be attached to the form that pops up a list of all the values in the column’s list of valid entries.

  • If the column’s data-entry form is based on the dbComboForm class, then the combo list will show all the values in the column’s list of valid entries.

  • If simple validation fails, then an error message is displayed that shows all the values in the column’s list of valid entries.

Special Note

  • A more-flexible and reusable way of specifying a list of valid entries is to use a Validation Table.

  • The following column settings are mutually exclusive: Simple Validation, Range From, and Checkbox. You may only apply one of these settings to any column.

  • This form sets the field_value_checkproperty in the data-dictionary.

Range From, To

The Range form allows you to define a minimum and maximum range for the column. It is expected that the column and its range will be Numeric or Date. The From value must be lower than the To value.

The validation is performed after the user has entered data into the column’s data-entry form. If the validation fails, then the user is prohibited from leaving the form. (The user may still exit the form by clicking elsewhere on the screen with the mouse).

The validation is also performed when a row is saved. If the validation fails, the save is aborted.

Special Note

  • The following column settings are mutually exclusive: Simple Validation, Range From, and Checkbox. You may only apply one of these settings to any column.

  • The Range From, to form sets the field_value_range property in the data-dictionary.

Validation Error

The Validation Error form allows you to define a specific error number (and message) to be raised whenever validation fails for the column. This means that each column can have its own specific validation error message.

Enter a number in the first form and a text in the second form. Choose an error number that is not being used internally by Visual DataFlex. You may re-use the error number for different columns.

Special Note

  • The error is triggered when one of the following validation tests fails: Simple Validation, Range Validation, Validation Table or an incorrect Checkbox Value. Validation Methods should have their own error texts coded inside the member function.

  • The Validation Error form sets the field_error property in the data-dictionary.

  • This form allows you to set validation error messages on a column-by-column basis. The Validation Error setting on the Options tab page sets the default message for every column.


Validation and Lookup (Lookup Objects)

Each column in the data-dictionary may reference an external lookup object. Lookup objects are usually defined to pop up a list of rows from the table allowing the user to search and select a row which is then loaded into the data-entry view.

In order to reference an external lookup object, you must specify the name of the object and the name of the file (package) where it is defined.

Lookup Object

Enter the name of the lookup object that is connected to the column.

The DataFlex naming convention for lookup objects is: filename_SL, where filename is the root name of the table that is listed in the lookup.

For example CUSTOMER_SL would be the name of the lookup object of the Customer table.

It is a good idea to use the naming conventions because they help you to organize your work better and the conventions allow the Visual DataFlex tools to automate some of your work.

Often, you will attach the same lookup object to several columns in one table. Database Builder registers each lookup object that you have attached to the table and allows you to select them from a pop-up list.

The prompt button pops up a list of all lookup objects that are currently attached to columns in the table.

This allows you to attach the same lookup object to multiple columns. When you select an object from this list, its object name and package name are loaded into the Lookup Object and Lookup Package forms.

Another way to select a lookup object that is already used by another column is to simply enter its name. The package name of the object will be retrieved and loaded into the Lookup Package form.

This form sets the field_prompt_object property in the data-dictionary.

Lookup Package

Enter the file name of the package that contains the object named in the Lookup Object form. This information is used by the compiler to link in the object definition.

The lookup package naming convention is filename.SL, where filename is the root name of the table that is listed in the lookup.

For example CUSTOMER.SL would be the name of the lookup package for the Customer table.

Private

If you do not want Database Builder to record any information about the file name of the lookup object’s package, then put a check in the Private checkbox. This erases the contents of the Lookup Package form and shadows the form.

Normally, you would not check the Private checkbox; however, in some advanced applications you may need to specifically control when and how each package is included by the compiler.

Editing Lookup-Object Links

When you specify a lookup object for a column, you are creating a link to that object. You can create links to a single lookup object from multiple columns in a table. Database Builder allows you to edit information about the link. For example, you can change the object name or the package name by editing that data in the Lookup Object and Lookup Package forms (see above). When you change the link information, that information is changed for every column that is linked to the object. Normally, this is what you would want. Note, that this does not change the object itself, just the links to it from columns in the data-dictionary.

It is important to distinguish the difference between editing a link and linking to a different object. If you want to change a column so that it links to a different object without changing the name of the object it used to link to, then you first need to break the existing link to the first object. If you do not break the link first, then Database Builder will assume you are editing the first object.

To break a link to a lookup object, you must click the Remove button (see below).

Special Buttons

Database Builder provides three special buttons to help you with linking a lookup object to the column:

The Find button will pop up an Open File dialog and enables you to select a lookup package. You may open any file from any directory. The default directory is the AppSrc subdirectory of the current workspace.

Once you select a file, Database Builder opens the file and searches for desktop-level objects. If no object is found, an error is displayed. If one object is found, its name is automatically placed in the Lookup Object column. If two or more objects are found, they are displayed in a list and you may select one of them for placement in the Lookup Object column.

The Auto button instructs Database Builder to search the AppSrc directory of the current workspace for a package file whose name conforms with the naming convention for lookup packages. If this file is found, its contents are searched for an object whose name conforms with the naming convention for lookup objects.

If the package and object are found, then the package name is automatically loaded into the Lookup Package column (below) and the object name is automatically loaded into the Lookup Object column.

The lookup package naming convention is filename.SL and the lookup object naming convention is filename_SL, where tablename is the root name of the table.

The Remove button clears the link from the column to the lookup object. The object that was referenced remains in the object list until the table is closed so that it can be attached to a different column.

Lookup Wizard

The Studio's Lookup Wizard is the best way to populate these data dictionary settings. This wizard creates a lookup object and allows you to select which columns will utilize the lookup. Refer to the Lookup Wizard for more information.


Validation Tables

The Validation Table tab page allows you to set up tables of valid column entries. The validation tables are attached to one or more columns in the table and can be used to validate the entered data or merely to provide a list of suggested values.

Validation tables act in the following ways:

  1. If a column with a validation table is shown using a dbForm object, then the form will contain a prompt button. If the user clicks the prompt button, a list of valid entries is popped up. The user may select one of the entries.

  2. If a column with a validation table is shown using a dbComboForm object, then the combo list will contain the list of valid entries. The user may select one of the entries.

  3. The default behavior of a validation table during a save is to check the value of any attached columns to make sure the column matches one of the items in the list. If the match fails, the save will be aborted and an error raised. (see also "Validation Error"). This behavior can be switched off.

Validation-table information is stored in special objects. These objects are then linked to the columns that use them. Database Builder allows you to actually create validation-table objects and maintain their properties.

Data Entry

Type

Several different types of validation table are supported by Database Builder. You must select a validation-table type first. The supported types are:

Type

Description

None

Select None to disable validation tables for the column. This is the default.

Static

A Static validation table has its list of valid entries and descriptions hard-coded into the object. Database Builder will allow you to edit the list, but the list cannot be changed at run time.

Static validation-table objects are based on either the ValidationTable class or the DescriptionValidationTable class, depending on whether you enter any descriptions in the list.

Clicking the Advanced button will display a dialog into which you can enter custom code. See below.

Dynamic

A Dynamic validation table maintains its list of valid entries and descriptions in two tables called CODEMAST and CODETYPE.

Database Builder allows you to edit the list. Since the values are stored in a database, they are global and can be shared by multiple validation tables. The values can also be changed at runtime.

Dynamic validation-table objects are based on the CodeValidationTable class.

Clicking the Advanced button will display a dialog into which you can enter custom code. See below.

Custom

A Custom validation table can be defined to perform any operation so long as it meets certain basic criteria expected from a validation-table object. Custom validation tables cannot be maintained by Database Builder; you must manually write the object code, then register the object’s name and the name of the file where it is located.

Custom Validation Tables offer the greatest flexibility, but require the greatest amount of manual coding. For more information about creating Validation Table objects, refer to the "Data-dictionary Validations" section of the Visual DataFlex Database Guide.

Validate

When there is a check in the Validate checkbox, the validation-table values will be used to validate the column’s data. When it is unchecked, the data-dictionary will allow column values to be saved that are not in the validation table’s list.

This option sets the Validate_State property in the column’s validation-table object.

Blanks

When there is a check in the Blanks checkbox, the validation table will accept a blank column value in addition to the items in its list. When it is unchecked, the data-dictionary will not allow a blank column value.

This option sets the allow_blank_state property in the column’s validation-table object.

Advanced

Dynamic and Static Validation Table objects support a "custom code" region where any valid DataFlex code can be entered. Each Validation Table object in the .DD file contains one of these regions inside the object declaration. The regions are delimited by the following special markers:

//DDB-ValidationCustomStart

//DDB-ValidationCustomEnd

Custom code can be entered between these markers using a text editor or in Database Builder by clicking the Advanced button in the Validation Table tab page. This activates the Validation Table – Custom Code Dialog.

See Also

Validation Tables (Custom Code)

Validation Tables (Object Name, Location and Title)

Validation Tables (Type Values)

Validation Tables (Value and Description)


Validation Tables (Custom Code)

Custom code can be entered for a data dictionary's validation table by clicking the Advanced button in the Validation Table tab page. To edit custom code inside a data dictionary's validation table definition, follow these steps:

  1. From the "Field Settings" Tab, select a column which is associated with the Validation Table you want to edit.

  2. Go to the "Validation Table" Tab.

  3. Click the Advanced button. This loads the Validation Table – Custom Code dialog.

  4. Enter any valid DataFlex code for the Validation Table object. Note that the dialog can be resized to obtain a larger edit area.

  5. Click OK to save the custom-code, or "Cancel" to abort your changes.

See Also

Validation Tables

Validation Tables (Object Name, Location and Title)

Validation Tables (Type Values)

Validation Tables (Value and Description)


Validation Tables (Object Name, Location and Title)

Object Name

This form displays the name of the validation-table object that is connected to the column. When you first select a validation-table type, an object name is automatically generated for you.

It is possible to attach the same validation-table object to several columns in one table. Database Builder registers each object that you have attached and allows you to select them from a pop-up list.

The prompt button pops up a list of all validation-table objects that are currently attached to columns in the table.

This allows you to easily attach the same validation table to multiple columns. When you select an object from this list, all of its details are displayed.

Another way to select a lookup object that is already used by another column is to simply enter its name. When you finish entering the name, all of the object’s details are displayed.

This form is used to specify the object name for the field_value_table property in the data-dictionary.

Location

This form is used to specify the file name of the package where the validation-table object is defined. This information is used by the compiler to link in the object definition.

You can only specify a location for the Custom validation-table type (see above). If you are using the Static or Dynamic type, then you do not need to enter a location because the object is stored internally in the data-dictionary package.

Private

If you do not want Database Builder to record any information about the file name of the Validation Table’s package, then enter the word Private. If you leave this form blank, then the word Private will be entered for you.

You may click on the prompt button to select a package filename using the Windows common file dialog. The default directory is the AppSrc subdirectory of the current workspace.

Title

This form allows you to define a title for the validation table. The title will be shown in the title bar of any pop-up forms that display the validation-table data.

The Title form sets the table_title property of the validation-table object.

See Also

Validation Tables

Validation Tables (Custom Code)

Validation Tables (Type Values)

Validation Tables (Value and Description)


Validation Tables (Type Values)

Type Value Maintenance

This form is used to specify one of the groups of values stored in the codemast table. You can only specify a Type Value for Dynamic validation-table.

The combo list contains all the categories that are currently available. When you select a category, all of the Values and Descriptions are loaded into the grid. You are then able to edit these Values and Descriptions (see below).

The Type Value Maintenance form sets the type_value property for the Dynamic validation table.

New Type Value

The New Type Value button allows you to create new categories for the codemast table. When you click this button, the Type Value Maintenance dialog is activated

To create a new category, fill in the three fields, then click OK. Below is a description of each element on this dialog:

Type

Type is the key identifier for each category, and is used to group all of the values and descriptions for each category. It must be unique (i.e. not shared by any other category).

Description

This is a simple description of the category.

Notes

You can enter up to 1000 characters of descriptive notes for each category.

Add

This button saves the category that you just entered, then clears the form for you to enter another category.

OK

This button saves the category that you just entered, then exits the pop-up form.

Cancel

This button exits the dialog without saving the category information currently shown on the popup form. All categories previously saved with the Add button will remain saved.

Special Note

Each new category you create will be added to the combo list of the Type Value form.

Visual DataFlex provides a more-sophisticated tool that allows full maintenance of CODEMAST data.

See Also

Validation Tables

Validation Tables (Custom Code)

Validation Tables (Object Name, Location and Title)

Validation Tables (Value and Description)


Validation Tables (Value and Description)

The Value and Description grid contains the list of valid column values and descriptions for the validation table.

You can edit, add, delete, or insert values in this list. The following shortcut keys can be used while you are editing data in this list.

Key | Action
ALT+A | To add a new row to the bottom of the list.
ALT+I | To insert a new row at the current cursor position.
ALT+D | To delete the row at the current cursor position.

These options are also available from the context menu.

Any changes you make to this list are not saved until you perform a save on the table that you are editing.

Value

The Value column contains the list of valid column values for the table column that is attached to the validation table.

Description

The Description column contains descriptions for each value. If a dbComboForm is used to represent the column in an application, then the data shown in the combo form is the Descriptions. However, the data that is actually stored as a result of selections from the combo form is the Value that corresponds to each description.

Special Note

In a Static validation table, the Value and Description data is stored in the object via the Add_Table_Value method.

In a Dynamic validation table, the Value and Description data is stored in the codemast table. The data is grouped in the codemast table according to the category that is selected in the Type Value form. You must select a Type Value before entering any Value and Description data.

The Visual DataFlex Studio has a sophisticated tool that allows full maintenance of CODEMAST data.

See Also

Validation Tables

Validation Tables (Custom Code)

Validation Tables (Object Name, Location and Title)

Validation Tables (Type Values)


Column Masks

The Column Mask tab page enables you to set up a data-entry mask for each Column. Masks can be used to:

  • Restrict data entry to a Column to a defined pattern of numbers, letters, and punctuation marks.

  • Provide displays of Column values complying with pre-defined patterns and formatting. For example, it is possible to display date values as abbreviations such as days of the week or months of the year. Translating from numbers to names is sensitive to the Windows locale settings at using sites.

Column masks are created by combining special token characters into a string.

Column masks are assigned to a Column by manipulating two data-dictionary elements; Mask Type and Custom Mask. The result of this manipulation is shown in the ‘Applied’ Mask form.

There is a global mask repository that you can use to store masks which can then be reused without having to redefine them each time. See Column Masks (Repository).

Data Entry

Mask Type

This combo form is used to select the mask type for the Column. Selecting a mask type causes two masking effects.

It assigns a default mask to the Column. There is a default mask for each mask type.

Special Note

  • You can change the default mask by editing global string associated with each type.

The mask type determines which tokens are available for creating the mask string. These are summarized in the Mask Tokens section.

Valid settings for Mask Type and the associated default masks are:

Mask Type | Default Mask | Notes | Data-Dictionary Setting
none | none | Use this for setting up masks for ASCII Columns. Same as the text mask type. | mask_window
Currency | $,*;($,*) | Use this to apply currency formatting to a numeric Column. | mask_currency_window
Numeric | * | Use this for numeric Columns. | mask_numeric_window
Date | None | Use this for formatting date Columns. | mask_date_window
Text | None | Use this for setting up masks for ASCII Columns. Same as no mask type. | mask_window
Special Note

  • Mask Type sets the field_mask_type property in the data-dictionary.

Custom Mask

This form is used to specify a mask string for the Column. You would set the Custom Mask form if you wanted a mask that is different from the default mask of the selected Mask Type.

The prompt button pops up a pick-list of masks from the Mask Repository.

Mask strings are made up by combining special token characters into a string. The tokens that you may use vary depending on the Mask Type.

See Mask Tokens for more information.

Special Note

  • Custom Mask sets the Column_mask property in the data-dictionary.

Add to Repository

Click this button to add the current Custom Mask to the Mask Repository.

Edit Repository

Click this button to edit the Mask Repository.

Applied Mask

This form displays the mask string that will be applied to the Column. This is derived from the combined settings of the Mask Type and Custom Mask forms (above).

See Also

Column Masks (Mask Tokens and Mask Examples)

Column Masks (Repository)

Selecting a Mask from the Repository

Adding a Mask to the Repository

Editing the Mask Repository


Column Masks (Mask Tokens and Mask Examples)

Mask Tokens

Mask strings are made up by combining special token characters into a string. The tokens that you may use vary depending on the Mask Type. The following is a summary of mask tokens grouped by Mask Type:

Text Mask Type or None

Token | Meaning
# | Any numeric digit (0-9)
@ | Any alphabetic character (A-Z, a-z)
! | Any punctuation character
* | Any single printable character

Date Mask Type

Token | Meaning
m | Month number, displayed as 1, 2, 3, …, 12
mm | Month number, displayed as 01, 02, 03, …, 12
mmm | Month name abbreviated (translated to local language setting). For example, in English this would display Jan, Feb, Mar, …, Dec
mmmm | Month name (translated to local language setting). For example, in English this would display January, February, March, …, December
d | Day number, displayed as 1, 2, 3, …, 31
dd | Day number, displayed as 01, 02, 03, …, 31
ddd | Day of the week name abbreviated (translated to local language setting). For example, in English this would display Sun, Mon, Tue, …, Sat
dddd | Day of the week name (translated to local language setting). For example, in English this would display Sunday, Monday, Tuesday, …, Saturday
yy | Year number, displayed as 00, 01, 02, …, 99
yyyy | Four-digit year number displayed as 2000, 2001, 2002, …, 2099
/ | This represents the local date separator.

Special Note: Tokens for the month and day names are case-sensitive. That is, Mmm would produce Apr, while MMM would produce APR for the fourth month.

Numeric and Currency Mask Type

Token | Meaning
, | This token formats the number by inserting a thousands’ separator every three places to the left of the decimal place. The thousands’ separator is translated into the local thousands’ separator symbol.
. | This token represents the position of the decimal point. It is translated into the local decimal point symbol.
; | The semi-colon token is used to split the mask string into two parts. The left half is be used to format numbers greater or equal to 0 and the right half for numbers less than 0.
# | Specifies no digit or one digit.
* | Specifies any number of digits.
0 | Specifies one digit, or a 0 (zero).

Non-token characters in mask strings are treated as literals (they are inserted where seen into masked column values). This includes the space character. Token characters can be deactivated and made to appear as literals in masked values by preceding them with a backslash (\).

Sample Mask Strings

Text Mask Type or None

Mask | Raw Data | Formatted Data
###-##-#### | "012345678" | 012-34-5678
###-##-#### | "012-34-5678" | 012-34-5678
(###) ###-#### | "2125551212" | (212) 555-1212
(###) ###*#### | "212-555-1212" | (212) 555-1212

Date Mask Type

Mask | Raw Data | Formatted Data (USA)
Ddd - Mmm d, yyyy | 9/22/1998 | Tue - Sep 22, 1998
m/d/yy | 9/22/1998 | 9/22/98
Mmmm d | 9/22/1998 | September 22

Numeric and Currency Mask Type

Mask | Raw Data | Formatted Data (USA)
$#,##0;($#,##0) | 1001.536 | $1,002
$#,##0;($#,##0) | -1001.536 | ($1,002)
0.00 | -0.2 | -0.20
00000 | 123 | 00123
-> * <- | 104 | -> 104 <-
*0.* | 30.2500 | 30.25
+*0.00;-*0.00 | 12 | +12.00

Special Note

Picture masking is not validation. In fact, the picture-masking data types can frustrate certain kinds of validation. An example is the Date mask type. An column to which data entry is done through this type might receive an entry of 13/13/13, a value that DataFlex’s basic Date type would reject as invalid by declaring an error. A Date mask, however, would accept this value, converting it to something like the thirteenth day of the first month of the fourteenth year (itself a valid date). For reasons such as this, you may choose not to use picture masking on validated columns, or perhaps on columns to which data entry is made. Picture masking never returns an error nor rings the bell, it just discards ineligible characters.

For more information on masking refer to the "Picture Masking for Display and Data Entry" section of the Visual DataFlex Database Guide.

See Also

Column Masks

Column Masks (Repository)

Selecting a Mask from the Repository

Adding a Mask to the Repository

Editing the Mask Repository


Column Masks (Repository)

You can save and reuse masks in a global repository. These are stored globally in the file \usr\global\idesrc\CustomMask.kwd.

  • The masks are filtered by Mask Type.

  • They are selected by popping up a selection list containing all custom masks & descriptions for the current Mask Type.

  • Masks are added by clicking on the "Add to Repository" Button, which prompts you to add a description, then the mask is saved using the current "custom mask" and mask type.

  • An "Edit Repository" button also exists, allowing you to edit and delete masks in a pop-up dialog box.

See Also

Column Masks

Column Masks (Mask Tokens and Mask Examples)

Selecting a Mask from the Repository

Adding a Mask to the Repository

Editing the Mask Repository


Selecting a Mask from the Repository

  1. Select the appropriate Mask Type in the Mask Type combo form. Selecting <none> is equivalent to selecting "Text".

  2. Click the prompt button or press F4 from the Custom Mask form. This activates up a list of masks from the repository, filtered by the selected Mask Type.

  3. Select a mask from the list and click OK.

See Also

Column Masks

Column Masks (Mask Tokens and Mask Examples)

Column Masks (Repository)

Adding a Mask to the Repository

Editing the Mask Repository


Adding a Mask to the Repository

Whenever there is a non-blank value in the Data-Dictionary’s "Custom Mask" form, the "Add to Repository" button is active.

Clicking this button will activate the Add Mask to the Repository dialog. Enter a description for the mask, then click Add to add it to the Mask Repository.

See Also

Column Masks

Column Masks (Mask Tokens and Mask Examples)

Column Masks (Repository)

Selecting a Mask from the Repository

Editing the Mask Repository


Editing the Mask Repository

The global mask repository can be edited by clicking the Edit Repository button. This activates a dialog that lists all the masks in the repository and allows a number of operations.

Changing a Mask

  1. Select the mask you wish to change.

  2. Edit the mask values in the edit windows.

Adding a New Mask

  1. Click the Append button. This will create a new "Text" mask with blank description and mask.

  2. Select the new mask and edit its values in the normal way

Deleting a Mask

  1. Select the mask you wish to delete.

  2. Click the Delete button.

Saving the Changes

None of the changes you have made in the Edit Mask Repository dialog are saved to the repository until you click the OK button. Clicking the OK button will overwrite the current Mask Repository with the list of Masks that you have been editing, then the dialog is closed.

Clicking the Cancel button will close the Edit Mask Repository dialog without saving any of your changes.

See Also

Column Masks

Column Masks (Mask Tokens and Mask Examples)

Column Masks (Repository)

Selecting a Mask from the Repository

Adding a Mask to the Repository


Column Appearance

The Appearance tab page enables you to make to set specific visual attributes for each column. These visual settings are stored in the data-dictionary and applied by the Visual DataFlex Studio when building program components.

Visual Control

This form assigns the class name of a specific control to each column. By default, the Studio will use this information to determine the class of control that is created whenever the column is dragged onto a component. This setting does not stop you from explicitly choosing a different control class for the column in the Studio, or from changing the class of control on an individual basis; it is only the default.

If you leave this blank, then the Studio will determine for itself which is the best control to use for the column.

You can enter any class name in this form. If you enter the name of a non-standard class, then the class must be correctly registered with the Studio before it can be used.

The class names that appear in the combo list represent each major type of database control; however, you may enter any valid database control class name (including custom subclasses). The Configure Environment | Classes panel in the Visual DataFlex Studio allows you to change the name of the classes that appear in the combo list.

Long Label

This form is used to assign a label for the column. The Studio will apply the label whenever a form-based control (such as dbForm or dbComboForm) is created. The label is only used as a default and can be adjusted on an object by object basis in the Studio.

If you leave this blank, then the Studio will use the column name as a label.

Short Label

This form is used to assign a secondary label for the column. The Studio will apply the label whenever a list-type control (such as a dbGrid or dbList) is created. The label is only used as a default and can be adjusted on an object-by-object basis in the Studio.

If you leave this blank, then the Studio will use the long label in list-type controls. If the long label is also blank, then the column name is used.


Other Column Settings

The Other tab page displays the remaining column-based data dictionary settings.

Checkbox (True / False)

The Checkbox option sets the column up for use in a checkbox control. A checkbox column can only store one of two values; a true value and a false value.

If the column is represented by a checkbox control (dbCheckbox ), then the control will be checked () when the column value equates to true, and is unchecked when the column value equates to false.

If the column is represented by a form control (dbForm ,) then data entry will be restricted to either the true value or the false value.

To set up a checkbox column, place a check () in the checkbox and enter the values that will represent true and false (checked and unchecked).

Special Note

  • Checkbox columns should use the ASCII data type.

  • By default, the Studio will create a checkbox control whenever the column is dragged onto a component.

  • The following column settings are mutually exclusive: Simple Validation, Range From, and Checkbox. You may only apply one of these settings to each column.

  • This group sets the field_checkbox_value property in the data-dictionary.

Related Topics

Dragging and Dropping Fields

Status Help

When the user moves the cursor to a data-entry object for the column, the Status Help is displayed in the status bar.

Enter text that will aid the user with data entry to this column.

Special Note

  • This form sets the status_help property in the data-dictionary.

Default Value

This form is used to provide a default value when the user is entering data into the column. Defaults are shown each time a clear is performed on the column.

You can enter a literal value or an expression into the Default Value form. Expressions must be surrounded by parentheses ().

Examples

(DefaultDate(Current_Object) +10)

This default value is an expression that calls a method called DefaultDate and adds 10 to its return value.

01/01/2001

This is a literal default value that sets a date column to January 1st, 2001.

Special Note

  • An alternative form of data-entry defaults can be applied by writing a method and then attaching it to the column’s entry method. For more information refer to the "Entry Method " section (below).

Related Topics

Field_Defaults

Field_Default_Value

Entry Method

This form allows you to specify the name of a method that is executed whenever the cursor moves into a data-entry form connected to the column.

The Entry Method procedure can be programmed to perform any action. For example, you might use it to display special information or default values each time the cursor goes to the column for data entry.

In Visual DataFlex, procedures can return an Integer value. In the case of an Entry Method, if a non-zero value is returned, then the cursor movement into the column’s data-entry form is aborted.

The Entry Method should be a member procedure of the table’s data-dictionary subclass. The prompt button can be used to pop up a list of all the data-dictionary’s member procedures and functions. The list only shows methods that are defined in this table’s data-dictionary excluding methods that are pre-defined in the standard DataDictionary class.

You may type in a method name or use the selection list to choose one of the existing methods. The Methods tab page is used to define member procedures for the table’s data-dictionary..

For example if you have the following member procedure…

Procedure Entry_Order_Date Integer iField Date dDate

// Add a default date if the column is blank

Boolean bChanged

Get Field_Changed_State iField to bChanged

If (not(bChanged AND dDate =0) Begin

SysDate4 dDate

Set Field_Default_Value iField to dDate

End

End_Procedure

To use this procedure on a Date column of a table, you would set the column’s Entry Method to Entry_Order_Date.

Special Note

The declaration prototype for an Entry Method takes the following general form.

procedure procedureName integer iField type currentValue

iField is the column number of the column that sent procedureName. The column number can be used to retrieve any information about the column.

Type and currentValue are the type and current value of the column that sent procedureName.

The parameters that are passed to the entry method allow you to write generalized entry-method procedures that can be re-used in other columns and tables.

The Entry Method form sets the field_entry_msg property in the data-dictionary.


Other Column Settings (Exit Method, etc.)

Other Field Settings (Exit Method, etc.)

Exit Method

This form allows you to specify the name of a method that is executed whenever the cursor moves out of a data-entry form connected to the column.

The Exit Method procedure can be programmed to perform any action. For example, you might use it to adjust the values of some calculated data that is dependent on the column value being entered.

In Visual DataFlex, procedures can return an Integer value. In the case of an Exit Method, if a non-zero value is returned, then the cursor movement out of the column’s data-entry form is aborted.

The Exit Method should be a member procedure of the table’s data-dictionary subclass. The prompt button can be used to pop up a list of all the data-dictionary’s member procedures and functions. The list only shows methods that are defined in this table’s data-dictionary excluding methods that are pre-defined in the standard DataDictionary class.

You may type in a method name or use the selection list to choose one of the existing methods. The Methods tab page is used to define member procedures for the table’s data-dictionary.

For example if you have the following member procedure…

Procedure Adjust_Display_total Integer iField Integer iValue

// This updates the extended price column, which will update any

// display balances. This is only done for display purposes. The

// amount is updated to the column during the save.

integer iQty

Number nAmnt

Get Field_Current_Value Field Orderdtl.Qty_Ordered to iQty

Get Field_Current_Value Field Orderdtl.Price to nAmnt

Set Field_Current_Value Field Orderdtl.Extended_Price to (nAmnt *iQty)

// note we set value, but not changed state!

End_Procedure

To use this procedure on a Numeric column of a table, you would set the column’s Entry Method to Adjust_Display_Total.

Special Note

The declaration prototype for an exit method takes the following general form.

procedure procedureName integer iField type currentValue

iField is the number of the column that sent procedureName. The column number can be used to retrieve any information about the column.

Type and currentValue are the type and current value of the column that sent procedureName.

The parameters that are passed to the exit method allow you to write generalized procedures that can be re-used in other columns and tables.

The Exit Method form sets the field_exit_msg property in the data-dictionary.

Auto Increment

Auto-increment columns are automatically assigned a sequentially incrementing number each time a new row is created.

This feature is ideal for having the system automatically assign order numbers to an order header file, or for maintaining unique key column values for any table.

In order for the data-dictionary to know what next the increment value will be for a new row, it must store the last assigned number in some external table. To specify that a column is an auto-increment column, all you need to do is enter the table name and column name of the external column that will maintain the last assigned number. The format of the table and column reference is:

tableName.columnName

Example

system.order_number

In the above example we are specifying that the order_number column in the system table is used to calculate sequentially incremented order numbers for a column.

Auto incrementing will only work correctly if the external table is a related parent table, or a system table.

The prompt button can be used to activate a list of parent and system tables (or all tables) in the current workspace.

Special Note

It is a current limitation of the DataDictionary class that you may only assign one auto increment column per table. If you attempt to assign two, then the first column’s auto-increment setting will be erased.

If you are using a column from an unrelated table (such as a system table) as the counter for an auto-incremented column, then you need to register this table as an External File so that it will be correctly locked during save operations. Refer to the External Files list on the Structures tab page for further details.

The Auto Increment form generates a define_auto_increment statement in the data dictionary.

See Also

Selecting an Auto Increment External Table and Column

Short Description

The Short Description form provides a way for you to enter a short technical description of the column for documentation purposes. The description is stored as a comment in the data-dictionary subclass and is printed on the Print Data Dictionaries report .

Special Note

The Short Description form maintains a programmer’s comment for the column in the data-dictionary source code.


Selecting an Auto Increment External Table and Column

Select the table you want from the Filelist on the left-hand side. The dialog has been optimized to only display tables that are System Files, or are related parents of the current table.

Normally, you will only want to use columns from a System File or a Related To File as accumulators for Auto-Increment columns. A checkbox is provided to switch the panel to displaying all tables. Select the column that you want to use then click on the OK button. The selected table.column is placed in the Auto Increment form.

When you use this dialog to choose an Auto Increment File.Field, database builder will automatically check if it is a system table and add it to the External Files list if it is not already there. The added table is given a lock mode of "Lock on All", which is safest. You can disable this operation by unchecking the "Add to external tables structure" checkbox.

Note: Removing tables from the external tables list must always be performed manually. This is because developers may have entered custom code that requires a table to be listed in the external tables structure.

See Also

Auto Increment


Data Dictionary Methods

The Methods tab page allows free-format editing of the data dictionary (.DD) file. You may enter any valid DataFlex code or comments.

The code that is displayed in the edit window is a subset of the full data dictionary (.DD) file. The code that you don’t see pertains to all the data-dictionary-property settings that are maintained and displayed in the other tab pages.

You can use this tab page for creating procedure and function methods inside the data-dictionary subclass. Typically these methods would perform the following tasks:

  • Save- and delete-row validations.

  • Field-level validation of data.

  • Field-entry and exit processing.

  • Any other specialized tasks.

Class Declaration

The edit window displays the Class Declaration for the table’s data dictionary. You may edit the table’s data dictionary class name. The default name is: tablename_DataDictionary.

Be aware that if you change the table’s data dictionary class name, then you must edit all references to that class in your application to reflect the new name. Otherwise, you may encounter compiler errors.

You may also edit the superclass name of the table’s data dictionary. The default is the Visual DataFlex standard DataDictionary superclass. However, before you edit the superclass name, you must register that class in the Class Preferences tab of the Studio's Configure Workspace dialog. Once you register a new data dictionary class, it will become the new default superclass whenever you create a new table and dictionary.

Database Builder will only recognize two data dictionary superclass names for each workspace: The standard DataDictionary class and the class name that you register.

Related Topics

Augmenting Inherited Methods

The Implemented Methods List

Editing Data Dictionary Code

The Class Command


Augmenting Inherited Methods

One of the things you would typically do in the Methods Editor is augment the behavior of key methods that are inherited from the DataDictionary superclass.

The inherited methods provide convenient programming hooks (called events) for such operations as:

  • Validation for row saves and deletes,

  • Changing the current row,

  • Creation of new rows,

  • Transaction aborts, etc.

The Tree View panel contains a list of the inherited methods from the DataDictionary class that you are likely to want to augment under the heading "Unaugmented Methods".

The list supports the following methods:

  • Attach_Main_File

  • Backout

  • Clear_Main_File

  • Creating

  • Delete_Main_File

  • Deleting

  • OnNewCurrentRecord

  • Operation_Not_Allowed

  • Relate_Main_File

  • Save_Main_File

  • Transaction_Aborted

  • Update

  • Validate_Delete

  • Validate_Save

Generating a Method Declaration

You can use the list of unaugmented methods to generate method declarations in the class editor. Once the method declaration has been created you can use the editor to add additional code to complete the augmentation.

Use one of the following methods to generate a method declaration to augment an inherited method:

  • Double-click the method's name in the unaugmented methods list

  • Select Augment Method from the context menu of the method you want to generate

After each inherited method is augmented the tree-view is adjusted by removing the method from the Unaugmented Methods list and moving it to the Implemented Methods list.

See Also

Data Dictionary Methods

The Implemented Methods List

Editing Data Dictionary Code


The Implemented Methods List

The Tree View tool contains a list of Implemented Methods.

The implemented methods is a list of each method that is declared in the data-dictionary class editor. As new methods are added to or removed from the data-dictionary class, the list is adjusted accordingly.

Finding Class Methods

The Implemented Methods list can be use to quickly position the cursor in the class editor on the method declaration line. This is particularly useful when you are editing a large class with many methods.

Use one of the following methods to find a method using the Implemented Methods list:

  • Click on the method name

  • Select Find Method from the context menu of the method you want to locate

See Also

Data Dictionary Methods

Augmenting Inherited Methods

Editing Data Dictionary Code


Editing Data Dictionary Code

The class-code editor is a fully-featured programmer's editor. It allows you to directly edit the data-dictionary (.DD) package.

The code that is displayed in the edit window is a subset of the full data dictionary package. The code that you don’t see pertains to all the data dictionary property settings that are maintained and displayed in the other tab pages.

Augmenting Define_Fields and Field_Defaults

The Define_Fields and Field_Defaults methods contain special markers (//DDB-Generated-Code-Location). These markers are place holders for the code that is generated by Database Builder via settings on from the other tab pages.

Procedure Define_Fields

Forward Send Define_Fields

//DDB-Generated-Code-Location

End_Procedure // Define_Fields

Any code that you type before the markers will be executed before the generated code. Similarly, code that you type after the markers will be executed after the generated code, when the Define_Fields or Field_Defaults methods are executed.

See Also

Data Dictionary Methods

Augmenting Inherited Methods

The Implemented Methods List

Creating New Methods


Creating New Methods

Database Builder provides a dialog to simplify the task of creating new method declarations.

To activate the New Custom Method dialog, select New Method… from the context menu within the class editor.

Method Name

Enter the name and parameter list of the new method you are creating.

Method Type

Select the type of method you want to create. For more information, refer to the Procedure and Function commands in the Language Reference.

Add

Click the Add button to create the new method. The panel will clear so that you can begin to define another method.

OK

Click the OK button to create the new method then exit the panel.

Cancel

Click the Cancel button to exit the panel without creating any further methods.

See Also

Data Dictionary Methods

Augmenting Inherited Methods

The Implemented Methods List

Editing Data Dictionary Code


Data Dictionary Options

The Options tab page displays various data dictionary options to apply across all columns in the table.

The three panels across the top of this page enable you to apply column options to three types of columns in the data-dictionary’s table. These options have effect when those columns are addressed by views whose main table is not this table.

The database controls that make up a data-entry view in your application are connected to data dictionary objects that serve them data. These are the Server data dictionary objects for each control. The table column that is shown in the control must belong to either the server data-dictionary or a parent table of the server data dictionary.

When a column from a parent table is used, the control is considered to be connected to a foreign column. It is important to understand the concept of a foreign column. The following example shows an entry item that is not foreign followed by an entry item that is foreign:

An example of a foreign column is the Customer.Name column when it appears in an Order Entry view. This is because it is the Order Header table that provides the server data-dictionary; the Customer table in this example is a parent of the order header.

However, when the Customer.Name column appears in a Customer Maintenance view, it is not a foreign column because in this case the Customer table provides the server data-dictionary.

When you are designing your application, the data-entry rules for columns of a parent table are usually different from those for the server table. For example, when entering a new row for the server’s table, the parent-table rows are not modified. Their role is to provide column values (customer number, etc.) on which relations are based from the main table to the ancestor. When the parent table must be changed (add, delete, modify), this is done in a different view, where that table is the main (server) table.

Because of this, foreign columns often require additional option settings. Three special type designators may be used to assign foreign-column options to all columns of a specified column type. Those types are key columns, index columns, and default (non-index) columns.

  • Key Column is any column for which the Key Field option is checked.

  • Indexed Column is any column that is not a key column and provides a segment in one or more of the table’s indexes.

  • Default Column is any column that is not a key column or an indexed column.

Foreign-column properties are added to any column properties that may have been already applied directly to the column.

Auto Find

This option executes an automatic find eq (equals) by the Main Index of the column when a user navigates out of a data-entry form for the column. The column must be defined in an index.

If a row is found whose value in the main index of the column exactly matches the values in the data-entry form and any other items whose columns participate in the index, it is displayed to the screen. If no such row is found, the cursor exits the form normally and no row is moved into the buffer. No error will be declared.

You would usually set the Auto Find option on all Key Columns in your database.

Special Note

  • Where you wish to find by a non-unique index, use Auto Find GE (below).

  • Where you are auto finding by a multi-segment index, place the Auto Find option on the last column that participates in the index.

  • If used together with Find Required (below), auto find will require users to enter a valid existing key before continuing.

Auto Find GE

This option performs a find ge (greater than or equal to) by the Main Index of the column when the user navigates out of a data entry form for the column. The column must be defined in an index. If a row is found whose value in the main index of the column exactly matches the values in the column’s data entry form and any other forms whose columns participate in the index, it is displayed to the screen. If no such row is found, the row whose index value next follows that in the form(s) is displayed.

Auto Find GE is useful for finding rows on partial entries to items or, in multi-segment indexes, by keys for only the first segment or segments (rather than all of them). All non-unique indexes, for example, are multi-segment (being composed of at least one column plus a row ID column).

You may use Auto Find GE with any indexed column regardless of whether the column’s main index is unique or non-unique.

If you are using Auto Find GE by a multi-segment index, place the Auto Find GE option on the last column which participates in the index for which you wish to use an entered key value. If any column which comes before the one with the Auto Find GE option on it in the index lacks an entered key value in its item, the value of that column will be considered empty for purposes of the find. The found row will be the one whose value for the first such column is the lowest in the index.

If you want to execute an automatic find eq (equals) command for an exact match with the entered data, use the Auto Find option instead (above).

Auto Find GE will always find a row, unless the table has no rows in it (is empty).

Display Only

This option causes the cursor to skip the data-entry form associated with this column and prevents any data in the form from being changed. The form will display the column data shadowed.

Display only is the equivalent of a combination of the No Enter and No Put options (see below).

Find Required

This option prevents the cursor from going to the next data-entry form until a successful find brings an active row into the view. An unsuccessful find causes Error 90 (Please enter a valid row ID).

Find required has no effect if an active row for the column’s table is already in the view.

You would normally apply Find Required only as a Foreign Field option in a parent table to ensure that a related parent row is found before a save occurs in the child. This forms part of the orphan-prevention rules in the child table.

Special Note

  • An Auto Find option on the column with a Find Required will attempt the required find automatically when a user attempts to move the cursor out of the form. If the automatic find is successful, the cursor will proceed. If it is not, Error 90 ( Please enter a valid record ID), is reported.

  • A Find Required with an Auto Find GE is unnecessary. Auto Find GE always finds a row in a non-empty table.

  • If a find is not possible or convenient from a data-entry form with find required, users may use the Shift Tab accelerator key to move the cursor to a previous form whose column is indexed, find through that form, and proceed through the from that has the find required.

  • If a find is not possible or convenient from a data-entry form with find required, users may use the Clear accelerator key to clear all forms, and "start over" again.

Related Topics

Validate_Save

Foreign Field Options

No Enter

This option prevents data entry through any form associated with the column.

Special Note

  • The Skip Found option (below) has a similar effect, except that it allows the cursor to go to the item only if no row is found. No Enter skips the form regardless of whether a row is found.

  • The Display Only option (above) is a combination of No Enter and No Put.

  • Although data cannot be entered from the keyboard to a form controlled by No Enter, data can still be moved to the form under program control. Such data changes will be moved to the row buffer and saved, unlike the Display Only or No Put column options.

No Put

This option stops data being moved from the data-entry form back to the row buffer for the column, even if new data is entered from the keyboard. No Put is useful for allowing data to be entered into a form for finding, where you do not wish to allow the column to be changed.

Special Note

  • Use the No Put column option when you want to prevent users from changing data.

Skip Found

This option prevents data entry to the column’s data-entry form if the buffer for the table has an active row in it.

If the buffer for table is clear (as after the Clear Panel accelerator key has been pressed), the cursor will stop at the form and allow data entry. A subsequent save will create a new row.

Special Note

  • No Enter is a similar column option. However, it skips the form in all cases, regardless of whether an active row is in the buffer.


Data Dictionary Options (Other Settings)

Error Report Modes

The Error Report Mode determines how errors that are generated by certain data-dictionary operations are handled. Data dictionaries route their errors through one of two messages: Operation_Not_Allowed and Data_Set_Error . By default, when such an error is reported, it is passed to the DataFlex error system.

The Error Report Mode can be set to one of two options: Standard Error Report Mode or Custom Error Report Mode.

Standard mode just utilizes the default DataFlex error system. Custom mode switches off the DataFlex error system inside the Operation_Not_Allowed and Data_Set_Error messages; however, the ERR indicator is still set to true.

Custom Error Report Mode allows you to augment the Operation_Not_Allowed and Data_Set_Error messages to perform your own specific error reporting such as logging errors to an error table.

Selecting an Error Report Mode sets the error_report_mode property in the data-dictionary.

Related Topics

Error_Report_Mode property

Operation_Not_Allowed message

ERR Indicator

Default Validation-Error Message

The Validation Error form allows you to define a specific error number (and message) to be reported whenever validation fails for the column.

Enter a number in the first form and a message text in the second form. Choose an error number that is not being used internally by Visual DataFlex.

Special Note

  • The error is triggered when one of the following validation tests fail: Simple Validation, Range Validation, Validation Table, or an incorrect Checkbox Value. Validation Methods should have their own error messages coded inside the member function.

  • The Validation Error form sets the field_error property in the data-dictionary.

  • This form allows you to set a default validation-error message text for every column in the table. The Validation Error setting on the Validation/Lookup tab page sets the message on a column-by-column basis.

Protect Key Fields

Checking this form instructs the data-dictionary to protect the data stored in key columns from being modified. A key column is determined by setting the Key Field option in the Field Settings tab page.

Special Note

  • This form sets the protect_key_state property in the data-dictionary.


Data Dictionary Structures

  • Delete Structure

  • Save Structure

  • External Structure


Data Dictionary Structures (Delete Structure)

The Structures tab page is used to define the database structure with respect to the current table you are editing. The data-dictionary will use this structure to perform certain checks and table locks during save and delete operations.

Before a save or delete is performed in a data dictionary structure, the entire structure is validated to make sure that it is complete. If the validation fails, the database change is canceled. This protects against design errors. The lists of all expected parent and child tables are compared to the actual data-dictionary structures in your views to ensure structural integrity. The validations all occur during an unlocked state.

Delete Structure

The delete structure declares all related tables that are affected by a row deletion. This is normally all of the immediate child tables. Before a row can be deleted, the dictionary needs to check for relating rows in child tables to ensure that no child rows are orphaned. To perform this checking, all the child-table data dictionaries must be correctly connected to the main table. This data-dictionary structure of connections is a property of each data-entry view in your application and is controlled by the Data-dictionary Structure tool in the Studio.

Validation Mode

The delete-structure-validation mode determines how often the required-descendant-tables structure is checked when rows are deleted in a view.

Setting | Action
Validate Always | The data-dictionary will check the descendant-tables structure every time a row is deleted.
Validate Never | Data-dictionary descendant-tables-structure checking is disabled.
Validate Once | The data-dictionary will check the descendant-tables structure once when the first row is deleted after loading the view. This assumes that the structure will not change during the session. This option sets the validate_delete_structure_mode property in the data-dictionary.

Cascade Deletes

Check this option to instruct the data dictionary to delete all related child rows whenever a delete is performed.

Unchecking this option instructs the data-dictionary to abort the deletion if relating child rows exist.

This option sets the cascade_deletes property in the data-dictionary.

Required Child Tables

This form lists all of the child tables whose data dictionaries should be correctly connected before a delete operation can be performed in a view. Normally, this list will contain all of the directly relating child tables of the current table.

Special Buttons

There are several buttons to the right of the list that are used for adding and removing tables.

The Scan button instructs Database Builder to scan the entire database for relating child tables. Each child table that is found is added to the list.

The Select Files button activates a list of all the tables in the database.

From this list, click the Add button to add the currently selected table and remain in the panel for further selections. Click the OK button to add the currently selected table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The New button activates a dialog that enables you add tables to the list that are not yet defined in the database.

Type the name of the new table in the File Name form then click one of the buttons below.

Click the Add button to add the table and remain in the panel. Click the OK button to add the table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The Remove button deletes the currently highlighted table from the Required Child Files list.

Special Note

Each table on this list generates an add_child_file message in the data-dictionary.


Data-Dictionary Structures (Save Structure)

Save Structure

The save structure declares all related-to tables that are affected by the saving of a row. This is normally all of the immediate parent tables. Before a record can be saved, the dictionary needs to ensure that the save can be propagated to any ancestor tables to ensure that any changes to ancestor records are also saved. To perform this checking, the main table must be correctly connected to the ancestor tables’ data dictionaries. This data-dictionary structure of connections is a property of each data-entry view in your application and is controlled by the Data-dictionary Structure tool in the Studio.

Validation Mode

The save-structure-validation mode determines how often the required-ancestor-tables structure is checked when rows are saved in a view.

Validate Always | The data-dictionary will check the ancestor-tables structure every time a record is saved.
Validate Never | Data dictionary parent-tables-structure checking is disabled.
Validate Once | The data-dictionary will check the ancestor-tables structure once when the first record is saved after loading the view. This assumes that the structure will not change during the session. This option sets the validate_save_structure_mode property in the data-dictionary.

Required Parent Files

This form lists all of the ancestor tables whose data dictionaries should be correctly connected before a save operation can be performed in a view. Normally, this list will contain all of the directly related parents of the current table.

Special Buttons

There are several buttons to the right of the list that are used for adding and removing tables.

The Scan button instructs Database Builder to scan the entire database for related parent tables. Each parent table that is found is added to the list.

The Select Files button pops up a list of all the tables in the database.

Click the Add button to add the currently selected table and remain in the panel for further selections. Click the OK button to add the currently selected table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The New Files button pops up a panel that enables you add tables to the list that are not yet defined in the database.

Type the name of the new table in the File Name form then click one of the buttons below.

Click the Add button to add the table and remain in the panel. Click the OK button to add the table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The Remove button deletes the currently highlighted table from the Required Parent Files list.

Special Note

Each table on this list generates an add_parent_file message in the data-dictionary.


Data Dictionary Structures (External Structure)

External Structure

Certain tables should be connected to the data-server structure for which you have no reason to create a data dictionary object. A prime example is a system table containing a counter either for census purposes or for the purpose of assigning unique identification keys. Such tables must be registered in the data-dictionary as an external table.

Any data dictionary code you write that updates a non-related table while the data-dictionary has locking control should be added to the External Structure list. Failure to do this will raise locking errors when your code tries to commit a save to the external table.

The data-dictionary methods that are sent while the data-dictionary has locking control are: Update, Backout, Creating, Deleting, Validate_Save and Validate_Delete .

Another common case where a table needs to be added to this list is when you are using a non-related table to increment values for an auto-incremented column. Refer to the Auto Increment column option for more information.

There are several buttons to the right of the list that are used for adding, removing, and adjusting tables in the list.

Special Buttons

The Lock Mode button pops up the Edit External File Lock Mode panel for the currently highlighted table, allowing you to fine tune exactly when the table should be locked during data-dictionary operations.

Lock On All | The table will be locked on all save and delete operations by the data-dictionary.
Lock On Delete | The table will be locked only during delete operations by the data-dictionary.
Lock On Save | The table will be locked only during save operations by the data-dictionary.
Lock On New Save | The table will be locked only during saving-to-create-a-new record operations by the data-dictionary.
Lock On New Save + Delete | The table will be locked only during saving-to-create-a-new record or delete operations by the data-dictionary.

The Select Files button pops up a list of all the tables in the database.

Click the Add button to add the currently selected table and remain in the panel for further selections. Click the OK button to add the currently selected table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The New Files button pops up a panel that lets you add tables to the list that are not yet defined in the database.

Type the name of the new table in the External File form and then select one of the following lock modes.

Lock On All | The table will be locked on all save and delete operations by the data-dictionary.
Lock On Delete | The table will be locked only during delete operations by the data-dictionary.
Lock On Save | The table will be locked only during save operations by the data-dictionary.
Lock On New Save | The table will be locked only during saving-to-create-a-new-record operations by the data-dictionary.
Lock On New Save + Delete | The table will be locked only during saving-to-create-a-new-record or delete operations by the data-dictionary.

Click the Add button to add the table and remain in the panel. Click the OK button to add the table then close the panel. Click the Cancel button to close the panel without adding any more tables.

The Remove button deletes the currently highlighted table from the External Files list.

Special Note

Each table on this list generates an add_parent_file message in the data-dictionary.


Other Topics

  • Saving

  • Closing

  • Data Dictionary Read Errors

  • Loading DEF Files

  • Outputting DEF Files


Saving

Select Save from the File menu or click the Save button on the toolbar to save the currently open table definition. If a table has data in it and you have changed its structure or indexes, you will see the Restructure options for saving panel with reindexing and restructuring options. If the table contains no data, the new definition will simply be saved on top of the previous one.

Restructure Options for Saving

If you have made changes to a table’s structure, such as changing the length of a column, deleting a column, or changing a column's type, the table data will need restructuring, and a notification in the dialog box will inform you so. There is a checkbox for forcing the table to restructure even if no such changes were made, and there is a checkbox for creating a new .DEF file for the table.

Use Temporary File or Attempt in Place

You may choose restructuring to a temporary file or restructuring in place (in case you do not have enough room to restructure to a temporary file). Restructuring in place should only be done if you have a copy of the table or are confident that the process will complete without interruption, since any interruption to the process will render the table completely unusable by any version of DataFlex. Restructuring to a temporary file is much safer and ensures that a workable copy of the table exists at all times, even in the event of a power outage. Database Builder first renames the existing table, then restructures it into a table with the old name.

There are some instances where restructuring cannot, or definitely should not, be done in place:

  • Turning on compression causes the creation of a .VLD file and a temporary file for the .DAT file. Restructuring in place is always ignored in this case.

  • If you are converting a 2.3b table to 3.0 format and reducing the row length, then two passes are made. First the header is converted, then the rows.

  • Restructuring a compressed table in place results in a much-larger table than the original one, because of the way operating systems utilize blocks.

  • If you added an index or changed the definition of an existing one, reindexing of the affected index(es) will be required, and a notification in the dialog box will inform you so.

When you have made any required choices concerning restructuring, press F2 to start the process. Alternatively, press Esc to cancel the process. Messages will remain on screen afterwards to tell you the results of the restructure and/or reindex. When you exit, you will be returned to the menu bar if you were saving from the Save choice, or you will exit Database Builder if you were saving from the Exit choice.


Closing

To close a table, select Close from the File menu or click the close box of the table’s panel. If you have made changes to the table definition in the current session, you will be asked to confirm whether you want to save those changes.

Close all

Select Close All from the File menu to close all open table definitions.


Data Dictionary Read Errors

For Database Builder to correctly open and analyze a data dictionary (.DD) file, the .DD file must be formatted in a special way. This formatting includes special marker strings and certain restrictions (see "Data-Dictionary File Structure").

If the data-dictionary (.DD) file is not correctly formatted, then you may see some error messages when you first open the table.

The error screen tells you the name and location of the data-dictionary (.DD) file, the error message, and the line number in the .DD file that triggered the error.

The following is a listing of each message, its meaning, and how the error can be corrected.

No

Error Message

Explanation/Solution

1

Illegal table number: fatal

The table number assigned to the data dictionary is outside the supported range of legal table numbers supported by Visual DataFlex.

This error will abort reading of the data dictionary file.

This is an internal error.

2

Table not open: fatal

The data-dictionary’s table has not been opened.

This error will abort reading of the data dictionary (.DD) file.

This is an internal error.

4

Unknown format in .DD file: fatal

Database Builder was unable to locate the special markers at the top of the data dictionary (.DD) file.

Check the .DD file with an editor and ensure that one of the following marker strings is on the first line:

//DDB-FileStart

or

//DDB-FileStartNoRead

See" Data-Dictionary File Structure" for more information.

5

Nested class definition

Database Builder has detected a new class definition inside the data-dictionary class definition. This is not supported in Visual DataFlex.

See Classes in the Visual DataFlex Language Guide for more information.

6

Incorrect Region Location

This indicates that one or more special markers are out of order. The ordering of these markers is important.

See "Data-Dictionary File Structure" for more information.

7

Illegal line in define fields region

The define-fields special region of the data dictionary only supports reading and writing of certain commands and properties.

See "Data-Dictionary File Structure" for more information.

8

Syntax error in Define_Fields

There is a syntax error on one of the lines inside the define-fields special region.

You should identify the line where the error occurred and check that it is correct.

If this does not fix the problem, then the way that the line is written may be too complex for the data-dictionary to analyze correctly. Try to simplify the syntax of that line.

See "Data-Dictionary File Structure" for more information.

9

Table reference in Define_Fields not main table

Inside the define-fields special region, you may only set column properties of the data dictionary’s main table (i.e. the table that the data dictionary is defining).

Check to see that the table name is entered correctly, move any property settings for other tables outside the special region markers.

See "Data-Dictionary File Structure" for more information.

10

Illegal column name in Define_Fields

The column name that is referenced in the define-fields special region does not belong to the main table.

This could happen if a column was deleted from the table and the data dictionary was not correctly adjusted. Normally this would not happen because Database Builder would adjust the data dictionary automatically.

Check that the column name is correct and belongs to the main table.

11

Illegal table name in Define_Fields

A table name has been entered incorrectly inside the define-fields special region.

Some property settings require that the string .FILE_NUMBER is appended to the end of the table name. Check the line where the error occurred and make sure that the table name is entered correctly.

12

Object not declared in Define_Fields

Some property settings in the define fields special region reference an object name. That object name must be previously registered in the data dictionary using a special command string and/or the Register_Object command.

Furthermore, these commands need to be defined inside a special region. Which region depends on the type of object.

Validation table objects need to be registered inside the validation-table special region and selection list objects need to be registered inside the first selection list special region.

See "Data-Dictionary File Structure" for more information.

13

Illegal line in field defaults region

The field-defaults special region of the data dictionary only supports reading and writing of certain commands and properties.

See "Data Dictionary File Structure" for more
information.

14

Illegal line in validation tables region

The validation table’s special region of the data dictionary only supports reading and writing of certain commands and properties.

See "Data Dictionary File Structure" for more
information.

15

Syntax error in validation tables region

There is a syntax error on one of the lines inside the validation table’s special region.

You should identify the line where the error occurred and check that it is correct.

If this does not fix the problem, then the way that the line is written may be too complex for the data-dictionary to analyze correctly. Try to simplify the syntax of that line.

See "Data Dictionary File Structure" for more information.

16

Object not pre-declared in validation tables region

Each validation table object in the validation table’s special region must first be registered using the Register_Object command. This command must also be inside the validation tables special region.

See "Data Dictionary File Structure" for more information.

17

Illegal line in selection list region

The selection-list special region of the data dictionary only supports reading and writing of certain commands and properties.

See "Data Dictionary File Structure" for more
information.


Loading DEF Files

Database Builder can automatically create a working file definition by loading the ASCII file with the name filename.DEF (created with the Output .DEF option).

To load a .DEF file, select Load DEF from the File menu.

Table Number

If no table is loaded, or you choose to create an additional table, this window displays the lowest unused number in your file list. You can enter another number.

File Name

Next, enter the directory file name for the new table, including a drive and path if you wish.

Def File

This window displays, by default, whatever is in the previous window. Enter the name of the .DEF file to use as the definition for the new (or new version of the) table, including path, if desired.

Select OK to have the program build the new table. If there is already a table of the same name, you will be asked if you want to overwrite it. If the .DEF can't be found, you will see a message to that effect. If the operation was successful, the new table is created and opened, and you are put into the table-definition dialog.


Outputting DEF Files

Database Builder can generate an ASCII file containing information about columns and indexes of a table. There are two types of file you may generate, .FD files and .DEF files (meaning files with the same name as the table and the extension of .FD or .DEF).

To output definition files, select Output .DEF from the File menu.

Select the tables you wish to output definitions for, then click the Output DEF/FD button. You are next presented with the Select Destination panel.

You may check one or both file-type boxes, and check whether you wish output to a file (it will otherwise go to the printer). You may use .DEF files to create new files with the same definitions, (then change those definitions if you wish), and you may also use them for reference and documentation purposes, since they are in plain ASCII text.

Output .DEF

The .DEF file stores information about the table itself and the length and type of columns, column names, offsets and indexes, as shown in the following example.

-------------------------------------------------------------------------

DATE: 09/09/97 TIME: 11:13 PAGE: 1

FILE DEFINITION FOR FILE: CUSTOMER (# 25)

-------------------------------------------------------------------------

DRIVER NAME : DATAFLEX

FILE ROOT NAME : CUSTOMER

USER DISPLAY NAME : Customers

DATAFLEX FILE NAME : CUSTOMER

-------------------------------------------------------------------------

RECORD LENGTH : 1280 ( USED: 1195 )

MAX NUMBER OF RECORDS : 20 ( USED: 15 )

FILE COMPRESSION : FAST

RE-USE DELETED SPACE : YES

LOCKING TYPE : FILE

HEADER INTEGRITY CHECKING : NO

TRANSACTION TYPE : CLIENT ATOMIC

RECORD IDENTITY INDEX : 0 ( 0 , 0 )

FILE LOGIN PARAMETER :

SYSTEM FILE : NO

-------------------------------------------------------------------------

NUM FIELD NAME TYPE SIZE OFFST IX RELATES TO FILE.FIELD

--- --------------- ---- ----- ----- -- -----------------------------

1 NUMBER NUM 6.0 1 1

2 NAME ASC 30 4 2

3 ADDRESS ASC 30 34

4 CITY ASC 14 64

5 STATE ASC 2 78

6 ZIP ASC 10 80

7 PHONE_NUMBER ASC 20 90

8 FAX_NUMBER ASC 20 110

9 EMAIL_ADDRESS ASC 30 130

10 CREDIT_LIMIT NUM 6.2 160

11 PURCHASES NUM 6.2 164

12 BALANCE NUM 6.2 168 3

13 COMMENTS TEX 1024 172

INDEX# FIELDS DES U/C LENGTH LEVELS SEGMENTS MODE

------ --------------- --- --- ------ ------ -------- -------

1 NUMBER NO NO 3 1 1 ON-LINE

2 NAME NO YES 33 2 2 ON-LINE

NUMBER NO NO

3 BALANCE NO NO 7 1 2 ON-LINE

NUMBER NO NO

DriverName defaults to DataFlex, and can accept other supported table types such as ODBC.

The Transaction Type parameter has meaning for access to tables through data-dictionary objects. Any table your programs access through data dictionaries must have their Transaction Type set to client_atomic or server_atomic. Setting it to none will cause an error when your program attempts to save to or delete from the table. These two parameters can be set in File, Attributes .

Record Identity Index contains information to support automatic system updates (such as is available with ODBC). The parameters are the index number and, in parentheses, the table number and column number.

System File reflects whether the table has been designated a system table. If so, the maximum number of rows is set to 1.

Output .FD

The DataFlex compiler uses .FD files to read in the name and type of each column of any table you open in a program. Following is an example of an .FD file.

#REPLACE FILE25 CUSTOMER

#REPLACE CUSTOMER.RECNUM |FN25,0

#REPLACE CUSTOMER.NUMBER |FN25,1

#REPLACE CUSTOMER.NAME |FS25,2

#REPLACE CUSTOMER.ADDRESS |FS25,3

#REPLACE CUSTOMER.CITY |FS25,4

#REPLACE CUSTOMER.STATE |FS25,5

#REPLACE CUSTOMER.ZIP |FS25,6

#REPLACE CUSTOMER.PHONE_NUMBER |FS25,7

#REPLACE CUSTOMER.FAX_NUMBER |FS25,8

#REPLACE CUSTOMER.EMAIL_ADDRESS |FS25,9

#REPLACE CUSTOMER.CREDIT_LIMIT |FN25,10

#REPLACE CUSTOMER.PURCHASES |FN25,11

#REPLACE CUSTOMER.BALANCE |FN25,12

#REPLACE CUSTOMER.COMMENTS |FS25,13


Connecting to SQL Databases

SQL databases are created and maintained using the native tools supplied with each database.

Database Drivers

To use a SQL database in a Visual DataFlex workspace you will need to load and configure the driver software for that database type, and specify a the connection parameters the database.

  • Configuring Database Drivers

  • Configuring a Database Connection

  • Login and Logout

Connecting to an SQL Database

To access tables from the database you are connecting to, you will need to create table descriptions. These describe the tables to the Visual DataFlex driver software.

  • Connecting to an MSSQL Database

  • Connecting to a DB2 Database

  • Connecting to an ODBC Database

Converting from/to an SQL Database

To convert an existing embedded database into an SQL database, use one of the database conversion wizards.

  • Converting to MSSQL

  • Converting to Pervasive.SQL

  • Converting to DB2

  • Converting to ODBC

  • Converting to the Embedded Database


Configuring Database Drivers

Drivers for SQL Databases are implemented as .DLLs. Before you can use a database driver, Database Builder must load the driver software contained in the DLL.

Database Builder contains a 'Driver Administrator' which allows you to load drivers on-demand and to configure which drivers should be loaded automatically when you start Database Builder.

Select 'Configure Drivers' from the Tools menu. This activates the Driver Administrator dialog.

The Driver Administrator dialog is divided into two panels:

  1. The 'Driver Overview' (left) panel. This is a treeview that lists each driver and indicates whether that driver is currently loaded.

  2. The 'Driver Details' (right) panel. This is used to edit the configuration of the currently highlighted driver in the driver overview.

Driver Overview

This treeview lists each driver that is installed with Visual DataFlex and indicates whether that driver is currently loaded. When you select a driver in the treeview, detailed information about that driver is displayed in the driver details panel.

The bottom node in the treeview is labeled 'Other Drivers'. This item is used for loading third-party drivers.

Below is a guide to the icon symbols used to represent each driver.

This symbol indicates that the driver is currently loaded.

This symbol indicates that the driver is not currently loaded.

Driver Details

Driver Name

This is the name of the driver's .DLL file (without the .DLL extension). This name is used in VDF commands that require a driver name parameter. You cannot edit this value.

Load On Startup

Check this item to ensure that the driver will be loaded automatically whenever Database Builder is started.

Special Note

  • This makes an entry for the driver in the global \usr\dfini.ini file. This setting will cause the driver to be automatically loaded for every VDF application that you run.

Load Now

Click the 'Load Now' button to load the selected driver now. You would use this button to load a driver on an as-needed basis.

Advanced…

Click the 'Advanced' button to activate the Advanced Driver Options dialog. This dialog is used to edit the drivers global configuration .INI file.

Other Drivers

Whenever you select the 'Other Drivers' item in the driver overview, a list of all 3rd party drivers that are currently loaded is displayed in the driver details panel.

You can load additional 3rd party drivers by selecting the 'Other Drivers' item in the overview, then clicking the 'Load Now' button. In this case, the 'load now' button activates the open file dialog. You will select the 3rd party driver's .DLL file from this dialog in order to load the driver.

OK

Click 'OK' to close this panel.

See Also

Connecting to SQL Databases

Advanced Driver Options


Advanced Driver Options

The Advanced Driver Options dialog is used to edit the global configuration files for each driver. To activate this dialog, you should select a driver in the Driver Administrator dialog, then click the 'Advanced…' button.

The name of the configuration file that you are editing is displayed in a status panel at the bottom of the dialog.

Refer to the specific driver's User Guide document for details of the configuration settings available for each driver.

OK

Click OK to save changes to the configuration file and close this dialog. Your changes will not be applied until the next time the driver is loaded.

Cancel

Click cancel to close the dialog without saving your changes.

See Also

Connecting to SQL Databases

Configuring Database Drivers


Configuring a Database Connection

When you run the Studio's 'New Workspace' wizard, you are prompted to configure a connection to some database. This becomes the 'default' connection settings for the workspace.

You can adjust, or change these connection settings by running the 'Configure Database Connection' wizard. To start this wizard, select 'Configure Database Connection' from the Workspace menu.

Follow the Wizard Prompts to configure the default connection for the current workspace.

Special Note

The connection settings are stored in the 'connection.ini' file in the workspace's DDSrc folder.

See Also

Configuring Database Drivers

Pervasive Connection Properties

MSSQL Connection Properties

ODBC Connection Properties

DB2 Connection Properties


Login and Logout

Some client-server database drivers (such as Microsoft SQL Server and DB2) require that you log in to the database for each session of work that you do. To log in or log out of a database, select Login or Logout from the Database menu.

See Also

Connecting to SQL Databases


Connecting to an MSSQL Database

It is recommended that you use the MSSQL Enterprise Manager tool for creating and maintaining MSSQL databases.

Before you can access the tables of an MSSQL Database in a VDF Workspace, you will need to create 'table descriptions'. Table descriptions are stored in .INT files in the workspace's Data folder. One .INT file is maintained for each table that you will use in the workspace.

Database Builder contains a wizard that can build these .INT files for you. This is the recommended way to describe your connection to an MSSQL database.

To start the wizard, select 'Connect to MSSQL Database' from the Database menu. Follow the wizard prompts on each page to complete the connection.

Updating the Table Descriptions

Each time that a database is modified, e.g. a table structure is changed, or a new table is added, etc, you should run the connection wizard to import the necessary changes into the VDF table descriptions.

See Also

Configuring a Database Connection

MSSQL Connection Properties

Connecting to SQL Databases


Connecting to a DB2 Database

It is recommended that you use DB2 native tools for creating and maintaining DB2 databases.

Before you can access the tables of a DB2 Database in a VDF Workspace, you will need to create 'table descriptions'. Table descriptions are stored in .INT files in the workspace's Data folder. One .INT file is maintained for each table that you will use in the workspace.

Database Builder contains a wizard that can build these .INT files for you. This is the recommended way to describe your connection to a DB2 database.

To start the wizard, select 'Connect to DB2 Database' from the Database menu. Follow the wizard prompts on each page to complete the connection.

Updating the Table Descriptions

Each time that a database is modified, e.g. a table structure is changed, or a new table is added, etc, you should run the connection wizard to import the necessary changes into the VDF table descriptions.

See Also

Configuring a Database Connection

DB2 Connection Properties

Connecting to SQL Databases


Connecting to an ODBC Database

It is recommended that you use the native database tools for creating and maintaining SQL databases.

Before you can access the tables of an SQL Database via ODBC in your VDF Workspace, you will need to create 'table descriptions'. Table descriptions are stored in .INT files in the workspace's Data folder. One .INT file is maintained for each table that you will use in the workspace.

Database Builder contains a wizard that can build these .INT files for you. This is the recommended way to describe your connection to an SQL database via ODBC.

To start the wizard, select 'Connect to ODBC Database' from the Database menu. Follow the wizard prompts on each page to complete the connection.

Updating the Table Descriptions

Each time that a database is modified, e.g. a table structure is changed, or a new table is added, etc, you should run the connection wizard to import the necessary changes into the VDF table descriptions.

See Also

Configuring a Database Connection

ODBC Connection Properties

Connecting to SQL Databases


Converting to MSSQL

Database Builder provides a wizard for converting tables into an MSSQL database from any other supported database type. This wizard will convert the table structures from the current workspace's database into an MSSQL database, then move all of the data.

To convert tables from the current workspace's database into an MSSQL database, select 'Convert to MS SQL Server' from the Database menu. This brings up the Conversion Wizard.

Press Next and the ‘Select source tables’ page will be displayed where you should select the tables that you wish to convert.

The checkbox above the list of tables – ‘Delete original table after conversion of table successfully completed’ – should be selected if you would like to delete the original tables after successful conversion. If any errors occur during conversion, the original tables will not be removed regardless of the selection you make here.

Once you have selected the tables, click the Next button so the wizard analyzes the selected tables. Click on Next again to continue with the conversion options.

For recnum tables, the ‘Define recnum table conversion’ page will be displayed listing all the tables identified as recnum tables. The checkbox ‘Convert recnum tables to standard tables’ lets you choose whether you want to keep the table as a recnum table or not. If you choose to convert the tables to standard tables, you will need to select the indexes for all the tables listed.

After that, click on Next to move on to the ‘Define Conversion Options’ page. On that page, enter the following:

Server

Enter the location of the server where the MSSQL database you want to access is located.

Database

Enter the name of the database you are connecting to.

User

Enter the user name to be used for authentication when you log on to the MSSQL database.

Password

Enter the password for your user name.

Owner

Enter the name of the table owner to be used for the tables being converted.

Use NT Authentication

Click Use NT Authentication to specify that the provider request a secure (or trusted) connection to the MSSQL Server. When selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The database administrator must have associated your Windows network ID with a SQL Server login ID.

Store Data in ANSI Format

This option sets the table character format of the resulting table. If checked, data will be stored in ANSI format. If not checked, data will be stored in OEM format.

Convert Definition Only

Check this option if you want to create the tables in MSSQL but do not want to have your data copied over to the MSSQL database.

See Also

Configuring a Database Connection

MSSQL Connection Properties

Connecting to SQL Databases


Converting to Pervasive.SQL

Database Builder provides a tool for converting tables into a Pervasive.SQL database from any other supported database type. This tool will convert the table structures from the current workspace's database into a Pervasive.SQL database, then move all of the data.

To convert tables from the current workspace's database into a Pervasive.SQL database, select 'Convert to Pervasive.SQL' from the Database menu. This activates the Table Selection Dialog.

Select the tables that you wish to convert, then click the 'Convert' button. This activates the 'Convert to Pervasive.SQL' dialog. Enter the following selections.

Server

Enter the server name, or host, where the PSQL database is located.

Database

Enter the name of the database that needs to be accessed.

User

Enter the user name to use for authentication when you log on to the PSQL database.

Password

Enter the password for your user name.

URI

URI (Uniform Resource Indicator) is used to specify in what Pervasive.SQL server and database the table is located. The format of an URI is btrv://psquser@PervasiveServer/DatabaseName?pwd=***

The URI will be generated to reflect the typed server, database, user and password.

Delete Original After Conversion

Select this option if you would like to delete the original tables after successful conversion. If any errors occur during conversion, the original tables will not be removed regardless of the selection you make here.

Recnum Support

Select this option if you need the converted tables to keep the same recnum values as the original tables.

Owner

Owner Name

This is used to restrict access to the tables. If a table is converted using an owner name, applications will have to pass the owner name in order to have full access to the table.

Read Only Access

If this option is checked, the application that does not pass the correct owner name will have read-only access to the tables.

This option is only available if an owner name is set.

Encrypt Data

If this option is checked, the data in the tables will be encrypted using the owner name specified. Applications that do not pass the correct owner name will not be able to read the data stored in the tables.

This option is only available if an owner name is set.

See Also

Configuring a Database Connection

Pervasive.SQL Connection Properties

Connecting to SQL Databases


Converting to DB2

Database Builder provides a wizard for converting tables into a DB2 database from any other supported database type. This wizard will convert the table structures from the current workspace's database into a DB2 database, then move all of the data.

To convert tables from the current workspace's database into a DB2 database, select 'Convert to DB2’ from the Database menu. This brings up the Conversion Wizard.

Press Next and the ‘Select source tables’ page will be displayed where you should select the tables that you wish to convert.

The checkbox above the list of tables – ‘Delete original table after conversion of table successfully completed’ – should be selected if you would like to delete the original tables after successful conversion. If any errors occur during conversion, the original tables will not be removed regardless of the selection you make here.

Once you have selected the tables, click the Next button so the wizard analyzes the selected tables. Click on Next again to continue with the conversion options.

For recnum tables, the ‘Define recnum table conversion’ page will be displayed listing all the tables identified as recnum tables. The checkbox ‘Convert recnum tables to standard tables’ lets you choose whther you want to keep the table as a recnum table or not. If you choose to convert the tables to standard tables, you will need to select the indexes for all the tables listed.

After that, click on Next to move on to the ‘Define Conversion Options’ page. On that page, enter the following:

Data Source

Enter the DSN that identifies the server and the DB2 database where you want to place the tables you are about top convert.

Login ID

Enter the user name to be used for authentication when you log on to the DB2 database.

Password

Enter the password for your user name.

Schema

Enter the name of the table schema to be used for the tables being converted.

Table space for:

The Conversion Wizard allows you to set 3 different table spaces (refer to your DB2 documentation for more details on how to configure and manage database table spaces):

Table data

Determines the table space to be used to store your data.

Index data

Determines the table space to be used to store your indexes.

Long data

Determines the table space to be used to store long or LOB table columns.

Store Data in ANSI Format

This option sets the table character format of the resulting table. If checked, data will be stored in ANSI format. If not checked, data will be stored in OEM format.

Convert Definition Only

Check this option if you want to create the tables in DB2 but do not want to have your data copied over to the DB2 database.

See Also

Configuring a Database Connection

DB2 Connection Properties

Connecting to SQL Databases


Converting to ODBC

Database Builder provides a wizard for converting tables into an ODBC database from any other supported database type. This wizard will convert the table structures from the current workspace's database into an ODBC database, then move all of the data.

To convert tables from the current workspace's database into an ODBC database, select 'Convert to ODBC’ from the Database menu. This brings up the Conversion Wizard. Press Next and the ‘Select source tables’ page will be displayed where you should select the tables that you wish to convert.

The checkbox above the list of tables – ‘Delete original table after conversion of table successfully completed’ – should be selected if you would like to delete the original tables after successful conversion. If any errors occur during conversion, the original tables will not be removed regardless of the selection you make here. Once you have selected the tables, click the Next button so the wizard analyzes the selected tables. Click on Next again to continue with the conversion options.

For recnum tables, the ‘Define recnum table conversion’ page will be displayed listing all the tables identified as recnum tables. The checkbox ‘Convert recnum tables to standard tables’ lets you choose whther you want to keep the table as a recnum table or not. If you choose to convert the tables to standard tables, you will need to select the indexes for all the tables listed.

After that, click on Next to move on to the ‘Define Conversion Options’ page. On that page, enter the following:

Data Source

Enter the data source names (DSN) of the data source you are connecting to. The prompt button activates a pick list of system and user DSNs.

Login ID

Enter the user name to use for authentication when you log on to the database.

Password

Enter the password for your user name.

Schema

Enter the name of the table schema to be used for the tables being converted.

Show user data sources

Check this option to display all your user DSNs in the Data Source combo box.

Show system data sources

Check this option to display all your system DSNs in the Data Source combo box.

Store Data in ANSI Format

This option sets the table character format of the resulting table. If checked, data will be stored in ANSI format. If not checked, data will be stored in OEM format.

File DSN

Click on this button to select the file (.DSN) where the data source is specified.

See Also

Configuring a Database Connection

ODBC Connection Properties

Connecting to SQL Databases


Converting to the Embedded Database

Database builder provides a tool for converting tables into the embedded database from any other supported database type. This tool will convert the table structures from the current workspace's database into an embedded database, then move all of the data.

To convert tables from the current workspace's database into an embedded database, select 'Convert to Embedded' from the Database menu. This activates the Table Selection Dialog.

Select the tables that you wish to convert, then click the 'Convert' button. This activates the 'Convert to Embedded Database' dialog. Enter the following selections.

Keep Identity

Select this option if you want to keep the record identity field of the table as part of its structure once it is converted to DataFlex.

Up to version 2.2 of the Connectivity Kits a new column – DFRECNUM – was created in the table being converted. When converting that kind of table back to DataFlex you have the option of keeping that column as part of your DataFlex table structure.

Delete Original After Conversion

Select this option if you would like to delete the original tables after successful conversion. If any errors occur during conversion, the original tables will not be removed regardless of the selection you make here.

Convert Definition Only

Check this option if you want to create the tables in DataFlex but do not want to have your data copied over to the table.

Run Unattended

Select this option if you prefer to have a log file (DATAFLEX.LOG) containing the results of the conversion created. Should errors occur during conversion, they will be written to the log file. The file will be placed in the current working directory (i.e. the data directory of the current workspace). If there is already a file with this name in that directory, the log information will be appended to the existing file.

If the option is not checked, error messages will pop up if errors occur during conversion.

Transaction Type

Select a transaction type parameter for each table to be converted. Refer to Transaction Type for more information.

Compression

Select the data compression type to be used for each table. Refer to Compression for more information.

See Also

Configuring a Database Connection

Connecting to SQL Databases


Database Maintenance

  • Repairing Indexes

  • Cleaning out Bad Data

  • Changing Lock Modes and Transaction Types

  • Repairing Corrupt Table Headers

  • Compressing Data

  • Creating Empty Data Dictionaries

  • Upgrading Embedded Database File Revisions

  • Clearing Data

  • Deleting Database Files

  • Maintaining Filelists

  • Copying Rows


Repairing Indexes

Special Note

This option is only relevant for the Embedded database.

Database Builder has the ability to rebuild a table’s indexes in the event that they may become corrupted. To rebuild a table’s indexes, select Reindex from the Maintenance menu or click the Sort Files button on the tool bar.

A table-selection panel pops up. Select the tables you wish to re-index, then click on the Sort button. This pops up the Rebuild Database Indexes panel. The panel is divided into the tab pages Rebuild selector and Options.

Rebuild Selector

This tab page shows a list of each selected table and its indexes. The indexes to be sorted can be selected by clicking their check boxes in the list. There are buttons for selecting indexes for the current selected table (row-selection buttons) and for all tables (table-selection buttons). There are separate buttons for selecting the on-line or batch indexes as groups.

The sort can be started by clicking the Rebuild button or F2.

Options

On this tab page, there is a checkbox for detecting bad rows. If this is checked, then you have the following choices for what to do when invalid data is detected.

Invalid Data

Option | Action
Write to .BAD file for cleanup | You can specify that when you re-index, a file will be created to contain the data from rows containing bad index data. It has the same rootname as the table being re-indexed, and the extension .BAD.
Abort rebuilding indexes | Select this if you want the re-index process to be aborted in the event rows are found containing bad data.
Overwrite data with space/zeroes | Select this if you want any row with bad data to be retained but columns with bad data to be emptied (filled with spaces or zeroed as appropriate to the type of the column).

Duplicate Records

There is also a checkbox for what to do if duplicate rows are detected. If duplicate rows are found with the same data in the columns making up the index, obviously something is wrong. The index may have been set to batch before data entry, or it may have been corrupted by a power failure, or there may some other cause.

See Also

Database Maintenance


Cleaning out Bad Data

Special Note

This option is only relevant for the Embedded database.

If duplicate rows are found in a re-index process with the same data in columns which should have unique values in every row, a file containing details of those duplicate rows can be requested. This option uses that file, (which has the same rootname as the table and the extension .BAD), to allow you to point and select the row to be kept, and to delete all the other duplicates.

To initiate the cleaning process, select Cleanup from the Maintenance menu. This pops up a file-selection panel.

  • First of all, the files to clean up must be selected. Provided there are duplicate rows in a .BAD file in the current directory for that file, you will see a screen with four boxes; Select row to keep, Row To Keep, Current Row and Delete status (if the .BAD file does not exist, you will see an error message).

  • The row numbers of the first set of duplicate rows are shown in the Select row to keep list and the first of those row numbers is marked (meaning it is the present Row To Keep). Its number is noted in the form labeled Keeping:, and details of its columns are displayed in the Row To Keep box. The total number of duplicate rows in that set are noted in the form labeled Duplicates:.

  • The selection cursor starts out on the row number of the first duplicate (the next row with the same key data). Details of its columns are displayed in the Current Row panel.

  • If there are too many columns to display at once, you can press the PGUP and PGDN keys to scroll the row-panel displays.

  • As you move the selection cursor down the list of duplicates, the Current Row panel changes to reflect the details of the row under the cursor, but the Mark doesn't move. To select a different row as the Row To Keep, move the selection cursor to its row number in the Select Rows to Keep column and press spacebar. That row number will be marked, and the Row To Keep panel will change to contain the details for that row.

  • Press OK to start the deletion process for the current duplicates, and all the duplicates except the one marked as the Row To Keep will be deleted. The same routine will be repeated for the next row that has any duplicates, so you may continue with this process until all the duplicates in the file have been deleted.

  • Press Skip to skip the current set of duplicates, without deleting any of them, and move to the next.

  • Press Auto to automatically delete all but the first row in all sets of duplicates.

  • Press Exit to exit the cleanup function.

  • If the process has not been completed (a row in the .BAD file still has a duplicate), you will see a warning message that the table is unusable until the process is completed.

  • When Cleanup has finished, the .BAD file is deleted.

See Also

Database Maintenance


Changing File Lock Mode and Transaction Type

Changing Lock Modes and Transaction Types

Database Builder provides a convenient method for editing the Lock Mode and Transaction Type table attributes of multiple tables in one simple operation. This is the same as opening each table and modifying the Lock Mode and Transaction Type in the Parameters tab page, only quicker.

To start a session of changing these table attributes, select Attributes from the Maintenance menu. Use the table-selection panel to select the tables whose attributes you want to change, then click the Attributes button to begin editing.

You can change the settings for either the lock or transaction type for all tables that have their settings displayed. If a table or its driver doesn't support a value (such as row locking), its database driver will assign the "closest" available choice instead.

See Also

Database Maintenance


Repairing Corrupted Table Headers

Repairing Corrupt Table Headers

Special Note

This option is only relevant for the Embedded database.

Under certain circumstances, a program crash can cause the header portion of your tables to become corrupted. Table-header corruptions can cause further damage to your data and indexes and must be addressed.

If header-integrity checking is enabled in a table's parameters , then Visual DataFlex will display an error message and stop the application from accessing the table while it is corrupted.

Database Builder can repair corrupted table headers provided header-integrity checking is enabled in a table's parameters. To start a repair operation, select Repair from the Maintenance menu, select the tables that you wish to perform a repair operation upon and press the Repair button.

Where possible, each table is repaired. If a table needs no repair, you will see a message to that effect. If an .HDR file cannot be found, or if header-integrity checking is not enabled for a table, you will see a message to that effect, and nothing is done to that table.

When you elect to repair a table, its header is subjected to two separate CRCs (Cyclic Redundancy Checks), and the results are compared with CRC numbers maintained in the header. If one or both checks fail, the repair process is started.

See Also

Database Maintenance


Compressing Data

Special Note

This option is only relevant for the Embedded database.

Tables that use custom compression for compressing data need to pass through a re-compressing process from time to time to ensure that the data is being efficiently compressed. It is recommended that you rebuild custom compression tables every six months or so (if the data is changing, of course).

To initiate a table re-compression, select Recompress from the Maintenance pull down menu, select the tables that you wish to re-compress, then click the Recompress button.

The option will be presented whether to use a temporary table during compression or to "attempt in place". Using a temporary file is far safer than attempting in place, as any system failure during such an attempt will leave the table corrupted. In-place re-compression should be attempted only on tables that are currently backed up. If the selected table is not custom-compressed, an error message will report this fact.

See Also

Database Maintenance


Create Empty Data Dictionary

Creating Empty Data Dictionaries

This option is for creating empty Data Dictionaries for tables that do not already have them. It is designed primarily for users who migrate applications from a Character-Mode environment.

To start the process, select Create Empty Data Dictionary from the Maintenance menu. A list of tables in the workspace’s Filelist appears, and you can re-order the display by clicking on any column heading, after which an asterisk appears in that heading, and the display is re-ordered according to the data in that column.

Check the tables for which you want to create empty Data Dictionaries, then press the "Create…" button. Empty Data Dictionaries are written into the DDSRC directory of your current workspace. If Data Dictionaries already exist for any tables check, they will not be overwritten, and empty Data Dictionaries will not be written for those tables.

See Also

Database Maintenance


Upgrading DataFlex Database-File Revisions

Upgrading Embedded Database Table Revisions

Special Note

This option is only relevant for the Embedded database.

For users who have embedded database tables that are still in 2.3 format, there is a conversion function that converts your tables to the DataFlex 3.0 database version. To convert your data, select Convert DF2.3 to 3.0 from the Database menu. This pops up the table-selection panel. Select the tables that you want to convert, then press the Convert to 3.0 button.

See Also

Database Maintenance


Clearing Data from Database Files

Clearing Data

To clear data from a table, choose Erase... from the Filelist pull down menu. Select the tables to clear, then press the Erase button to clear them.

Use this when you want to delete all the data in the selected tables. This could come in handy when you want to delete sample data, and bring the system up under actual conditions, or when an end-of-period emptying of a table is necessary. This is a powerful command. You will be asked to confirm that you actually do want to erase the data in the table before it executes. After the data has been erased, the empty table remains.

See Also

Database Maintenance


Deleting Tables

To delete a table, choose Delete from the File menu. Select the tables to delete, then press the Delete button to delete them.

When you select Delete, a confirmation box appears, asking if you wish to permanently remove the selected table(s).

All the .DAT files that have any of the root names of the selected tables are deleted, together with their .FD, .TAG, and .K files, and their Filelist entries.

The data-dictionary file may also be deleted. This depends upon the Database Builder configuration setting that controls deleting data-dictionary files (see "Configuring Database Builder" for more information).

The only associated files not deleted are the .DEF files; these remain even after the use of this command.

See Also

Database Maintenance


Maintaining File Lists

Each workspace maintains a central registry that lists all of the tables assigning a table number to each one. This is known as the DataFlex file list. The file list is located in a document named filelist.cfg in the workspace’s Data directory.

Database Builder provides several tools for maintaining and editing the file list. You can open a specific file list, create a new Filelist document, Save as, remove tables from the list, and move tables to a different table number.

Open Filelist

To open a different Filelist document than the standard filelist.cfg in the workspace’s Data directory, choose Open Filelist from the Filelist menu. This pops up the open-file dialog for opening a Filelist document. Use this dialog for finding the Filelist file to open.

Selecting a new file list changes the current file list throughout Database Builder. For example, the list of tables to open is determined by the current file list, so this function actually determines the operating scope of this tool.

Create Empty Filelist

To create a new Filelist document, choose Create Empty Filelist from the Filelist menu. This pops up the save-file dialog for creating a new Filelist document.

The new file list will be empty of registered tables regardless of whether there are tables that you have created in the workspace. The new file list will become the current table list throughout Database Builder.

Save Filelist as

To save a copy of the current table list under a different name, choose Save Filelist as… from the Filelist menu. This pops up the save-file dialog for saving the new Filelist document.

Remove Entry

To remove tables from the file list, choose Remove Entry from the Filelist menu. This pops up the table-selection panel. Select the tables to remove from the table list, then press Remove to remove them. Before any entry is removed, you will see a confirmation message, so you will always have an opportunity of changing your mind.

This choice does not delete any actual tables, just the Filelist entries.

New Entry

To create a new entry in the file list, choose New Entry from the Filelist menu. This pops up the Create New Filelist Entry panel.

The next available table number in the list will appear by default, but you can change that if you wish.

Enter a file name. The file name you enter will be repeated for your convenience in the User Display Name and DataFlex Name windows, but you can change those if you wish. Press OK to add the entry to the current file list.

This process does not create any tables; it just adds the entry for a table into the current file list. It also does not check for duplicate names, since this might be what you want.

Related Topics

Connecting with Other Database Systems

Edit entry

To edit the table information in the filelist, choose Edit Entry from the Filelist menu. This pops up the table-selection panel. Use this panel to select the tables that you want to edit, then press the Edit button. This now pops up the Edit Filelist Entry panel for each table you selected.

The selection cursor bypasses the table number, since that is what you are working on; it cannot be changed from this list. All other information on the panel can be edited.

File Name

The present file name is shown, and you may edit it in any way you wish, including adding an explicit drive and path. If you change the file name to a value that does not indicate the actual location of the tables in the directory, programs may not be able to use such tables.

User Display Name

The present user-display name is shown, and you may edit it in any way you wish. This will affect its display in various Visual DataFlex tools such as Database Explorer.

DataFlex Name

The present DataFlex name is shown, and you may edit it in any way you wish. Be aware, however, that if you change the DataFlex name, and the old name appears in the source code of a program being compiled, the program will be unable to compile, or may even find the wrong table.

Move entry

To move tables in the file list from one table number to another, choose Move Entry from the Filelist menu. This pops up the table-selection panel. Use this panel to select the tables that you want to move, then press the Move button. This now pops up the Move Filelist Entry panel for each table you selected.

The number of the table to be moved (the "source table") is shown in the Source form. Enter the number that you want to move the table to in the Destination and press OK to accept.

You should be aware (you will see a warning), that if you change the table number, but do not change the .FD file, programs already compiled that reference that table will no longer find it. If you subsequently move another table to this number, or create a new one under it, there are likely to be unwanted effects in your program (if it runs at all).

To overcome this problem, you should output new .FD files for each table that was moved then re-compile your applications.

Related Topics

Outputting DEF Files

File Extensions

See Also

Database Maintenance


Copy Rows

Copying Rows

This option allows you to copy rows from one table to another, providing that both table already exist (although entries in the Filelists do not have to exist). The process will copy rows from matching column names only.

To copy rows, select Copy Rows from the Maintenance menu. Select the table from which rows are to be copied and click Open. You may select a table from a different workspace by navigating to it, but don’t forget to navigate back in the next dialog, where you select the table to which you want the rows copied, then click Open.

New rows are appended after any rows already in the receiving table, and duplicate rows will not be copied. A file named CopyRec.log is written to the Data directory of the receiving table’s workspace to show which rows were copied and any errors.

See Also

Database Maintenance


General Topics

  • Administration Modes

  • Alias Tables

  • Configuring Database Builder

  • File Extensions

  • File Relationships

  • Data Dictionary File Structure

  • Database Reports


Administration Modes

Database Builder also supports being run in three different Administration Modes that are user-configurable via a system registry setting. Each Administration Mode enables or disables various functions in Database Builder.

To change mode, you must edit the following registry setting.

Defaults\dbAdminMode

This can be found under the Visual DataFlex key in

hkey_local_machine\Software\Data Access Worldwide\Visual DataFlex\revision

Valid options for this registry setting are:

End User Mode

In this mode, Database Builder cannot modify any table structures, indexes, or data dictionaries. You can only print table status reports and access the table-maintenance options.

End User Mode allows your application users to access functions such as re-indexing, repair data, and other maintenance functions. Users will be locked out of all other functions.

To place Database Builder in End User Mode, set the dbAdminMode variable as follows:

dbAdminMode = OFF

Database Mode

In this mode, Database Builder can modify table structures and indexes, but not data dictionaries. You also have full access to table status reports and the table-maintenance options.

Database Mode gives you access to everything except the data-dictionary-building capabilities.

To place Database Builder into Database Mode, set the dbAdminMode variable as follows:

dbAdminMode = ON

Full Mode

Full mode gives you full access to all of Database Builder’s capabilities.

To place Database Builder in Full Mode set the dbAdminMode variable as follows:

dbAdminMode = DDB

See Also

General Topics


Alias Tables

Application requirements arise where the same physical table is needed to be used more than once within a single view’s table structure. This occurs when the same physical table serves more than one logical purpose. Some examples of this are:

  • A single order requires two addresses – a bill-to address and a ship-to address. Both of these address rows are maintained in the same table. In this case, a child row relates to the same physical row two times.

  • You maintain a list of service technicians. Each technician is assigned a backup technician (who is called when the primary technician is unavailable). Both main and backup technician rows are stored in a single table. In this case, a physical row must relate to itself.

  • A single order requires two addresses – a bill-to address and a ship-to address. Both of these address rows have a parent row of their own (region) which will be different for each address row (bill-to region and ship-to region). In this case, a child row relates to the same physical parent row two times which, in turn, will relate to the same physical grandparent row two times.

In all of the above cases, each of the logical tables is unique and serves a distinct purpose in your table hierarchy. The last example would be structured as follows:

The tables, while logically distinct, need to be mapped to the same physical table.

A technique called alias tables supports this ability. An alias table is created by placing the same physical table in multiple locations in your file list. An alias Filelist entry is created using the same physical name as the main table (File name) while creating unique logical names (DFName and User Name). When the tables are opened within the same program, a separate table buffer is created for each logical table. Data from each table is read and written to the same physical table.

Using alias tables creates certain multi-user challenges. The same physical table cannot be locked twice. To avoid this condition, you must identify logical tables sharing the same physical table as one master table and the remainder, an alias table (or tables). This identification technique (set_attribute, DF_FILE_ALIAS ) is described below.

Alias Table Tips

  1. Once an alias table is created and a data-dictionary for that alias is created, it is treated (almost) like a normal table. When Visual DataFlex builds your views, it will treat the alias table like any other table in your list.

  2. Try to keep the alias table simple. Ideally an alias table should be read-only. When a child row is saved, the child should avoid changing data in the alias (which can happen through the child data-dictionary’s Update and Backout procedures).

  3. Master and alias tables will each have their own data-dictionary and each of these DDs will be different. Usually, the alias data-dictionary will be much simpler than the master data-dictionary.

  4. Do not try to make master and alias tables share the same lookup object. If an alias table requires a lookup object, create a separate lookup object for the alias table.

  5. If you change the master table’s structure, you will also change the alias table’s structure. In such a case, you will need to reload the alias table’s data-dictionary and make the appropriate changes.

  6. If you delete an alias table, you will also delete the master table. Instructions for removing an alias table are provided at the end of this section.

  7. If alias tables are being written to during a save, you must be careful if the main and alias row happen to be the same physical row. If the row’s numerical balance is changed in the child’s data-dictionary Update or Backout procedure (e.g., Add 1 to Address2.OrderCount), an inaccurate balance may be created. It is up to you to protect against this

  8. Keep in mind that the use of alias tables is an advanced technique. A typical developer may never need to use this technique. Avoid using alias tables if you do not really need them.

  9. Do not use this as a method to see multiple sets of rows within the same view. For example, creating an alias table so you could view all open invoices and all closed invoices within the same view is an inappropriate use of this technique.

  10. You do not need to create an alias table to support diamond relationships. Consider the following example:

In a diamond relationship, the top of the diamond (A) will be the same parent for both child rows (B and C). If a row is found in the grandchild (D), the relate process will result in same parent row in A regardless of the finding path (D to B to A, D to C to A). These types of diamond relationships are directly supported by data dictionaries. Using an alias for this is unnecessary.

Creating an Alias File

An alias table is created by:

  1. Adding a new entry into file list.

  2. Creating an .FD file for the new alias.

  3. Opening the new alias table and creating a data dictionary.

  4. Setting the alias file’s and the main file’s attributes to the proper alias and master values

  5. Setting all additional relationships and relationship requirements.

The following provides full detail for completing each of these steps.

Create an entry in Filelist

From the Filelist menu, select the "New entry…" option. This will pop up the "Create a new Filelist entry" panel. Select an unused table number for the alias table. The File name must be the same name as the main table’s File name. This is what designates this as an alias table. Enter a unique User Name and DataFlex Name for this entry and press the Ok button.

At this point, you will now have two entries in your file list pointing to the same physical table. Each entry will have a unique logical name (DataFlex Name) and description (User Name)

Create an .FD file

Adding an entry to the file list does not create an .FD file for this new entry. The .FD must be manually created as follows:

From the File menu, select Output DEF/FD….

Select the alias table and press the Output DEF/FD button.

Make sure that the Output .FD checkbox is selected. The Output .DEF checkbox does not need to be selected. Press the Ok button.

The .FD for the alias table has now been created and placed in your DDSrc directory for the current workspace.

Load File and Create the DD

Before loading the alias table, make sure that the master table is not opened in exclusive mode . If it is, you will not be able to load the alias table. If the master table is currently opened in exclusive mode, you should either close it or reset it back to normal mode.

The first time you attempt to load the alias table, you will be informed that the data-dictionary file has not yet been created for this table. You will be asked if you wish to create a data-dictionary for this table. Answer yes.

You may now create any data-dictionary settings for this table. If your purposes allow it, attempt to make the alias table a "read-only" table.

Setting Alias File Modes

You will need to identify this table as an alias table and identify the original table as a master table. You can do this with the methods editor as follows:

Select the Methods tab page and navigate to the editor window.

At the top of your editor, add the following lines:

Open Main File Name

Set_attribute DF_FILE_ALIAS of Alias File Name.File_number ;
to DF_FILE_IS_ALIAS

Set_attribute DF_FILE_ALIAS of Main File Name.File_number ;
to DF_FILE_IS_MASTER

A typical code section might look like the following:

// You must tell DF about the Alias-Master condition

Open Customer// make sure that the main table is open

Set_Attribute DF_FILE_ALIAS Customer2.File_number to DF_FILE_IS_ALIAS

Set_Attribute DF_FILE_ALIAS Customer.File_number to DF_FILE_IS_MASTER

//

Class Customer2_DataDictionary is a DataDictionary

// Define_Fields:

// This procedure is used to set up all data-dictionary rules.

Procedure Define_Fields

:

You must make sure you mark your tables as master and alias. Normally both of these settings can be made within the alias table’s data-dictionary. This makes good sense because the main, non-alias, table does not need to be recognized as a master until an alias table is used. We add the open command to make sure that the master table is already opened (the table cannot be designated a master table if it is not open). This way, you do not have to worry about the ordering of tables in your program.

Setting Alias-File Relationships

Creating relationships to an alias table (where the alias is defined to be a related-to parent) is easy. After an alias table has been properly created, child tables may relate to the alias as they would to any other normal table. This is how alias tables are normally used.

If your master table relates to other tables (i.e., it is the child of a relates-to relationship) or your alias tables need to relate to a parent table, special care must be taken. Relationship information is stored in the physical table’s header. If a relationship is created in the master table, that relationship will also exist in the alias table. Similarly, if you change a relationship in the alias table, that change will also be made for the master table. You are changing the same physical table structure. Any change in one table affects the other.

Normally, you will not want a master and alias row to always point to the same physical parent. If relationships exist in the master table, you will want to clear them or change them in the alias. While this cannot be done by changing the relationship in the table, it can be done at runtime using the set_relate command. The set_relate command is "soft". It only changes relationships at run time—which is exactly what is needed with alias tables. The set_relate command will be added to your data-dictionary in the top of the methods section.

Clearing a Relationship in an Alias File

An alias file will usually not require all of the same parent files that its master-file counterpart does. Alias files are often read-only and will have no required parents. When the alias file is opened, it inherits all of the file relationships of its master. These can be cleared by using the set_relate command as follows:

Set_Relate Alias_File.Field_Name to |FN0,0

Where:

Alias_file.Field_Name is the name of the relating-from field to set.

FN0,0 is a special symbol that indicates that the relationship should be cleared.

Establishing a New Relationship in an Alias File

Sometimes an alias file will need to relate to a different parent file. Most-likely, the different parent file will be an alias file itself. The new relationship would be set using the set_relate command as follows:

Set_Relate Alias_File.Field_Name to ParentFile.Field_Name

Where:

Alias_file.Field_Name is the name of the relating-from field to set.

ParentFile.Field_Name is the name of the parent file and field to relate to.

Setting Data-Dictionary Required-Parent Lists

If any new relationships are established, you must remember to adjust your data-dictionary required-parent-file-structure list to reflect these changes. An alias file will almost always have a relating-to child file (this is why you created the alias file). If a hard or soft relationship is created between the child and the alias parent, this dependency must be added to the child’s "Required-Parent" list. If the alias file relates to a parent file, this dependency should be added to the alias file’s "Required-Parent" list. The required structures are set within a DD using the Structure tab page.

Sample Alias Structures

Simple Alias Structure

A single order requires two addresses—a bill-to address and a ship-to address. Both of these address rows are maintained in the same physical file. The main address file is named Address. We will create an alias file named Address2. Assume that Address relates to a file named Region and that we will wish to remove this relationship in the alias. The steps for creating this alias file are:

Add the alias, Address2, to your file list, create the .FD and create the alias DD file.

In the Address2 methods section, define the master and alias files and clear the existing file relationship. The methods section for the Address2 alias file will look like this:

// You must tell DF about the Alias-Master condition

Open Address // make sure that the main file is open

Set_attribute DF_FILE_ALIAS Address2.File_number to DF_FILE_IS_ALIAS

Set_attribute DF_FILE_ALIAS Address.File_number to DF_FILE_IS_MASTER

// clear the relationship in the alias file

Set_relate Address2.Region_key to |FN0,0

//

Class Address2_DataDictionary is a DataDictionary

:

In the child file (Order) that relates to the Address2 alias file, create a "hard" (Database Builder) or soft (set_relate) relationship to this file. In the Order data-dictionary Structures tab page, make sure that both files, Address and Address2, are added to the required-parent list.

Recursive Alias Structure

You maintain a list of service technicians. Each technician is assigned a backup technician (who is called when the primary technician is unavailable). The main file is Tech, the alias file will be named TechBkup. The steps for creating this alias file are:

Add the alias, TechBkup, to your file list, create the .FD and create the alias data-dictionary file.

In the TechBkup methods editor, define the master and alias files. The methods editor for the alias file will look like this:

// TechBkup

// You must tell DF about the Alias-Master condition

Open Tech // make sure that the main file is open

Set_attribute DF_FILE_ALIAS TechBkup.File_number to DF_FILE_IS_ALIAS

Set_attribute DF_FILE_ALIAS Tech.File_number to DF_FILE_IS_MASTER

//

Class TechBkup_DataDictionary is a DataDictionary

:

In Tech create a soft (set_relate) relationship to the alias file. The must be a soft relationship because a hard relationship would appear in both the main and alias file. This would be done as follows:

// Tech

// define soft relationship to alias file

Set_relate Tech.Bkup_Tech to TechBkup.Tech_Key

//

Class Tech_DataDictionary is a DataDictionary

:

In the Tech data-dictionary Structures tab page, make sure that TechBkup is added to the required-parent list.

Multi-level Alias Structure

A single order requires two addresses—a bill-to address and a ship-to address. Both of these address rows have a parent row of their own (Region), which will be different for each address row. The main files Address and Region already exist. We need to create two alias entries, Address1 and Region1, as follows:

Add the alias, Address2, to your file list, create the .FD, and create the alias data-dictionary file.

Add the alias, Region2, to your file list, create the .FD, and create the alias data-dictionary file.

In the methods section for Region2, define the master and alias files for region. The methods section for the Region2 file will look like this:

// Region2

// You must tell DF about the Alias-Master condition

Open Region // make sure that the main file is open

Set_attribute DF_FILE_ALIAS Region2.File_number to DF_FILE_IS_ALIAS

Set_attribute DF_FILE_ALIAS Region.File_number to DF_FILE_IS_MASTER

//

Class Region2_DataDictionary is a DataDictionary

:

In the methods section for Address2, define the master and alias files and set a relationship between Address2 and Region2. The methods section for the Address2 file will look like this:

// Address2

// You must tell DF about the Alias-Master condition

Open Address // make sure that the main file is open

Set_attribute DF_FILE_ALIAS Address2.File_number to DF_FILE_IS_ALIAS

Set_attribute DF_FILE_ALIAS Address.File_number to DF_FILE_IS_MASTER

// set the relationship between address2 and region2

Set_relate Address2.Region_key to Region2.Region_key

//

Class Address2_DataDictionary is a DataDictionary

:

In the Address2 data-dictionary Structures tab page, add Region2 to the required parent list.

In the child file (Order) that relates to the Address2 alias file, create a "hard" (Database Builder) or "soft" (Set_Relate) relationship to Address2.

In the Order data-dictionary Structures tab page, make sure that both files, Address and Address2, are added to the required parent list.

Deleting an Alias File

Remember that an alias and a master file share the same physical file. If you erase or delete an alias file, you will also erase or delete your main file. Normally you do not delete an alias file. You may remove an alias file as follows:

  1. Remove the alias file from your file list. From the Filelist menu select Remove Entry…

  2. Manually delete the alias .FD and .DD files from your disk. These should be found your DDSrc directory or the current workspace.

  3. Change any child files that related to this file. Remove any relationships from the file and remove any parent relationship requirements from their data-dictionary.

See Also

General Topics


Configuring Database Builder

Database Builder supports a number of user-defined configuration options. To edit these options, select Properties from the File menu. This pops up the Database Builder Options panel.

The Options panel is split into three tab pages; Data-dictionary, Application Builder, and Database Builder. These tab pages help to categorize the properties.

Data Dictionary

Create backup on overwrite

When this option is checked, Database Builder will create a copy of your old .DD source-code file before overwriting it with a new one. Note that each time a backup copy is made, the last backup copy is overwritten.

Backup Extension

This is the filename extension given to the backup of your .DD source-code file.

Delete along with database

When this option is checked, Database Builder will delete the .DD source file along with all the other database components when you select to delete a table.

Confirm delete

When this option is checked, you will be asked for confirmation before any .DD source file is deleted.

Generate comments with methods

When this option is checked, then descriptive comments will be generated with each new inherited method that is created in the methods editor.

DataDictionary Class

This is the name of the superclass to use when generating a data-dictionary subclass for a table. The default superclass is the standard DataFlex DataDictionary class, but you can use this setting to specify your own superclass.

Code Generation

Indent Spaces

This is the number of spaces used for auto indenting of text in the free-format editor. This option also affects the object editor in the Studio.

Database Builder

Open File in Normal Mode (Default)

This option configures the default Open Mode that Database Builder will use to open tables. Place a check in this box to open tables in non-exclusive mode, unchecked means that tables will be opened in exclusive mode.

Related Topics:

  • Open Modes

  • Opening a Table and Dictionary

  • Switching File-Open Modes

Special Note

  • You must re-load Database Builder before any changes will take effect.

  • The Configure panel in the Visual DataFlex Studio also has options for modifying all of the Database Builder user-defined options.

  • Database Builder also supports being run in three different Administration Modes. These are user-configurable via a system registry setting.

See Also

General Topics


File Extensions

Each embedded database table in your database actually stores its information in several files that all share the same root name but have different extensions. Each extension and its meaning is explained below.

.DAT

This is the table. The table stores the physical data. A special portion of this file is reserved for storing information about the structure of the data and the indexes of the table, called the header.

If compression is used in the table, then the data is removed from the .DAT and stored in a .VLD file (see below). Instead of data, the .DAT file now only contains pointers to the .VLD. For more information see "Compressing Data".

.DD

This is the data-dictionary source-code file. This file contains DataFlex source code for the table’s data-dictionary. This file is compiled into your application so that the table’s business rules can be applied.

.DEF

This is the table-definition file. This file contains a printable copy of the structure of the table and its indexes. This can be used for documentation and can also be used for rebuilding the structure of a table.

.DEF files are not automatically created or updated when a table is changed. You must specifically output .DEF files using the appropriate Database Builder option for this. See "Output .DEF Files" for more information.

.FD

This is the column-definition file. This file contains symbolic replacements for all of the columns in the table. This file is compiled into your application with the DataFlex Open command.

.FLT

This is a column-template file. A column template is a record of the structure and data-dictionary rules for a single table column.

The templates store information about the column's name, type, length, index, options, validation rules, lookup objects, mask, appearance and other data-dictionary attributes.

See Saving and Inserting Templates for more information.

.HDR

This is the header-backup file. This file stores an up-to-date copy of the structure information that is stored in the .DAT file (see above). A .HDR file is only maintained for tables that use header-integrity checking.

.K#

Are the index files. One .k file is created for each index. These are numbered according to the index number they represent i.e. CUSTOMER.K2 stores Index 2 for the customer table.

.TAG

This is the column tags file. This file stores all the column names.

.VLD

This is the variable-length-table. This file stores the data when the table is employing data compression.

See "Compressing Data" for more information.

Example

If the table name is customer, then the following files are created to store all the information:

CUSTOMER.DAT

CUSTOMER.DD

CUSTOMER.DEF

CUSTOMER.FD

CUSTOMER.HDR

CUSTOMER.K1

CUSTOMER.K2

CUSTOMER.K3

CUSTOMER.TAG

CUSTOMER.VLD

This example assumes that the customer table has 3 indexes, uses data compression, and file-header-integrity checking. Some of these files will only exist if certain settings are switched on (e.g. Compression and Header-Integrity checking). The .DEF is only created if the user runs the Output .DEF/.FD option.

See Also

General Topics


Table Relationships

Relationships are used to "Normalize" your data. Some of the goals of normalization are…

  1. To Eliminate Repeating Groups - Make a separate lookup file for each set of related attributes, and give each table a primary key. For example, you may be recording contacts that you have with your clients. You should not store the contacts in the client database. Instead you would move the contact information into a separate table and relate each row to the primary key in the Client table.

  2. To Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table. Let’s say each contact you have with a client is categorized (Telephone call, mail-out, personal visit, etc.). You should store the Contact-Types in a separate table and relate the Contacts to the contact-types.

  3. To Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table. For example, suppose you are storing the Client's Employer Name, Employer Address and Employer Phone Number. These attributes describe the client's place of work, not the client, so you should create an Employer table and remove the Employer information to this table relating to its Key from the customer table.

These are the first three forms of data normalization and are probably the ones you will apply most-commonly to your database.

See Also

General Topics


Data Dictionary File Structure (Overview)

Special Regions

Data-dictionary .DD source-code files must contain markers to divide the file into regions. These regions have special significance while the file is being analyzed by Database Builder.

The .DD source file should start with the symbol //DDB-FileStart. This informs Database Builder that the source file is correctly marked up and should be analyzed.

Alternatively you could start with the symbol //DDB-FileStartNoRead. This informs Database Builder that the source file is externally maintained and should not be analyzed by Database Builder.

The remainder of the .DD source file is split into special regions. Each region and the markers used to define its range are outlined below:

Header Information

This is the region of the .DD source file that contains some explanatory comments, Use commands for required packages, and all the necessary Open commands.

This region is mainly for documentation purposes. It is regenerated by Database Builder after each save and is ignored while the file is being analyzed. The following symbols are used to mark the Header Information region:

//DDB-HeaderStart

//DDB-HeaderEnd

Validation Tables

This is the region of the .DD source file where all of the validation-table objects, used by the DataDictionary class, are defined. The following symbols are used to mark the Validation Tables region. See also Validation Tables.

//DDB-ValidationStart

//DDB-ValidationEnd

Validation Custom Code

This is the region of the .DD source file where custom code may be contained. The following symbols are supported inside the Validation Region of a .DD file.

//DDB-ValidationCustomStart

//DDB-ValidationCustomEnd

These symbols are used to mark the beginning and ending of custom code that can be entered for each Validation Table object. The location of the markers is important, and they must be nested inside each validation table object that is controlled by the .DD file’s Validation Region. If they are moved outside the object then an error is raised whenever Database Builder parses the .DD file.

Selection Lists

This is the region of the .DD source file where all of the selection-list (lookup) objects, used by the DataDictionary class, are declared.

This section is located just above the data-dictionary class definition. The region contains code that allows the data-dictionary object to reference the lookup objects.

The following symbols are used to mark the Selection List region:

//DDB-SelectionStart

//DDB-SelectionEnd

Define Field Settings

This is the region inside the Define_Fields procedure which contains the data-dictionary property settings analyzed by Database Builder. The following symbols are used to mark the Define Field Settings region:

//DDB-Generated-Code-Location

//DDB-DefineFieldStart

//DDB-DefineFieldEnd

Field Default Settings

This is the region inside the Field_Defaults procedure which contains the field default settings analyzed by Database Builder. The following symbols are used to mark the Field Default Settings region:

//DDB-Generated-Code-Location

//DDB-FieldDefaultStart

//DDB-FieldDefaultEnd

Selection List Package

This is the region of the .DD source file that contains Use commands for including the external selection-list packages.

This region is located below the data-dictionary class definition so that objects of that class can be created and used inside the selection-list package.

The following symbols are used to mark the Selection List Package region:

//DDB-Selection-pkg-Start

//DDB-Selection-pkg-End

See Also

General Topics


Data Dictionary File Structure (Example)

The sample below is the sample Orderhea.dd source file correctly marked up to be analyzed by Database Builder.

//DDB-FileStart

//DDB-HeaderStart

// File Name : ORDERHEA.DD

// Class Name: Orderhea_DataDictionary

// Revision : 5

// Version : 2

Use Windows // Basic Definitions

Use DataDict.pkg // DataDictionary Class Definition

Use DDvalTbl // Validation Table Class Definitions

Open Orderhea

Open Orderdtl

Open Customer

Open Salesp

Open Ordsys

//DDB-HeaderEnd

//DDB-ValidationStart

Register_Object Terms_table

Register_Object Ship_Table

Object Terms_table is a DescriptionValidationTable

Procedure Fill_List

Forward Send Fill_List

Send Add_Table_Value "NONE" "None established"

Send Add_Table_Value "COD" "COD"

Send Add_Table_Value "NET30" "Net 30"

Send Add_Table_Value "NET60" "Net 60"

Send Add_Table_Value "NET90" "Net 90"

Send Add_Table_Value "PREPAY" "Pre-payment required"

End_Procedure // Fill_List

//DDB-ValidationCustomStart

//DDB-ValidationCustomEnd

End_Object // Terms_table

Object Ship_Table is a CodeValidationTable

Set Type_Value To "SHIPPING"

Set Allow_Blank_State To TRUE

//DDB-ValidationCustomStart

//DDB-ValidationCustomEnd

End_Object // Ship_Table

//DDB-ValidationEnd

//DDB-SelectionStart

//DDB/ ExternalSelectionList ORDERHEA_SL ORDERHEA.SL

Register_Object ORDERHEA_SL

//DDB-SelectionEnd

Class Orderhea_DataDictionary Is A DataDictionary

Procedure Define_Fields

Forward Send Define_Fields

//DDB-Generated-Code-Location

//DDB-DefineFieldStart

Set Main_File To Orderhea.File_Number

Set Foreign_Field_Options DD_KEYFIELD To DD_FINDREQ

Set Foreign_Field_Options DD_INDEXFIELD To DD_NOPUT

Set Foreign_Field_Options DD_DEFAULT To DD_DISPLAYONLY

// Child (Client) file structure................

Send Add_Client_File Orderdtl.File_Number

// Parent (Server) file structure...............

Send Add_Server_File Customer.File_Number

Send Add_Server_File Salesp.File_Number

// External (System) file structure.............

Send Add_System_File Ordsys.File_Number DD_LOCK_ON_NEW_SAVE_DELETE

Define_Auto_Increment Ordsys.Order_Number To Orderhea.Order_Number

// Field-based properties.......................

// Orderhea.Order_Number

Set Field_Options Field Orderhea.Order_Number To DD_AUTOFIND

Set Field_Prompt_Object Field Orderhea.Order_Number To (ORDERHEA_SL(Self))

Set Key_Field_State Field Orderhea.Order_Number To TRUE

Set Status_Help Field Orderhea.Order_Number To "Order Number - New orders are assigned numbers automatically"

// Orderhea.Customer_Number

// Orderhea.Order_Date

Set Field_Class_Name Field Orderhea.Order_Date To "dbSpinForm"

Set Field_Entry_msg Field Orderhea.Order_Date To Entry_Order_Date

Set Field_Mask_Type Field Orderhea.Order_Date To MASK_DATE_WINDOW

Set Field_Prompt_Object Field Orderhea.Order_Date To (ORDERHEA_SL(Self))

Set Status_Help Field Orderhea.Order_Date To "Date on which the order was placed"

// Orderhea.Terms

Set Field_Class_Name Field Orderhea.Terms To "dbComboForm"

Set Field_Value_Table Field Orderhea.Terms To (Terms_table(Self))

Set Status_Help Field Orderhea.Terms To "Payment terms"

// Orderhea.Ship_Via

Set Field_Class_Name Field Orderhea.Ship_Via To "dbComboForm"

Set Field_Value_Table Field Orderhea.Ship_Via To (Ship_Table(Self))

Set Status_Help Field Orderhea.Ship_Via To "Shipping method"

// Orderhea.Ordered_By

//DDB/ Comment_Short Field Orderhea.Ordered_By To "This is just a suggested list. No parent files support this"

Set Status_Help Field Orderhea.Ordered_By To "Order placed by"

// Orderhea.Salesperson_Id

Set Field_Label_Long Field Orderhea.Salesperson_Id To "Sales Person Id"

Set Field_Label_Short Field Orderhea.Salesperson_Id To "Sales ID"

Set Status_Help Field Orderhea.Salesperson_Id To "Sales Person who initiated the order"

// Orderhea.Order_Total

//DDB/ Comment_Short Field Orderhea.Order_Total To "Maintained by the Orderdtl DD"

Set Field_Mask_Type Field Orderhea.Order_Total To MASK_CURRENCY_WINDOW

Set Field_Options Field Orderhea.Order_Total To DD_DISPLAYONLY

// Orderhea.Last_Detail_Num

//DDB/ Comment_Short Field Orderhea.Last_Detail_Num To "This is the "

//DDB-DefineFieldEnd

End_Procedure // Define_Fields

Procedure Field_Defaults

Forward Send Field_Defaults

//DDB-Generated-Code-Location

//DDB-FieldDefaultStart

//DDB-FieldDefaultEnd

End_Procedure

// Add a default date if the field is blank

Procedure Entry_Order_Date Integer iField Date dDate

Integer iChanged

Get Field_Changed_State iField To iChanged

If (iChanged=0 And dDate=0) Begin

SysDate4 dDate

Set Field_Default_Value iField To dDate

End

End_Procedure

Procedure Update

Forward Send Update

Send Adjust_Balances OrderHea.Order_Total

End_Procedure

Procedure Backout

Forward Send Backout

Send Adjust_Balances (-OrderHea.Order_Total)

End_Procedure

Procedure Adjust_Balances Number Amt

Add Amt To Customer.purchases

Add Amt To Customer.Balance

End_Procedure

Procedure Deleting

Forward Send Deleting

// see if we can decrement the order number in sys file...can only do

// this if this is the newest order.

If (OrderHea.Order_Number=OrdSys.Order_Number) Begin // if this is

Decrement OrdSys.Order_Number // the last number,

Saverecord Ordsys // decrement and save.

End

End_Procedure

End_Class // Orderhea_DataDictionary

//DDB-Selection-pkg-Start

#IFDEF Is$WebApp

#ELSE

Use ORDERHEA.SL // ORDERHEA_SL

#ENDIF

//DDB-Selection-pkg-End

//DDB-FileEnd

The ordering of special regions inside the .DD source file must follow the above example:

    1. Header Region

    2. Validation Region

    3. Selection Region

    4. Field Definition Region

    5. Field Defaults Region

    6. Selection Package Region

The code that exists within the special regions is maintained automatically by Database Builder. Each time you open a table in Database Builder, these regions are analyzed and each time you save a table, the regions are re-generated.

You may use a text editor to manually edit code inside the regions, but if you add a line of code that the analyzer does not recognize, then an error will be raised when the table is opened and that line will be removed when the table is saved again.

The following sections detail each special region and which symbols are recognized by Database Builder when the region is analyzed.


Data Dictionary File Structure (Region Details)

Header Region

//DDB-HeaderStart

This symbol marks the beginning of the Header Region.

// Revision : number

This is where the .DD source file’s revision number is maintained. This number is incremented after each editing session by Database Builder.

//DDB-HeaderEnd

This symbol marks the end of the Header Region.

Database Builder ignores all other symbols in this region.

Validation Region

//DDB-ValidationStart

This symbol marks the beginning of the Validation Region.

//DDB/ ExternalValidationTable objectName {externalFileName | PRIVATE}

This symbol is used to register external validation-table objects to the analyzer. You must have one of these commands for each external validation-table object.

Special Note

An external validation-table object does not have to be in a separate file! It is just an object whose definition lies outside the Validation Region.

If, instead of registering both the external object and its file, you only want to register the external object, then substitute the file name for the word PRIVATE.

In the case where the external object is not from an external file but just exists elsewhere in the .DD source, you should again specify PRIVATE in place of the external file name.

Register_Object objectName

Each validation-table object that is referenced in the Field Definition Region must be registered with the register_object command inside the Validation Table region.

The register_object commands used to register validation-table objects must come after any //DDB/ ExternalValidationTable commands. Otherwise, the reader will not consider that the object is correctly registered.

Use externalFileName

Each external file that is registered by the //DDB/ ExternalValidationTable command must have a corresponding Use command inside the Validation Table region.

Object objectName is a {ValidationTable | ;
DescriptionValidationTable | ;
CodeValidationTable}

Each validation-table object that is not external must be declared inside the Validation Region. These objects may only be based on one of the three above-mentioned classes. validation-table objects that are based on any other class must be registered as external.

The following symbols are supported inside the validation-table object:

Set Allow_Blank_State To {TRUE | FALSE}

Set Table_Title To Title String

Set Validate_State To {TRUE | FALSE}

For ValidationTable and DescriptionValidationTable objects, the following is mandatory (it will be ignored for CodeValidationTable)

Procedure Fill_List

Send Add_Table_Value value_string description_string

End_Procedure

For CodeValidationTable objects, the following is mandatory (it will be ignored for ValidationTable and DescriptionValidationTable objects)

Set Type_Value To type_identifier

End_Object

Each internal validation-table object must end inside the Validation Region.

//DDB-ValidationEnd

This symbol marks the end of the Validation Region.

All other symbols are ignored by Database Builder and will generate an error when this region is analyzed.

Selection Region

//DDB-SelectionStart

This symbol marks the start of the Selection Region.

//DDB/ ExternalSelectionList objectName {externalFileName | PRIVATE}

This symbol is used to register selection-list (lookup) objects. You must have one of these commands for each selection-list (lookup) object.

If, instead of registering both the object and its file, you only want to register the object, then substitute the file name for the word PRIVATE.

In the case where the external object is not from an external file but just exists elsewhere in the .DD source,you should again specify PRIVATE in place of the external file name.

Register_Object objectName

Each selection-list object that is referenced in the Field Definition Region must be registered with the register_object command inside the Selection Region.

These commands must come after any //DDB/ ExternalSelectionList commands. Otherwise, the reader will not consider that the object is correctly registered.

//DDB-SelectionEnd

This symbol marks the end of the Selection Region.

All other symbols inside this region are ignored by Database Builder and will generate an error when this region is analyzed.

Field Definition Region

//DDB-Generated-Code-Location

//DDB-DefineFieldStart

These symbols mark the start of the Field Definition Region.

//DDB-DefineFieldEnd

This symbol marks the end of the Field Definition Region.

//DDB Comment_Short Field file.field To comment

This symbol is used for recording descriptive comments for each column in the table. Database Builder displays the column comments in the Field Settings ; Other tab page.

The following data-dictionary properties may be set inside the Field Definition Region:

Cascade_Delete_State, Error_Report_Mode, Field_Checkbox_Values, Field_Class_Name, Field_Entry_msg, Field_Error, Field_Exit_msg, Field_Label_Long, Field_Label_Short, Field_Mask, Field_Mask_Type, Field_Mask_Value_State, Field_Options, Field_Prompt_Object, Field_Validate_msg, Field_Value_Check, Field_Value_Range, Field_Value_Table, Field_Zoom_Object, Foreign_Field_Options, Key_Field_State, Main_File, Protect_Key_State, Status_Help, Validate_Delete_Structure_Mode, Validate_Save_Structure_Mode

The following data-dictionary methods may be sent inside the Field Definition Region:

Add_Client_File, Add_Server_File, Add_System_File, Remove_Client_File, Remove_Server_File, Remove_System_File

The following data-dictionary command may be given inside the Field Definition Region:

Define_Auto_Increment

All other symbols in this region are ignored by Database Builder and will generate an error when this region is analyzed.

Field Defaults Region

//DDB-Generated-Code-Location

//DDB-FieldDefaultStart

These symbols mark the start of the Field Defaults Region.

//DDB-FieldDefaultEnd

This symbol marks the end of the Field Defaults Region.

The following data-dictionary properties may be set inside the Field Definition Region:

Field_Changed_Value

Field_Current_Value

All other symbols in this region are ignored by Database Builder and will generate an error when this region is analyzed.

Selection-Package Region

//DDB-Selection-pkg-Start

This symbol marks the start of the Selection-Package Region.

Use externalFileName

Each external file that is registered by the //DDB/ ExternalSelectionList command must have a corresponding Use command inside the Selection-Package Region.

//DDB-Selection-pkg-End

This symbol marks the end of the Selection-Package Region.


Database Reports

File Definition Report

This report prints a formatted version of the columns, index, and parameter information carried in the .DEF files.

Related Topics

Outputting .DEF Files

Data-dictionary Report

This report provides a hard copy of all settings in the data-dictionary subclass for the selected tables including Field Options, Status Help, Parent, Child, External File Structures, and Augmented Methods.

Percentage Filled Report

This report compares the number of rows with the defined maximum for each table.

Related Topics

Max Records

Check for Orphans Report

This report checks the selected tables for orphaned rows.

Check Relationships Report

This report performs a full check of all table relationships. The following tests are made, and all mismatches reported:

  • The length and type of each relating column are compared with those of each related-to column.

  • The tables to which the tested table relates are compared with those listed in the Parent File Structure in the data-dictionary.

  • The tables relating to the tested table are compared with those listed in the Child File Structure in the data-dictionary.

Related Topics

Relationship Wizard

Data-Dictionary Structures

Check for Damaged Headers Report

This report shows if the file headers are OK.

Related Topics

Repair Corrupt Table Headers

See Also

General Topics


Database Builder Help Topics

Database Builder

This document explains how to use and configure the Visual DataFlex Database Builder tool.

The Database Builder enables you to create, modify, and delete the tables used in a DataFlex application. It supports creation and design of data dictionaries (.DD files), including declaration and configuration of the data-dictionary subclass required for every table. In addition, it enables you to repair indexes and data, and to restructure tables (including tables from earlier revisions of DataFlex).

Getting Started with Database Builder

Explains how to launch Database Builder, selecting workspaces, and a guided tour of the tool’s main elements.

Building Tables and Data Dictionaries

Explains how to create and edit tables, columns, indexes, and data dictionaries.

Connecting with Other Database Systems

Explains how to use Database Builder with SQL & ODBC database drivers such as those for Microsoft SQL Server, Pervasive.SQL, IBM DB2, ODBC, etc.

Database Maintenance

Explains how to perform database-maintenance operations such as rebuilding indexes, cleaning up bad data, fixing corrupted table headers, editing file lists, etc.

Database Reports

Explains about the various reports that Database Builder provides for documenting and analyzing your database.

General Topics

This section covers other database-related topics such as dealing with alias tables, data-dictionary-file structure, relational database techniques, etc.