DATABASE

Function AFIELDS()

  AFIELDS([<aFieldNames>], [<aTypes>],
 [<aWidths>], [<aDecimals>]) --> nFields
 
 

AFIELDS() is an array function that fills a series of arrays (structure attribute arrays) with the structure of the database file currently open, one element in each array per field. AFIELDS() works like ADIR(), filling a series of existing arrays with information. To use AFIELDS(), you must first create the arrays to hold the database structure information, each with the same number of elements as the number of fields (i.e. FCOUNT()). Once the structure attribute arrays are created, you can then invoke AFIELDS() to fill the structure arrays with information about each field.

By default, AFIELDS() operates on the currently selected work area. It can operate on an unselected work area if you specify it within an aliased expression (see example below).

AFIELDS() is a compatibility function and therefore is not recommended. It is superseded by DBSTRUCT(), which does not require the existence of any arrays prior to invocation and returns a multidimensional array containing the current database file structure.

  This example demonstrates how AFIELDS() and ACHOICE() can be
 used together to create a fields picklist:
 
 USE Sales NEW
 PRIVATE aFieldNames[FCOUNT()]
 AFIELDS(aFieldNames)
 @ 1, 0 TO 10, 10 DOUBLE
 nChoice := ACHOICE(2, 1, 9, 9, aFieldNames)
 @ 12, 0 SAY IF(nChoice != 0, aFieldNames[nChoice],;
 "None selected")
 RETURN
 
 This example uses AFIELDS() with an aliased expression to fill
 arrays with the structure of Sales.dbf, open in an unselected work
 area:
 
 LOCAL aFieldNames, aTypes, aWidths, aDecimals
 USE Sales NEW
 USE Customer NEW
 //
 aFieldNames := Sales->(ARRAY(FCOUNT()))
 aTypes := Sales->(ARRAY(FCOUNT()))
 aWidths := Sales->(ARRAY(FCOUNT()))
 aDecimals := Sales->(ARRAY(FCOUNT()))
 //
 Sales->(AFIELDS(aFieldNames, aTypes, ;
 aWidths, aDecimals))
 
 

Function ALIAS()

 ALIAS([<nWorkArea>]) --> cAlias

ALIAS() is a database function that determines the alias of a specified work area. An alias is the name assigned to a work area when a database file is USEd. The actual name assigned is either the name of the database file, or a name explicitly assigned with the ALIAS clause of the USE command.

ALIAS() is the inverse of the SELECT() function. ALIAS() returns the alias name given the work area number, and SELECT() returns the work area number given the alias name.

This example returns the name of the previously selected work area:

USE File1 NEW ALIAS Test1 nOldArea := SELECT() USE File2 NEW ALIAS Test2 ? ALIAS( nOldArea ) // Returns Test1

Function BOF()

 BOF() --> lBoundary

BOF() is a database function used to test for a boundary condition when you are moving the record pointer backward through a database file using the SKIP command. A simple usage example is a descending order record list with an ascending order index file. A more sophisticated example is a screen paging routine that pages forward or backward through the current database file based on the key the user presses. When the user attempts to page backward, you would use BOF() to test for a beginning of file condition before using the SKIP command to move the record pointer and repaint the screen.

Once BOF() is set to true (.T.), it retains its value until there is another attempt to move the record pointer.

By default, BOF() operates on the currently selected work area. It can be made to operate on an unselected work area by specifying it within an aliased expression (see example below).

The SKIP command is the only record movement command that can set BOF() to true (.T.).

  This example demonstrates BOF() by attempting to move the
 record pointer before the first record:
 
 USE Sales NEW
 ? RECNO(), BOF()               // Result: 1 .F.
 SKIP -1
 ? RECNO(), BOF()               // Result: 1 .T.
 
 This example uses aliased expressions to query the value of
 BOF() in unselected work areas:
 
 USE Sales NEW
 USE Customer NEW
 USE Invoices NEW
 
 ? Sales->(BOF()), Customer->(BOF())

Function BROWSE()

 BROWSE([<nTop>], [<nLeft>], [<nBottom>], [<nRight>]) --> lSuccess

BROWSE() is a user interface function that invokes a general purpose table-oriented browser and editor for records in the current work area. For a list of the navigation keys which are used by BROWSE(), refer to the DBEDIT() function. Note that Browse() is a compatibility function. DBEDIT() should be used in its place. For a more complicated BROWSE(), TBROWSE() should be used.

  This is an example of browsing a file:
 
 USE File1 NEW
 BROWSE()
 
 

Function DBAPPEND()

 DBAPPEND([<lReleaseRecLocks>]) --> NIL

DBAPPEND() is a database function that lets you add records to the current database. The enhancement to this function lets you maintain multiple record locks during an append.

DBAPPEND() without a parameter as in earlier versions of xClipper, clears all pending record locks prior to an append. This is the same as DBAPPEND(.T.).

Function DBCLEARFILTER()

 DBCLEARFILTER() --> NIL

DBCLEARFILTER() clears the logical filter condition, if any, for the current work area.

DBCLEARFILTER() performs the same function as the standard SET FILTER command with no expression specified. For more information, refer to the SET FILTER command.

 The following example sets a filter, lists data as filtered,
 and then clears the filter:
 
 USE Employee NEW
 DBSETFILTER( {|| Age < 40}, "Age < 40" )
 LIST Employee->Name
 DBCLEARFILTER()

Function DBCLEARINDEX()

 DBCLEARINDEX() --> NIL

DBCLEARINDEX() closes any active indexes for the current work area. Any pending index updates are written and the index files are closed.

DBCLEARINDEX() performs the same function as the standard SET INDEX command with no indexes specified. For more information, refer to the SET INDEX command.

 The following example clears index files if any are set:
 
 cFirst := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "FIRSTNAM" )
 DBSETINDEX( "LASTNAME" )
 //
 IF INDEXORD() > 0               // is there an index?
 DBCLEARINDEX()               // clear index files
 ELSE
 COPY TO FILE TEMP SDF      // copy to SDF in natural
 ENDIF                           // order

Function DBCLEARRELATION()

 DBCLEARRELATION() --> NIL

DBCLEARRELATION() clears any active relations for the current work area.

DBCLEARRELATION() performs the same function as the standard SET RELATION TO command with no clauses specified. For more information, refer to the SET RELATION command.

 The following example sets a relation, lists data, and then
 clears the relation:
 
 USE Employee NEW
 USE Department NEW INDEX Dept
 //
 SELECT Employee
 DBSETRELATION("Department", ;
 {|| Employee->Dept}, "Employee->Dept")
 LIST Employee->Name, Department->Name
 DBCLEARRELATION()

Function DBCLOSEALL()

 DBCLOSEALL() --> NIL

DBCLOSEALL() releases all occupied work areas from use. It is equivalent to calling DBCLOSEAREA() on every occupied work area. DBCLOSEALL() has the same effect as the standard CLOSE DATABASES command. For more information, refer to the USE and CLOSE commands.

 The following example closes all work areas:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "SALEFNAM" )
 DBSETINDEX( "SALELNAM" )
 //
 DBUSEAREA( .T., "DBFNTX", "Colls", "Colls", .T. )
 DBSETINDEX( "COLLFNAM" )
 DBSETINDEX( "COLLLNAM" )
 //
 DBSELECTAREA( "Sales" )      // select "Sales" work area
 //
 
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( DELETED() )
 IF RLOCK()
 Sales->( DBRECALL() )
 ? "Record deleted: ", Sales( DELETED() )
 ENDIF
 ENDIF
 ELSE
 ? "Not found"
 ENDIF
 DBCLOSEALL()                  // close all work areas

Function DBCLOSEAREA()

 DBCLOSEAREA() --> NIL

DBCLOSEAREA() releases the current work area from use. Pending updates are written, pending locks are released, and any resources associated with the work area are closed or released. DBCLOSEAREA() is equivalent to the standard CLOSE command or the USE command with no clauses. For more information, refer to the USE and CLOSE commands.

 The following example closes a work area via an alias
 reference:
 
 cLast := "Winston"
 //
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "SALEFNAM" )
 DBSETINDEX( "SALELNAM" )
 //
 DBUSEAREA( .T., "DBFNTX", "Colls", "Colls", .T. )
 DBSETINDEX( "COLLFNAM" )
 DBSETINDEX( "COLLLNAM" )
 //
 DBSELECTAREA( "Sales" )      // select "Sales" work area
 //
 IF ( Sales->(DBSEEK(cLast)) )
 
 IF Sales->( DELETED() ) .AND. Sales->( RLOCK() )
 Sales->( DBRECALL() )
 ? "Record deleted: ", Sales( DELETED() )
 ENDIF
 ELSE
 ? "Not found"
 Colls->( DBCLOSEAREA() )
 ENDIF

Function DBCOMMIT()

 DBCOMMIT() --> NIL

