Sorting a table physically in Delphi

Question

Delphi does not seem to be like multi-field indexes.

How to physically sort a table so that I finish a table that has rows in the correct 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 create a table sorted alphabetically "Payee" + "Payer"

When I tried to use the "Payee + Payer" index, I received an error message:

Out-of-range field index

Answer 1

If you are 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 2

Index field names must be separated by semicolons, not plus characters. Try it and it should work.

Answer 3

Ok, try to put some sort of order.

First, it is not recommended to physically sort the table. In fact, most RDBMS do not even provide you with this feature. As a rule, one, in order not to force a full scan of the table (sometimes called natural scanning), creates indexes in the fields of the table on which it considers that the table will be sorted / searched.

As you can see, the first step in sorting a table is usually to create an index. This is a separate step, it is done once, usually at, let them say, "development time". After that, the database engine will take care of automatically updating indexes.

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

If your "database engine" is actually a Delphi memory dataset (TClientDataSet), then you will go to the IndexDefs property, open it, add a new index and set the appropriate properties. An interesting property in our discussion of Fields . Install it on Payee;Payer . Set also Name , for example. "IdxPayee". If you are using another TDataSet descendant, consult the docs of your database engine or ask another question here on SO.com for details.

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

Once again, note that the above example is based on the TClientDataSet. What you must save from the above (if you are not using it) is that an index must already be created to use it.

In addition, to answer your question, yes, there are some types of “tables” (descendants of TDataSet in Delphi terminology) that support sorting either using the Sort method (or the like) or through the SortFields property,

But at present, usually when you are working with a SQL server, the preferred solution is to create indexes using the appropriate administration tool, and then release (using Delphi) SELECT * FROM myTable ORDER BY Field1 .

NTN

Answer 4

Cannot verify, but try IndexFieldNames = "Payee, Payer".
Mandatory indexes on these two fields must exist.

Answer 5

You can create an index in your table using the TTable.AddIndex method in one call. This will sort your data when you read it, that is, if you use a new index by setting the TTable.IndexName property to a new index. Here is 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;