USE

USE

Open an existing database (.dbf) and its associated files

Syntax

      USE [<xcDatabase>
            [INDEX <xcIndex list>]
            [ALIAS <xcAlias>] [EXCLUSIVE | SHARED]
            [NEW] [READONLY]
            [VIA <cDriver>]]

Arguments

<xcDatabase> is the name of the database file to be opened and may be specified either as a literal file name or as a character expression enclosed in parentheses.

INDEX <xcIndex list> specifies the names of 1 to 15 index files to be opened in the current work area. Specify each index as a literal file name or as a character expression enclosed in parentheses. The first index in the list becomes the controlling index. If you specify an <xcIndex> as an expression and the value returned is spaces or NIL, it is ignored.

ALIAS <xcAlias> specifies the name to associate with the work area when the database file is opened. You may specify the alias name as a literal name or as a character expression enclosed in parentheses. 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, Harbour will not accept duplicate aliases. If this clause is omitted, the alias defaults to the database file name.

EXCLUSIVE opens the database file for nonshared use in a network environment. All other users are denied access until the database file is CLOSEd.

SHARED opens the database file for shared use in a network environment. Specifying this clause overrides the current EXCLUSIVE setting.

NEW opens <xcDatabase> in the next available work area making it the current work area. If this clause is not specified, <xcDatabase> is opened in the current work area.

READONLY opens <xcDatabase> with a read-only attribute. This lets you open database files marked read-only. If you cannot open the <xcDatabase> this way, a runtime error is generated. If this clause is not specified, <xcDatabase> is opened as read-write.

VIA <cDriver> specifies the replaceable database driver (RDD) with which to process the current work area. <cDriver> is the name of the RDD specified as a character expression. If <cDriver> is specified as a literal value, it must be enclosed in quotes.

If the VIA clause is omitted, the DBFNTX driver is used by default. Note that if the specified driver is not linked, an unrecoverable error occurs.

In no arguments are specified, the database file open in the current work area is closed.

Description

USE opens an existing database (.dbf) file, its associated memo (.dbt) file, and optionally associated index (.ntx or .ndx) file(s) in the current or the next available work area. In Harbour, there are 250 work areas with a maximum of 255 total files open in DOS 3.3 and above. Before USE opens a database file and its associated files, it closes any active files already open in the work area. When a database file is first opened, the record pointer is positioned at the first logical record in the file (record one, if there is no index file specified).

In a network environment, you may open database files as EXCLUSIVE or SHARED. EXCLUSIVE precludes the USE of the database file by other users until the file is closed. SHARED allows other users to USE the database file for concurrent access. If the database file is SHARED, responsibility for data integrity falls upon the application program. In Harbour, FLOCK() and RLOCK() are the two basic means of denying other users access to a particular work area or record. If a USE is specified and neither EXCLUSIVE nor SHARED is specified, the database file is opened according to the current EXCLUSIVE setting. In Harbour, all USE commands should explicitly specify how the database file is to be opened, EXCLUSIVE or SHARED. The implicit open mode specified by SET EXCLUSIVE is supplied for compatibility purposes only and not recommended.

Opening a database file in a network environment requires some special handling to be successful. First, attempt to USE the database file without specifying the INDEX list. Then, test for the success of the operation using NETERR(). If NETERR() returns false (.F.), the open operation succeeded and you can SET INDEX TO the index list. A USE will fail in a network environment if another user has EXCLUSIVE USE of the database file. Refer to the “Network Programming” chapter in the Programming and Utilities Guide for more information on opening files in a network environment.

You can open index files with USE or SET INDEX. The first index in the list of indexes defines the current ordering of records when they are accessed. This index is referred to as the controlling index. You can change the current controlling index without closing any files by using the SET ORDER command.

To close a database and its associated files in the current work area, specify USE or CLOSE with no arguments. To close database files in all work areas, use CLOSE DATABASEs. To close index files in the current work area without closing the database file, use CLOSE INDEX or SET INDEX TO with no arguments.

Refer to the “Basic Concepts” chapterfor more information about the Harbour database paradigm.

Notes

. Setting the maximum open files: Control of the number of file handles available to a Harbour application is controlled by a combination of the CONFIG.SYS FILES command, and the F parameter of the CLIPPER environment variable. The F parameter specifies the maximum number of files that can be opened at any one time within the current Harbour program. Harbour determines the number of files that can be opened using the smaller of the two parameters. For example, if the FILES command is set to 120 and the F parameter is set to 50, the maximum number of files that can be opened is 50. In a network environment, file handles also need to be set in the network configuration file.

The file limit is controlled by the operating system. Under DOS versions less than 3.3, the maximum number of files that can be opened at one time is 20 files. In DOS versions 3.3 and greater, the maximum limit is 255 files.

