Indexing 
Author Message
 Indexing

Hi,
Can anyone tell me how to Re-Index my tables?
Sometimes the indexes get corrupted and I would like to rebuild them.
In Clipper I used to simply create new indexes every time the program
started to make sure the indexes are OK. Now I use a small Clipper
program to reindex my DBASE tables for D3.

Thank you



Mon, 09 Apr 2001 03:00:00 GMT  
 Indexing



Quote:
> Hi,
> Can anyone tell me how to Re-Index my tables?
> Sometimes the indexes get corrupted and I would like to rebuild them.
> In Clipper I used to simply create new indexes every time the program
> started to make sure the indexes are OK. Now I use a small Clipper
> program to reindex my DBASE tables for D3.

> Thank you

To pack a dBASE table that has been opened with a TTable,
use the BDE function DbiPackTable.  There are two basic stepsto do this:
1. Add the following units to your uses clause:
{ For Delphi 1.0: } DBITYPES, DBIPROCS and DBIERRS;
{ For Delphi 2.0 and 3.0: } BDE;
2) Then call the DbiPackTable BDE function as follows:
Check(DbiPackTable(Table1.DbHandle, Table1.Handle, Nil, szDBASE,
TRUE));Notes:
* The table must be opened in exclusive mode.
* Use the Check procedure when calling BDE API functions.  Check
  will raise an exception if an error occurs on the BDE call.

J-F Petit



Mon, 09 Apr 2001 03:00:00 GMT  
 Indexing


Quote:
>Can anyone tell me how to Re-Index my tables?
>Sometimes the indexes get corrupted and I would like to rebuild them.
>In Clipper I used to simply create new indexes every time the program
>started to make sure the indexes are OK. Now I use a small Clipper
>program to reindex my DBASE tables for D3.

There are a number of ways you could approach this.

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 (for Delphi 1, the BDE.DCU unit for Delphi 2 and 3)
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).

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Mon, 09 Apr 2001 03:00:00 GMT  
 Indexing
HI,

Thank you very much for your reply.

Now I get the message: Table is busy

The message is generated on the OPEN statement.

Any idea why this is?

Thanks again -

Werner

Quote:



>>Can anyone tell me how to Re-Index my tables?
>>Sometimes the indexes get corrupted and I would like to rebuild them.
>>In Clipper I used to simply create new indexes every time the program
>>started to make sure the indexes are OK. Now I use a small Clipper
>>program to reindex my DBASE tables for D3.

>There are a number of ways you could approach this.

>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;  <<<<<<<<<<<<< Error Message Here <<<<<<<<<<<<<<<<<<<
>      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 (for Delphi 1, the BDE.DCU unit for Delphi 2 and 3)
>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.



Tue, 10 Apr 2001 03:00:00 GMT  
 Indexing
: Thank you very much for your reply.
: Now I get the message: Table is busy
: The message is generated on the OPEN statement.
: Any idea why this is?

Because the table is in use, and an exclusive lock cannot be placed
on the table.  Either another user has it open, or you have it open
in a window somewhere, or in use in your form.



: >
: >>Can anyone tell me how to Re-Index my tables?
: >>Sometimes the indexes get corrupted and I would like to rebuild them.
: >>In Clipper I used to simply create new indexes every time the program
: >>started to make sure the indexes are OK. Now I use a small Clipper
: >>program to reindex my DBASE tables for D3.
: >
: >There are a number of ways you could approach this.
: >
: >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;  <<<<<<<<<<<<< Error Message Here <<<<<<<<<<<<<<<<<<<
: >      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 (for Delphi 1, the BDE.DCU unit for Delphi 2 and 3)
: >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.

--
============================================================

============================================================



Tue, 10 Apr 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Field Index Out of Range when trying to re-open a table with a different index

2. ? Index does not exist; index: PrimaryKey

3. Index out of date, but no index

4. Index Error - Index out date

5. Adding Secondary Indexes without a Primary Index

6. index re index with paradox

7. Making a new Index: Invalid Index Descriptor (CDX)

8. FindKey() on index with multiple-key-index and using NOT ALL fields...

9. FindKey() on index with multiple-key-index and using NOT ALL fields??!?

10. EDBEngineError with message 'Index does not exist. Index:Division_ID'

11. TechTips: The Basics of Indexing

12. Corrupt table/index header

 

 
Powered by phpBB® Forum Software