Out of Date Indexes with Paradox

Hi ZifNab

I went to that site and downloaded two aps and tried them out - neither of them worked!!  I really need some help here.  Do you have a code snippet or something.  I can't open the Tables, so I can't fix the Indexes because the primary key is lost.

What if you open the tables from the database desktop?

The Database Desktop won't open them - I just get the message saying Index out of Date

Hi jdthedj,

This is a problem if none of the components can help repair the table. The only thing I'm able to think of is to delete all the index tables and let them regenerate. I found an article about this. Be aware that I don't know the result of these function so make a backup of your database!!!! Hope this helps, because otherwise I really shouldn't know, sorry :

One way would be to make a call to the BDE API function DbiRegenIndexes.
This insulates you from having to know what indexes exist, if any, the BDE
handling all the code for you. Error checking can be handled by examining
the return value (type DBIResult) or by using the Check function (defined
in the BDE wrapper unit DbiProcs).

  procedure TForm1.Button4Click(Sender: TObject);
  var
    aExclusive, aActive: Boolean;
  begin
    with Table1 Do Begin
      aActive := Active;
      Close;
      aExclusive := Exclusive;
      Exclusive := True;
      Open;
      Check(DbiRegenIndexes(Table1.Handle));
      Close;
      Exclusive := aExclusive;
      Active := aActive;
      Check(DbiSaveChanges(Table1.Handle));
    end;
  end;

As when calling any BDE API function, the BDE API wrapper units DbiTypes,
DbiErrs, and DbiProcs must be referenced in the Uses section of the unit
from which the call is to be made. The BDE API function DbiSaveChanges,
used here, forces any data changes in memory buffer to be written to disk
at that point.

Another way to handle this situation -- if you know at design-time all the
indexes that will exist for the table -- would be to iterate through the
items in the TIndexDefs object of the TTable component, delete each index
(DeleteIndex method), and then add all needed indexes back (AddIndex
method).

  procedure TForm1.Button3Click(Sender: TObject);
  var
    aName: String;
    i: Byte;
    aExclusive, aActive: Boolean;
  begin
    with Table1 do begin
      aActive := Active;
      Close;
      aExclusive := Exclusive;
      Exclusive := True;
      IndexDefs.Update;
      i := IndexDefs.Count;
      while i > 0 do begin
        aName := IndexDefs.Items[i - 1].Name;
        DeleteIndex(aName);
        Dec(i);
      end;
      AddIndex('', 'MainField', [ixPrimary]);
      AddIndex('Field1', 'Field1', []);
      AddIndex('Field2', 'Field2', []);
      IndexDefs.Update;
      Exclusive := aExclusive;
      Active := aActive;
      Check(DbiSaveChanges(Table1.Handle));
    end;
  end;

When iterating through the items in the TIndexDefs object, the cycling must
be backwards, from highest to lowest. This is to account for those table
types that have primary indexes. With those table types, deleting a primary
index first causes all secondary indexes to be unavailable, which
interferes with this iterating based on the TIndexDefs array object
contents. This is because a secondary index cannot exist in some table
types (such as Paradox) without an existing primary index. For the same
reason, when recreating the indexes, the process should start with the
primary index and then progress through all secondary indexes (if any are
to be created).

If information about the index definitions is not known at design-time,
this process becomes emminently more complex. The data from all indexes
will need to be saved to memory, the information for all indexes existing
simultaneously in memory. This is because a primary index would need to be
deleted at some point (to later be rebuilt), destroying references to any
secondary indexes (whether retrieval for the secondary indexes takes place
before or after deletion of the primary index). To accomplish this, some
multi-entity storage structure would need to be created to hold a variable
number of elements, one element per index. Each element would need to be
able to store the four bits of data that comprise an index's definition:
Name (String), Fields (String), Expression (String), and Options
(TIndexOptions). An array or a TList object of Pascal records with these
data fields would suffice for this purpose.

