PRODUCT   :  R:BASE                  VERSION      :  3.1
   Here's a thorough explanation of broken pointers - what they are, how
   to know if your database has them, how to fix them, and how to prepare
   for the possibility of broken pointers, so you can easily recover from
   them. Broken pointers can cause trouble unless you find them early and
   fix them right away.
   How R:BASE Stores & Tracks Data
   Each R:BASE database is always made up of three files regardless of
   the number of tables. File 1 (CONCOMP1.RBF) holds the database
   structure and statistics about the database, file 2 (CONCOMP2.RBF)
   holds data, and file 3 (CONCOMP3.RBF) holds indexes. All three files
   must be thought of as a unit, a single database. When you back up a
   database, you must back up all three files, or the backup will be
   R:BASE finds a row of data in a database by using an address, just as
   you use an address to find a house. Each row resides at a specific
   address in database file 2.
   A pointer is a row address. Each row stored in file 2 has two pointers
   - the "next row pointer" (zero if it's the last row) and the "previous
   row pointer" (zero if it's the first row).
   A next row pointer is the address of the next row - it points to the
   next row. A previous row pointer is the address of the previous row -
   it points to the previous row. R:BASE follows the chain of next row
   pointers to find all the rows in a table.
   Broken Pointers Kink the Chain
   When something happens to damage a database, the damage is usually not
   as bad as it looks. Maybe only a single row is damaged (broken), but
   even one broken pointer causes R:BASE to lose its place in the chain
   that links all the rows in the table. R:BASE doesn't know where to
   look for the next row because the next row pointer is broken.
   However, if the table has an indexed column, you may be able to use a
   procedure outlined later in this article to jump over the damage in
   the chain and find the good rows on the other side. This is because
   R:BASE stores row addresses in database file 3 with index values in
   addition to storing row addresses as pointers in database file 2.
   Indexes Store Values & Addresses
   For each row that contains an indexed column, R:BASE stores the index
   value and row address in database file 3. An index on a column works
   like an index in a book. R:BASE looks in the index (database file 3)
   to get the correct row address. Then armed with the address, R:BASE
   goes directly to the correct row in database file 2 instead of reading
   through all the rows.
   Whether or not a row has an indexed column is up to you. Usually
   database designers will index one or more of the columns in a table
   for fast access to selected rows in that table. Typically, you will
   want to index columns that identify rows in the table or that link
   that table to other tables.
   An indexed four-byte column (INTEGER, TEXT 4, REAL, DATE, or TIME)
   helps to protect the database because you may be able to use it to
   recover from a broken pointer. Therefore, you might want to have an
   indexed four-byte column in every table. To add an index to a column,
   use the CREATE INDEX command or define the column as an indexed
   column. R:BASE maintains the index for you.
   Row Address Locations
   In summary, R:BASE stores each row's address in multiple locations:
     o  In the next row pointer of the immediately preceding row.
     o  In the previous row pointer of the next row.
     o  In database file 3 if there is an indexed column in the row.
   An Example Situation
   Say you have a hundred rows of data in your CUSTOMER table, and each
   customer has a unique CUSTID number ranging from 1 to 100. On a normal
   (non-indexed) search of your data, R:BASE consults file 1 to get the
   address (pointer) for the first row of data. Then it goes to file 2 to
   look for the data. Once it finds the first row, it uses the first
   row's next row pointer (address), to go find the next row, then the
   next, and so on.
   But say R:BASE gets stuck at CUSTID 75 because the next row address is
   somehow corrupted so that it has a value of zero or some astronomical
   number. In such a case, R:BASE does not know where to find the next
   row. CUSTID 76 is most likely still out there, but because CUSTID 75's
   next row pointer is broken, R:BASE can't find the remaining 25 rows of
   R:BASE can use either of two methods to locate rows of data in
   CUSTOMER: the forward pointing method or the direct indexing method.
   Forward Pointing Method
   Database file 1 points at (holds the address of) the first row of data
   in each table. That first row stores the address for the next row,
   which in turn stores the address for the next, and so on.
   Database file 2 holds all the data - including not only the data that
   you loaded, but also the definitions for rules, forms, reports,
   labels, and views.
   Many people think of their data as being in nice, neat, separate files
   or tables in file 2. But actually, R:BASE adds data to file 2 as you
   enter it. Therefore, the rows of data for any given table are not
   always contiguous (right next to each other). One row may point to a
   next row that is in fact separated by a lot of data from all the other
   tables. Therefore, the addresses for any one table can often leap-frog
   data from other tables.
   Each row points to the next row in sequence in a table, so it's called
   a "forward pointing address method."
   Direct Indexing Method
   In addition to the forward pointing address method, R:BASE also
   locates rows by using the "direct indexing method." As mentioned
   earlier, there's index information in database file 3 for every row in
   file 2 that has an indexed column. File 3 holds the row's index value
   and file 2 address. R:BASE uses the address to go directly to the row
   in database file 2.
   Going back to the example, say you had an index on the CUSTID column
   in CUSTOMER. When you initially created that index, R:BASE went down
   through all the current data in file 2 collecting the addresses from
   the next row pointers in file 2 and recording the addresses in file 3.
   When you add a new row to CUSTOMER, R:BASE inserts the new row's
   address in file 2 as the previous row's next row pointer. If there's
   an indexed column in the row, R:BASE also inserts the new row's
   address in file 3.
   Using Indexes to Recover Data
   R:BASE can't find the missing 25 rows in CUSTOMER because of the
   broken pointer. But file 3 still has the address information stored
   with each CUSTID index value, so you can recover the data by using the
   direct indexing method.
   For example, in R:BASE 3.1, use the following code to recover
     RENAME TABLE customer TO xx NOCHECK
     PROJECT customer FROM xx USING +
       ALL WHERE custid BETWEEN 0 AND 101
     INSERT INTO customer SELECT * +
       FROM xx WHERE custid IS NULL
     DROP TABLE  xx
     CREATE INDEX ON customer custid
   The RENAME...NOCHECK command preserves all your form and report
   locations. The PROJECT command uses the addresses stored in file 3
   with each CUSTID index value to find all the rows because its WHERE
   clause uses indexes.
   The INSERT command adds any rows where the indexed column is null. You
   need it because null values don't fall into the range specified in the
   PROJECT WHERE clause. The DROP TABLE command erases the broken table
   (XX). Then the CREATE INDEX command reindexes the CUSTID column in the
   new CUSTOMER table.
   If you're going to use an indexed column to recover data, it must be a
   four-byte column; that is it must be defined as an indexed INTEGER,
   REAL, DATE, TIME, or TEXT 4 column. Indexes on larger TEXT columns
   like COMPANY or LASTNAME are useful for speed reasons, but they won't
   recover data.
   Using R:SCOPE to Recover Tables
   Microrim's R:SCOPE is an easy-to-use tool that may make it possible
   for you to recover a table that was heretofore unrecoverable. R:SCOPE
   lets you see the raw data and pointers stored in file 2 and helps you
   make sense of it so that you may be able to find and jump over the
   break in the prointer chain.
   Programs such as The Norton Utilities or PC Tools can also see raw
   R:BASE data, but they have no mechanism for making sense of it, so
   they won't help you fix broken pointers.
   R:SCOPE's AUTOFIX feature makes recovery easy. AUTOFIX uses a
   sophisticated recovery method, a technique that only a professional
   programmer would know how to do manually. In addition, you can use
   R:SCOPE to fix other problems. Use R:SCOPE regularly to examine your
   database and correct problems while they're small.
   Victor Andrews, an R:BASE application developer, told us of a client
   who had a badly corrupted database. Using R:SCOPE, Victor was able to
   fix more than  200 broken pointers. After fixing the pointers, his
   client lost only three rows out of 50,000. R:SCOPE had saved months of
   You can purchase R:SCOPE by calling Microrim at 800-248-2001. The cost
   of purchasing R:SCOPE to fix a table will often be less than the cost
   of reentering the data.
   Causes for Broken Pointers
   Broken pointers are primarily caused by an interruption of some kind
   while R:BASE writes or modifies data in database file 2. The source of
   the interruption could be a power fluctuation, network connection
   problems, interference from memory-resident programs, turning the
   computer off while in R:BASE, or some electromagnetic interference
   such as a sudden static discharge or radio frequencies coming from
   speakers placed too close to your PC. Often it's impossible to know
   exactly what caused the broken pointers.
   Checking Database Health
   Use one of the following methods regularly to check your database for
   broken pointers.
   Enter this command at the R> prompt:
     COMPUTE ALL (1) FROM tblname
   Replace TBLNAME with the name of the table you're checking. The first
   two numbers (rows and count) should be the same. If they're different,
   you probably have a broken pointer. This technique works because the
   COUNT function counts all the rows in database file 2 and the ROWS
   function looks in file 1 for the row count.
   Run the short R:BASE 3.1B program listed below to check tables for
   broken pointers. First, connect the database. Then run TBLCHECK.CMD.
   The program lists all the tables inside a check box menu. Check off
   the tables you want to verify as correct, press [F2], and R:BASE will
   automatically check each one.
     *( TBLCHECK.CMD--Check table integrity.)
     SET VAR vcnt INTEGER = 0, vtables TEXT
     WRITE 'Press [Enter] to choose the tables +
     you want to check. Press [F2] when finished.'
     IF vtables IN ('[Esc]','HELP') +
       OR vtables IS NULL THEN
       WRITE 'You did not choose a table.'
       SET VAR vcnt = (.vcnt + 1)
       SET VAR vtblpick = (SSUB(.vtables,.vcnt))
       IF vtblpick IS NULL THEN; BREAK; ENDIF
       COMPUTE v1 AS COUNT (1) +
         FROM &vtblpick
       COMPUTE v2 AS ROWS FROM &vtblpick
       IF v1 <> .v2 THEN
         WRITE .vtblpick 'may have some errors.'
         WRITE '  Database file 2 counts' +
           .v1 'rows,'
         WRITE '  but file 1 says there are' +
           .v2 'rows.'
         WRITE .vtblpick 'looks good.'
     WRITE 'Finished checking tables.'
     CLEAR VAR v1, v2, vcnt, vtblpick, vtables
   You need R:BASE 3.1B because it allows check box menus with the
   dynamic table (#TABLES) option in a CHOOSE command.
   Use RBCHECK, a program that comes with Personal R:BASE and R:BASE 3.x.
   To use it, exit to the DOS prompt, move to the directory that contains
   your three database files, and enter this command:
     RBCHECK dbname > rbcheck.dat
   Replace DBNAME with the name of your database exclusive of the 1, 2,
   or 3.RBF extensions. For example, use CONCOMP, not CONCOMP1.RBF.
   The output will be captured in a file (RBCHECK.DAT). Print it out or
   call it up in an editor when RBCHECK finishes. If you experience any
   trouble or get error messages associated with file 2, such as "Id=
   ..., ROW COUNT ...," or "IDEND ...," you may have a broken pointer.
   For more information on RBCHECK, see "Blunder Insurance" in this
   Use AUTOCHK, a program that comes with R:SCOPE and R:BASE 3.1B, to
   check the integrity of your database. Here's the syntax:
     AUTOCHK dbname /n
   Replace DBNAME with the name of the database.
   AUTOCHK opens the database and performs the same database integrity
   checking routine that R:SCOPE performs when you choose the Database
   Info, Structure, and Data options from the Check menu. AUTOCHK doesn't
   check indexes.
   If AUTOCHK finds an error, it stops checking and displays one error
   message.  If AUTOCHK finds no errors, it displays "No errors found."
   If you press a key while AUTOCHK is checking the database, the program
   stops and displays "User abort." To suppress messages include the /n
   AUTOCHK automatically sets the DOS exit code (error variable) to the
   number corresponding to the message returned. For example, if AUTOCHK
   displays this error: "Unable to open database file number 2," it sets
   the DOS error variable to 52 - if you specified the /n option.
   Use the DOS error variable to alert a user to database errors. Below
   are two examples showing you how. The first demonstrates how to run
   AUTOCHK from inside R:BASE by using the ZIP command, and the second
   shows how to use it in a DOS batch file.
   AUTOCHK from Inside R:BASE
   If you have R:BASE 3.1B, you can run AUTOCHK from inside R:BASE
   because the ZIP command passes the DOS error variable back to R:BASE.
   Set an error variable before executing ZIP. Then on return to R:BASE,
   the error variable will hold the DOS error variable value. CHECKDB.CMD
   (listed below) checks database integrity.
     *( CHECKDB.CMD--Use AUTOCHK to)
     *( check database integrity.)
       WRITE 'Choose a database or press [Esc].'
       IF vdb IN ('[Esc]','HELP') +
         OR vdb IS NULL THEN
       WRITE 'Checking database for errors...'
       SET ERROR VAR evar
       ZIP AUTOCHK &vdb /n
       SET VAR e1 = .evar
       IF e1 > 53 AND e1 <> 456 THEN
         WRITE 'AUTOCHK found errors in' .vdb
       IF e1 > 0 AND e1 < 54 OR e1 = 456 THEN
         WRITE 'AUTOCHK did not run.'
       IF e1 = 0 THEN
         WRITE 'No errors found in' .vdb
     CLEAR VAR e1, evar, vdb
   If you don't have R:BASE 3.1B, run AUTOCHK from a DOS batch file.
   AUTOCHK from a DOS Batch File
   When executing AUTOCHK from a DOS batch file, use the DOS IF command
   to check the DOS error variable. Below is an example batch file that
   uses AUTOCHK to check the database and, depending on the outcome,
   starts R:BASE or displays a message. It assumes DBNAME is the database
   name and DBFILES is the directory holding the database.
     @ECHO OFF
     CD \dbfiles
     ECHO Checking database for errors...
     AUTOCHK dbname /n
     IF errorlevel 50 GOTO errors
     IF errorlevel 20 GOTO abort
     ECHO No errors found  - Starting R:BASE.
     RBASE -r
     GOTO end
     ECHO Errors found in the database.
     ECHO Correct before running R:BASE.
     GOTO end
     ECHO AUTOCHK not run - User aborted.
     ECHO You must check the database before
     ECHO starting R:BASE.
   AUTOCHK Errors
   After examining a database, AUTOCHK may return a zero, meaning no
   errors were found, or it may return any one of 37 error messages, each
   with its own number. All 37 are listed in the R:BASE 3.1B
   Once AUTOCHK determines there is an error, you'll want to use R:SCOPE
   to see complete information.
   AUTOCHK in Multi-user
   If you use AUTOCHK on a database that's currently open with MULTI set
   ON, AUTOCHK opens the database and places a database lock on it. The
   database stays locked until AUTOCHK finishes. The other users must
   wait to make any changes.
   If you use AUTOCHK on a database that's currently open with MULTI set
   OFF, AUTOCHK won't be able to open the database. It says: "Unable to
   open the database file 1."
   If you try to open a database currently being checked by AUTOCHK and
   nobody else is in the database, you'll see this message: "The database
   is currently open in a mode that makes it unavailable." If others have
   the database open with MULTI set ON and the database is currently
   being checked, you'll see the "Waiting in lock queue" message.
   AUTOCHK & Passwords
   If you run AUTOCHK on a database that has an owner password, AUTOCHK
   won't prompt you for the password but will continue the integrity
   check. AUTOCHK doesn't display data or structure information, so
   security isn't compromised.
   An Indexed INTEGER in Each Table
   As explained above, you can use an indexed INTEGER column containing
   unique values to quickly restore a table that has broken pointers.
   Therefore, as a precaution, you might want to make sure every table
   has one.
   First, use one of the methods outlined above to ensure database
   integrity. Then add an indexed INTEGER autonumber column to those
   tables that don't already have an indexed four-byte column.
   By making it an autonumber column, you'll ensure unique values. Give
   each autonumber column a unique name, so it doesn't become a common
   (linking) column. Then create an index for each autonumber column.
   R:BASE will manage the indexes and automatically number the autonumber
   columns for you, but the overhead involved will slow down data entry.
   You have to decide if the extra protection is worth the cost in speed.
   Back Up, Back Up, Back Up
   Back up your database regularly. First, check database integrity. Then
   back it up by using the R:BASE BACKUP command, the DOS BACKUP command,
   or a third-party backup utility. The few minutes it takes to make a
   backup may save you hours, even months, of work.
   Rotate your set of backup disks so you never overwrite your most
   recent backup. That is, back up Wednesday's database on top of
   Monday's. Then if Wednesday's database is bad, you'll still have
   Tuesday's database.