DBCOMMIT() causes all updates to the current work area to be written to disk. All updated database and index buffers are written to DOS and a DOS COMMIT request is issued for the database (.dbf) file and any index files associated with the work area.

DBCOMMIT() performs the same function as the standard COMMIT command except that it operates only on the current work area. For more information, refer to the COMMIT command.

 In this example, COMMIT is used to force a write to disk after
 a series of memory variables are assigned to field variables:
 
 USE Sales EXCLUSIVE NEW
 MEMVAR->Name := Sales->Name
 
 MEMVAR->Amount := Sales->Amount
 //
 @ 10, 10 GET MEMVAR->Name
 @ 11, 10 GET MEMVAR->Amount
 READ
 //
 IF UPDATED()
 APPEND BLANK
 REPLACE Sales->Name WITH MEMVAR->Name
 REPLACE Sales->Amount WITH MEMVAR->Amount
 Sales->( DBCOMMIT() )
 ENDIF
 

Function DBCOMMITALL()

 DBCOMMITALL() --> NIL

DBCOMMITALL() causes all pending updates to all work areas to be written to disk. It is equivalent to calling DBCOMMIT() for every occupied work area.

For more information, refer to DBCOMMIT() and the COMMIT command.

 The following example writes all pending updates to disk:
 
 cLast := "Winston"
 //
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "SALEFNAM" )
 DBSETINDEX( "SALELNAM" )
 //
 
 DBUSEAREA( .T., "DBFNTX", "Colls", "Colls", .T. )
 DBSETINDEX( "COLLFNAM" )
 DBSETINDEX( "COLLLNAM" )
 
 DBSELECTAREA( "Sales" )      // select "Sales" work area
 
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( DELETED() ) .AND. Sales( RLOCK() )
 Sales->( DBRECALL() )
 ? "Deleted record has been recalled."
 ENDIF
 ELSE
 ? "Not found"
 ENDIF
 //
 // processing done, write updates to disk and close
 DBCOMMITALL()
 DBCLOSEALL()
 QUIT

Function DBCREATE()

 DBCREATE(<cDatabase>, <aStruct>,[<cDriver>]) --> NIL

DBCREATE() is a database function that creates a database file from an array containing the structure of the file. You may create the array programmatically or by using DBSTRUCT(). DBCREATE() is similar to the CREATE FROM command which creates a new database file structure from a structure extended file. Use CREATE or COPY STRUCTURE EXTENDED commands to create a structure extended file.

Before using DBCREATE(), you must first create the <aStruct> array and fill it with the field definition arrays according to the structure in Field Definition Subarray table (above). There are some specific rules for creating a field definition array, including:

Specify all field attributes with a value of the proper data type for the attribute. The decimals attribute must be specified-- even for non-numeric fields. If the field does not have a decimals attribute, specify zero.

Specify the type attribute using the first letter of the data type as a minimum. Use longer and more descriptive terms for readability. For example, both "C" and "Character" can be specified as the type attribute for character fields.

In xClipper, character fields contain up to 64,000 characters. Unlike the CREATE FROM command, DBCREATE() does not use the decimals attribute to specify the high-order part of the field length. Specify the field length directly, regardless of its magnitude.

To make references to the various elements of the field definition subarray more readable, the header file called dbstruct.ch is supplied. It contains the #defines to assign a name to the array position for each field attribute. It is located in \include.

Field Definition Subarray
------------------------------------------------------------------------
Position     Metasymbol     dbstruct.ch
------------------------------------------------------------------------
1            cName          DBS_NAME
2            cType          DBS_TYPE
3            nLength        DBS_LEN
4            nDecimals      DBS_DEC
------------------------------------------------------------------------

 This example creates an empty array and then adds field
 definition subarrays using the AADD() function before creating
 People.dbf.  You might use this technique to add field definitions to
 your structure array dynamically:
 
 aDbf := {}
 AADD(aDbf, { "Name", "C", 25, 0 })
 AADD(aDbf, { "Address", "C", 1024, 0 })
 AADD(aDbf, { "Phone", "N", 13, 0 })
 //
 DBCREATE("People", aDbf)
 
 This example performs the same types of actions but declares
 the structure array as a two-dimensional array, and then uses
 subscript addressing to specify the field definitions.  It will be
 created using the DBFMDX RDD:
 
 #include "dbstruct.ch"
 //
 LOCAL aDbf[1][4]
 aDbf[1][ DBS_NAME ] := "Name"
 aDbf[1][ DBS_TYPE ] := "Character"
 aDbf[1][ DBS_LEN ]  := 25
 aDbf[1][ DBS_DEC ]  := 0
 //
 DBCREATE("Name", aDbf, "DBFMDX")

Function DBCREATEINDEX()

 DBCREATEINDEX(<cIndexName>, <cKeyExpr>,
 [<bKeyExpr>], [<lUnique>]) --> NIL

DBCREATEINDEX() creates an index for the database (.dbf) file associated with the current work area. If the work area has active indexes, they are closed. After the new index is created, it becomes the controlling index for the work area and is positioned to the first logical record.

DBCREATEINDEX() performs the same function as the standard INDEX command. For more information, refer to the INDEX command.

 This example creates an index file, Name, indexed on the Name
 field:
 
 USE Employees NEW
 DBCREATEINDEX( "Name", "Name", { || Name })

Function DBDELETE()

 DBDELETE() --> NIL

DBDELETE() marks the current record as deleted. Records marked for deletion can be filtered using SET DELETED or removed from the file using the PACK command.

DBDELETE() performs the same function as the standard DELETE command with a scope of the current record. For more information, refer to the DELETE command.

 The following example deletes a record after a successful
 record lock:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "LASTNAME" )
 //
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( RLOCK() )
 Sales->( DBDELETE() )
 ? "Record deleted: ", Sales->( DELETED() )
 ELSE
 ? "Unable to lock record..."
 ENDIF
 ELSE
 ? "Not found"
 ENDIF
 

Function DBEDIT()

 DBEDIT([<nTop>], [<nLeft>],
 [<nBottom>], <nRight>],
 [<acColumns>],
 [<cUserFunction>],
 [<acColumnSayPictures> | <cColumnSayPicture>],
 [<acColumnHeaders> | <cColumnHeader>],
 [<acHeadingSeparators> | <cHeadingSeparator>],
 [<acColumnSeparators> | <cColumnSeparator>],
 [<acFootingSeparators> | <cFootingSeparator>],
 [<acColumnFootings> | <cColumnFooting>]) --> NIL

DBEDIT() is a user interface and compatibility function that displays records from one or more work areas in a table form. The DBEDIT() window display is a grid of cells divided into columns and rows. Columns correspond to database fields and rows correspond to database records. Each column is defined by an element of the <acColumns> array. The display width of each column is determined by the evaluation of the column expression in <acColumns> array or the column picture specified in the <acColumnSayPictures> array.

All cursor movement keys are handled within DBEDIT(), including Page up, Page down, Home, End, the four arrow keys, and all Ctrl key combinations that produce cursor movement. The navigation keys that DBEDIT() responds to when a user function argument is not specified are listed in the Active Keys table below:

DBEDIT() Active Keys
 ------------------------------------------------------------------------
 Key                 Action
 ------------------------------------------------------------------------
 Up arrow            Up one row
 Down arrow          Down one row
 Left arrow          Column left
 Right arrow         Column right
 Ctrl+Left arrow     Pan left one column
 Ctrl+Right arrow    Pan right one column
 Home                Leftmost current screen column
 End                 Rightmost current screen column
 Ctrl+Home           Leftmost column
 Ctrl+End            Rightmost column
 PgUp                Previous screen
 PgDn                Next screen
 Ctrl+PgUp           First row of current column
 Ctrl+PgDn           Last row of current column
 Return              Terminate DBEDIT()
 Esc                 Terminate DBEDIT()
 ------------------------------------------------------------------------
 

When the user function argument (<cUserFunction>) is specified, all keys indicated in the Active Keys table are active with the exception of Esc and Return. When DBEDIT() calls the user function, it automatically passes two arguments:

The current mode passed as a numeric value

The index of the current column in <acColumns> passed as a numeric value

The mode parameter indicates the current state of DBEDIT() depending on the last key executed. The possible mode values are listed in the DBEDIT() Modes table below:

DBEDIT() Modes
 ------------------------------------------------------------------------
 Status  Dbedit.ch      Description
 ------------------------------------------------------------------------
 0       DE_IDLE        Idle, any cursor movement keystrokes have been
 handled and no keystrokes are pending
 1       DE_HITTOP      Attempt to cursor past top of file
 2       DE_HITBOTTOM   Attempt to cursor past bottom of file
 3       DE_EMPTY       No records in work area
 4       DE_EXCEPT      Key exception
 ------------------------------------------------------------------------
 

