SET UNIQUE

Posted on February 17, 2014 by vivaclipper

SET UNIQUE*

Toggle inclusion of non-unique keys into an index

Syntax

      SET UNIQUE on | OFF | <xlToggle>

Arguments

ON causes index files to be created with a uniqueness attribute.

OFF causes index files to be created without a uniqueness attribute.

<xlToggle> is a logical expression that must be enclosed in parentheses. A value of true (.T.) is the same as ON, and a value of false (.F.) is the same as OFF.

Description

SET UNIQUE is a database command that controls whether indexes are created with uniqueness as an attribute. With UNIQUE ON, new indexes are created including only unique keys. This is the same as creating an index with the INDEX…UNIQUE command.

If, during the creation or update of an unique index, two or more records are encountered with the same key value, only the first record is included in the index. When the unique index is updated, REINDEXed, or PACKed, only unique records are maintained, without regard to the current SET UNIQUE value.

Changing key values in a unique index has important implications. First, if a unique key is changed to the value of a key already in the index, the changed record is lost from the index. Second, if there is more than one instance of a key value in a database file, changing the visible key value does not bring forward another record with the same key until the index is rebuilt with REINDEX, PACK, or INDEX…UNIQUE.

With UNIQUE OFF, indexes are created with all records in the index. Subsequent updates to the database files add all key values to the index independent of the current UNIQUE SETting.

SET UNIQUE is a compatibility command not recommended. It is superseded by the UNIQUE clause of the INDEX command.

Seealso

DBCREATEIND(), INDEX, PACK, REINDEX, SEEK

REINDEX

Posted on February 16, 2014 by vivaclipper

Rebuild open indexes in the current work area

Syntax

      REINDEX
            [EVAL <lCondition>]
            [EVERY <nRecords>]

Arguments

EVAL <lCondition> specifies a condition that is evaluated either for each record processed or at the interval specified by the EVERY clause. This clause is identical to the EVAL clause of the INDEX command, but must be respecified in order for the reindexing operation to be monitored since the value of <lCondition> is transient.

EVERY <nRecords> specifies a numeric expression that modifies how often EVAL is evaluated. When using EVAL, the EVERY option offers a performance enhancement by evaluating the condition for every nth record instead of evaluating each record reindexed. The EVERY keyword is ignored if no EVAL condition is specified.

Description

REINDEX is a database command that rebuilds all open indexes in the current work area. When the reindexing operation finishes, all rebuilt indexes remain open, order is reset to one, and the record pointer is positioned to the first record in the controlling index. If any of the indexes were created with SET UNIQUE ON, REINDEX adds only unique keys to the index. If any of the indexes were created using a FOR condition, only those key values from records matching the condition are added to the index.

In a network environment, REINDEX requires EXCLUSIVE USE of the current database file. Refer to the “Network Programming” chapter for more information.

Caution! REINDEX does not recreate the header of the index file when it recreates the index. Because of this, REINDEX does not help if there is corruption of the file header. To guarantee a valid index, always use INDEX ON in place of REINDEX to rebuild damaged indexes

Notes

Index key order, UNIQUE status, and the FOR condition are known to the index (.ntx) file and are, therefore, respected and maintained by REINDEX.

Examples

      .  This example REINDEXes the index open in the current work
         area:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX

      .  This example REINDEXes using a progress indicator:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX EVAL NtxProgress() EVERY 10
         FUNCTION NtxProgress
         LOCAL cComplete := LTRIM(STR((RECNO()/LASTREC()) * 100))
         @ 23, 00 SAY "Indexing..." + cComplete + "%"
         RETURN .T.

Seealso

INDEX, PACK, SET INDEX, USE

RECALL

Posted on February 16, 2014 by vivaclipper

Restore records marked for deletion

Syntax

      RECALL [<scope>] [WHILE <lCondition>]
             [FOR <lCondition>]

Arguments

<scope> is the portion of the current database file to RECALL. The default scope is the current record, or NEXT 1. If a condition is specified, the default scope becomes ALL.

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

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

Description