. Opening the same database file in more than one work area: Although opening a database file in more than one work area is possible in a network environment, this practice is strongly discouraged. If done, each file must be opened with a different alias, otherwise a runtime error will occur.

. Opening two database files with the same names, in different directories: Although opening two database files with the same names in different directories is possible, the database files MUST have unique alias names; otherwise, a runtime error will occur.

Examples

      .  This example opens a shared database file with associated
         index files in a network environment.  If NETERR() returns
         false (.F.), indicating the USE was successful, the indexes
         are opened:

      USE Accounts SHARED NEW
      IF !NETERR()
         SET INDEX TO AcctNames, AcctZip
      ELSE
         ? "File open failed"
         BREAK
      ENDIF

      .  This example opens a database file with several indexes
         specified as extended expressions.  Note how the array
         of index names is created as a constant array:

      xcDatabase = "MyDbf"
      xcIndex = {"MyIndex1", "MyIndex2", "MyIndex3"}
      USE (xcDatabase) INDEX (xcIndex[1]), ;
            (xcIndex[2]), (xcIndex[3])

Seealso

CLOSE, DBRSELECT(), DBSETINDEX(), DBUSEAREA(), NETERR()

SET RELATION

SET RELATION

Relate two work areas by a key value or record number

Syntax

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

Arguments

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

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

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

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

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

Description

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

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

Notes

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

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

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

Examples

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

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

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

         USE BackOrder INDEX BackOrder NEW
         SELECT Customer

         SET RELATION TO CustNum INTO BackOrder ADDITIVE

Seealso

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

dbFilter()

DBFILTER()

Return the filter expression in a work area

Syntax

      DBFILTER() --> cFilter

Returns

DBFILTER() returns the filter expression.

Description

This function return the expression of the SET FILTER TO command for the current or designated work area. If no filter condition is present, a NULL string will be returned.

Examples

      USE test INDEX test NEW
      SET FILTER TO Name == "Harbour"
      USE testid INDEX testid NEW
      SET FILTER TO Id == 1
      SELECT Test
      //
      ? DBFILTER()
      ? testid->( DBFILTER() )

Compliance

Clipper

Files

Library is rdd

Seealso

DBRELATION(), DBRSELECT()

C5_DBRSELECT

 DBRSELECT()
 Return the target work area number of a relation
------------------------------------------------------------------------------
 Syntax

     DBRSELECT(<nRelation>) --> nWorkArea

 Arguments

     <nRelation> is the position of the desired relation in the list of
     current work area relations.  The relations are numbered according to
     the order in which they were defined with SET RELATION.

 Returns

     DBRSELECT() returns the work area number of the relation specified by
     <nRelation> as an integer numeric value.  If there is no RELATION SET
     for <nRelation>, DBRSELECT() returns zero.

 Description

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

 Examples

     .  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

 Files   Library is CLIPPER.LIB.

See Also: DBFILTER() DBRELATION() RLOCK() UNLOCK SET RELATION



C5 Database Functions

AFIELDS() :

Fill arrays with the structure of the current database file

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

ALIAS() : 

Return a specified work area alias

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

BOF() : 

Determine when beginning of file is encountered

BOF() --> lBoundary

DBAPPEND() : 

Add a new record

DBAPPEND() --> NIL

DBCLEARFILTER() : 

Clear a filter condition

    DBCLEARFILTER() –> NIL

DBCLEARIND() :  Close all indexes for the current work area

    DBCLEARINDEX() --> NIL

DBCLEARREL() : 

Clear active relations

DBCLEARRELATION() --> NIL

DBCLOSEALL() : 

Close all occupied workareas

DBCLOSEALL() --> NIL

DBCLOSEAREA() : 

Close a workarea

DBCLOSEAREA() --> NIL

DBCOMMIT() : 

Flush pending updates

DBCOMMIT() --> NIL

DBCOMMITALL() : 

Flush pending updates in all workareas

DBCOMMITALL() --> NIL

DBCREATE() : 

Create a database file from a database structure array

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

DBCREATEIND() : 

Create an Index

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

DBDELETE() : 

Mark a record for deletion

DBDELETE() --> NIL

DBEDIT() : 

        Browse records in a table layout

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

DBEVAL() : 

Evaluate a code block for each record

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

DBF()* : 

Return current alias name

DBF() --> cAlias

DBFIELDINFO() : 

Return and optionally change information about a field

   DBFIELDINFO(<nInfoType>,

        <nFieldPos>,
        [<expNewSetting>]) --> uCurrentSetting

DBFILEGET() : 

Insert the contents of a field into a file

     DBFILEGET(<nFieldPos>, <cTargetFile>, <nMode>)
        --> lSuccess

DBFILEPUT() : 

