SET ORDER

SET ORDER

Select the controlling order

Syntax

      SET ORDER TO [<nOrder> | [TAG <cOrderName>]
            [IN <xcOrderBagName>]]

Arguments

TAG is an optional clause that provides compatibility with RDDs that access multiple-order order bags. You must use this keyword anytime you specify <cOrderName>.

<cOrderName> is the name of an order, a logical arrangement of a database according to a keyed pair. This order will become the controlling order in the order list. If you specify <cOrderName>, you must use the keyword TAG.

Note: This differs from dBASE and FoxPro where TAG is totally optional.

<nOrder> is the number of the target order in the order list. You may represent the order as an integer or as a character string enclosed in quotes.

IN <xcOrderBagName> is the name of a disk file containing one or more orders. You may specify <xcOrderBagName> as the file name with or without the path name or appropriate extension. If you do not include the extension as part of <xcOrderBagName>, Harbour uses the default extension of the current RDD.

Description

When you SET ORDER TO a new controlling order (index), all orders are properly updated when you either append or edit records. This is true even if you SET ORDER TO 0. After a change of controlling order, the record pointer still points to the same record.

SET ORDER TO 0 restores the database access to natural order, but leaves all orders open. SET ORDER TO with no arguments closes all orders and empties the order list

Though you may use <cOrderName> or <nOrder> to specify the target order, <nOrder> is only provided for compatibility with earlier versions of Harbour. Using <cOrderName> is a surer way of accessing the correct order in the order list.

If you supply <xcOrderBagName>, only the orders belonging to <xcOrderBagName> in the order list are searched. Usually you need not specify <xcOrderBagName> if you use unique order names throughout an application.

To determine which order is the controlling order use the ORDSETFOCUS() function.

In RDDs that support production or structural indices (e.g., DBFCDX), if you specify a tag but do not specify an order bag, the tag is created and added to the index. If no production or structural index exists, it will be created and the tag will be added to it. When using RDDs that support multiple order bags, you must explicitly SET ORDER (or ORDSETFOCUS()) to the desired controlling order. If you do not specify a controlling order, the data file will be viewed in natural order.

SET ORDER can open orders in a network environment instead of the INDEX clause of the USE command. Generally, specify USE, and then test to determine whether the USE succeeded. If it did succeed, open the associated orders with SET ORDER. See the example below.

Examples

      USE Customer NEW
      IF (! NETERR())
         SET ORDER TO Customer
      ENDIF

      SET ORDER TO "CuAcct"         // CuAcct is an Order in Customer

Seealso

INDEX, INDEXORD(), SEEK, SET INDEX, USE

Advertisements

SET SOFTSEEK

SET SOFTSEEK

Toggle relative seeking

Syntax

      SET SOFTSEEK on | OFF | <xlToggle>

Arguments

ON causes the record pointer to be moved to the next record with a higher key after a failed index search.

OFF causes the record pointer to be moved to EOF() after a failed index search.

<xlToggle> is a logical expression that must be enclosed in parentheses. A value of true (.T.) is the same as ON, and a value of false (.F.) is the same as OFF.

Description

SET SOFTSEEK enables relative seeking, a method of searching an index and returning a record even if there is no match for a specified key.

When SOFTSEEK is ON and a match for a SEEK is not found, the record pointer is set to the next record in the index with a higher key value than the SEEK argument. Records are not visible because SET FILTER and/or SET DELETED are skipped when searching for the next higher key value. If there is no record with a higher key value, the record pointer is positioned at LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.). FOUND() returns true (.T.) only if the record is actually found. It never returns true (.T.) for a relative find.

When SOFTSEEK is OFF and a SEEK is unsuccessful, the record pointer is positioned at LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.).

Notes

. SET RELATION: SET RELATION ignores SOFTSEEK updating the record pointer in all linked child work areas as if SOFTSEEK is OFF.

