How to Remember Current ISAM Record for Different Indexes (79149)


The information in this article applies to:

  • Microsoft Basic Professional Development System (PDS) for MS-DOS and MS OS/2 7.1
  • Microsoft Basic Professional Development System (PDS) for MS-DOS and MS OS/2 7.0
This article was previously published under Q79149

SUMMARY

Using the SETINDEX statement in ISAM sets the current index for the specified table. It also sets the current record to the first record according to the ordering of that index. Basic Professional Development System (PDS) ISAM does not directly support maintaining the current record position for an index between uses of that index.

This article describes three methods for remembering the current record for each index and restoring to that position after changing indexes to work around this limitation.

MORE INFORMATION

Method 1

One way to remember the position of the current record according to an index is to count the record's relative position (from the beginning or end of the table) before changing to another index. This could be accomplished by using either MOVEPREVIOUS, with the beginning of the table as a reference point, or MOVENEXT, with the end of the table as a reference point. However, because this method is sequential, it is slow on larger data bases. Below is an example, using "One" and "Two" as arbitrary index names: 
Index1Pos = 0            'using index "One"
WHILE NOT BOF(1)
  MOVEPREVIOUS #1
  Index1Pos = Index1Pos + 1
WEND
SETINDEX #1, "Two"
The variable Index1Pos can then be used to find the same record when the index is returned to: 
SETINDEX #1, "One"
'to get to Nth record, MOVENEXT N-1 times:
FOR i% = 1 to (Index1Pos - 1)
   MOVENEXT #1
NEXT i%
This will make the original record the current one, thereby achieving the desired result. It is important to note that this method will only work if no INSERTs, UPDATEs, or DELETEs have been done with the second index that would affect the position of any record under the first index. Otherwise, the counting used above may return to a different record than expected, because the index may no longer be in the original state.

Method 2

A temporary variable can be used to maintain the key value(s) for the current record for the desired index. These key values could be used in a SEEKEQ when the original index is returned to, making the record with those key values the current record. Assume the following type and index are used in the example below: 
TYPE DataType
Element1 AS STRING * 15
Element2 AS CURRENCY
END TYPE

DIM Temp AS DataType
OPEN "test.mdb" FOR ISAM DataType "tabletype" AS #1
CREATEINDEX #1, "One", 1, "Element1", "Element2"   'Note Unique Index
SETINDEX #1, "One"
...
RETRIEVE #1, Temp      'Marker for record to return to
SETINDEX #1, "Two"
...
SETINDEX #1, "One"
SEEKEQ #1, Temp.Element1, Temp.Element2     'Return to marked record
When this is done, the unique index property ensures that the current record is the same as it was when the index was changed away from "One". This method is much faster than the previous one because ISAM does the searching; however, this method requires that the index in question be unique so there is only one possible record for the SEEKEQ to find. With a non-unique index, this method may not find the desired record, because there could be several records that match the seek criteria.

Method 3

In some situations, you may want to know the physical position of a record within an index. This position could be used to display the relative position of the current record to the user of the database application, giving them an indication of "where they are" in their database file. Knowing the record's position also allows you to easily return to that record when indexes are changed. To do this, keep a position element in the type used to create the table as in the example below: 
TYPE DataType
LastName AS STRING * 15
PhoneNumber AS STRING * 12
RecordPosition AS INTEGER  ' keeps track of record's position in index
END TYPE

DIM Temp AS DataType

OPEN "test.mdb" FOR ISAM DataType "tabletype" AS #1
' Misc. code here that would insert records into table, leaving the
' RecordPosition element empty (zero)
CREATEINDEX #1, "One", 0, "LastName"
SETINDEX #1, "One"      ' forces first record to be current record
count = 0
WHILE NOT EOF(1)
count = count + 1      ' mark first record with a one, etc.
RETRIEVE #1, Temp
Temp.RecordPosition = count
UPDATE #1, Temp
MOVENEXT #1
WEND
...
The element Temp.RecordPosition can be used as a marker in correlation with the second part of Method 1 (for example, Index1Pos = Temp.RecordPosition). Index1Pos can be used to count to the record that was current when the index was last used. Also, any time a RETRIEVE is done with index "One," the associated record number can be displayed (for example, LOCATE 25, 77: PRINT Temp.RecordPosition). Note that the RecordPosition field of records would have to be updated any time records are INSERTed, UPDATEd or DELETEed, because these operations could change the order of the records in the index.
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:KB79149
©2003 Microsoft Corporation. All rights reserved.