Click above to get retro games delivered to your door ever month!

   Indexing on Variable Length Fields

   There are several issues that must be addressed by the programmer in
   order to create and maintain an index on part (or all) of a FlexFile
   Variable Length Field.

   First, creating an index is as easy as:

      INDEX ON V_RET( vlf_fld, "ALIAS_1", 1, 15 ) TO NTX_FILE.NTX

   This would create an index on the first fifteen characters of a FlexFile
   VLF. However, every element of a clipper index must be the same length
   and the above example does not guarantee this.  (If V_RETRIEVE() returns
   a string of less than 15 characters, you will corrupt your index.)  So,
   you should PAD() the return from V_RETRIEVE() with spaces as in:

      INDEX ON PAD( V_RET(.....), 15 ) TO NTX_FILE.NTX

   The second issue is to note that only data of the same type can be
   indexed in this manner.  Accordingly, make sure that the character data
   is not binary data.  If a CHR(0) is found in the first 15 characters of
   the above example, Clipper will consider that to indicate the end of the
   string.  This will make the string's length different from other strings
   in the index and, thereby, corrupt the index.

   The last issue is described below, but is more complex. However, it is
   not necessary to understand why, as long as you always abide by the
   rule:  You must always have a REPLACE command that does not have a
   V_REPLACE in its syntax before REPLACEs that contain V_REPLACE(). For
   example,

      && INCORRECT....will corrupt the index if the index relys on either
      && the data pointed to by vlf_1 or vlf_2.
      REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
      REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
      SKIP

      && CORRECT....the first replace is a "dummy" but maintains integrity.
      REPLACE any_fld WITH any_fld
      REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
      REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
      SKIP

   First, the only reason I placed two V_REPLACE()s in the example is to
   show that only one "dummy" replace is necessary before any number of
   imbedded V_REPLACE()s.  Remember that anything that causes the index to
   be updated (such as COMMIT, SKIP, SEEK, etc.) require that a new "dummy"
   replace be used before attempting another REPLACE with imbedded
   V_REPLACE()s.  For example,

      && CORRECT
      REPLACE any_fld WITH any_fld
      REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
      REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )
      SKIP

      && INCORRECT....will corrupt the index without another "dummy"
      && replace after the COMMIT.  Assuming, of course, that vlf_2
      && is included in the index key.
      REPLACE any_fld WITH any_fld
      REPLACE vlf_1 WITH V_REPLACE( "testing", vlf_1 )
      COMMIT
      REPLACE vlf_2 WITH V_REPLACE( "testing", vlf_2 )


   Ok, so what is going on?  Understanding the sequence of events that
   occurs during a REPLACE is critical to understanding the apparent
   paradox in the examples above.

   Remembering that the arguments of a function or command are all
   evaluated before the function itself is called, the syntax

      REPLACE vlf_fld WITH V_REP( "Testing an indexed replace", vlf_fld )

   first executes FlexFile's V_REPLACE() and then executes Clipper's
   REPLACE.

   This affects indexing because Clipper always evaluates the index key
   twice.  Once at the begining of the first of one or more REPLACEs (to
   remember which index key has to be removed from the index) and the
   second after any of several "commit" commands such as COMMIT, SKIP,
   SEEK, GOTO, etc. (to calculate and insert a new index key).  However, if
   FlexFile already deleted the old information pointed to by vlf_fld
   before CLIPPER began to execute the REPLACE code, then the first
   evaluation of the index key occurs after its data has already been
   deleted by FlexFile.

   I realize this is confusing so I will try again in steps:

                                       +- step 2  +- step 1
                                       |          |
      REPLACE vlf_fld WITH V_REPLACE( "testing", vlf_fld )
      |                    |
      +- step 4            +- step 3
         step 5

   1. FlexFile deletes the data associated with the pointer-field vlf_fld
   that is in the file before the REPLACE.

   2. FlexFile puts away the text "testing" in an appropriate location.

   3. FlexFile returns a six byte pointer-field which is immediately passed
   to REPLACE.  This pointer-field is different than that of step 1 and
   this new pointer-field is not yet in the file (it is being stored in
   memory for the moment).

   4. Clipper begins executing its REPLACE code with the string passed to
   it from step 3.

   5. Clipper evaluates the index and stores the evaluated value in a
   buffer.  This sets up the error.  The index key should evaluate to the
   data pointed to by the vlf_fld which is still in the file. However,
   FlexFile deleted that data in step 1. Therefore, the V_RETRIEVE() in the
   index key will return a null string and your PAD function will change
   this to 15 bytes of spaces.

   No Clipper error actually occurs until the first COMMIT, SKIP, SEEK,
   etc.  It is at this point that Clipper does the work on the index.  The
   first thing Clipper does is delete the old index key (which was put into
   a memory buffer in step 5).  However, Clipper will not be able to find
   an index key which matches the 15 spaces in the memory buffer and,
   therefore, assumes that the index is corrupted.

   So, the way to resolve this is to have a "dummy" replace that will force
   Clipper to evaluate the index key BEFORE FlexFile deletes the key.  The
   thing that may be confusing is that Clipper does not care which field is
   in the first REPLACE.  It is simply a fact that the first REPLACE
   evaluates the "before" index key regardless of whether the REPLACE even
   affects the index key.  It is also a fact that no other index action
   occurs until a "commit" type command.  At that point the "before" key is
   deleted from the index and the "after" key is inserted into the index.