Two Ways of Indexing a Paradox Table

Two Ways of Indexing a Paradox Table

Two ways of Indexing a paradox table

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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as