Apollo VCL Components > Apollo VCL Component Reference > TApolloTable > TApolloTable Methods

Declaration

function Index( sFileName, sExpr: String; bOption : Integer;

bDescend: Boolean; sCondition: String ): Integer;

Description

Creates a new single-order (.NTX or .IDX) index file and make it the active order. To create a tag within a compound (.CDX/.NSX) index, use IndexTag instead.

The progress of the function may be visually monitored if a gauge hook has been set with SetGaugeHook.

Parameters

sFileName: The name of the index file including full path (for local tables) and extension if the index type is NTX or IDX.

$image\tip.gif$ When working with a remote table via Apollo Database Server, you may only create new indexes within the same Database directory as specified in the DatabaseName property of the TApolloTable component making this method call. The remote client cannot specify a physical server drive/path location and must use the defined server alias instead. Any physical drive/path included on the file sFileName parameter will be ignored for remote tables.

sExpr: An string containing an xBase expression that defines the order key value.

iOption: One of the following constant values:

IDX_NONE 0 Standard index (Not UNIQUE or EMPTY (RYO))

IDX_UNIQUE 1 UNIQUE, allows unique keys only

IDX_EMPTY 2 Roll-Your-Own (RYO) empty index header

$image\tip.gif$ The IDX_EMPTY type is only supported under SDEFOX and SDENSX table types. The SDENTX table type does not support Roll-Your-Own (RYO) indexes.

bDescend:

Pass as True if the index is to be constructed in descending sequence (default is ascending). If this parameter is passed as true, the entire key (which may be built of a number of field elements) is inverted. If you wish to have some portions of a key in ascending sequence and other elements sorted in descending sequence, use the xBase DESCEND function to invert the appropriate elements as follows:

'UPPER(CUSTNAME) + DESCEND(DTOS(DUEDATE))'

This would produce an index on ascending customer name and descending due date sequence. Do not pass bDescend as True in this case.

Seeks performed on an order that was created with bDescend True do not have to use the Descend function to invert the key value prior to calling Seek.

sCondition: If you wish to construct a conditional order that contains a subset of the table, pass an xBase conditional expression (i.e., one that can be evaluated logically) in this parameter. If the order is to contain all of the records in the table, pass this parameter as a NULL string ('' or #0).

The use of a conditional index makes it appear that the table only contains records that satisfy the condition (e.g., "upper(trim(country)) = 'GERMANY'"). Apollo will properly maintain the index when records in the table are added, deleted, or changed.

If the conditional expression is able to use query optimization, it will do so (see Query and QueryTest).

Lock Note

Use FLock to lock the table or open the table for exclusive use before creating an index. Don't forget to unlock the file after the index has been built.

Return Value

The order identifying number of the index relative to this work area. This value should be retained in a global variable for future SetOrder calls.

If the index is successfully created, it becomes the controlling order and the record pointer is positioned to top.

If zero is returned, the index function has failed.

Search Path

If no path is supplied for sFileName, Apollo searches for a matching file in this order:

1. The current directory.

2. The Windows (or WinNT) directory.

3. The Windows System (or System32) directory.

4. The directory containing the executable file for the current task.

5. The directories listed in the PATH environment variable.

6. Network mapped directories.

Delphi Example

with ApTbl do

begin

// Open table

Open;

// Index on CUSTOMER, non-unique, non-descending, no condition

iOrd1 := Index('t1.ntx', 'CUSTOMER', IDX_NONE, False, '');

// Index on upper-cased LAST, unique, non-descending, no condition

iOrd2 := Index('t2.ntx', 'upper(LAST)', IDX_UNIQUE, False, '');

// Index on COMPANY, non-unique, descending, for STATE="CA"

iOrd3 := Index('t3.ntx', 'upper(COMPANY)', IDX_NONE, True, 'STATE="CA"');

// Make T2.NTX the active order

SetOrder( iOrd1 );

end;

C++Builder Example

// Open table

ApTbl->Open();

// Index on CUSTOMER, non-unique, non-descending, no condition

iOrd1 = ApTbl->Index("t1.ntx", "CUSTOMER", IDX_NONE, false, "");

// Index on upper-cased LAST, unique, non-descending, no condition

iOrd2 = ApTbl->Index("t2.ntx", "upper(LAST)", IDX_UNIQUE, false, "");

// Index on COMPANY, non-unique, descending, for STATE="CA"

iOrd3 = ApTbl->Index("t3.ntx", "upper(COMPANY)", IDX_NONE, true, "STATE='CA'");

// Make T2.NTX the active order

ApTbl->SetOrder( iOrd1 );