We help IT Professionals succeed at work.

RE-INDEX TABLES / PACKING  (PARADOX) DELPHI 5.0

aerokevs
aerokevs asked
on
HOW DO WE RE-INDEX TABLES(PARADOX) IN dELPHI 5.0
AND HOW DO WE PACK THE TABLES....
Comment
Watch Question

Commented:
This provide you information about how to Pack and Re-build the indexes on Paradox and DBase tables:

I copy it from my E-Book (Delphi Programming Guide) www.geocities.com/motaz1

----------------------


When you delete records from Paradox or DBase tables, the size of phisycal file will not reduced by this deletion because it is a logical deletion. When you add new records to that tables, the new records will occupy the same location of deleted records, if all spaces that left by deleted records already occupied, then new records will allocate new size for the table. You can remove unused space allocated by deleted records by packing the table. Packing Paradox and DBase tables does not remove only deleted records, but it regenerate out-of-date indexes, also it removes passwords from tables. To pack Paradox or DBase table call this function:


Add to uses clause:

DB, DBTables, and BDE

procedure PackTable(Table: TTable);
var
  Props: CURProps;
  hDb: hDBIDb;
  TableDesc: CRTblDesc;

begin
  // Make sure the table is open exclusively so we can get the db handle...
  if Table.Active = False then
    raise EDatabaseError.Create('Table must be opened to pack');
  if Table.Exclusive = False then
    raise EDatabaseError.Create('Table must be opened exclusively to pack');

  // Get the table properties to determine table type...
  Check(DbiGetCursorProps(Table.Handle, Props));

  // If the table is a Paradox table, you must call DbiDoRestructure...
  if Props.szTableType = szPARADOX then
  begin
    // Blank out the structure...
    FillChar(TableDesc, sizeof(TableDesc), 0);
    //  Get the database handle from the table's cursor handle...
    Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE, hDBIObj(hDb)));
    // Put the table name in the table descriptor...
    StrPCopy(TableDesc.szTblName, Table.TableName);
    // Put the table type in the table descriptor...
    StrPCopy(TableDesc.szTblType, Props.szTableType);
    // Set the Pack option in the table descriptor to TRUE...
    TableDesc.bPack := True;
    // Close the table so the restructure can complete...
    Table.Close;
    // Call DbiDoRestructure...
    Check(DbiDoRestructure(hDb, 1, @TableDesc, nil, nil, nil, FALSE));
  end
  else
    // If the table is a dBASE table, simply call DbiPackTable...
    if Props.szTableType = szDBASE then
      Check(DbiPackTable(Table.DBHandle, Table.Handle, nil, szDBASE, TRUE))
    else
      // Pack only works on PAradox or dBASE; nothing else...
      raise EDatabaseError.Create('Table must be either of Paradox or dBASE ' +
               'type to pack');

  Table.Open;

end;


Example of calling this function:


  Table1.TableName:= 'd:\phone\phone.db';
  Table1.Exclusive:= True;
  Table1.Open;
  PackTable(Table1);
  Table1.Close;

--------------------------

Motaz

Commented:
How to reindex Paradox tables
--------------------------------------------------------------------------------

There are a number of ways to 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 BDE).



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

Commented:
How to pack a Paradox or dBase table programatically
--------------------------------------------------------------------------------

function dgPackParadoxTable(Tbl: TTable; Db: TDatabase): DBIResult;
{Packs a Paradox table by calling the BDE DbiDoRestructure function. The TTable passed as the
first parameter must be closed. The TDatabase passed as the second parameter must be connected.}
var
  TblDesc: CRTblDesc;
begin
  Result := DBIERR_NA;
  FillChar(TblDesc, SizeOf(CRTblDesc), 0);
  StrPCopy(TblDesc.szTblName, Tbl.TableName);
  TblDesc.bPack := True;
  Result := DbiDoRestructure(Db.Handle, 1, @TblDesc, nil, nil, nil, False);
end;

Commented:
These are some tips from net, not mine .

Author

Commented:
okey motaz please propose so that i can reward you the points... at any time.... okey
Commented:
Thanks aerokovs.

btw. You can accept comment as an answer, you will find it at the header of each comment, but now it will not appear because I'll post this as an answer

Motaz

Author

Commented:
thank you very much...