Temporary Indexes

Normally, an index is created or modified using the Create_Index command within a Structure_Start / Structure_End block. These become part of the table’s global definition. If the index type is a server index, both the DataFlex definition of the index (in the .INT file) and the back-end index are modified.

With the DataFlex SQL drivers, you can create temporary indexes. Temporary indexes are indexes that are created dynamically at run time. They will only exist as long as the table is open. Temporary indexes are really not indexes at all – they will not be created on the database server. They behave the same way a client-only index behaves – it asks the server to filter and order your rows based on the WHERE and ORDER BY clauses generated using this index. A temporary index is dynamically assigned an index number, perhaps better thought of as a handle, at run time when the index is created.

Temporary indexes are created like regular indexes, but they must be created using an already opened table outside of a Structure_Start…Structure_End block. An index created outside a Structure_Start…Structure_End block will be a temporary index that only exists at runtime while the affected table is open.

Temporary indexes will have the type DF_INDEX_TEMPORARY for DF_INDEX_SQL_TYPE.

A temporary index can be deleted outside a Structure_Start / Structure_End block with Delete_Index.

There is very little overhead associated with a temporary index. You may create as many as you wish. No changes are made to the SQL Table, and they merely determine how the SQL statement sent to the server is constructed.

Example

The following example shows how a temporary index could be created in an application.

// Create a temporary index for State x City x Customer and return its Index handle.
// The index handle is what can be used to reference this index and will be valid until the
// application ends or the index is deleted.
Function StateCityOrdering Returns Integer
    Handle hTable
    Integer iIndex

    Move Customer.File_Number to hTable
    Create_Index hTable at iIndex
    Set_Attribute DF_INDEX_NUMBER_SEGMENTS of hTable iIndex to 3
    Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 1 to (RefTable(Customer.State))
    Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 2 to (RefTable(Customer.City))
    Set_Attribute DF_INDEX_SEGMENT_FIELD of hTable iIndex 3 to (RefTable(Customer.Customer_Number))

    Function_Return iIndex
End_Function

// Create a temporary index and use this to control the ordering of a report.
Procedure StartReport
    Integer iOrder

    // Use the temporary index
    Get StateCityOrdering to iOrder
    Set Ordering of oReport to iOrder
    Send Run_Report of oReport

    // Delete the temporary index
    Delete_Index Customer.File_Number iOrder
End_Procedure

Temporary Indexes

Example

See Also