Execute Structure_Start

Creating New Tables

 

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

Defaults

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

 

Restructuring Existing Tables

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:

 

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"

Querying Table Attributes

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

 

See Also

Creating and Modifying Table Structures

Database Essentials