The index parameter points to the position of the current column definition in the <acColumns> array. If <acColumns> is not specified, the index parameter points to the position of the field in the current database structure. Access the field name using FIELD().

A user-defined function must return a value that indicates to DBEDIT() the action to perform. The User Function Return Values table below lists the possible return values and the corresponding actions:

DBEDIT() User Function Return Values
 ------------------------------------------------------------------------
 Value   Dbedit.ch      Description
 ------------------------------------------------------------------------
 0       DE_ABORT       Abort DBEDIT()
 1       DE_CONT        Continue DBEDIT()
 2       DE_REFRESH     Force reread/repaint and continue; after repaint,
 process keys and go to idle
 ------------------------------------------------------------------------
 

A number of instances affect calls to the user function:

A key exception occurs. This happens when DBEDIT() fetches a keystroke that it does not recognize from the keyboard. Any pending keys remain in the keyboard buffer until fetched within the user function or until DBEDIT() continues.

DBEDIT() enters the idle mode (i.e., all pending keys have been processed). This happens when the keyboard is empty or after a screen refresh. In this instance, there is one call to the user function and then DBEDIT() waits for a key.

Beginning or end of file is encountered. This is the same as idle. All executable keys are performed, and there is one call to the user function with the appropriate status message.

Note that when DBEDIT() is first executed, all keys pending in the keyboard buffer are executed and then DBEDIT() enters the idle mode with a user function call. If no keys are pending, the idle mode is immediate.

The user function should handle all modes and status messages received from DBEDIT().

A user-defined function must ensure that the DBEDIT() status is equivalent to DE_EXCEPT (4); otherwise, the value of LASTKEY() is meaningless and a Return value of DE_REFRESH (2) will place the application into an endless loop. For example:

FUNCTION DBEditFunc ( nMode, nColumnPos ) LOCAL RetVal := DE_CONT

IF ( nMode == DE_EXCEPT ) IF ( LASTKEY() == K_F5 ) RetVal := DE_REFRESH ENDIF ENDIF RETURN( RetVal )

DBEDIT() is fully re-entrant, which means you can make nested calls to it. Using this feature, you can have multiple browse windows on the screen at the same time.

DBEDIT() is a compatibility function and, therefore, no longer recommended as a programmable browse facility. As such, it is superseded by the TBrowse object class. For more information, refer to TBrowse class in this chapter.

 This example demonstrates a generic call to DBEDIT():
 
 USE Names NEW
 DBEDIT()
 
 This example demonstrates calling DBEDIT() with a user
 function:
 
 #include "dbedit.ch"
 #include "inkey.ch"
 
 // Array must be visible to other user-defined programs in
 // program
 
 STATIC acColumns := {}
 
 PROCEDURE Main()
 
 USE Names NEW
 INDEX ON Names->Lastname + Names->FirstName TO Names
 
 CLS
 
 acColumns := { "LastName", "FirstName" }
 
 DBEDIT( 5, 5, 20, 70, acColumns, "UserFunc" )
 
 RETURN
 
 
 FUNCTION UserFunc( nMode, nCol )
 LOCAL nKey := LASTKEY()
 LOCAL nRetVal := DE_CONT         // Default return value
 
 DO CASE
 CASE nMode == DE_IDLE
 nRetVal := IdleFunc()
 CASE nMode == DE_HITTOP
 TONE( 100, 3 )
 CASE nMode == DE_HITBOTTOM
 TONE( 100, 3 )
 nRetVal := AppendFunc( nKey )
 CASE nMode == DE_EMPTY
 nRetVal := EmptyFunc()
 CASE nMode == DE_EXCEPT
 nRetVal := ExceptFunc( nKey, nCol )
 OTHERWISE
 TONE( 100, 3 )
 ENDCASE
 
 RETURN nRetVal
 
 FUNCTION AppendFunc( nKey )
 LOCAL nRetVal := DE_CONT         // Default return value
 
 IF nKey == K_DOWN                  // If DOWN ARROW
 APPEND BLANK                  // Append blank record
 // Note: The appended record will appear at the top of the
 //       DBEDIT() screen when the database file is indexed.
 
 nRetVal := DE_REFRESH         // Refresh screen
 ENDIF
 
 
 RETURN nRetVal
 
 FUNCTION ExceptFunc( nKey, nCol )
 LOCAL nRetVal := DE_CONT         // Default return value
 
 DO CASE
 CASE nKey == K_ESC                  // If ESCAPE
 nRetVal := DE_ABORT               // Exit
 CASE nKey == K_RETURN               // If RETURN
 nRetVal := EditFunc( nCol )      // Function to edit
 // field
 
 // Toggle DELETED status
 CASE nKey == K_DEL .AND. LASTREC() != 0  // DELETE pressed
 IF DELETED()
 RECALL
 ELSE
 DELETE
 ENDIF
 OTHERWISE
 TONE( 100, 1 )
 ENDCASE
 
 RETURN nRetVal
 
 
 FUNCTION EditFunc( nCol )
 LOCAL cIndexVal         // Value of current key expression
 LOCAL nRetVal            // Return value
 LOCAL nField            // Position of current field
 LOCAL cFieldVal         // Value of current field
 LOCAL nCursSave         // Preserve state of cursor
 
 // This will return an error if no index is open
 cIndexVal := &( INDEXKEY(0) )
 
 nField := FIELDPOS( acColumns[nCol] )
 
 IF nField != 0
 nCursSave := SETCURSOR()         // Save state of cursor
 SETCURSOR(1)                     // Change cursor shape
 cFieldVal := FIELDGET( nField )         // Save contents
 // of field
 @ ROW(), COL() GET cFieldVal            // GET new value
 READ
 FIELDPUT( nField, cFieldVal )            // REPLACE with
 // new value
 SETCURSOR( nCursSave )                  // Restore cursor
 // shape
 ENDIF
 
 
 IF cIndexVal != &( INDEXKEY(0) )         // If key expression
 
 // changed
 nRequest := DE_REFRESH               // Refresh screen
 ELSE                                    // Otherwise
 nRequest := DE_CONT                  // Continue
 ENDIF
 
 RETURN nRequest
 
 FUNCTION IdleFunc()
 // Idle routine
 RETURN DE_CONT
 
 FUNCTION EmptyFunc()
 // Empty Records routine
 RETURN DE_CONT

Function DBEVAL()

 DBEVAL(<bBlock>,[<bForCondition>],[<bWhileCondition>],[<nNextRecords>],
 [<nRecord>],[<lRest>]) --> NIL

DBEVAL() is a database function that evaluates a single block for each record within the current work area that matches a specified scope and/or condition. On each iteration, DBEVAL() evaluates the specified block. All records within the scope or matching the condition are processed until the end of file is reached.

By default, DBEVAL() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression.

DBEVAL() is similar to AEVAL() which applies a block to each element in an array. Like AEVAL(), DBEVAL() can be used as a primitive for the construction of user-defined commands that process database files. In fact, many of the standard xClipper database processing commands are created using DBEVAL().

Refer to the Code Blocks section in the "Basic Concepts" chapter of the Programming and Utilities Guide for more information on the syntax and theory of code blocks; and refer also to the Database System section in the same chapter for information on record scoping and conditions. Also refer to the xClipper standard header file, std.ch, found in \include for examples of xClipper database command definitions that use DBEVAL().

  This example uses DBEVAL() to implement Count(), a user-
 defined function that counts the number of records in a work area
 matching a specified scope.  The scope is passed as an array to
 Count().  To make the example more interesting, there is a user-
 defined command to create the scope array, thereby allowing you to
 specify the scope in a familiar form.  Additionally, there is a set
 of manifest constants that define the attributes of the scope object.
 
 // Scope command definition
 #command CREATE SCOPE <aScope> [FOR <for>] ;
 [WHILE <while>] [NEXT <next>] [RECORD <rec>] ;
 [<rest:REST>] [ALL];
 =>;
 <aScope> := { <{for}>, <{while}>, <next>, ;
 <rec>, <.rest.> }
 //
 
 // Scope attribute constants
 #define FOR_COND      1
 #define WHILE_COND    2
 #define NEXT_SCOPE    3
 #define REC_SCOPE     4
 #define REST_SCOPE    5
 //
 // Create a scope and count records using it
 LOCAL mySet, myCount
 USE Customer NEW
 CREATE SCOPE mySet FOR Customer = "Smith" WHILE ;
 Zip > "90000"
 myCount := Count( mySet )
 RETURN
 
 FUNCTION Count( aScope )
 LOCAL nCount := 0
 DBEVAL( {|| nCount++},;
 aScope[ FOR_COND ],;
 aScope[ WHILE_COND ],;
 aScope[ NEXT_SCOPE ],;
 aScope[ REC_SCOPE ],;
 aScope[ REST_SCOPE ];
 )
 RETURN nCount
 
 

Function DBF()

 DBF() --> cAlias

DBF() is a compatibility function that replicates the DBF() function in xBASE. xClipper implements it by invoking the ALIAS() function without an argument.

DBF() is a compatibility function and, therefore, no longer recommended. It is superseded entirely by the ALIAS() function.

Function DBFILTER()

 DBFILTER() --> cFilter

DBFILTER() is a database function used to save and re-execute an active filter by returning the filter expression as a character string that can be later recompiled and executed using the macro operator (&). This function operates like the DBRELATION() and DBRSELECT() functions which save and re-execute the linking expression of a relation within a work area.

Since each work area can have an active filter, DBFILTER() can return the filter expression of any work area. This is done by referring to DBFILTER() within an aliased expression as demonstrated below.

 This example opens two database files, sets two filters, and
 then displays the filter expressions for both work areas:
 
 USE Customer INDEX Customer NEW
 SET FILTER TO Last = "Smith"
 USE Invoices INDEX Invoices NEW
 SET FILTER TO CustId = "Smi001"
 SELECT Customer
 //
 ? DBFILTER()                      // Result: Last = "Smith"
 ? Invoices->(DBFILTER())      // Result: Custid = "Smi001"
 
 This user-defined function, CreateQry(), uses DBFILTER() to
 create a memory file containing the current filter expression in the
 private variable cFilter:
 
 FUNCTION CreateQry( cQryName )
 PRIVATE cFilter := DBFILTER()
 SAVE ALL LIKE cFilter TO (cQryName + ".qwy")
 RETURN NIL
 
 You can later RESTORE a query file with this user-defined
 function, SetFilter():
 
 FUNCTION SetFilter()
 PARAMETER cQryName
 RESTORE FROM &cQryName..qwy ADDITIVE
 SET FILTER TO &cFilter.
 RETURN NIL

Function DBGOBOTTOM()

 DBGOBOTTOM() --> NIL

DBGOBOTTOM() moves to the last logical record in the current work area.

DBGOBOTTOM() performs the same function as the standard GO BOTTOM command. For more information, refer to the GO command.

 The following example uses DBGOBOTTOM() to position the record
 pointer on the last logical record:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "LASTNAME" )
 //
 Sales->( DBGOBOTTOM() )
 IF ( Sales->Last == "Winston" )
 IF RLOCK()
 Sales->( DBDELETE() )
 ? "Record deleted: ", Sales->( DELETED() )
 ELSE
 ? "Unable to lock record..."
 ENDIF
 END

Function DBGOTO()

 DBGOTO(<xIdentity>) --> NIL

DBGOTO() is a database function that positions the record pointer in the current work area at the specified <xIdentity>. In an Xbase data structure, this identity is the record number because every record, even an empty record, has a record number. In non-Xbase data structures, identity may be defined as something other than record number.

Function DBGOTOP()

 DBGOTOP() --> NIL

DBGOTOP() moves to the first logical record in the current work area.

DBGOTOP() performs the same function as the standard GO TOP command. For more information, refer to the GO TOP command.

 This example demonstrates the typical use of DBGOTOP():
 
 DBGOTOP()
 WHILE ( !EOF() )
 ? FIELD->Name
 DBSKIP()
 END

Function DBRECALL()

 DBRECALL() --> NIL

DBRECALL() causes the current record to be reinstated if it is marked for deletion.

DBRECALL() performs the same function as the RECALL command. For more information, refer to the DELETE and RECALL commands.

 The following example recalls a record if it is deleted and
 attempts to lock the record if successful:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "LASTNAME" )
 //
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( DELETED() )
 IF Sales( RLOCK() )
 Sales( DBRECALL() )
 ? "Record recalled"
 ELSE
 "Unable to lock record..."
 ENDIF
 ENDIF
 ELSE
 ? "Not found"
 ENDIF

Function DBREINDEX()

 DBREINDEX() --> NIL

DBREINDEX() rebuilds all active indexes associated with the current work area. After the indexes are recreated, the work area is moved to the first logical record in the controlling order.

DBREINDEX() performs the same function as the standard REINDEX command. For more information, refer to the REINDEX command.

 The following example reindexes the work area:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "LASTNAME" )
 //
 IF ( Sales->(DBSEEK(cLast)) )
 IF RLOCK()
 DELETE FOR Sales->LastName == "Winston"
 Sales->( DBREINDEX() )
 ELSE
 ? "Unable to lock record..."
 ENDIF
 ELSE
 ? "Not found"
 ENDIF

Function DBRELATION()

 DBRELATION(<nRelation>) --> cLinkExp

DBRELATION() is a database function used with DBRSELECT() to determine the linking expression and work area of an existing relation created with the SET RELATION command.

DBRELATION() returns the linking expression defined by the TO clause. DBRSELECT() returns the work area linked as defined by the INTO clause.

By default, DBRELATION() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

 This example opens three database files, sets two child
 relations from the parent work area, and then displays the linking
 expression to the second child work area:
 
 USE Invoices INDEX Invoices NEW
 USE BackOrder INDEX BackOrder NEW
 USE Customer INDEX Customer NEW
 SET RELATION TO CustNum INTO Invoices, OrderNum ;
 INTO BackOrder
 //
 ? DBRELATION(2)                  // Result: OrderNum
 
 Later you can query the same linking expression from an
 unselected work area by using an aliased expression like this:
 
 USE Archive NEW
 ? Customer->(DBRELATION(2))   // Result: OrderNum
 
 This example is a user-defined function, Relation(), that
 returns the results of both DBRELATION() and DBRSELECT() as an array:
 
 FUNCTION Relation( nRelation )
 RETURN { DBRELATION(nRelation), ;
 ALIAS(DBRSELECT(nRelation)) }

Function DBRSELECT()

 DBRSELECT(<nRelation>) --> nWorkArea

DBRSELECT() is a database function used in combination with DBRELATION() to determine the work area and linking expression of an existing relation created with the SET RELATION command. DBRSELECT() returns the work area defined by the INTO clause. DBRELATION() returns the linking expression defined by the TO clause. To determine the alias of the relation instead of the work area number, use the expression ALIAS(DBRSELECT(<nRelation>)).

By default, DBRSELECT() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

 This example opens three database files, sets two child
 relations from the parent work area, and then displays the linking
 expression to the second child work area, as well as the target work
 area of the relation:
 
 USE Invoices INDEX Invoices NEW
 USE BackOrder INDEX BackOrder NEW
 USE Customer INDEX Customer NEW
 SET RELATION TO CustNum INTO Customer, ;
 OrderNum INTO BackOrder
 //
 ? DBRELATION(2), DBRSELECT(2)         // Result: OrderNum 3
 
 ? ALIAS(DBRSELECT(2))               // Result: BACKORDER
 
 Later, you can query the same information from an unselected
 work area by using an aliased expression:
 
 USE Archive NEW
 ? Customer->(DBRELATION(2))      // Result: OrderNum
 ? Customer->(DBRSELECT(2))         // Result: 3

Function DBSEEK()

 DBSEEK(<expKey>, [<lSoftSeek>], [<lLast>]) --> lFound

DBSEEK() moves to the first logical record whose key value is equal to <expKey>. If such a record is found, it becomes the current record and DBSEEK() returns true (.T.); otherwise, it returns false (.F.). the positioning of the work area is as follows: for a normal (not soft) seek, the work area is positioned to LASTREC() + 1 and EOF() returns true (.T.); for a soft seek, the work area is positioned to the first record whose key value is greater than the specified key value. If no such record exists, the work area is positioned to LASTREC() + 1 and EOF() returns true (.T.).

For a work area with no active indexes, DBSEEK() has no effect.

DBSEEK() performs the same function as the standard SEEK command. For more information, refer to the SEEK command.

 In this example, DBSEEK() moves the pointer to the record in
 the database, Employee, in which the value in FIELD "cName" matches
 the entered value of cName:
 
 ACCEPT "Employee name: " TO cName
 IF ( Employee->(DBSEEK(cName)) )
 Employee->(VIEWRECORD())
 ELSE
 ? "Not found"
 END

Function DBSELECTAREA()

 DBSELECTAREA(<nArea> | <cAlias>) --> NIL

DBSELECTAREA() causes the specified work area to become the current work area. All subsequent database operations will apply to this work area unless another work area is explicitly specified for an operation. DBSELECTAREA() performs the same function as the standard SELECT command. For more information, refer to the SELECT command.

 The following example selects a work area via the alias name:
 
 cLast := "Winston"
 DBUSEAREA( .T., "DBFNTX", "Sales", "Sales", .T. )
 DBSETINDEX( "SALEFNAM" )
 DBSETINDEX( "SALELNAM" )
 //
 DBUSEAREA( .T., "DBFNTX", "Colls", "Colls", .T. )
 DBSETINDEX( "COLLFNAM" )
 DBSETINDEX( "COLLLNAM" )
 //
 DBSELECTAREA( "Sales" )      // select "Sales" work area
 //
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( DELETED() ) .AND. Sales->( RLOCK() )
 Sales->( DBRECALL() )
 ? "Deleted record has been recalled."
 ENDIF
 ELSE
 ? "Not found"
 ENDIF