Examples

      .  This example illustrates the possible results of a SEEK with
         SET SOFTSEEK ON:

      SET SOFTSEEK ON
      USE Salesman INDEX Salesman NEW
      ACCEPT "Enter Salesman: " TO cSearch
      SEEK cSearch
      DO CASE
      CASE FIELD->Salesman = cSearch
         ? "Match found:", FOUND(), EOF(), FIELD->Salesman
      CASE !EOF()
         ? "Soft match found:", FOUND(), EOF(), ;
                  FIELD->Salesman
      OTHERWISE
         ? "No key matches:", FOUND(), EOF(), FIELD->Salesman
      ENDCASE

Seealso

FOUND(), SEEK, SET INDEX, SET ORDER, SET RELATION

SET RELATION

SET RELATION

Relate two work areas by a key value or record number

Syntax

      SET RELATION TO [<expKey> | <nRecord> INTO <xcAlias>]
            [, [TO] <expKey2> | <nRecord2> INTO <xcAlias2>...]
            [ADDITIVE]

Arguments

TO <expKey> is an expression that performs a SEEK in the child work area each time the record pointer moves in the parent work area. For this to work, the child work area must have an index in USE.

TO <nRecord> is an expression that performs a GOTO to the matching record number in the child work area each time the record pointer moves in the parent work area. If <nRecord> evaluates to RECNO(), the relation uses the parent record number to perform a GOTO to the same record number in the child work area. For a numeric expression type of relation to execute correctly, the child work area must not have an index in USE.

INTO <xcAlias> identifies the child work area and can be specified either as the literal alias name or as a character expression enclosed in parentheses.

ADDITIVE adds the specified child relations to existing relations already set in the current work area. If this clause is not specified, existing relations in the current work area are released before the new child relations are set.

SET RELATION TO with no arguments releases all relations defined in the current work area.

Description

SET RELATION is a database command that links a parent work area to one or more child work areas using a key expression, record number, or numeric expression. Each parent work area can be linked to as many as eight child work areas. A relation causes the record pointer to move in the child work area in accordance with the movement of the record pointer in the parent work area. If no match is found in the child work area, the child record pointer is positioned to LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.).

The method of linking the parent and child work areas depends on the type of <expKey> and presence of an active index in the child work area. If the child work area has an active index, the lookup is a standard SEEK. If the child work area does not have an active index and the type of <expKey> is numeric, a GOTO is performed in the child work area instead.

Notes

. Cyclical relations: Do not relate a parent work area to itself either directly or indirectly.

. Soft seeking: SET RELATION does not support SOFTSEEK and always behaves as if SOFTSEEK is OFF even if SOFTSEEK is ON. This means that if a match is not found in the child work area, the child record pointer is always positioned to LASTREC() + 1.

. Record number relations: To relate two work areas based on matching record numbers, use RECNO() for the SET RELATION TO expression and make sure the child work area has no active indexes.

Examples

      .  This example relates three work areas in a multiple parent-
         child configuration with Customer related to both Invoices and Zip:

         USE Invoices INDEX Invoices NEW
         USE Zip INDEX Zipcode NEW
         USE Customer NEW
         SET RELATION TO CustNum INTO Invoices, Zipcode INTO Zip
         LIST Customer, Zip->City, Invoices->Number, ;
                  Invoices->Amount

      .  Sometime later, you can add a new child relation using the
         ADDITIVE clause, like this:

         USE BackOrder INDEX BackOrder NEW
         SELECT Customer

         SET RELATION TO CustNum INTO BackOrder ADDITIVE

Seealso

DBRELATION(), DBRSELECT(), FOUND(), RECNO(), SET INDEX

SELECT

SELECT

Change the current work area

Syntax

      SELECT <xnWorkArea> | <idAlias>

Arguments

<xnWorkArea> is the work area number between 0 and 250 inclusive. This argument is an extended expression and can be specified either as a literal number or as a numeric expression enclosed in parentheses.

<idAlias> is the name of an existing work area to SELECT if there is a database file open in that area.

