Solved

Out of Date Indexes with Paradox

Posted on 1998-02-11
8
645 Views
Last Modified: 2010-07-01
I am using D1 with WIN 3.11

I accidentally overwrote a couple of Tables and have made the Indexes out of date.  How do I repair them?.  I tried to read the IndexDefs, then drop the Indexes and rebuild them but it doesn't work.
0
Comment
Question by:jdthedj
  • 5
  • 3
8 Comments
 
LVL 8

Accepted Solution

by:
ZifNab earned 100 total points
ID: 1358962
There are a number of freeware components for this. Go to
the database section of http://torry.rimini.com/.
Take the one you like. You can offcourse do it yourself, but why do that if others already did it?
0
 
LVL 3

Author Comment

by:jdthedj
ID: 1358963
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.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358964
What if you open the tables from the database desktop?
0
 
LVL 3

Author Comment

by:jdthedj
ID: 1358965
The Database Desktop won't open them - I just get the message saying Index out of Date
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Expert Comment

by:ZifNab
ID: 1358966
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.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358967
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
0
 
LVL 3

Author Comment

by:jdthedj
ID: 1358968
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
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358969
Well, glad to help but it's sad that the problem can't be solved.
Zif.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now