Function DBSETDRIVER()

 DBSETDRIVER([<cDriver>]) --> cCurrentDriver

DBSETDRIVER() sets the database driver to be used when activating new work areas without specifying a driver. If the specified driver is not available to the application, the call has no effect. DBSETDRIVER() returns the name of the current default driver, if any.

 This example makes the "DBFNDX" driver the default driver.  If
 the driver is unavailable, a message is issued:
 
 DBSETDRIVER("DBFNDX")
 IF ( DBSETDRIVER() <> "DBFNDX" )
 ? "DBFNDX driver not available"
 ENDIF

Function DBSETFILTER()

 DBSETFILTER(<bCondition>, [<cCondition>]) --> NIL

DBSETFILTER() sets a logical filter condition for the current work area. When a filter is set, records which do not meet the filter condition are not logically visible. That is, database operations which act on logical records will not consider these records.

The filter expression supplied to DBSETFILTER() evaluates to true (.T.) if the current record meets the filter condition; otherwise, it should evaluate to false (.F.).

The filter expression may be a code block (<bCondition>) or both a code block and equivalent text (<cCondition>). If both versions are supplied, they must express the same condition. If the text version is omitted, DBFILTER() will return an empty string for the work area.

DBSETFILTER() performs the same function as the standard SET FILTER command. For more information, refer to the SET FILTER command.

 This example limits data access to records in which the Age
 field value is less than 40:
 
 USE Employee NEW
 DBSETFILTER( {|| Age < 40}, "Age < 40" )
 DBGOTOP()

Function DBSETINDEX()

 DBSETINDEX(<cOrderBagName>) --> NIL

DBSETINDEX() is a database function that adds the contents of an order bag into the order list of the current work area. Any orders already associated with the work area continue to be active. If the newly opened order bag is the only order associated with the work area, it becomes the controlling order; otherwise, the controlling order remains unchanged. If the order bag contains more than one order, and there are no other orders associated with the work area, the first order in the new order bag becomes the controlling order.

Note: DBSETINDEX() does not close all currently open index files.

DBSETINDEX() is a compatibility command and therefore is not recommended. It is superseded by the ORDLISTADD() function.

Function DBSETORDER()

 DBSETORDER(<nOrderNum>) --> NIL

DBSETORDER() controls which of the current work area's active indexes is the controlling index. The controlling index is the index which determines the logical order of records in the work area.

Active indexes are numbered from 1 to the number of active indexes, based on the order in which the indexes were opened. <nOrderNum> specifies the number of the desired index.

DBSETORDER() performs the same function as the standard SET ORDER command. For more information, refer to the SET ORDER command.

 This example sets the second named index, Age, as the
 controlling index:
 
 USE Employee NEW
 SET INDEX TO Name, Age
 DBSETORDER(2)

Function DBSETRELATION()

 DBSETRELATION(<nArea> | <cAlias>, <bExpr>, <cExpr>) --> NIL

DBSETRELATION() relates the work area specified by <nArea> or <cAlias> (the child work area) to the current work area (the parent work area). Any existing relations remain active.

Relating work areas synchronizes the child work area with the parent work area. This is achieved by automatically repositioning the child work area whenever the parent work area moves to a new record. If there is an active index in the child work area, moving the parent work area causes an automatic SEEK operation in the child work area; the seek key is based on the expression specified by <bExpr> and/or <cExpr>. If the child work area has no active index, moving the parent work area causes an automatic GOTO in the child work area; the record number for the GOTO is based on the expression specified by <bExpr> and/or <cExpr>.

The relational expression may be a code block (<bExpr>) or both a code block and equivalent text (<cExpr>). If both versions are supplied, they must be equivalent. If the text version is omitted, DBRELATION() will return an empty string for the relation.

DBSETRELATION() performs the same function as the standard SET RELATION command with the ADDITIVE clause. For more information, refer to the SET RELATION command.

 This example demonstrates a typical use of the DBSETRELATION()
 function:
 
 USE Employee NEW
 USE Department NEW INDEX Dept
 SELECT Employee
 DBSETRELATION("Department", {|| Employee->Dept},;
 "Employee->Dept")
 LIST Employee->Name, Department->Name

Function DBSKIP()

 DBSKIP([<nRecords>]) --> NIL

DBSKIP() moves either forward or backward relative to the current record. Attempting to skip forward beyond the last record positions the work area to LASTREC() + 1 and EOF() returns true (.T.). Attempting to skip backward beyond the first record positions the work area to the first record and BOF() returns true (.T.).

DBSKIP() performs the same function as the standard SKIP command. For more information, refer to the SKIP command.

 This example demonstrates a typical use of the DBSKIP()
 function:
 
 DBGOTOP()
 
 DO WHILE ( !EOF() )
 ? FIELD->Name
 DBSKIP()
 ENDDO

Function DBSTRUCT()

 DBSTRUCT() --> aStruct

DBSTRUCT() is a database function that operates like COPY STRUCTURE EXTENDED by creating an array of structure information rather than a database file of structure information. There is another function, DBCREATE(), that can create a database file from the structure array.

By default, DBSTRUCT() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression as shown below.

Note, a header file, dbstruct.ch, located in \include contains a series of manifest constants for each field attribute.

DBSTRUCT() Return Array
 ------------------------------------------------------------------------
 Position     Metasymbol     dbstruct.ch
 ------------------------------------------------------------------------
 1            cName          DBS_NAME
 2            cType          DBS_TYPE
 3            nLength        DBS_LEN
 4            nDecimals      DBS_DEC
 ------------------------------------------------------------------------
 

 This example opens two database files and then creates an
 array containing the database structure using DBSTRUCT() within an
 aliased expression.  The field names are then listed using AEVAL():
 
 #include "dbstruct.ch"
 //
 LOCAL aStruct
 USE Customer NEW
 USE Invoices NEW
 //
 aStruct := Customer->(DBSTRUCT())
 AEVAL( aStruct, {|aField| QOUT(aField[DBS_NAME])} )

Function DBUNLOCK()

 DBUNLOCK() --> NIL

DBUNLOCK() releases any record or file locks obtained by the current process for the current work area. DBUNLOCK() is only meaningful on a shared database in a network environment.

DBUNLOCK() performs the same function as the standard UNLOCK command. For more information, refer to the UNLOCK command.

 The following example illustrates a basic use of the
 DBUNLOCK() function:
 
 cLast := "Winston"
 USE Sales SHARED NEW VIA "DBFNTX"
 DBSETINDEX( "LASTNAME" )
 //
 IF ( Sales->(DBSEEK(cLast)) )
 IF Sales->( RLOCK() )
 Sales->( DBDELETE() )
 
 ? "Record deleted: ", Sales( DELETED() )
 Sales->( DBUNLOCK() )
 ELSE
 ? "Unable to lock record..."
 ENDIF
 ELSE
 ? "Not found"
 ENDIF

Function DBUNLOCKALL()

 DBUNLOCKALL() --> NIL

DBUNLOCKALL() releases any record or file locks obtained by the current process for any work area. DBUNLOCKALL() is only meaningful on a shared database in a network environment. It is equivalent to calling DBUNLOCK() on every occupied work area.

DBUNLOCKALL() performs the same function as the UNLOCK ALL command. For more information, refer to the UNLOCK ALL command.

 The following example marks a record for deletion if an
 RLOCK() attempt is successful, then clears all locks in all work
 areas:
 
 cLast := "Winston"
 USE Sales SHARED NEW VIA "DBFNTX"
 DBSETINDEX( "SALEFNAM" )
 DBSETINDEX( "SALELNAM" )
 //
 USE Colls SHARED NEW VIA "DBFNTX"
 DBSETINDEX( "COLLFNAM" )
 DBSETINDEX( "COLLLNAM" )
 //
 DBSELECTAREA( "Sales" )      // select "Sales" work area
 //
 IF ( Colls->(DBSEEK(cLast)) )
 IF Colls->( DELETED() )
 ? "Record deleted: ", Colls->( DELETED() )
 
 IF Colls->( RLOCK() )
 Colls->( DBRECALL() )
 ? "Record recalled..."
 ENDIF
 ENDIF
 ELSE
 ? "Not found"
 DBUNLOCKALL()            // remove all locks in
 ENDIF                        // all work areas

Function DBUSEAREA()

 DBUSEAREA( [<lNewArea>], [<cDriver>], <cName>, [<xcAlias>],
 [<lShared>], [<lReadonly>]) --> NIL

DBUSEAREA() associates the specified database (.dbf) file with the current work area. It performs the same function as the standard USE command. For more information, refer to the USE command.

 This example is a typical use of the DBUSEAREA() function:
 
 DBUSEAREA(.T., "DBFNDX", "Employees")

Function DELETED()

 DELETED() --> lDeleted

DELETED() is a database function that determines if the current record in the active work area is marked for deletion. Since each work area with an open database file can have a current record, each work area has its own DELETED() value.

By default, DELETED() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

In applications, DELETED() is generally used to query the deleted status as a part of record processing conditions, or to display the deleted status as a part of screens and reports.

 This example uses DELETED() in the current and in an
 unselected work area:
 
 USE Customer NEW
 USE Sales NEW
 ? DELETED()                     // Result: .F.
 DELETE
 ? DELETED()                     // Result: .T.
 ? Customer->(DELETED())         // Result: .F.
 
 This example uses DELETED() to display a record's deleted
 status in screens and reports:
 
 @ 1, 65 SAY IF(DELETED(), "Inactive", "Active")

Function DESCEND()

 DESCEND(<exp>) --> ValueInverted

DESCEND() is a conversion function that returns the inverted form of the specified expression to be used with INDEX to create descending order indexes. Specify that part of the index expression you want to be descending as the DESCEND() argument. To subsequently perform a lookup with SEEK, specify DESCEND() in the search expression.

 This example uses DESCEND() in an INDEX expression to create a
 descending order date index:
 
 USE Sales NEW
 INDEX ON DESCEND(OrdDate) TO SalesDate
 
 Later, use DESCEND() to SEEK on the descending index:
 
 SEEK DESCEND(dFindDate)
 
 This example illustrates how to create a descending order
 index using more than one data type.  Here, the key is created using
 the concatenation of date and character fields after the appropriate
 type conversion has taken place.  This example uses STR() instead of
 DTOS(), since DESCEND() of a date returns a numeric value:
 
 USE Sales NEW
 INDEX ON STR(DESCEND(SaleDate)) + Salesman TO LastSale

Function EOF()

 EOF() --> lBoundary

EOF() is a database function used to test for an end of file boundary condition when the record pointer is moving forward through a database file. Any command that can move the record pointer can set EOF().

The most typical application is as a part of the <lCondition> argument of a DO WHILE construct that sequentially processes records in a database file. Here <lCondition> would include a test for .NOT. EOF(), forcing the DO WHILE loop to terminate when EOF() returns true (.T.).

EOF() and FOUND() are often used interchangeably to test whether a SEEK, FIND, or LOCATE command failed. With these commands, however, FOUND() is preferred.

When EOF() returns true (.T.), the record pointer is positioned at LASTREC() + 1 regardless of whether there is an active SET FILTER or SET DELETED is ON. Further attempts to move the record pointer forward return the same result without error. Once EOF() is set to true (.T.), it retains its value until there is another attempt to move the record pointer.

By default, EOF() operates on the currently selected work area. It can be made to operate on an unselected work area by specifying it within an aliased expression (see example below).

 This example demonstrates EOF() by deliberately moving the
 record pointer beyond the last record:
 
 USE Sales
 GO BOTTOM
 ? EOF()            // Result: .F.
 SKIP
 ? EOF()            // Result: .T.
 
 This example uses aliased expressions to query the value of
 EOF() in unselected work areas:
 
 USE Sales NEW
 USE Customer NEW
 ? Sales->(EOF())
 ? Customer->(EOF())
 
 This example illustrates how EOF() can be used as part of a
 condition for sequential database file operations:
 
 USE Sales INDEX CustNum NEW
 DO WHILE !EOF()
 nOldCust := Sales->CustNum
 nTotalAmount := 0
 DO WHILE nOldCust = Sales->CustNum .AND. (!EOF())
 ? Sales->CustNum, Sales->Description, ;
 Sales->SaleAmount
 nTotalAmount += Sales->SaleAmount
 SKIP
 ENDDO
 ? "Total amount: ", nTotalAmount
 ENDDO

Function FCOUNT()

 FCOUNT() --> nFields

FCOUNT() is a database function. It is useful in applications containing data-independent programs that can operate on any database file. These include generalized import/export and reporting programs. Typically, you use FCOUNT() to establish the upper limit of a FOR...NEXT or DO WHILE loop that processes a single field at a time.

By default, FCOUNT() operates on the currently selected work area.

 This example illustrates FCOUNT(), returning the number of
 fields in the current and an unselected work area:
 
 USE Sales NEW
 USE Customer NEW
 ? FCOUNT()                     // Result: 5
 ? Sales->(FCOUNT())            // Result: 8
 
 This example uses FCOUNT() to DECLARE an array with field
 information:
 
 LOCAL aFields := ARRAY(FCOUNT())
 AFIELDS(aFields)
 
 This example uses FCOUNT() as the upper boundary of a FOR loop
 that processes the list of current work area fields:
 
 LOCAL nField
 USE Sales NEW
 FOR nField := 1 TO FCOUNT()
 ? FIELD(nField)
 NEXT

Function FIELDGET()

 FIELDGET(<nField>) --> ValueField

FIELDGET() is a database function that retrieves the value of a field using its position within the database file structure rather than its field name. Within generic database service functions this allows, among other things the retrieval of field values without use of the macro operator.

 This example compares FIELDGET() to functionally equivalent
 code that uses the macro operator to retrieve the value of a field:
 
 LOCAL nField := 1, FName, FVal
 USE Customer NEW
 //
 // Using macro operator
 FName := FIELD( nField )           // Get field name
 FVal := &FName                     // Get field value
 // Using FIELDGET()
 FVal := FIELDGET( nField )         // Get field value

Function FIELDNAME()

 FIELDNAME/FIELD(<nPosition>) --> cFieldName

FIELDNAME() is a database function that returns a field name using an index to the position of the field name in the database structure. Use it in data-independent applications where the field name is unknown. If information for more than one field is required, use AFIELDS() to create an array of field information or COPY STRUCTURE EXTENDED to create a database of field information.

If you need additional database file structure information, use TYPE() and LEN(). To obtain the number of decimal places for a numeric field, use the following expression:

LEN(SUBSTR(STR(<idField>), RAT(".", ;

STR(<idField>)) + 1))

By default, FIELDNAME() operates on the currently selected work area as shown in the example below.

 These examples illustrate FIELDNAME() used with several other
 functions:
 
 USE Sales
 ? FIELDNAME(1)              // Result: BRANCH
 ? FCOUNT()                  // Result: 5
 ? LEN(FIELDNAME(0))         // Result: 0
 ? LEN(FIELDNAME(40))        // Result: 0
 
 This example uses FIELDNAME() to list the name and type of
 each field in Customer.dbf:
 
 USE Customer NEW
 FOR nField := 1 TO FCOUNT()
 ? PADR(FIELDNAME(nField), 10),;
 VALTYPE(&(FIELDNAME(nField)))
 NEXT
 
 This example accesses fields in unselected work areas using
 aliased expressions:
 
 USE Sales NEW
 USE Customer NEW
 USE Invoices NEW
 //
 ? Sales->(FIELDNAME(1))            // Result: SALENUM
 ? Customer->(FIELDNAME(1))         // Result: CUSTNUM

Function FIELDPOS()

 FIELDPOS(<cFieldName>) --> nFieldPos

FIELDPOS() is a database function that is the inverse of the FIELDNAME() function. FIELDPOS() is most often used with the FIELDPUT() and FIELDGET() functions.

FIELDPOS() return the names of fields in any unselected work area by referring to the function using an aliased expression. See the example below.

 This example demonstrates a typical specification of the
 FIELDPOS() function:
 
 USE Customer NEW
 ? FIELDPOS("Name")                     // Result: 1
 ? FIELDGET(FIELDPOS("Name"))           // Result: Kate
 
 This example uses FIELDPOS() to return the position of a
 specified field in a unselected work area:
 
 USE Customer NEW
 USE Invoices NEW
 ? Customer->(FIELDPOS("Name"))         // Result: 1
 ? Customer->(FIELDGET(FIELDPOS("Name")))
 // Result: Kate

Function FIELDPUT()

 FIELDPUT(<nField>, <expAssign>) --> ValueAssigned

FIELDPUT() is a database function that assigns <expAssign> to the field at ordinal position <nField> in the current work area. This function allows you to set the value of a field using its position within the database file structure rather than its field name. Within generic database service functions this allows, among other things, the setting of field values without use of the macro operator.

 This example compares FIELDPUT() to functionally equivalent
 code that uses the macro operator to set the value of a field:
 
 // Using macro operator
 FName := FIELD(nField)           // Get field name
 FIELD->&FName := FVal            // Set field value
 // Using FIELDPUT()
 FIELDPUT(nField, FVal)           // Set field value

Function FLOCK()

 FLOCK() --> lSuccess

FLOCK() is a database function used in network environments to lock an open and shared database file, preventing other users from updating the file until the lock is released. Records in the locked file are accessible for read-only operations.

FLOCK() is related to USE...EXCLUSIVE and RLOCK(). USE...EXCLUSIVE opens a database file so that no other user can open the same file at the same time and is the most restrictive locking mechanism in xClipper. RLOCK() is the least restrictive and attempts to place an update lock on a shared record, precluding other users from updating the current record. FLOCK() falls in the middle.

FLOCK() is used for operations that access the entire database file. Typically, these are commands that update the file with a scope or a condition such as DELETE or REPLACE ALL. The following is a list of such commands:

Commands that require an FLOCK()
 ------------------------------------------------------------------------
 Command                       Mode
 ------------------------------------------------------------------------
 APPEND FROM                   FLOCK() or USE...EXCLUSIVE
 DELETE (multiple records)     FLOCK() or USE...EXCLUSIVE
 RECALL (multiple records)     FLOCK() or USE...EXCLUSIVE
 REPLACE (multiple records)    FLOCK() or USE...EXCLUSIVE
 UPDATE ON                     FLOCK() or USE...EXCLUSIVE
 ------------------------------------------------------------------------
 

For each invocation of FLOCK(), there is one attempt to lock the database file, and the result is returned as a logical value. A file lock fails if another user currently has a file or record lock for the same database file or EXCLUSIVE USE of the database file. If FLOCK() is successful, the file lock remains in place until you UNLOCK, CLOSE the DATABASE, or RLOCK().

By default, FLOCK() operates on the currently selected work area as shown in the example below.

 This example uses FLOCK() for a batch update of prices in
 Inventory.dbf:
 
 USE Inventory NEW
 IF FLOCK()
 REPLACE ALL Inventory->Price WITH ;
 Inventory->Price * 1.1
 ELSE
 ? "File not available"
 ENDIF
 
 This example uses an aliased expression to attempt a file lock
 in an unselected work area:
 
 USE Sales NEW
 USE Customer NEW
 //
 IF !Sales->(FLOCK())
 ? "Sales is in use by another"
 ENDIF

Function FOUND()

 FOUND() --> lSuccess

FOUND() is a database function that determines whether a search operation (i.e., FIND, LOCATE, CONTINUE, SEEK, or SET RELATION) succeeded. When any of these commands are executed, FOUND() is set to true (.T.) if there is a match; otherwise, it is set to false (.F.).

If the search command is LOCATE or CONTINUE, a match is the next record meeting the scope and condition. If the search command is FIND, SEEK or SET RELATION, a match is the first key in the controlling index that equals the search argument. If the key value equals the search argument, FOUND() is true (.T.); otherwise, it is false (.F.).

The value of FOUND() is retained until another record movement command is executed. Unless the command is another search command, FOUND() is automatically set to false (.F.).

Each work area has a FOUND() value. This means that if one work area has a RELATION set to a child work area, querying FOUND() in the child returns true (.T.) if there is a match.

By default, FOUND() operates on the currently selected work area. It can be made to operate on an unselected work area by specifying it within an aliased expression (see example below).

FOUND() will return false (.F.) if there is no database open in the current work area.

 This example illustrates the behavior of FOUND() after a
 record movement command:
 
 USE Sales INDEX Sales
 ? INDEXKEY(0)              // Result: SALESMAN
 SEEK "1000"
 ? FOUND()                  // Result: .F.
 SEEK "100"
 ? FOUND()                  // Result: .T.
 SKIP
 ? FOUND()                  // Result: .F.
 
 This example tests a FOUND() value in an unselected work area
 using an aliased expression:
 
 USE Sales INDEX Sales NEW
 USE Customer INDEX Customer NEW
 SET RELATION TO CustNum INTO Sales
 //
 SEEK "Smith"
 ? FOUND(), Sales->(FOUND())
 
 This code fragment processes all Customer records with the key
 value "Smith" using FOUND() to determine when the key value changes:
 
 USE Customer INDEX Customer NEW
 SEEK "Smith"
 DO WHILE FOUND()
 .
 
 . <statements>
 .
 SKIP
 LOCATE REST WHILE Name == "Smith"
 ENDDO

Function HEADER()

 HEADER() --> nBytes

HEADER() is a database function that is used with LASTREC(), RECSIZE(), and DISKSPACE() to create procedures for backing up files.

By default, HEADER() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

 This example determines the header size of Sales.dbf:
 
 USE Sales NEW
 ? HEADER()            // Result: 258
 
 This example defines a pseudofunction, DbfSize(), that uses
 HEADER() with RECSIZE() and LASTREC() to calculate the size of the
 current database file in bytes:
 
 #define DbfSize()      ((RECSIZE() * LASTREC()) + ;
 HEADER() + 1)
 
 Later you can use DbfSize() as you would any function:
 
 USE Sales NEW
 USE Customer NEW
 ? DbfSize()
 ? Sales->(DbfSize())

Function INDEXEXT()

 INDEXEXT() --> cExtension

INDEXEXT() returns the default index file extension by determining which database driver is currently linked. Note that it is preferable to use ORDBAGEXT() than INDEXEXT().

 In this example, INDEXEXT() creates an existence test for the
 Customer index file independent of the database driver linked into
 the current program:
 
 USE Customer NEW
 //
 IF .NOT. FILE("Customer" + INDEXEXT())
 INDEX ON CustName TO Customer
 ENDIF

Function INDEXKEY()

 INDEXKEY(<nOrder>) --> cKeyExp

INDEXKEY() is a database function that determines the key expression of a specified index in the current work area and returns it as a character string. To evaluate the key expression, specify INDEXKEY() as a macro expression like this: &(INDEXKEY(<nOrder>)).

INDEXKEY() has a number of applications, but two specific instances are important. Using INDEXKEY(), you can TOTAL on the key expression of the controlling index without having to specify the key expression in the source code. The other instance occurs within a DBEDIT() user function. Here, you may want to determine whether or not to update the screen after the user has edited a record. Generally, it is only necessary to update the screen if the key expression of the controlling index has changed for the current record. Both of these examples are illustrated below.

By default, INDEXKEY() operates on the currently selected work area. It can be made to operate on an unselected work area by specifying it within an aliased expression (see example below).

 This example accesses the key expression of open indexes in
 the current work area:
 
 #define ORD_NATURAL      0
 #define ORD_NAME         1
 #define ORD_SERIAL      2
 //
 USE Customer INDEX Name, Serial NEW
 SET ORDER TO ORD_SERIAL
 ? INDEXKEY(ORD_NAME)         // Result: Name index exp
 ? INDEXKEY(ORD_SERIAL)      // Result: Serial index exp
 ? INDEXKEY(ORD_NATURAL)      // Result: Serial index exp
 
 This example accesses the key expression of the controlling
 index in an unselected work area:
 
 USE Customer INDEX Name, Serial NEW
 USE Sales INDEX Salesman NEW
 ? INDEXKEY(0), Customer->(INDEXKEY(0))
 
 This example uses INDEXKEY() as part of a TOTAL ON key
 expression.  Notice that INDEXKEY() is specified using a macro
 expression to force evaluation of the expression:
 
 USE Sales INDEX Salesman NEW
 TOTAL ON &(INDEXKEY(0)) FIELDS SaleAmount TO ;
 SalesSummary
 
 This example uses INDEXKEY() to determine whether the DBEDIT()
 screen should be updated after the user has edited the current field
 value.  Generally, you must update the DBEDIT() screen if the user
 changes a field that is part of the controlling index key.
 FieldEdit() is a user-defined function called from a DBEDIT() user
 function to edit the current field if the user has pressed an edit
 key.
 
 #include "Dbedit.ch"
 #define ORD_NATURAL   0
 FUNCTION FieldEdit()
 LOCAL indexVal
 // Save current key expression and value
 indexVal = &(INDEXKEY(ORD_NATURAL))
 .
 . <code to GET current field value>
 .
 // Refresh screen if key value has changed
 IF indexVal != &amp;(INDEXKEY(ORD_NATURAL))
 nRequest = DE_REFRESH
 ELSE
 nRequest = DE_CONT
 ENDIF
 RETURN nRequest

Function INDEXORD()

 INDEXORD() --> nOrder

INDEXORD() is a database function that determines the position of the controlling index in the list of index files opened by the last USE...INDEX or SET INDEX TO in the current work area. It is often useful to save the last controlling index so it can be restored later.

By default, INDEXORD() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

 This example uses INDEXORD() to save the current order.  After
 changing to a new order, it uses the saved value to restore the
 original order:
 
 USE Customer INDEX Name, Serial NEW
 nOrder := INDEXORD()                  // Result: 1
 SET ORDER TO 2
 ? INDEXORD()                           // Result: 2
 SET ORDER TO nOrder
 ? INDEXORD()                           // Result: 1
 
 This example uses an aliased expression to determine the order
 number of the controlling index in an unselected work area:
 
 USE Sales INDEX Salesman, CustNum NEW
 USE Customer INDEX Name, Serial NEW
 ? Sales->(INDEXORD())               // Result: 1

