Sort

Purpose

The Sort command is used to sort or re-build one or more indexes of a database table.

Syntax

sort {table-handle} [{index-list} [{options} [{callback-object}]]]

where:

{table-handle} is a handle to the database table that you wish to sort. The table must have been opened.
{index-list} is a string of numeric characters listing each index that you wish to sort. The index numbers are delimited in the string using space characters. Each index number must be within the minimum and maximum range of index numbers for the table. If you omit the index-list parameter, then every index of the table will be sorted.
{options} are the options to use for the sort. Available options are listed below.
{callback-object} is a handle to an object that will receive the Callback message during the sort operation. Refer to Implementing Callback Functions for more information.

What it Does

Below are some examples of using the Sort command:

Handle hOrder
Open "Order" as hOrder mode DF_EXCLUSIVE
Sort hOrder

In the above example, an order table is opened and a handle to the table is placed in the variable hOrder. The Sort command is invoked to sort every index of this table.

Handle hEmployee
Open "Employee" as hEmployee mode DF_EXCLUSIVE
Sort hEmployee "1 6 12"

In the above example, an employee table is opened and a handle to the table is placed in the variable hEmployee. The Sort command is invoked to sort index numbers 1, 6, and 12 of this table.

Sort Options

The options listed below may have different effects on the sort operation depending upon the database driver in use. The effects listed here are for the DataFlex driver. The {option} parameter of the Sort command can be any one of the following:

DF_SORT_OPTION_NO_DATA_CHECK - The sort operation will not check for bad data in the records. However, duplicate records will always be checked for. Bad data is defined to be data in a record that does not match the data's field type.
DF_SORT_OPTION_BAD_DATA_FIXUP – The sort operation will fix bad data by replacing bad characters with blanks or zeros as appropriate.
DF_SORT_OPTION_BAD_DATA_FILE – The sort operation will not fix bad data in the database, but logs the bad record(s) in a .BAD file.
DF_SORT_OPTION_BAD_DATA_ABORT - If bad data is encountered, the sort operation is aborted with an error.
DF_SORT_OPTION_DUP_DATA_FILE – The sort operation will leave any duplicate records in the database and also log them to a .BAD file. Note that this will result in corrupted indexes. Duplicate records are defined to be two or more records with matching key fields in an index.
DF_SORT_OPTION_DUP_DATA_ABORT - If duplicate data is encountered, the sort operation is aborted with an error.

If you want to use more than one of these options, you can combine them in a bitwise expression. Note that some of these options are mutually exclusive.

The default options for sort are to not check for bad data and to write errors to a .BAD file (DF_SORT_OPTION_NO_DATA_CHECK or DF_SORT_OPTION_DUP_DATA_FILE).

Below is an example of using the Sort command with options:

Handle hCustomer
Open "Customer" as hCustomer mode DF_EXCLUSIVE
Sort hCustomer "1 3 4" (DF_SORT_OPTION_BAD_DATA_FIXUP or DF_SORT_OPTION_DUP_DATA_ABORT)

In the above example, a customer table is opened and a handle to the table is placed in the variable hCustomer. The Sort command is invoked to sort index numbers 1, 3, and 4. The Sort operation is instructed to fix any bad data that it finds and abort if duplicate records are detected.

Notes

Normally, the indexes of a database table are maintained on-line and do not need sorting. You would need to sort a table's indexes for one of the following reasons: The indexes have become corrupted, you have modified the structure of an index, you have created a new index, or the table has batch indexes.
To use the sort command on a table in a multi-user environment, the table must be opened in exclusive mode (DF_EXCLUSIVE).
You do not need to write a DataFlex program to sort database tables. The Database Builder tool can be used to sort your database tables.
A callback function can be implemented to display the progress of long sort operations. Refer to Implementing Callback Functions for further information.
The speed of sorting may be improved by increasing the amount of memory allocated to the sort buffer (default: 8Mb). On machines having more than the default amount of memory available, the FLEX_SET_MAX_SORT_BUFFER function of the DataFlex database driver may be used by the Call_Driver command to increase it.