Insert the contents of a file into a field

     DBFILEPUT(<nFieldPos>, <cSourceFile>)
        --> lSuccess

 DBFILTER() : 

Return the current filter expression as a character string

DBFILTER() --> cFilter

DBGOBOTTOM() : 

Move to the last logical record

DBGOBOTTOM() --> NIL

DBGOTO() : 

Move to a particular record

DBGOTO(<nRecordNumber>) --> NIL

DBGOTOP() : 

Move to the first logical record

DBGOTOP() --> NIL

DBINFO() : 

         Return and optionally change database file information

    DBINFO(<nInfoType>, [<expNewSetting>])
        --> uCurrentSetting

DBORDERINFO() :          Return and optionally change information about orders and index files

    DBORDERINFO(<nInfoType>,   [<cIndexFile>],
        [<cOrder> | <nPosition>],
        [<expNewSetting>]) --> uCurrentSetting

DBRECALL() : 

Reinstate a record marked for deletion

DBRECALL() --> NIL

DBRECORDINFO() :

         Return and optionally change information about a record

    DBRECORDINFO(<nInfoType>,
        [<nRecord>],
        [<expNewSetting>]) --> uCurrentSetting

DBREINDEX() :

         Recreate all active indexes for the current work area

    DBREINDEX() --> NIL

DBRELATION() :

Return the linking expression of a specified relation

DBRELATION( <nRelation> ) --> cLinkExp

DBRLOCK() :      

         Lock the record at the current or specified identity

    DBRLOCK([<xIdentity>]) --> lSuccess

DBRLOCKLIST()          Return an array of the current lock list

   DBRLOCKLIST() --> aRecordLocks

DBRSELECT() :

Return the target workarea number of a relation

DBRSELECT( <nRelation> ) --> nWorkArea

DBRUNLOCK()    

          Release all or specified record locks

    DBRUNLOCK([<xIdentity>]) --> NIL

DBSEEK() : 

Search for a key value

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

DBSELECTAR() : 

Change the current workarea

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

DBSETDRIVER() : 

Set the default database driver

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

DBSETFILTER() : 

Set a filter condition

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

DBSETINDEX()    

         Empty orders from an order bag into the order list

    DBSETINDEX(<cOrderBagName>) --> NIL

DBSETORDER()

Set the controlling order

    DBSETORDER(<nOrderNum>) --> NIL

DBSETRELAT() :

Relate two workareas

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

DBSKIP() : 

Move relative to the current record

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

DBSTRUCT() : 

Create an array containing the structure of a database file

DBSTRUCT() --> aStruct

DBUNLOCK()

Release all locks for the current work area

    DBUNLOCK() --> NIL

DBUNLOCKALL()

Release all locks for all work areas

    DBUNLOCKALL() --> NIL

DBUSEAREA() : 

Use a database file in a workarea

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

DELETED() : 

Return the deleted status of the current record

DELETED() --> lDeleted

EOF() : 

Determine when end of file is encountered

EOF() --> lBoundary

FCOUNT() : 

Return the number of fields in the current (.dbf) file

FCOUNT() --> nFields

FIELD() : 

Return a field name from the current (.dbf) file

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

FIELDBLOCK() :

Return a set-get code block for a field variable

FIELDBLOCK(<cFieldName>) --> bFieldBlock

FIELDGET() : 

Retrieve the value of a field using the ordinal position of the field in the database structure

FIELDGET(<nField>) --> ValueField

FIELDNAME() : 

Return a field name from the current (.dbf) file

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

FIELDPOS() : 

Return the position of a field in a workarea

FIELDPOS(<cFieldName>) --> nFieldPos

FIELDPUT() : 

Set the value of a field variable using the ordinal position of the field in the database structure

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

FIELDWBLOCK() : 

Return a set-get block for a field in a given workarea

FIELDWBLOCK(<cFieldName>, <nWorkArea>) --> bFieldWBlock

FLOCK() : 

Lock an open and shared database file

    FLOCK() –> lSuccess

FOUND() : 

Determine if the previous search operation succeeded

FOUND() --> lSuccess

HEADER() : 

Return the current database file header length

HEADER() --> nBytes

LASTREC() : 

Determine the number of records in the current (.dbf) file

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

LUPDATE() : 

Return the last modification date of a (.dbf) file

LUPDATE() --> dModification

RECCOUNT()* : 

Determine the number of records in the current (.dbf) file

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

RECNO() : 

Return the current record number of a work area

RECNO() --> nRecord

RECSIZE() : 

Determine the record length of a database (.dbf) file

RECSIZE() --> nBytes

RLOCK()

Lock the current record in the active work area

    RLOCK() --> lSuccess

SELECT() : 

Determine the work area number of a specified alias

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

USED() : 

Determine if a database file is in USE

USED() --> lDbfOpen