13.29. DATABASE

About
Functions order

13.29.1. About DATABASE

13.29.2. DATABASE functions order

AFIELDS() Fill arrays with the structure of the current database file
ALIAS() Return a specified work area alias
BOF() Determine when beginning of file is encountered
BROWSE() Browse records within a window
DBAPPEND() Append a new record to the database open in the current work area
DBCLEARFILTER() Clear a filter condition
DBCLEARINDEX() Close all indexes for the current work area
DBCLEARRELATION() Clear active relations
DBCLOSEALL() Close all occupied work areas
DBCLOSEAREA() Close a work area
DBCOMMIT() Flush pending updates
DBCOMMITALL() Flush pending updates in all work areas
DBCREATE() Create a database file from a database structure array
DBCREATEINDEX() Create an index file
DBDELETE() Mark a record for deletion
DBEDIT() Browse records in a table layout
DBEVAL() Evaluate a code block for each record matching a scope and condition
DBF() Return current alias name
DBFILTER() Return the current filter expression as a character string
DBGOBOTTOM() Move to the last logical record
DBGOTO() Position record pointer to a specific identity
DBGOTOP() Move to the first logical record
DBRECALL() Reinstate a record marked for deletion
DBREINDEX() Recreate all active indexes for the current work area
DBRELATION() Return the linking expression of a specified relation
DBRSELECT() Return the target work area number of a relation
DBSEEK() Move to the record having the specified key value
DBSELECTAREA() Change the current work area
DBSETDRIVER() Return the default database driver and optionally set a new driver
DBSETFILTER() Set a filter condition
DBSETINDEX() Empty orders from an order bag into the order list
DBSETORDER() Set the controlling order
DBSETRELATION() Relate two work areas
DBSKIP() Move relative to the current record
DBSTRUCT() Create an array containing the structure of a database file
DBUNLOCK() Release all locks for the current work area
DBUNLOCKALL() Release all locks for all work areas
DBUSEAREA() Use a database file in a work area
DEFPATH() Returns the true path defined in SET DEFAULT command.
DELETED() Return the deleted status of the current record
DESCEND() Create a descending index key value
DOSERROR() Return the last DOS error number
EOF() Determine when end of file is encountered
FCOUNT() Return the number of fields in the current .dbf file
FERROR() Test for errors after a binary file operation
FIELDBLOCK() Return a set-get code block for a given field
FIELDGET() Retrieve the value of a field using the ordinal position of the field in the
FIELDNAME() Return a field name from the current database (.dbf) file
FIELDPOS() Return the position of a field in a work area
FIELDPUT() Set the value of a field variable using the ordinal position of the field in
FIELDWBLOCK() Return a set-get code block for a field in a given work area
FILE() Determine if files exist in the xClipper default directory or path
__FLEDIT() Select the appointed elements from the array of a DbStruct() type.
FLOCK() Lock an open and shared database file
FOUND() Determine if the previous search operation succeeded
HEADER() Return the current database file header length
INDEXEXT() Return the default index extension based on the database driver currently
INDEXKEY() Return the key expression of a specified index
INDEXORD() Return the order position of the controlling index
LASTREC() Determine the number of records in the current .dbf file
LUPDATE() Return the last modification date of a database (.dbf) file
MEMOEDIT() Display or edit character strings and memo fields
MEMOREAD() Return the contents of a disk file as a character string
MEMOWRIT() Write a character string or memo field to a disk file
NETERR() Determine if a network command has failed
ORDBAGEXT() Return the default order bag RDD extension
ORDBAGNAME() Return the order bag name of a specific order
ORDLISTADD() Add orders to the order list
RECCOUNT() Determine the number of records in the current database (.dbf) file
RECNO() Return the identity at the position of the record pointer
RECSIZE() Determine the record length of a database (.dbf) file
RLOCK() Lock the current record in the active work area
SELECT() Determine the work area number of a specified alias
USED() Determine whether a database file is in USE
WEIGHTASC() Returns the weight characterics of character.
WEIGHTTABLE() Returns the weight table of characters.

13.29.2.1. Function AFIELDS()

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

13.29.2.3. Function BOF()

 BOF() --> lBoundary

13.29.2.13. Function DBCREATE()

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

13.29.2.13.3. Description

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.

13.29.2.16. Function DBEDIT()

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

13.29.2.16.1. Arguments

<nTop>, <nLeft>, <nBottom>, and <nRight> define the
  upper-left and lower-right coordinates of the DBEDIT() window. Row
  values can range from zero to MAXROW() and column positions can range
  from zero to MAXCOL(). If not specified, the default coordinates are 0,
  0, MAXROW(), and MAXCOL().
  
<acColumns> is an array of character expressions containing database
  field names or expressions to use as column values for each row
  displayed. If this argument is not specified, DBEDIT() displays all
  fields in the current work area as columns.
  
<cUserFunction> is the name of a user-defined function that executes
  when an unrecognizable key is pressed or there are no keys pending in
  the keyboard buffer. Specify the function name as a character
  expression without parentheses or arguments. Note that the behavior of
  DBEDIT() is affected by the presence of this argument. Refer to the
  discussion below for more information.
  
