SORT

SORT

Copy to a database (.dbf) file in sorted order

Syntax

      SORT TO <xcDatabase> ON <idField1> [/[A | D][C]]
            [, <idField2> [/[A | D][C]]...]
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

TO <xcDatabase> is the name of the target file for the sorted records and can be specified either as a literal file name or as a character expression enclosed in parentheses. Unless otherwise specified, the new file is assigned a (.dbf) extension.

ON <idField> is the sort key and must be a field variable.

/[A|D][C] specifies how <xcDatabase> is to be sorted. /A sorts in ascending order. /D sorts in descending order. /C sorts in dictionary order by ignoring the case of the specified character field. The default SORT order is ascending.

<scope> is the portion of the current database file to SORT. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to SORT within the given scope.

Description

SORT is a database command that copies records from the current work area to another database file in sorted order. Clipper SORTs character fields in accordance with the ASCII value of each character within the string unless the /C option is specified. This option causes the database file to be sorted in dictionary order–capitalization is ignored. Numeric fields are sorted in numeric order, date fields are sorted chronologically, and logical fields are sorted with true (.T.) as the high value. Memo fields cannot be sorted.

SORT performs as much of its operation as possible in memory, and then, it spools to a uniquely named temporary disk file. This temporary file can be as large as the size of the source database file. Note also that a SORT uses up three file handles: the source database file, the target database file, and the temporary file. In a network environment, you must lock the database file to be SORTed with FLOCK() or USE it EXCLUSIVEly.

Notes

. Deleted source records: If DELETED is OFF, SORT copies deleted records to the target database file; however, the deleted records do not retain their deleted status. No record is marked for deletion in the target file regardless of its status in the source file.

If DELETED is ON, deleted records are not copied to the target database file. Similarly, filtered records are ignored during a SORT and are not included in the target file.

Examples

      .  This example copies a sorted subset of a mailing list to a
         smaller list for printing:

      USE Mailing INDEX Zip
      SEEK "900"
      SORT ON LastName, FirstName TO Invite WHILE Zip = "900"
      USE Invite NEW
      REPORT FORM RsvpList TO PRINTER

Seealso

ASORT(), FLOCK(), INDEX, USE

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

SET EXCLUSIVE

SET EXCLUSIVE*

Establish shared or exclusive USE of database files

Syntax

      SET EXCLUSIVE ON | off | <xlToggle>

Arguments

ON causes database files to be opened in exclusive (nonshared) mode.

OFF causes database files to be opened in shared mode.

<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

In a network environment, SET EXCLUSIVE determines whether a USE command specified without the EXCLUSIVE or SHARED clause automatically opens database, memo, and index files EXCLUSIVE. When database files are opened EXCLUSIVE, other users cannot USE them until they are CLOSEd. In this mode, file and record locks are unnecessary.

When EXCLUSIVE is ON (the default), all database and associated files open in a nonshared (exclusive) mode unless the USE command is specified with the SHARED clause. Use EXCLUSIVE only for operations that absolutely require EXCLUSIVE USE of a database file, such as PACK, REINDEX, and ZAP.

When EXCLUSIVE is OFF, all files are open in shared mode unless the USE command is specified with the EXCLUSIVE clause. Control access by other users programmatically using RLOCK() and FLOCK().

SET EXCLUSIVE is a compatibility command and not recommended. It is superseded by the EXCLUSIVE and SHARED clauses of the USE command.

Refer to the “Network Programming” chapter for more information.

Notes

. Error handling: Attempting to USE a database file already opened EXCLUSIVE by another user generates a runtime error and sets NETERR() to true (.T.). After control returns to the point of error, you can test NETERR() to determine whether the USE failed.

Seealso

FLOCK(), NETERR(), RLOCK(), USE, DBUSEAREA()

SET AUTOSHARE

SET AUTOSHARE

Defines network detection for shared file access.

Syntax

      SET AUTOSHARE TO [<nMode>]

Arguments

<nMode> A numeric value 0, 1 or 2 can be specified for <nMode>. The default is 0. If omitted, the network detection mode is switched off.

Description

SET AUTOSHARE is a compatibility command useful for changing a multi-user application to a stand-alone application by changing one line of code in the start routine of a program.

This requires changing only the value of <nMode>:

                    Values for SHARE mode detection
         ---------------------------------------------------------
         Value   Description
         -----   -------------------------------------------------
           0     Disables SHARE mode detection ( default )
           1     Opens database SHARED in a network, and EXCLUSIVE
                 if no network is detected
           2     Always opens databases EXCLUSIVE

To take advantage of SET AUTOSHARE, an application must be programmed for multi-user access, respecting the rules for network programming. For example, record locks must be obtained with RLock() before changing field variables. This way, a multi-user application is created.