Once the definition information for all indexes are stored to memory, the
succeeding steps are similar to those for the previous method: Delete each
index (DeleteIndex) and then recreate each index based on the definition
information stored in the array or TList (AddIndex).

Good luck,
Regards, Zif.

something interesting :

TechTips: Avoiding index-out-of-date and other Delphi(16) problems


This TechTip addresses:
        PREVENTING "INDEX OUT OF DATE" & LOST UPDATES
        NETWORK MULTI-ACCESS CONSIDERATIONS
-------------------------------------------------------------------------------

PREVENTING "INDEX OUT OF DATE" & LOST UPDATES

Delphi-16 applications can experience the problem that database updates do not
get written out to disk expediently.  In fact, updates may not write to disk
for many hours -- until the table is closed.  If the application fails for any
reason, the updates might not go out at all.  

The root problem is that BDE is designed to write out "dirty" information as a
background activity, and since Win16 has no threads, BDE has an API routine
that must be called on application-idle (e.g. between keystrokes) and also by
a timer routine (for safety) in order to perform this.

Here is example code:

[snip!]

{ ======================= DOING WHAT BDE FAILS TO DO ======================== }

{
  This function is used to determine if the DB engine has been initialized and
  it is therefore safe to make a call to it.
}
function TMainForm.DBIsConnected: boolean;
var
   dbCount:  integer;
begin
   result := False;
   for dbCount := 0 to Session.DatabaseCount - 1 do begin
      if Session.Databases[dbCount].Connected then begin
         result := True;
         break;
      end;
   end; {for}
end; {DBIsConnected}

{
  This idle-time routine gives BDE a chance to do background processing,
}
procedure TMainForm.AppIdle(Sender: TObject; var done: boolean);
begin
  FlushPages;
  done := True;
end;

{
  This timer-event forces the same idle-time action to occur periodically
  when the application is not-idle for longer periods of time, such as during
  intensive processing.
}
procedure TMainForm.IdleTimerTimer(Sender: TObject);
begin
  FlushPages;
end;

{
  This is the worker-bee for flushing those dirty pages.  Problems can occur if
  it gets called when the database isn't connected (aside from the fact that it
  is useless then).  Even though we don't enable the timer or the OnIdle trap
  until the database is connected, we nevertheless check here explicitly.

  Although Borland "fixed" this problem in later releases of BDE, the basic
  problem is that if you don't explicitly flush dirty-pages, lots of changes
  will not get written out to disk timely.  We use both an idle-event trap and
  a timer to be certain that, no matter what, the pages get written out!
}
procedure TMainForm.FlushPages;
begin
   if DBIsConnected then begin
      DbiUseIdleTime;
   end;
end;

[snip!]

BDE 2.5.2 helps to address the problem by inserting some other explicit calls
to this routine [somewhere!] in the mainline paths of BDE.  In my
applications, however, I cover my bases with the above code.

++++++++++++++++++++++++++++++++++++++++++++

NETWORK MULTI-ACCESS CONSIDERATIONS:

Paradox/BDE supports multi-user access very well.  We're comfortable deploying
applications with up to ten or fifteen simultaneous users.  But in order to
set it up properly you must use the BDE Configuration Utility on each machine
to set up the "NET FILE DIR" on page #1 (PARADOX driver section) of BDE Config.

The "NET FILE DIR" is where the locking-files are stored.  All users must
point to the same lockfile-directory, must have read/write/create privileges
there, and must know the directory by the same name.  The only thing that can
be safely different is the drive-lette

Hi Zif

I'm going to give you the points on this one, because you've done a lot of work on my question. It is the Primary Key that has gone missing from the Tables, and on reading all your latest info it would appear that if the Primary Key goes missing it may not be able to be retrieved.
I wonder if anyone else has any comments to add.

Thanx for all your help.

JD

Well, glad to help but it's sad that the problem can't be solved.
Zif.