Clipper Support Bulletin #10
Solutions to DBFNTX/DBFNDX indexing problems

Copyright (c) 1991, 1992 Nantucket Corporation.  All rights reserved.


Version:  Clipper 5.0, revisions 1.00 - 1.29, version 5.01
Date:     24th March, 1992
Revised:  22nd May, 1992
Status:   Active

================================================================================

This Support Bulletin covers the following topics:

   1. Replacing key fields
   2. Index key length must be fixed
   3. Convert numeric key expressions to character
   4. Recreate index with INDEX instead of REINDEX
   5. Opening the same database in multiple work areas
   6. Don't use memory variables in key expressions
   7. Preventing index corruption on a network
   8. Interacting with dBASE III PLUS
   9. Runtime errors during index write operations
   10.Expanded memory problems
   11.Memory-resident programs and disk caches
   12.Workstation crashes when INDEXing under DOS 4.x
   13.Indexes improperly updated
   14.Determining which index is causing a problem

================================================================================
1. Replacing key fields
   
   When replacing a key field, the current index is updated and the
   relative position of the record pointer within the index is
   changed.  This means that specifying a REPLACE command with a
   scope or condition to change a key field may yield an erroneous
   result.  To update a key field, SET ORDER TO 0 before the REPLACE.
   This ensures that the record pointer moves sequentially in natural
   order.  However, all open indexes are still updated.
   
================================================================================
2. Index key length must be fixed
   
   Make sure the length of the index expression is the same for every
   record.  Clipper stores key values in fixed increments, and index
   key sizes are calculated by evaluating the expression on a blank
   record.  A trimmed expression, therefore, always evaluates to a
   null string, leading to a size mismatch between the target and the
   defined key length.  To use any of the TRIM() functions within a
   key expression, use PADR() to guarantee a fixed length, like this:
   
   USE Customer NEW
   INDEX ON PADR(RTRIM(Last) + First, 40) TO CustName
   
================================================================================
3. Convert numeric key expressions to character
   
   Use of numeric keys can sometimes lead to precision problems.  You
   can largely avoid this issue by converting numeric indexes to
   character indexes using the STR() function in the index
   expression.  Note, be sure to specify both the numeric value as
   well as the length arguments when specifying the STR() in the key
   expression.
   
================================================================================
4. Recreate index with INDEX instead of REINDEX
   
   When recreating damaged or corrupted indexes, use INDEX ON rather
   than the REINDEX command.  REINDEX uses the existing index header
   which may be the source of the corruption.  On local drives, you
   may want to issue CHKDSK /F before recreating the indexes.
   
================================================================================
5. Opening the same database in multiple work areas
   
   If you open the same database file in multiple work areas you need
   to treat each work area as if it is a different workstation on a
   network.  SHARE must be loaded or the database file must be
   located on a network drive and opened shared.  If you plan to
   update the database file from one of the work areas, you must have
   all applicable indexes open to ensure all stay up to date.  Do not
   open database files in multiple work areas if you do not have
   SHARE loaded or the database files are not located on a network
   drive.
   
================================================================================
6. Don't use memory variables in key expressions
   
   Avoid including memory variables in key expressions for the
   following reasons:
   
   1. Values are undependable
   
   2. Scope dependent
   
   3. Application dependent
   
   Use fields, operators, and functions in key expressions only.
   
================================================================================
7. Preventing index corruption on a network
   
   Because network operating systems buffer disk writes, it is
   possible to corrupt indexes if a workstation crashes after
   updating an index and the writes are still in the buffer.
   
   To prevent this, do all the REPLACEs, unlock the file, and then
   COMMIT to force writes. If the database file was opened exclusive,
   do all the REPLACEs then COMMIT to force writes.
   
   For multiuser applications under DOS 3.3 or above, issue the
   COMMIT command after unlocking the record or file.  For DOS 3.2 or
   below, the only way to ensure that the writes are actually written
   to disk is to close the file.
   
   For more information, refer to the Update Visibility section of
   the Network Programming chapter in the Programming and Utilities
   Guide.
   
================================================================================
8. Interacting with dBASE III PLUS
   
   Avoid opening a database in dBASE III PLUS and issuing a PACK or
   ZAP.  This will cause end of file markers to be inserted in the
   file, which may result in database or index corruption when the
   file is read by Clipper.
   
================================================================================
9. Runtime errors during index write operations
   
   If either an unrecoverable, and internal fatal runtime error such
   as "Disk full," "Out of memory," occurs during a write operation,
   the indexes being written may become corrupted.  If this happens,
   it is advisable to recreate all indexes using the INDEX command.
   
================================================================================
10.Expanded memory problems
   
   Since some expanded memory boards or third-party software may not
   be "well-behaved" in their use of expanded memory, try setting the
   E parameter of the CLIPPER environment variable to 0 (e.g. SET
   CLIPPER=E0).  This should only be attempted if the BADCACHE
   setting mentioned in point 9 has not helped.
   
================================================================================
11.Memory-resident programs and disk caches
   
   Check for memory-resident programs and disk caches that may cause
   problems.  If you suspect that a cache may be the source of your
   problems, set the CLIPPER environment variable to BADCACHE (e.g.
   SET CLIPPER=BADCACHE).
   
================================================================================
12.Workstation crashes when INDEXing under DOS 4.x
   
   If the workstation crashes during an INDEX operation under DOS
   4.x, issue SHARE with different F and L parameters. For example,
   in your AUTOEXEC.BAT file, insert the following command line:
   
   SHARE /F:4096 /L:31
   
================================================================================
13.Indexes improperly updated
   
   If an application program doesn't have all indexes open when key
   fields are updated, it is a strong probability an index will not
   be correctly updated.  This problem will not be discovered until
   another application that uses the index that wasn't updated when
   an attempt is made to use the index.
   
================================================================================
14.Determining which index is causing a problem
   
   Sometimes, an index file problem may not be noticed until another
   index is opened.  This means that the file actually causing the
   problem may not be the one specified in the error message.  If
   this is the case, try recreating the previously opened file.
   

                              *  *  *