To change this application to a single user version, SET AUTOSHARE TO 2 is coded in the start routine.

A developer can SET AUTOMODE TO 1 on the development machine. In this case, performance advantages from EXCLUSIVE file access are available during the development cycle, while SHARED file access is granted in a multi-user environment.

Seealso

Set(), SET AUTOPEN, SET AUTORDER, USE

SET AUTORDER

SET AUTORDER

Defines the default controlling index for automatically opened index files.

Syntax

      SET AUTORDER TO <nOrder>

Arguments

<nOrder> This is a numeric value specifying the ordinal position of the index to select as the controlling index when SET AUTOPEN is set to ON. The default is zero.

Description

When SET AUTOPEN is set to ON and the RDD supports automatic opening of structural indexes, the SET AUTORDER command specifies the index to activate as the controlling index. The default value for <nOrder> is zero, i.e. no controlling index is activated when an index file is automatically opened with the USE command. In this case, records of the database are accessible in physical order in the work area.

Note

Refer to SET AUTOPEN for an example of automatic selection of the controlling index.

Seealso

OrdSetFocus(), Set(), SET AUTOPEN, SET AUTOSHARE, USE

SET AUTOPEN

SET AUTOPEN

Toggles automatic opening of a structural index file

Syntax

      SET AUTOPEN ON | off | (<lOnOff>)

Arguments

ON | off | (<lOnOff>) The option toggles if a structural index file is automatically opened with the USE command. With ON or .T. (true), an index file is automatically opened. OFF or .F. (false) switch this mode off.

Description

Some replaceable database drivers support automatic opening of index files with the USE command when the index file has the same file name as the database file (without extension). An example is the DBFCDX driver. SET AUTOPEN toggles this behavior.

When SET AUTOPEN is set to ON, which is the default, the USE command automatically opens an index file having the same name as the database file and the file extension returned from OrdBagExt().

Note

If an index file is automatically opened, a controlling index is not activated. The default index order is zero, i.e. records are accessible in physical order in the work area. To select a controlling index, call OrdSetFocus() or use SET AUTORDER for a default controlling index

Example

      // The example demonstrates the effect of SET AUTOPEN with the
      // DBFCDX driver.

      REQUEST DBFCDX

      PROCEDURE Main

         RddSetDefault( "DBFCDX" )
         SET AUTOPEN OFF

         USE Customer
         INDEX ON CustID                    TAG ID   TO Customer
         INDEX ON Upper(LastName+FirstName) TAG Name TO Customer

          USE Customer
          ? OrdCount(), OrdKey()        // result: 0  ""

          SET AUTOPEN ON

          USE Customer
          ? OrdCount(), OrdKey()        // result: 2  ""

          SET AUTORDER TO 1

          USE Customer
          ? OrdCount(), OrdKey()        // result: 2  CUSTID
          ? Ordkey( 2 )                 // result: Upper(LastName+FirstName)

          USE
       RETURN

Seealso

OrdListAdd(), OrdSetFocus(), Set(), SET AUTORDER, SET AUTOSHARE, USE

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()

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

CLOSE

CLOSE

Close a specific set of files

Syntax

      CLOSE [<idAlias> | ALL | ALTERNATE | DATABASES |
             FORMAT | INDEXES]

Arguments

<idAlias> specifies the work area where all files are to be closed.

ALL closes alternate, database, and index files in all work areas, releasing all active filters, relations, and format definitions.

ALTERNATE closes the currently open alternate file, performing the same action as SET ALTERNATE TO with no argument.

DATABASES closes all open databases, memo and associated index files in all work areas, and releases all active filters and relations. It does not, however, have any effect on the active format.

FORMAT releases the current format, performing the same action as SET FORMAT TO with no argument.

INDEXES closes all index files open in the current work area.

Description

CLOSE is a general purpose command that closes various types of Clipper files depending on the specified option. CLOSE with no option closes the current database and associated files, the same as USE with no arguments.

In Harbour, a number of other commands also close files including:

          .  QUIT
          .  CANCEL*
          .  RETURN from the highest level procedure
          .  CLEAR ALL*
          .  USE with no argument

Seealso

QUIT, RETURN, SET ALTERNATE, SET INDEX, USE

ZAP

ZAP

Remove all records from the current database file

Syntax

      ZAP

Arguments

(This command has no arguments)

Description

This command removes all of the records from the database in the current work area. This operation also updates any index file in use at the time of this operation. In addition, this command removes all items within an associated memo file. In a network enviroment, any file that is about to be ZAPped must be used exclusively.

Examples

      USE tests NEW INDEX tests
      ZAP
      USE

Compliance

Clipper

Seealso

DELETE, PACK, USE