Question:
Two ways of Indexing a paradox table

Answer:
Having done a lot of work with Paradox in the past, there are things I’ve learned in Paradox that I’ve needed to duplicate in Delphi. One of those things is indexing a Paradox table at runtime.

With Delphi,I’ve written two ways to do this that I’ll share with you below. The first method uses a TTable and the AddIndex method to create any type of index. The second uses SQL to create a secondary index on a table. The SQL method tends to be more on the generic side of things and should work a greater number of tables because it’s using some basic SQL syntax. In any case, go ahead an play with these procedures. I think you’ll find them usefu, and they’ll work in either 32- or 16-bit Delphi versions.

TTable Method

Indexing a table using a TTable is a really simple thing. In fact, it only requires a few lines of code. The reason I’ve created a procedure for this operation is to reduce code redundancy. Let’s look at the code:

{=================================================================================== Add an Index to a TTable  Notes: 1) If using more than one field, separate field names with a semi-colon, because     AddIndex requires it in that format (eg., ‘Field1;Field2;Field3’)  2) TIndexOptions are being used from DB unit. TIndexOptions are defined as follows:    TIndexOptions = set of (ixPrimary, ixUnique, ixDescending,                             ixExpression, ixCaseInsensitive) ===================================================================================}procedure IndexTbl(dbName,                              {Database Name to connect to}                         tblName,                               {Table Name}                          idxName,                               {Name of Index}                    fldName : String;            {List of Fields to index on}                         idxOpts : TIndexOptions);    {Index Options set}var  tbl : TTable;begin  tbl := TTable.Create(Application);  with tbl do begin    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    AddIndex(idxName, fldName, idxOpts);    Free;  end;end;

As you can see, it’s fairly straightforward. Using the formal parameters, we merely set the values of the various TTable properties and AddIndex params. However, you have to have a couple of things in place to use this procedure:

• Make sure you declare the DBTables AND DB units in your uses statement.
• For the fldName, make sure you separate field names with a semi-colon. I required this param to be equivalent to what AddIndex requires for its field name string to avoid having to do any translation to the proper format.
• Use appropriate combinations of TIndexOptions (see the help file for a good description).

The SQL Method

If you look at the SQLIndexTbl parameters, you see they are almost the same as the IndexTbl parameters, except a TIndexOptions parameter is not required. In fact, for the four parameters it requires, SQLIndexTbl is syntactically the equivalent of IndexTbl. I did it this way just in case I wanted to make a quick switch from one indexing methodology to another. This can be accomplished without a lot of trouble. Here’s the code:

procedure SQLIndexTbl(dbName, tblName, idxName, fldName : String);var  sqlIdx : TQuery;  fldStr,  buf,  idxStr : String;begin  idxStr := ”;  fldStr := fldName;  {Parse the field string. If we find a semicolon, we know we’ve reached a delimiter.   After we get the field name, if there’s a space, tack on the standard SQL naming syntax   and precede the field name with table name (e.g. “MyTable”.”My Field Name”)}  while Pos(‘;’, fldStr) > 0 do begin    buf := Copy(fldStr, 0, Pos(‘;’, fldStr) – 1);    if (Pos(‘ ‘, buf) > 0) then       buf := ‘”‘ + tblName + ‘”.”‘ + buf + ‘”‘;    fldStr := Copy(fldStr, Pos(‘;’, fldStr) + 1, Length(fldStr) – Pos(‘;’, fldStr) + 1);    idxStr := idxStr + buf + ‘,’  end;  {Have to repeat this step at the end because there will always be a remainder in buf}  buf := fldStr;  if (Pos(‘ ‘, buf) > 0) then     buf := ‘”‘ + tblName + ‘”.”‘ + buf + ‘”‘;  idxStr := idxStr + buf;  {Now, create the TQuery and execute it.}  sqlIdx := TQuery.Create(Application);  with sqlIdx do  begin    Active := False;    DatabaseName := dbName;    Sql.Clear;    Sql.Add(‘CREATE INDEX ‘ + idxName + ‘ ON “:’ + dbName + ‘:’ + tblName + ‘” (‘ + idxStr + ‘)’);    SQL.SaveToFile(‘creatidx.sql’);    try      try        ExecSql;      finally        Free;      end;    except      Abort;    end;  end;end;

As you can see, the code for the SQLIndexTbl is quite a bit longer than the IndexTbl code. But most of that is due to having to specially parse the field string to convert it to the appropriate SQL syntax. In actuality, the parse step happens really fast, so you don’t gettoo much of a degradation in performance.

For quick no-brainer secondary index indexing, I use the SQLIndexTbl. Since it’s just strings that I have to input, it’s a bit easier. However, if I want to have finer control over the indexing, I almost invariably use the IndexTbl procedure.