Sorting a table physically in Delphi

Question

Delphi does not seem to like multi-field indexes.

How do I physically sort a a table so that I wind up with a table that has the rows in the desired order?

Example:

mytable.dbf

Field   Field-Name   Field-Type   Size
  0     Payer        Character     35
  1     Payee        Character     35
  2     PayDate      Date
  3     Amount       Currency

I need to produce a table sorted alphabetically by "Payee"+"Payer"

When I tried using an index of "Payee+Payer", I got an error:

"Field Index out of range"

Answer 1

Cannot check, but try IndexFieldNames = "Payee, Payer".
Sure indexes by these 2 fields should exist.

Answer 2

The index field names need to be separated by semicolons, not plus symbols. Try that and it should work.

Answer 3

Ok, let's try to put some order.

First, isn't advisable to physically sort a table. In fact the most RDBMS even don't provide you this feature. Usually, one, in order to not force a full table scan (it is called sometimes natural scan) creates indexes on the table fields on which he thinks that the table will be sorted / searched.

As you see, the first step in order to sort a table is usually index creation. This is a separate step, it is done once, usually at, let's say, "design time". After this, the DB engine will take care to automatically update the indexes.

The index creation is done by you (the developer) using (usually) not Delphi (or any other development tool) but the admin tool of your RDBMS (the same tool which you used when you created your table).

If your 'DB engine' is, in fact, a Delphi memory dataset (TClientDataSet) then you will go to IndexDefs property, open it, add a new index and set the properties there accordingly. The interesting property in our discussion is Fields. Set it to Payee;Payer. Set also the Name to eg. "idxPayee". If you use other TDataSet descendant, consult the docs of your DB engine or ask another question here on SO.com providing the details.

Now, to use the index. (IOW, to sort the table, as you say). In your program (either at design time either at run time) set in your 'Table' the IndexName to "idxPayee" or any other valid name you gave or set IndexFieldNames to Payee;Payer.

Note once again that the above is an example based on TClientDataSet. What you must retain from the above (if you don't use it) is that you must have an already created index in order to use it.

Also, to answer at your question, yes, there are some 'table' types (TDataSet descendants in Delphi terminology) which support sorting, either via a Sort method (or the like) either via a SortFields property.

But nowadays usually when one works with a SQL backend, the preferred solution is to create the indexes using the corresponding admin tool and then issue (using Delphi) an SELECT * FROM myTable ORDER BY Field1.

HTH

Answer 4

If you're still using BDE you can use the BDE API to physically sort the DBF table:

uses
  DbiProcs, DbiTypes, DBIErrs;

procedure SortTable(Table: TTable; const FieldNums: array of Word; CaseInsensitive: Boolean = False; Descending: Boolean = False);
var
  DBHandle: hDBIDb;
  RecordCount: Integer;
  Order: SORTOrder;
begin
  if Length(FieldNums) = 0 then
    Exit;

  Table.Open;
  RecordCount := Table.RecordCount;
  if RecordCount = 0 then
    Exit;
  DBHandle := Table.DBHandle;
  Table.Close;

  if Descending then
    Order := sortDESCEND
  else
    Order := sortASCEND;

  Check(DbiSortTable(DBHandle, PAnsiChar(Table.TableName), nil, nil, nil, nil, nil,
    Length(FieldNums), @FieldNums[0], @CaseInsensitive, @Order, nil, False, nil, RecordCount));
end;

for example, in your case:

  SortTable(Table1, [2, 1]); // sort by Payee, Payer

Answer 5

You can create an index on your table using the TTable.AddIndex method in one call. That will sort your data when you read it, that is if you use the new index by setting the TTable.IndexName property to the new index. Here's an example:

xTable.AddIndex('NewIndex','Field1;Field2',[ixCaseInsensitive]);
xTable.IndexName := 'NewIndex';
// Read the table from top to bottom
xTable.First;
while not xTable.EOF do begin
  ..
  xTable.Next;
end;