<acColumnSayPictures> is a parallel array of picture clauses to
  format each column. Specifying <cColumnSayPicture> instead of an array
  displays all columns with the same format. Refer to TRANSFORM() or
  @...SAY for more information on pictures.
  
<acColumnHeaders> is a parallel array of character expressions that
  define the headings for each column. Specifying <cColumnHeader> gives
  the same heading for all columns. To display a multi-line heading,
  embed a semicolon in the heading expression where you want the string to
  break. If not specified, column headings are taken from the <acColumns>
  array or the field names in the current work area, if the <acColumns>
  argument is not specified.
  
<acHeadingSeparators> is a parallel array of character expressions
  that define the characters used to draw horizontal lines separating
  column headings from the field display area. Specifying
<cHeadingSeparator> instead of an array uses the same heading separator
  for all columns. If this argument is not specified, the default
  separator is a double graphics line.
  
<acColumnSeparators> is a parallel array of character expressions
  that define the characters used to draw vertical lines separating the
  columns. Specifying <cColumnSeparator> instead of an array uses the
  same separator for all columns. If this argument is not specified, the
  default separator is a single graphics line.
  
<acFootingSeparators> is a parallel array of character expressions
  that define the characters used to draw horizontal lines separating
  column footings from the field display area. Specifying
<cFootingSeparator> instead of an array uses the same footing separator
  for all columns. If this argument is not specified, there is no footing
  separator.
  
<acColumnFootings> is a parallel array of character expressions that
  define footings for each column. Specifying <cColumnFooting> instead of
  an array gives the same footing for all columns. To display a multi-
  line footing, embed a semicolon in the footing expression where you want
  the string to break. If this argument is not specified, there are no
  column footings.

13.29.2.16.3. Description

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.

13.29.2.16.4. Example

 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 := &amp;( 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 != &amp;( 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

13.29.2.17. Function DBEVAL()

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

13.29.2.19. Function DBFILTER()

 DBFILTER() --> cFilter

13.29.2.25. Function DBRELATION()

 DBRELATION(<nRelation>) --> cLinkExp

13.29.2.26. Function DBRSELECT()

 DBRSELECT(<nRelation>) --> nWorkArea

13.29.2.27. Function DBSEEK()

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

13.29.2.30. Function DBSETFILTER()

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

13.29.2.33. Function DBSETRELATION()

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

13.29.2.35. Function DBSTRUCT()

 DBSTRUCT() --> aStruct

13.29.2.38. Function DBUSEAREA()

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

13.29.2.38.1. Arguments

<lNewArea> is an optional logical value. A value of true (.T.)
  selects the lowest numbered unoccupied work area as the current work
  area before the use operation. If <lNewArea> is false (.F.) or omitted,
  the current work area is used; if the work area is occupied, it is
  closed first.
  
<cDriver> is an optional character value. If present, it specifies
  the name of the database driver which will service the work area. If
<cDriver> is omitted, the current default driver is used (see note
  below).
  
<cName> specifies the name of the database (.dbf) file to be opened.
  
<xcAlias> is an optional character value. If present, it specifies
  the alias to be associated with the work area. The alias must
  constitute a valid xClipper identifier. A valid <xcAlias> may be any
  legal identifier (i.e., it must begin with an alphabetic character and
  may contain numeric or alphabetic characters and the underscore).
  Within a single application, xClipper will not accept duplicate
  aliases. If <xcAlias> is omitted, a default alias is constructed from
<cName>.
  
<lShared> is an optional logical value. If present, it specifies
  whether the database (.dbf) file should be accessible to other processes
  on a network. A value of true (.T.) specifies that other processes
  should be allowed access; a value of false (.F.) specifies that the
  current process is to have exclusive access. If <lShared> is omitted,
  the current global _SET_EXCLUSIVE setting determines whether shared
  access is allowed.
  
<lReadonly> is an optional logical value that specifies whether
  updates to the work area are prohibited. A value of true (.T.)
  prohibits updates; a value of false (.F.) permits updates. A value of
  true (.T.) also permits read-only access to the specified database
  (.dbf) file. If <lReadonly> is omitted, the default value is false
  (.F.).

13.29.2.40. Function DESCEND()

 DESCEND(<exp>) --> ValueInverted

13.29.2.41. Function EOF()

 EOF() --> lBoundary

13.29.2.44. Function FIELDNAME()

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

13.29.2.47. Function FLOCK()

 FLOCK() --> lSuccess

13.29.2.47.3. Description

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.

13.29.2.48. Function FOUND()

 FOUND() --> lSuccess

13.29.2.51. Function INDEXKEY()

 INDEXKEY(<nOrder>) --> cKeyExp

13.29.2.51.4. Example

 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 &amp;(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 = &amp;(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

13.29.2.52. Function INDEXORD()

 INDEXORD() --> nOrder

13.29.2.53. Function LASTREC()

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

13.29.2.57. Function ORDLISTADD()

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

13.29.2.60. Function RECSIZE()

 RECSIZE() --> nBytes

13.29.2.61. Function RLOCK()

 RLOCK() --> lSuccess