RECALL is a database command that restores records marked for deletion in the current work area. This is the inverse of the DELETE command. If DELETED is ON, RECALL can restore the current record or a specific record, if you specify a RECORD scope. Note that once you PACK a database file, all marked records have been physically removed from the file and cannot be recovered.

In a network environment, RECALLing the current record requires an RLOCK(). RECALLing several records requires an FLOCK() or EXCLUSIVE USE of the current database file. Refer to the “Network Programming” chapter for more information.

Examples

      .  This examples show the results of RECALL:

      USE Sales NEW
      //
      DELETE RECORD 4
      ? DELETED()               // Result: .T.
      //
      RECALL
      ? DELETED()               // Result: .F.

Seealso

DELETE, DELETED, FLOCK(), PACK, RLOCK(), SET DELETED

DELETE

Posted on February 15, 2014 by vivaclipper

DELETE

Mark records for deletion

Syntax

      DELETE [<scope>] [WHILE <lCondition>]
             [FOR <lCondition>]

Arguments

<scope> is the portion of the current database file to DELETE. If a scope is not specified, DELETE acts only on the current record. If a conditional clause is specified, the default becomes ALL records.

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

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

Description

DELETE is a database command that tags records so they can be filtered with SET DELETED ON, queried with DELETED(), or physically removed from the database file with PACK. In addition, display commands such as LIST and DISPLAY identify deleted records with an asterisk (*) character. Once records are deleted, you can reinstate them by using RECALL. If you want to remove all records from a database file, use ZAP instead of DELETE ALL and PACK.

Any deleted record can be recalled as long as the PACK or ZAP command has not been issued. Once the PACK or ZAP command has been issued, the deleted data cannot be retrieved.

In a network environment, DELETE requires the current record be locked with RLOCK() if you are deleting a single record. If you are deleting several records, the current database file must be locked with FLOCK() or USEed EXCLUSIVEly. Refer to the “Network Programming” chapter in the Programming and Utilities Guide for more information.

Notes

. DELETE with SET DELETED ON: If the current record is deleted with SET DELETED ON, it will be visible until the record pointer is moved.

Examples

      .  This example demonstrates use of the FOR clause to mark a set
         of records for deletion:
      USE Sales INDEX Salesman NEW
      DELETE ALL FOR Inactive

Seealso

DBEVAL(), DELETED(), FLOCK(), PACK, RECALL, RLOCK()

ZAP

Posted on January 18, 2014 by vivaclipper

ZAP

Remove all records from the current database file

Syntax

ZAP

Arguments

(This command has no arguments)

Description

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

Examples

      USE tests NEW INDEX tests
      ZAP
      USE

Compliance

Clipper

Seealso

DELETE, PACK, USE

SP_BLANKREC

Posted on October 30, 2013 by vivaclipper