Description

SELECT is a database command that changes work areas. CA-Clipper supports 250 work areas, with each work area a logical handle to an open database file and all of its attributes. You can refer to work areas with SELECT by number or by alias. The alias of a work area is automatically assigned when a database file is USEd in that work area or by using the ALIAS clause.

Work area 0 refers to the first empty or next available work area. Using this, you can SELECT 0 and USE <xcDatabase> as a method of opening database files.

Notes

. Aliased expressions: Aliased expressions are a much more powerful method of selecting new work areas than the SELECT command. Instead of SELECTing a work area, and then performing an operation for that work area, you can apply an alias to an expression that performs the operation. This is done by specifying the alias of the remote work area and the expression enclosed in parentheses. For example, to access the value of EOF() in an unselected work area, you would normally execute a series of statements like the following:

            SELECT Remote
            ? EOF()
            SELECT Main

Using the aliased expression form, these statements become:

            ? Remote->(EOF())

. USE…NEW: Instead of using SELECT0 and USE <xcDatabase> to open a database file in a new work area, the preferred method is to USE <xcDatabase> NEW.

Examples

      .  This example opens a series of database files by SELECTing
         each work area by number then USEing each database file in that
         work area:

         SELECT 1
         USE Customer
         SELECT 2
         USE Invoices
         SELECT 3
         USE Parts
         SELECT Customer

      .  A better method is to open each database in the next available
         work area by specifying the NEW clause on the USE command line.
          In this example USE...NEW is employed instead of SELECT 0 and
         then USE:

         USE Customer NEW
         USE Invoices NEW

         SELECT Customer

      .  This code fragment changes work areas while saving the current
         work area name to a variable using the SELECT() function.  After
         executing an operation for the new work area, the original work
         area is restored:

         nLastArea := SELECT()
         USE Newfile NEW
         //
         <statements>...
         //
         SELECT (nLastArea)

Seealso

ALIAS(), EOF(), SELECT(), SET INDEX, USE, USED()

SEEK

Search an order for a specified key value

Syntax

      SEEK <expSearch> [SOFTSEEK]

Arguments

<expSearch> is an expression to match with an order key value.

SOFTSEEK causes the record pointer to be moved to the next record with a higher key value after a failed order search. Default behavior moves the record pointer to EOF() after a failed order search.

Description

SEEK is a database command that searches the controlling order from the first or last key value (depending on whether the LAST keyword is specified) and proceeds until a match is found or there is a key value greater than <expSearch>. If there is a match, the record pointer is positioned to the identity found in the order. If SOFTSEEK is OFF (the default) and SEEK does not find a match, the record pointer is positioned to LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.).

SOFTSEEK enables a method of searching an order and returning a record even if there is no match for a specified key.

When SOFTSEEK is ON and a match for a SEEK is not found, the record pointer is set to the next record in the order with a higher key value than the SEEK argument. Records are not visible because SET FILTER and/or SET DELETED are skipped when searching for the next higher key value. If there is no record with a higher key value, the record pointer is positioned at LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.). FOUND() returns true (.T.) only if the record is actually found. FOUND() never returns true (.T.) for a relative find.

When SOFTSEEK is OFF and a SEEK is unsuccessful, the record pointer is positioned at LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.).

SEEK with the SOFTSEEK clause is, effectively, the same as performing SET SOFTSEEK and then SEEK in earlier versions of Clipper except that it does not change the global setting of SOFTSEEK.

Examples

      .  The following example searches for "Doe" using the SEEK
         command:

      USE Customer NEW
      SET ORDER TO Customer
      ? SET( _SET_SOFTSEEK )      // (.F.)
      SEEK "Doe"
      ? SET( _SET_SOFTSEEK )      // Still (.F.)
      IF FOUND()
         .
         . < statements >
         .
      ENDIF

      .  The following example performs a soft seek for "Doe" using
         SOFTSEEK clause of the SEEK command:

      USE Customer NEW
      SET ORDER TO Customer
      ? SET( _SET_SOFTSEEK )      // (.F.)
      SEEK "Doe" SOFTSEEK
      ? SET( _SET_SOFTSEEK )      // Still (.F.)
      IF !FOUND()
         ? Customer->Name         // Returns next logical name after "Doe"
      ENDIF

Seealso

DBSEEK(), DBSETINDEX(), DBSETORDER(), EOF(), SET INDEX

REINDEX

Rebuild open indexes in the current work area

Syntax

      REINDEX
            [EVAL <lCondition>]
            [EVERY <nRecords>]

Arguments

EVAL <lCondition> specifies a condition that is evaluated either for each record processed or at the interval specified by the EVERY clause. This clause is identical to the EVAL clause of the INDEX command, but must be respecified in order for the reindexing operation to be monitored since the value of <lCondition> is transient.

EVERY <nRecords> specifies a numeric expression that modifies how often EVAL is evaluated. When using EVAL, the EVERY option offers a performance enhancement by evaluating the condition for every nth record instead of evaluating each record reindexed. The EVERY keyword is ignored if no EVAL condition is specified.

Description

REINDEX is a database command that rebuilds all open indexes in the current work area. When the reindexing operation finishes, all rebuilt indexes remain open, order is reset to one, and the record pointer is positioned to the first record in the controlling index. If any of the indexes were created with SET UNIQUE ON, REINDEX adds only unique keys to the index. If any of the indexes were created using a FOR condition, only those key values from records matching the condition are added to the index.

In a network environment, REINDEX requires EXCLUSIVE USE of the current database file. Refer to the “Network Programming” chapter for more information.

Caution! REINDEX does not recreate the header of the index file when it recreates the index. Because of this, REINDEX does not help if there is corruption of the file header. To guarantee a valid index, always use INDEX ON in place of REINDEX to rebuild damaged indexes

Notes

Index key order, UNIQUE status, and the FOR condition are known to the index (.ntx) file and are, therefore, respected and maintained by REINDEX.

Examples

      .  This example REINDEXes the index open in the current work
         area:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX

      .  This example REINDEXes using a progress indicator:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX EVAL NtxProgress() EVERY 10
         FUNCTION NtxProgress
         LOCAL cComplete := LTRIM(STR((RECNO()/LASTREC()) * 100))
         @ 23, 00 SAY "Indexing..." + cComplete + "%"
         RETURN .T.

Seealso

INDEX, PACK, SET INDEX, USE

FIND

FIND*

Search an index for a specified key value

Syntax

      FIND <xcSearchString>

Arguments

<xcSearchString> is part or all of the index key of a record to search for, and can be specified either as a literal string or as a character expression enclosed in parentheses. If an expression is specified instead of a literal string, FIND operates the same as SEEK.

Description

FIND is a database command that searches an index for the first key matching the specified character string and positions the record pointer to the corresponding record.

If SOFTSEEK is OFF and FIND does not find a record, the record pointer is positioned to LASTREC() + 1, EOF() returns true (.T.), and FOUND() returns false (.F.).

If SOFTSEEK is ON, the record pointer is positioned to the record with the first key value greater than the search argument and FOUND() returns false (.F.). In this case, EOF() returns true (.T.) only if there are no keys in the index greater than the search argument. FIND is a compatibility command and therefore not recommended. Its usage is superseded entirely by the SEEK command.

Examples

      .  These examples show simple FIND results:
      USE Sales INDEX Branch NEW
      FIND ("500")
      ? FOUND(), EOF(), RECNO()         // Result: .F. .T. 85
      FIND "200"
      ? FOUND(), EOF(), RECNO()         // Result: .T. .F. 5
      FIND "100"
      ? FOUND(), EOF(), RECNO()         // Result: .T. .F. 1

Seealso

EOF(), FOUND(), RECNO(), SEEK, SET INDEX, SET ORDER