Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
Apr 17, 1997

Two Ways of Indexing a Paradox Table

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 get too 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.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date