BLANKREC()

  Short:
  ------
  BLANKREC() Blanks out a record

  Returns:
  --------
  <lSuccess> => success, or not

  Syntax:
  -------
  BLANKREC([nTries,lInteractive,cMessage])

  Description:
  ------------
  Attempts to lock the record <nTries> (default 5), and
  if no luck and <lInteractive> (default False), prompts
  the user with <cMessage> (default "Unable to lock record. Keep
  trying?")

  If a lock is gotten, the record is replaced with
  blank values.

  See BLANKFIELD()

  Examples:
  ---------
   DELETE
   BLANKREC()

  Notes:
  -------
  Use ISBLANKREC() to locate blank records. This is a
  good way to re-use records, rather than using PACK.

  Source:
  -------
  S_BLANK.PRG

C5DG-4 DBFCDX Driver

Posted on July 26, 2013 by vivaclipper

Clipper 5.x – Drivers Guide

Chapter 4 DBFCDX Driver Installation and Usage

DBFCDX is the FoxPro 2 compatible RDD for Clipper. As such, it connects to the low-level database management subsystem in the Clipper architecture. When you use the DBFCDX RDD, you add a number of new features including:

. FoxPro 2 file format compatibility

. Compact indexes

. Compound indexes

. Conditional indexes

. Memo files smaller than DBFNTX format

In This Chapter

This chapter explains how to install DBFCDX and how to use it in your applications. The following major topics are discussed:

. Overview of the DBFCDX RDD

. Installing DBFCDX Driver Files

. Linking the DBFCDX Driver

. Using the DBFCDX Driver

Overview of the DBFCDX RDD

The DBFCDX driver lets you create and maintain (.cdx) and (.idx) files with features different from those supplied with the original DBFNTX driver and is compatible with files created under FoxPro 2. The new features are supplied in the form of several syntactical additions to the INDEX and REINDEX commands. Specifically, you can:

. Create indexes smaller than those created with the DBFNTX
driver. The key data is stored in a compressed format that
substantially reduces the size of the index file.

. Create a compound index file that contains multiple indexes
(TAGs), making it possible to open several indexes under one file
handle. A single (.cdx) file may contain up to 99 index keys.

. Create conditional indexes (FOR / WHILE / REST / NEXT).

. Create files with FoxPro 2 file format compatibility.

Compact Indexes

Like FoxPro 2, The DBFCDX driver creates compact indexes. This means that the key data is stored in a compressed format, resulting in a substantial size reduction in the index file. Compact indexes store only the actual data for the index keys. Trailing blanks and duplicate bytes between keys are stored in one or two bytes. This allows considerable space savings in indexes with much empty space and similar keys. Since the amount of compression is dependent on many variables, including the number of unique keys in an index, the exact amount of compression is impossible to predetermine.

Compound Indexes

A compound index is an index file that contains multiple indexes (called tags). Compound indexes (.cdx)’s make several indexes available to your application while only using one file handle. Therefore, you can overcome the Clipper index file limit of 15. A compound index can have as many as 99 tags, but the practical limit is around 50. Once you open a compound index, all the tags in the file are automatically updated as the records are changed.

Once you open a compound index, all the tags contained in the file are automatically updated as the records are changed. A tag in a compound index is essentially identical to an individual index (.idx) and supports all the same features. The first tag (in order of creation) in the compound index is, by default, the controlling index.

Conditional Indexes

The DBFCDX driver can create indexes with a built-in FOR clause. These are conditional indexes in which the condition can be any expression, including a user-defined function. As the database is updated, only records that match the index condition are added to the index, and records that satisfied the condition before, but don’t any longer, are automatically removed.

Expanded control over conditional indexing is supported with the revised INDEX and REINDEX command options as in the new DBFNTX driver.

Installing DBFCDX Driver Files

The DBFCDX driver is supplied as the file, DBFCDX.LIB.

The Clipper installation program installs this driver in the \CLIPPER5\LIB subdirectory on the drive that you specify, so you need not install the driver manually.

Linking the DBFCDX Database Driver

To link the DBFCDX database driver into an application program, you must specify DBFCDX.LIB to the linker in addition to your application object files (.OBJ).

1. To link with .RTLink using positional syntax:

C>RTLINK <appObjectList> ,,,DBFCDX

2. To link with .RTLink using freeformat syntax:

C>RTLINK FI <appObjectList> LIB DBFCDX

Note: These link commands all assume the LIB, OBJ, and PLL environment variables are set to the standard locations. They also assume that the Clipper programs were compiled without the /R option.

Using the DBFCDX Database Driver

To use FoxPro 2 files in a Clipper program:

1. Place REQUEST DBFCDX at the beginning of your application or at the top of the first program file (.prg) that opens a database file using the DBFCDX driver.

2. Specify the VIA “DBFCDX” clause if you open the database file with the USE command.

-OR-

3. Specify “DBFCDX” for the <cDriver> argument if you open the database file with the DBUSEAREA() function.

-OR-

4. Use ( “DBFCDX” ) to set the default driver to DBFCDX.

Except in the case of REQUEST, the RDD name must be a literal character string or a variable. In all cases it is important that the driver name be spelled correctly.

The following program fragments illustrate:

  REQUEST DBFCDX
  .
  .
  .
  USE Customers INDEX Name, Address NEW VIA "DBFCDX"

-OR-

  REQUEST DBFCDX

  RDDSETDEFAULT( "DBFCDX" ) .
  . 
  .
  USE Customers INDEX Name, Address NEW

Using (.idx) and (.ntx) Files Concurrently

You can use both (.idx) and (.ntx) files concurrently in a Clipper program like this:

// (.ntx) file using default DBFNTX driver
 USE File1 INDEX File1 NEW

// (.idx) files using DBFCDX driver
 USE File2 VIA "DBFCDX" INDEX File2 NEW

Note, however, that you cannot use (.idx) and (.ntx) files in the same work area. For example, the following does not work:

USE File1 VIA "DBFNTX" INDEX File1.ntx, File2.idx

Using (.cdx) and (.idx) Files Concurrently

You may use (.cdx) with (.idx) files concurrently (even in the same work area); however, in most cases it is easier to use a single (.cdx) index for each database file or separate (.idx) files. When using both types of index at the same time, attempting to select an Order based on its Order Number can be confusing and will become difficult to maintain.

File Maintenance under DBFCDX

When an existing tag in a compound index (.cdx) is rebuilt using INDEX ON…TAG… the space used by the original tag is not automatically reclaimed. Instead, the new tag is added to the end of the file, increasing file size.

You can use the REINDEX command to “pack” the index file. REINDEX rebuilds each tag, eliminating any unused space in the file.

If you rebuild your indexes on a regular basis, you should either delete your (.cdx) files before rebuilding the tags or use the REINDEX command to rebuild them instead.

DBFCDX and Memo Files

The DBFCDX driver uses FoxPro compatible memo (.fpt) files to store data for memo fields. These memo files have a default block size of 64 bytes rather than the 512 byte default for (.dbt) files.

DBFCDX memo files can store any type of data. While (.dbt) files use an end of file marker (ASCII 26) at the end of a memo entry, (.fpt) files store the length of the entry. This not only eliminates the problems normally encountered with storing binary data in a memo field but also speeds up memo field access since the data need not be scanned to determine the length.

Tips For Using DBFCDX

1. Make sure index extensions aren’t hard-coded in your application. The default extension for DBFCDX indexes is (.idx), not (.ntx). You can still use (.ntx) as the extension as long as you specify the extension when you create your indexes. The best way to determine index extensions in an application is to call ORDBAGEXT().

For example, if you currently use the following code to determine the existence of an index file:

IF .NOT. FILE("index.ntx")
    INDEX ON field TO index
ENDIF

Change the code to include the INDEXEXT() function, as follows:

IF .NOT. FILE("index"+ORDBAGEXT())
   INDEX ON field TO index
ENDIF

2. If your application uses memo fields, you should convert your (.dbt) files to (.fpt) files.

There are some good reasons for using (.fpt) files. Most important is the smaller block size (64 bytes). Clipper’s (.dbt) files use a fixed block size of 512 bytes which means that every time you store even 1 byte in a memo field Clipper uses 512 bytes to store it. If the data in a memo field grows to 513 bytes, then two blocks are required.

When creating (.fpt) files, the block size is set at 64 bytes to optimize it for your needs. A simple conversion from (.dbt) files to (.fpt) files will generally shrink your memo files by approximately 30%.

3. Add DBFCDX.LIB as a library to your link command or link script.

Summary

In this chapter, you were given an overview of the features and benefits of the DBFCDX RDD. You also learned how to link this driver and how to use it in your applications.

C5_PACK

Posted on July 19, 2013 by vivaclipper

 PACK
 Remove deleted records from a database file
------------------------------------------------------------------------------
 Syntax

     PACK

 Description

     PACK is a database command that removes all records marked for deletion
     from the current database file, REINDEXes all active indexes in the
     current work area, and recovers all the physical space occupied by the
     deleted records.  During its operation, PACK does not create any backup
     files, although the associated REINDEX operation may.  After the PACK
     command terminates, the record pointer is reset to the first logical
     record in the current work area.

     In a network environment, PACK requires that the current database be
     USEd EXCLUSIVEly.  If this condition is not met when PACK is invoked,
     Clipper generates a runtime error.

     Note that PACKing large database files can be a time-consuming process
     and may not be feasible in a high-volume transaction system on a
     network.  By modifying the system design, you can remove the necessity
     of physically removing records from the database file altogether.  See
     the "Network Programming" chapter in the Programming and Utilities Guide
     for more information.

 Examples

     .  The following example shows the result of a simple PACK:

        USE Sales NEW
        ? LASTREC()                  // Result: 84
        //
        DELETE RECORD 4
        PACK
        ? LASTREC()                  // Result: 83

 Files   Library is CLIPPER.LIB.

See Also: DELETE DELETED() FLOCK() RECALL REINDEX SET DELETED

C5 Commands

Posted on July 18, 2013 by vivaclipper

 ?|??            Display one or more values to the console
 @...BOX         Draw a box on the screen
 @...CLEAR       Clear a rectangular region of the screen
 @...GET         Create a new Get object and display it
 @...PROMPT      Paint a menu item and define a message
 @...SAY         Display data at a specified screen or printer row and column
 @...TO          Draw a single- or double-line box
 ACCEPT*         Place keyboard input into a memory variable
 APPEND BLANK    Add a new record to the current database file
 APPEND FROM     Import records from a database (.dbf) file or ASCII text file
 AVERAGE         Average numeric expressions in the current work area
 CALL*           Execute a C or Assembler procedure
 CANCEL*         Terminate program processing
 CLEAR ALL*      Close files and release public and private variables
 CLEAR GETS      Release Get objects from the current GetList array
 CLEAR MEMORY    Release all public and private variables
 CLEAR SCREEN    Clear the screen and return the cursor home
 CLEAR TYPEAHEAD Empty the keyboard buffer
 CLOSE           Close a specific set of files
 COMMIT          Perform a solid-disk write for all active work areas
 CONTINUE        Resume a pending LOCATE
 COPY FILE       Copy a file to a new file or to a device
 COPY STRUCTURE  Copy the current .dbf structure to a new database (.dbf) file
 COPY STRU EXTE  Copy field definitions to a .dbf file
 COPY TO         Export records to a database (.dbf) file or ASCII text file
 COUNT           Tally records to a variable
 CREATE          Create an empty structure extended (.dbf) file
 CREATE FROM     Create a new .dbf file from a structure extended file
 DELETE          Mark records for deletion
 DELETE FILE     Remove a file from disk
 DELETE TAG      Delete a tag
 DIR*            Display a listing of files from a specified path
 DISPLAY         Display records to the console
 EJECT           Advance the printhead to top of form
 ERASE           Remove a file from disk
 FIND*           Search an index for a specified key value
 GO              Move the pointer to the specified identity
 INDEX           Create an index file
 INPUT*          Enter the result of an expression into a variable
 JOIN            Create a new database file by merging from two work areas
 KEYBOARD        Stuff a string into the keyboard buffer
 LABEL FORM      Display labels to the console
 LIST            List records to the console
 LOCATE          Search sequentially for a record matching a condition
 MENU TO         Execute a lightbar menu for defined PROMPTs
 NOTE*           Place a single-line comment in a program file
 PACK            Remove deleted records from a database file
 QUIT            Terminate program processing
 READ            Activate full-screen editing mode using Get objects
 RECALL          Restore records marked for deletion
 REINDEX         Rebuild open indexes in the current work area
 RELEASE         Delete public and private memory variables
 RENAME          Change the name of a file
 REPLACE         Assign new values to field variables
 REPORT FORM     Display a report to the console
 RESTORE         Retrieve memory variables from a memory (.mem) file
 RESTORE SCREEN* Display a saved screen
 RUN             Execute a DOS command or program
 SAVE            Save variables to a memory (.mem) file
 SAVE SCREEN*    Save the current screen to a buffer or variable
 SEEK            Search an order for a specified key value
 SELECT          Change the current work area
 SET ALTERNATE   Echo console output to a text file
 SET BELL        Toggle sounding of the bell during full-screen operations
 SET CENTURY     Modify the date format to include or omit century digits
 SET COLOR*      Define screen colors
 SET CONFIRM     Toggle required exit key to terminate GETs
 SET CONSOLE     Toggle console display to the screen
 SET CURSOR      Toggle the screen cursor on or off
 SET DATE        Set the date format for input and display
 SET DECIMALS    Set the number of decimal places to be displayed
 SET DEFAULT     Set the CA-Clipper default drive and directory
 SET DELETED     Toggle filtering of deleted records
 SET DELIMITERS  Toggle or define GET delimiters
 SET DESCENDING  Change the descending flag of the controlling order
 SET DEVICE      Direct @...SAYs to the screen or printer
 SET EPOCH       Control the interpretation of dates with no century digits
 SET ESCAPE      Toggle Esc as a READ exit key
 SET EXACT*      Toggle exact matches for character strings
 SET EXCLUSIVE*  Establish shared or exclusive USE of database files
 SET FILTER      Hide records not meeting a condition
 SET FIXED       Toggle fixing of the number of decimal digits displayed
 SET FORMAT*     Activate a format when READ is executed
 SET FUNCTION    Assign a character string to a function key
 SET INDEX       Open one or more order bags in the current work area
 SET INTENSITY   Toggle enhanced display of GETs and PROMPTs
 SET KEY         Assign a procedure invocation to a key
 SET MARGIN      Set the page offset for all printed output
 SET MEMOBLOCK   Change the block size for memo files
 SET MESSAGE     Set the @...PROMPT message line row
 SET OPTIMIZE    Change the setting that optimizes using open orders
 SET ORDER       Select the controlling order
 SET PATH        Specify the CA-Clipper search path for opening files
 SET PRINTER     Toggle echo of output to printer or set the print destination
 SET PROCEDURE*  Compile procedures and functions into the current object file
 SET RELATION    Relate two work areas by a key value or record number
 SET SCOPE       Change the boundaries for scoping keys in controlling order
 SET SCOPEBOTTOM Change bottom boundary for scoping keys in controlling order
 SET SCOPETOP    Change top boundary for scoping keys in controlling order
 SET SCOREBOARD  Toggle the message display from READ or MEMOEDIT()
 SET SOFTSEEK    Toggle relative seeking
 SET TYPEAHEAD   Set the size of the keyboard buffer
 SET UNIQUE*     Toggle inclusion of non-unique keys into an index
 SET WRAP*       Toggle wrapping of the highlight in menus
 SKIP            Move the record pointer to a new position
 SORT            Copy to a database (.dbf) file in sorted order
 STORE*          Assign a value to one or more variables
 SUM             Sum numeric expressions and assign results to variables
 TEXT*           Display a literal block of text
 TOTAL           Summarize records by key value to a database (.dbf) file
 TYPE            Display the contents of a text file
 UNLOCK          Release file/record locks set by the current user
 UPDATE          Update current database file from another database file
 USE             Open an existing database (.dbf) and its associated files
 WAIT*           Suspend program processing until a key is pressed
 ZAP             Remove all records from the current database file

Clipper and Networking

Posted on January 26, 2013 by vivaclipper

Viva Clipper !

Notes of a Clipper Language Student

Tag Archives: PACK

SET UNIQUE

SET UNIQUE*

REINDEX

RECALL

DELETE

DELETE

ZAP

ZAP

SP_BLANKREC

C5DG-4 DBFCDX Driver

Clipper 5.x – Drivers Guide

Chapter 4

DBFCDX Driver Installation and Usage

C5_PACK

Clipper and Networking

Clipper and Networking

SET UNIQUE*

Rate this:

Rate this:

Rate this:

DELETE

Rate this:

ZAP

Rate this:

Rate this:

Clipper 5.x – Drivers Guide

Chapter 4

DBFCDX Driver Installation and Usage

Rate this:

Rate this:

Rate this:

Rate this: