Execute Structure_Start
Set table attributes
Create columns and set column attributes
Create indexes and set index attributes
Execute Structure_End
The following example shows how to create a table.
Integer iColNumber iNewColumn
Handle hTable
String sFileName
String sColumn1Value
Integer iColumn2Value
Date dColumn3Value
// The name of your database table
Move "test3" to sFileName
// Call to Structure_Start puts the value of the table handle returned into the handle number
// Initializing hTable to 0 signifies creating a new table
Move 0 to hTable
// Start of the structure operation. The DataFlex driver name is "DATAFLEX"
Structure_Start hTable "DATAFLEX"
Set_Attribute DF_FILE_PHYSICAL_NAME of hTable to sFileName
// Create all the columns in the table
// Create_Field always inserts a column in the table specified by hTable
For iColNumber From 1 to 3
Create_Field hTable at iNewColumn
Loop
// Set column attributes
// column 1
Set_Attribute DF_FIELD_NAME of hTable 1 to "A1"
Set_Attribute DF_FIELD_TYPE of hTable 1 to DF_TEXT
Set_Attribute DF_FIELD_LENGTH of hTable 1 to 20
// column 2
Set_Attribute DF_FIELD_NAME of hTable 2 to "A2"
Set_Attribute DF_FIELD_TYPE of hTable 2 to DF_BCD
Set_Attribute DF_FIELD_LENGTH of hTable 2 to 12
// column 3
Set_Attribute DF_FIELD_NAME of hTable 3 to "A3"
Set_Attribute DF_FIELD_TYPE of hTable 3 to DF_DATE
// All table creation and modification starts with Structure_Start
// and ends with Structure_End. This example does not use a
// callback function, because the (new) table has no data in it.
Structure_End hTable 0
// At this point, there is no entry in the filelist. File attributes have
// to be set to make an entry into the filelist. Selection of a filelist
// number to assign to your data table is random. You can assign any number
// not currently in use, or get the next higher available number by using
// the following:
Get_Attribute DF_FILE_NEXT_EMPTY to hTable
Showln "The next available entry in filelist is" " " hTable
// Set File attributes
Set_Attribute DF_FILE_LOGICAL_NAME of hTable to sFileName
Set_Attribute DF_FILE_DISPLAY_NAME of hTable to sFileName
Set_Attribute DF_FILE_ROOT_NAME of hTable to sFileName
// The Structure_End command closed the table. To
// modify or set column values, you must reopen the table.
Open "test3" as hTable
showln "Opening table TEST3"
// Output the .DEF and .FD tables
Output_Aux_File DF_AUX_FILE_DEF for hTable
Output_Aux_File DF_AUX_FILE_FD for hTable
// add data
set_Field_Value hTable 1 to "HELLO"
set_Field_Value hTable 2 to 200
set_Field_Value hTable 3 to 12/12/94
SaveRecord hTable
// Get the values of the columns, and display them.
Get_Field_Value hTable 1 to sColumn1Value
showln "The value of Field 1 is " sColumn1Value
Get_Field_Value hTable 2 to iColumn2Value
showln "The value of Field 2 is " iColumn2Value
Get_Field_Value hTable 3 to dColumn3Value
showln "The value of Field 3 is " dColumn3Value
inkey WindowIndex // hold display for a keystroke
Abort
When creating a table, there are many attributes which have default values. You may, of course, override any of these by setting their value manually. The following lists these default values for the DataFlex database driver:
Attribute |
Default value |
Integrity Checking |
TRUE |
Lock Type |
DF_LOCK_TYPE_FILE |
Maximum Records |
10,000 |
Single/Multiuser |
DF_FILE_USER_MULTI |
Physical Name |
"dfdeflt" |
Reuse Deleted |
TRUE |
Transaction Processing |
DF_FILE_TRANSACTION_CLIENT_ATOMIC |
Column Name |
"FIELD_N" (FIELD_1, FIELD_2, etc.) |
Column Length |
0 |
Column Type |
ASCII |
Number of Segments in Index |
1 |
Index Segment Column Number |
0 |
Index Segment Features |
0 |
To modify the structure of a table, you must begin a restructure operation. This is done with the command Structure_Start. This command takes the handle for an open table (or 0 if creating a new table) and returns a handle to the new structure. You can then set or get the attributes of this structure, changing column types, index segments, etc. Other commands, such as create_column and create_index, allow you to add new columns and indexes. Using the Set_Attribute command to modify any database attribute of a table may only be done after you have executed a Structure_Start for the table; you may use Set_Attribute to modify non-database attributes anytime.
When you have completed your changes, you execute the command Structure_End, which saves your new table definition, restructuring and/or reindexing the table if necessary. One feature of Structure_End is its ability to provide status information as the restructuring and reindexing operations are going on. This is done through the use of a callback function.
When you execute Structure_End, you may optionally pass it the ID of a UIMS object. During the restructure or reindex operation, this object will be sent a specific message, get_callback, which is a function that takes a String and an Integer parameter and returns an Integer. The String is text that conveys either status information or a warning message. The Integer is a classification of the message, e.g., if it is a warning, the Integer parameter is set to the constant value DF_Message_Warning. The value returned from Get_Callback is a flag that tells the runtime whether to continue the restructure operation; if it is non-zero, the restructure is aborted.
What happens to a table when you abort a restructure or reindex operation after it has begun depends on the specific database driver being used to access the table, and can result in a damaged table. You may, of course, abort your restructure operation before it has started by executing the Structure_Abort command; this is guaranteed to leave your table untouched.
The Make_Table command also can be used on existing tables containing data. There are advantages and disadvantages, however, to using Make_Table to restructure a table. To insert or delete columns, you must modify the .DEF table by hand using a text editor. Inserted columns must be given a column number of 0; deleted columns can simply be deleted from the .DEF. Using Make_Table in this way provides a quick and easy method to modify a table; however, modifying the .DEF table by hand must be done with great care to ensure that the data in the .DEF table is properly formatted and valid. Since this format has never been documented by Data Access Corporation, you are on your own when undertaking this operation. Nevertheless, if you need to produce a program quickly that will modify a table, the capability is there.
To recap:
Open the existing table using the DF_EXCLUSIVE open mode
Execute Structure_Start
Change table attributes as required
Create or delete columns and set column attributes as required
Create or delete indexes and set index attributes as required
Execute Structure_End
Note: If you change the structure of a table, any previous constraints on that table may no longer be valid; you should reset them after the restructure operation.
The following example shows how to modify an existing table. It also shows how to create an index.
Open test3
Handle hTable
Integer iIndex
String sColumn1Value
Integer iColumn2Value
Date dColumn3Value
Move test3.File_Number to hTable
// Let's say we want to create index 2
Move 2 to iIndex
// Start the structure operation
Move hTable to hTable // pass Structure_Start its own variable
Structure_Start hTable "DATAFLEX"
Create_Index hTable at iIndex
// This shows that the index will be set based on two columns
Set_Attribute DF_INDEX_NUMBER_SEGMENTS of hTable iIndex to 2
// Identify the columns
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 1 to 0
Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 2 to 2
Structure_End hTable 0
showln "Index Creation Completed"
Open test3
Move test3.table_number to hTable
showln hTable
//Output new .DEF and .FD tables
Output_Aux_File DF_AUX_FILE_DEF for hTable
Output_Aux_File DF_AUX_FILE_FD for hTable
showln "DEF and FD output completed"
Table attributes can be queried with the Get_Attributes command. The following example illustrates how table, index, and index-segment attributes can be queried to display a table from a running program that lists the numbers of the columns making up each index of a database table named calories:
Handle hTable
Integer iLastIndex iCounter iIndexNum iNumSegments iColNum
// Open CALORIES, and retrieve its filelist number to hTable
Move 0 to hTable
Open "Calories" as hTable
// Get the number of the highest numbered index,
// and use that as the upper limit for the loop.
Get_Attribute DF_FILE_LAST_INDEX_NUMBER of hTable to iLastIndex
For iIndexNum from 1 to iLastIndex
// For each index, retrieve the number of segments.
Get_Attribute DF_INDEX_NUMBER_SEGMENTS of hTable iIndexNum to iNumSegments
show "INDEX " (String(iIndexNum)) " is based on columns: "
For iCounter From 1 to iNumSegments
// For each index segment, retrieve the number
// of the column used for this segment.
Get_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndexNum iCounter ;
to iColNum
show (String(iColNum)) " "
Loop
showln
Loop
Creating and Modifying Table Structures