Function LASTREC()

 LASTREC() | RECCOUNT()* --> nRecords

LASTREC() is a database function that determines the number of physical records in the current database file. LASTREC() is identical to RECCOUNT() which is supplied as a compatibility function.

By default, LASTREC() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

Note: Although the functionality of RECNO() has been expanded to encompass the concept of "identity," the LASTREC() function continues to return only record numbers--not identities. LASTREC() has no expanded functionality, so it is not "identity-aware."

 This example illustrates the relationship between LASTREC(),
 RECCOUNT(), and COUNT:
 
 USE Sales NEW
 ? LASTREC(), RECCOUNT()            // Result: 84 84
 //
 SET FILTER TO Salesman = "1001"
 COUNT TO nRecords
 ? nRecords, LASTREC()            // Result: 14 84
 
 This example uses an aliased expression to access the number
 of records in a open database file in an unselected work area:
 
 USE Sales NEW
 USE Customer NEW
 ? LASTREC(), Sales->(LASTREC())

Function LUPDATE()

 LUPDATE() --> dModification

LUPDATE() is a database function that determines the date the database file in the current work area was last modified and CLOSEd. By default, LUPDATE() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression, as shown in the example below.

 This example demonstrates that the modification date of the
 database file is not changed until the database file is closed:
 
 ? DATE()                  // Result: 09/01/90
 USE Sales NEW
 ? LUPDATE()               // Result: 08/31/90
 //
 APPEND BLANK
 ? LUPDATE()               // Result: 08/31/90
 CLOSE DATABASES
 //
 USE Sales NEW
 ? LUPDATE()               // Result: 09/01/90
 
 This example uses an aliased expression to access LUPDATE()
 for a database file opened in an unselected work area:
 
 USE Sales NEW
 USE Customer NEW
 ? LUPDATE(), Sales->(LUPDATE())

Function ORDBAGEXT()

 ORDBAGEXT() --> cBagExt

ORDBAGEXT() is an order management function that returns a character expression that is the default order bag extension of the current or aliased work area. cBagExt is determined by the RDD active in the current work area.

Function ORDBAGNAME()

 ORDBAGNAME(<nOrder> | <cOrderName>) --> cOrderBagName

ORDBAGNAME() is an order management function that lets you access the name of the order bag in which <cOrderName> resides. You may identify the order as a character string or with an integer that represents its position in the order list. In case of duplicate names, ORDBAGNAME() only recognizes the first matching name.

Note: ORDBAGNAME(0) works as ORDBAGNAME(INDEXORD())

Function ORDLISTADD()

 ORDLISTADD(<cOrderBagName> [, <cOrderName>]) --> NIL

ORDLISTADD() is an order management function that adds the contents of an order bag, or a single order in an order bag, to the order list. This function lets you extend the order list without issuing a SET INDEX command that, first, clears all the active orders from the order list.

Any orders already associated with the work area continue to be active. If the newly opened order bag contains the only order associated with the work area, it becomes the controlling order; otherwise, the controlling order remains unchanged.

After the new orders are opened, the work area is positioned to the first logical record in the controlling order.

ORDLISTADD() is similar to the SET INDEX command or the INDEX clause of the USE command, except that it does not clear the order list prior to adding the new order(s).

ORDLISTADD() supersedes the DBSETINDEX() function.

The active RDD determines the order capacity of an order bag. The default DBFNTX and the DBFNDX drivers only support single-order bags, while other RDDs may support multiple-order bags (e.g., the DBFCDX driver). 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 first order.

Function RECCOUNT()

 RECCOUNT() | LASTREC() --> nRecords

RECCOUNT() is a database function that is a synonym for LASTREC(). By default, RECCOUNT() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below). Note that RECCOUNT() is a compatibility function. LASTREC() should be used in its place.

 This example illustrates the relationship between COUNT and
 RECCOUNT():
 
 USE Sales NEW
 ? RECCOUNT()                      // Result: 84
 //
 SET FILTER TO Salesman = "1001"
 COUNT TO nRecords
 ? nRecords                        // Result: 14
 ? RECCOUNT()                      // Result: 84
 
 This example uses an aliased expression to access the number
 of records in an unselected work area:
 
 USE Sales NEW
 USE Customer NEW
 ? RECCOUNT(), Sales->(RECCOUNT())

Function RECNO()

 RECNO() --> Identity

RECNO() is a database function that returns the identity found at the current position of the record pointer. Identity is a unique value guaranteed by the structure of the data file to reference a specific record of a data file. The data file need not be a traditional Xbase file. Therefore, unlike earlier versions of xClipper, the value returned need not be a numeric data type.

Under all RDDs, RECNO() returns the value at the position of the record pointer; the data type and other characteristics of this value are determined by the content of the accessed data and the RDD active in the current work area. In an Xbase database this value is the record number.

Function RECSIZE()

 RECSIZE() --> nBytes

RECSIZE() is a database function that determines the length of a record by summing the lengths of each field then adding one for the DELETED() status flag. When this value is multiplied by LASTREC(), the product is the amount of space occupied by the file's records.

RECSIZE() is useful in programs that perform automatic file backup. When used in conjunction with DISKSPACE(), the RECSIZE() function can assist in ensuring that sufficient free space exists on the disk before a file is stored.

By default, RECSIZE() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below).

 The following user-defined function, DbfSize(), uses RECSIZE()
 to calculate the size of the current database file:
 
 FUNCTION DbfSize
 RETURN ((RECSIZE() * LASTREC()) + HEADER() + 1)
 
 This example illustrates the use of RECSIZE() to determine the
 record length of database files open in unselected work areas:
 
 USE Customer NEW
 USE Sales NEW
 //
 ? RECSIZE(), Customer->(RECSIZE())
 ? DbfSize(), Customer->(DbfSize())

Function RLOCK()

 RLOCK() --> lSuccess

RLOCK() is a network function that locks the current record, preventing other users from updating the record until the lock is released. RLOCK() provides a shared lock, allowing other users read-only access to the locked record while allowing only the current user to modify it. A record lock remains until another record is locked, an UNLOCK is executed, the current database file is closed, or an FLOCK() is obtained on the current database file.

For each invocation of RLOCK(), there is one attempt to lock the current record, and the result is returned as a logical value. An attempt to obtain a record lock fails if another user currently has a file or record lock on that particular record, or EXCLUSIVE USE of the database file. An attempt to RLOCK() in an empty database returns true (.T.).

By default, RLOCK() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression (see example below). This feature is useful since RLOCK() does not automatically attempt a record lock for related files.

As a general rule, RLOCK() operates solely on the current record. This includes the following commands:

@...GET

DELETE (single record)

RECALL (single record)

REPLACE (single record)

Refer to the "Network Programming" chapter in the Programming and Utilities Guide for more information.

 This example deletes a record in a network environment, using
 RLOCK():
 
 USE Customer INDEX CustName SHARED NEW
 SEEK "Smith"
 IF FOUND()
 IF RLOCK()
 DELETE
 ? "Smith deleted"
 ELSE
 ? "Record in use by another"
 ENDIF
 ELSE
 ? "Smith not in Customer file"
 ENDIF
 CLOSE
 
 This example specifies RLOCK() as an aliased expression to
 lock a record in an unselected work area:
 
 USE Sales SHARED NEW
 USE Customer SHARED NEW
 //
 IF !Sales->(RLOCK())
 ? "The current Sales record is in use by another"
 ENDIF

Function SELECT()

 SELECT([<cAlias>]) --> nWorkArea

SELECT() is a database function that determines the work area number of an alias. The number returned can range from 0 to 250. If <cAlias> is not specified, the current work area number is returned. If <cAlias> is specified and the alias does not exist, SELECT() returns zero.

Note: The SELECT() function and SELECT command specified with an extended expression argument look somewhat alike. This should not be a problem since the SELECT() function is not very useful on a line by itself.

  This example uses SELECT() to determine which work area
 USE...NEW selected:
 
 USE Sales NEW
 SELECT 1
 ? SELECT("Sales")            // Result: 4
 
 To reselect the value returned from the SELECT() function, use
 the SELECT command with the syntax, SELECT (<idMemvar>), like this:
 
 USE Sales NEW
 nWorkArea:= SELECT()
 USE Customer NEW
 SELECT (nWorkArea)
 
 

Function USED()

 USED() --> lDbfOpen

USED() is a database function that determines whether there is a database file in USE in a particular work area. By default, USED() operates on the currently selected work area. It will operate on an unselected work area if you specify it as part of an aliased expression.

 This example determines whether a database file is in USE in
 the current work area:
 
 USE Customer NEW
 ? USED()               // Result: .T.
 CLOSE
 ? USED()               // Result: .F.