If you are editing an SQL table then primary
key columns will be indicated here with the primary key icon.While Database Builder can be used to maintain tables, we recommend that you use the Studio to maintain tables during development.
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 "Open Modes " for more information.
The following information is maintained for each column in the column list:
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.
This is the column name. For embedded database tables, the maximum length is 32 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.
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. Numeric data is stored in BCD format. |
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 |
ObsoleteOverlap columns were replaced by multi-segment relationships in DataFlex 11.0 and are obsolete.
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. |
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. In the embedded database, the maximum negative value that can be stored is 1 digit less than the maximum positive value. For example, a column with length 6.2 can store a range of 999,999.99 to -99,999.99. 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 |
ObsoleteOverlap columns were replaced by multi-segment relationships in DataFlex 11.0 and are obsolete.
|
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. |
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.
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:
Check this option if the column allows null values.
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'.
Enter a default value for the column. This will be the value committed to the column value when no value is present.
Database Builder supports the concept of column templates. A column template is a record of the structure of a single column.
Each template is stored in a template file (.FLT) in your workspace's data-dictionary source path (DDSrc) folder.
The templates store information about the column's name, type, length and main index.
The Columns tab contains a toolbar which allows you to perform common column creation operations.
Click the Add Column button to begin creating a new column at the bottom of the column list.
Click the Delete Column button to delete the currently highlighted column from the table's definition.
Keyboard shortcut: Shift+F2
Click the Insert Column button to insert a new column above the currently highlighted column.
Keyboard shortcut: Shift+F10
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.
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 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>
<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.