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

Advertisements

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