Reverse the order on an index for a ClientDataSet

Question

I'm wanting to reverse the order of an index in a TClientDataSet, the following code looks like it should do the trick but does nothing. Is there a nice way to reverse the order of an index?

procedure TForm8.Button1Click(Sender: TObject);
var
  index: TIndexDef;
begin
  index := ClientDataSet1.IndexDefs.Find('LengthIndex');
  if ixDescending in index.Options then
    index.Options := index.Options - [ixDescending]
  else
    index.Options := index.Options + [ixDescending];
end;

Ol

Why not delete the index and create it again? There should be no faster way to do it, because (CMIIW) if there would be a way to very quickly reverse an index, there would be no reason to have the distinction between ascending and descending indices - the database engine could order on the fly then (based on the query). – Shadwell

Maybe you will have a look at edn.embarcadero.com/article/29056 Not only a long explanation on CDS Index but also some code that fits your needs - i think so :o) – Tesch

Answer 1

TIndexDef.Options are used when creating the indexes. They can't be used to try and affect an existing index. See the documentation (emphasis mine):

When creating a new index, use Options to specify the attributes of the index. Options can contain zero or more of the TIndexOption constants ixPrimary, ixUnique, ixDescending, ixCaseInsensitive, and ixExpression.

When inspecting the definitions of existing indexes, read Options to determine the option(s) used to create the index.

You'll need to create a separate index with the ixDescending value set. You can then switch back and forth by just changing the IndexName property.

Adrastus

+1, the proper way is create two indexes and switch between them using the IndexName property. – Approximate

What does (emphasis mine) mean? – Sleepwalk

@CapeCodGunny: It means I'm the one who added the bold to it to emphasis those points. – Adrastus

Okay, got it. So, you lifted parts of the documentation and bolded those points that drive home the concept. That's pretty good. Thanks. – Sleepwalk

Great algo suggestion You'll need to create a separate index with the ixDescending value set. You can then switch back and forth by just changing the IndexName property. Simple, elegant, and very useful to me! – Courage

Answer 2

This is the method I ended up settling on for sorting in both directions. Basically it just creates and frees indexes, not very pretty but works. This is much easier to do with a TFDMemTable (if you have access to FireDAC)

type
  TSortByFieldOption = (ForceAscending, ForceDescending);
  TSortByFieldOptions = set of TSortByFieldOption;

procedure SortClientDataSetByField(cds : TClientDataSet; FieldName : String; Options : TSortByFieldOptions = []);
const
  IndexName = 'GridSort';
var
  i: integer;
  index: TIndexDef;
  OldOrder: string;
  IndexOptions : TIndexOptions;
begin
  cds.DisableControls;
  try
    i := cds.IndexDefs.IndexOf(IndexName);
    if i <> - 1  then
    begin
      index := cds.IndexDefs.Find(IndexName);
      OldOrder := index.Fields;
      try
        cds.DeleteIndex(IndexName);
      except;
        OutputDebugString('no index?');
        //there seem to be conditions where the index does not exist but
      end;
      index.Free; //delete index for some reason does not free the index
      indexOptions := index.Options;
    end else
      IndexOptions := [ixDescending];

    index := cds.IndexDefs.AddIndexDef;
    index.Name := IndexName;
    index.Fields := FieldName;
    if ForceAscending in Options then
      index.Options := []
    else if ForceDescending in Options then
      index.Options := [ixDescending]
    else if OldOrder = FieldName  then
    begin
      if (IndexOptions = [ixDescending]) then
        index.Options := []
      else
        index.Options := [ixDescending];
    end;
    cds.IndexName := IndexName;
  finally
    cds.EnableControls;
  end;
end;

Ol