BDE to FireDAC: pack table, regenerate index

Question

An old program of ours uses dBase tables and an .MDX index - other systems use these tables too, so we're stuck with them. We wish to replace BDE with FireDAC in our software. It seems that BDE methods DbiRegenIndex and DbiPackTable (regenerate index and pack table, respectively) are not provided by FireDAC - is there a way to perform these functions using FireDAC?

Answer 1

The code below shows how to index a dBase table using the MS dBase driver. I've used the Ado components, rather than FireDAC because it is easier to set up all their properties in code, so you can see what I'm doing. Note that as well as CREATE INDEX the driver also supports DROP INDEX. See e.g. https://learn.microsoft.com/en-us/sql/odbc/microsoft/create-index-for-paradox (which is for Paradox, but works for dBase as well)

To set yourself up for this project, you need to set up an ODBC system DSN called DBFTest using the MS dBase driver.

It should be straightforward to translate this Ado example into FireDAC.

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    btnCreateTable: TButton;
    ADOQuery1: TADOQuery;
    btnOpenTable: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    btnDropTable: TButton;
    btnAddIndex: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnAddIndexClick(Sender: TObject);
    procedure btnCreateTableClick(Sender: TObject);
    procedure btnDropTableClick(Sender: TObject);
    procedure btnOpenTableClick(Sender: TObject);
  public
    procedure CreatedBaseTable;
  end;

[...]

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoConnection1.ConnectionString :=  'Provider=MSDASQL.1;Persist Security Info=False;Data Source=DBFTest';
end;

procedure TForm1.btnAddIndexClick(Sender: TObject);
var
  Sql : String;
begin
  if AdoQuery1.Active then
    AdoQuery1.Close;

  Sql := 'create index byID on dBaseTest (ID)';
  AdoConnection1.Execute(Sql);

  AdoQuery1.Open;
end;

procedure TForm1.btnCreateTableClick(Sender: TObject);
begin
  CreatedBaseTable;
end;

procedure TForm1.btnDropTableClick(Sender: TObject);
var
  Sql : String;
begin
  Sql := 'drop table dBaseTest';
  AdoConnection1.Execute(Sql);
end;

procedure TForm1.btnOpenTableClick(Sender: TObject);
begin
  AdoQuery1.SQL.Text := 'select * from dBaseTest';
  AdoQuery1.Open;
end;

procedure TForm1.CreatedBaseTable;
var
  Sql : String;
  i : Integer;
begin
  Screen.Cursor := crSqlWait;
  Update;
  try
    Sql := 'create table dBaseTest(ID int, AName char(20))';
    AdoConnection1.Execute(Sql);
    for i := 1 to 100 do begin
       Sql := Format('insert into dBaseTest(ID, AName) values(%d, ''%s'')', [i, 'Name' + IntToStr(i)]);
      AdoConnection1.Execute(Sql);
    end;
  finally
    Screen.Cursor := crDefault
  end;
end;

Obviously, to "regenerate" the indexes this way, you would just drop them if they exist, handling any exceptions if they don't, and then create them again.

I don't know whether the dBase driver supports a "pack table" command, but you could probably do this yourself using an INSERT INTO ... SELECT * FROM ..." to copy the active rows into temporary table, then delete all rows from your working table, then copy them back from the temporary one.