How do I sort tables?

Ok next issue… (sorry for all the questions)

Sorting. Is there any info or examples on how to do this?

Firstly, if I’m using a TableRequestInfoV5 structure passed to the Fill method, then I see I can specify a ColumnSorting parameter, the problem is this only appears to support a single field, so I can’t sort on more than one field.

Secondly, if I wanted to perform sorting on more than one field then I presume my only option would be to create another data table in my Schema which had this sorting imposed, but I can’t see anywhere in the Schema to specify the sort order of a data table.

Any info appreciated.

You are right - current solution allows to have only one sorting field but some workarounds are present:

if you want to have hardcoded “order by” clause, you can switch from stAutoSQL to stSQL mode in DASM so your SQL will look as

   field1, .... FieldN
where {WHERE}
order by field1 asc, field2 desc

by other hand, you can create custom server method which will call TDASchema.NewDataset method and pass into it TDAOrderByCollection

also I’ve logged this issue as bugs://57808 for investigation


With regards to the NewDataset method, I’ve not used this before. It seems to support pretty much everything you could possibly want to do in a single call, such as dynamic select fields, parameters, a where clause and an order by field list.

If, therefore, I’m doing back-end database work within the server itself, rather than from the client, should I be using this NewData set call rather than creating a data table and using the Fill method? Not sure what the pros/cons of each solution are and which is preferable?

Please use the following workaround:

  1. Set StatementType to stAutoSQL or stSQL. If you use stSQL than set SQL to “select … From … Where {WHERE} {ORDERBY}”
  2. Create a param ‘OrderBy’ (DataType=datString) in schema table
  3. This param also must be in client table
  4. Add TDataService.BeforeGetDatasetData event
procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject;
  const aDataset: IDADataset; const aIncludeSchema: Boolean;
  const aMaxRecords: Integer);
  ParamName, AscModeName:String;
  I, J: Integer;
  if (aDataset.Name <>'CUSTOMER') then exit; //allow only CUSTOMER table

    ParamList.Delimiter := ',';

    P := aDataset.Params.FindParam('OrderBy');
    if Assigned(P) then
      ParamList.DelimitedText := P.Value;
    for I := 0 to ParamList.Count - 1 do
      J := Pos(':', ParamList[I]);
      if (J>0) then ParamName := Copy(ParamList[I], 1, J-1) else ParamName := ParamList[I];
      if (J>0) then AscModeName := Copy(ParamList[I], J+1, 1) else AscModeName := '';

      //allow only known names
      //also it's protection against exploits
      if not Assigned(aDataset.FindField(ParamName)) then raise exception.Create('Unknown field :'+ParamName);

      with aDataset.OrderBy.Add as TDAOrderByItem do
        TableName := '';
        FieldName := ParamName;
        AscMode := StrToBoolDef(AscModeName, True);
  1. On the client side you can use
  ClientDataModule.tbl_CUSTOMER.ParamByName('OrderBy').AsString := 'CONTACT_FIRST:1, CUSTOMER:0, CONTACT_LAST';

Thanks, that’s quite a nifty solution.
Is there any way to use that method when using the data adapter’s Fill method to populate the table? I’m doing it this way so I can use dynamic select, rather than just opening the table, but I can’t find a way of passing the OrderBy parameter when using Fill.

I’m afraid this doesn’t work.

I’ve stepped through the above code and can see it’s receiving the parameter and using it to add order by items correctly but the resulting SQL sent to the database server doesn’t contain an order by clause at all.

This is using stAutoSQL by the way. If I try using stSQL and adding a {ORDERBY} macro to the end of the generated statement, as suggested, I just get errors.

Seems TDAQueryBuilder.GenerateSelectSQL is called before the TDataService.BeforeGetDatasetData event is fired, so any changes to the order by items of the dataset by the event handler are too late and ignored.

Please look at the attached example

Dear vovanl,
I have the same issue working on Cocoa DA, your Delphi code not work for me,
with aDataset.OrderBy.Add as TDAOrderByItem do
aDataset don’t have OrderBy method… I am using the version as ,
it’s any other solutions, that I can use the “dynamicWhere” with “order by” on client side in Cocoa?


try to cast aDataset to IDAServerDataset like
(dataset as IDAServerDataset).OrderBy.Add

Hi , thanks for the quick reply, it works on the Delphi server side now ,
but on the cocoa side, how I should add the parameter on the client side ?
request = pa.adapterSvCap.beginGetDataTable(tableName, select: nil, where: aWhere, start: false)


Hmm, I’m not sure that’s exposed in the API in the client side (but I’ll check later today or tomorrow when I’m back at the computer); what I usually just do, and I used da/cocoa a lot, is use the regular cocoa APIs to sort (and client-side filter, where needed) the rows locally. (In most apps, I find, the local sorting is often independent of what set of data you get from the server. Ie, you might wanna let your user change the sorting by clicking a column header, for example, but you’d not wanna refetch the data from the server, for that…

I am working with a big qty of orders, with this way, I want to search with dynamic where also, with this way to order before it send back to the client side before . thank you …

Yeah filtering I definitely see the point to do server side, to save traffic. You can use Dynamic Where, DA SQL and even NSPredicate for that (NSPredicate-driven DA SQL with the Cocoa predicate editor is powerful). Its just the sorting I find I better done client side (and you shouldn’t be fetching more data than could trivially be sorted client side, either ;).

-getDataTableWithSQL: & Co should allow you to pass DA SQL that can contain both select, where, and order by, allowing you to do what you need, though.

if with -getDataTableWithSQL: on cocoa. How I can convert the Dynamic Where to sql string ? it seems only have the stringValue… that was in XML…

getDataTableWithSQL uses DA SQL. essentially there are two days of queuing data from the client:

  • Dynamic Where/Dynamic Select are XML structures describing what to filter, and what fields you want.
  • DA SQL uses client-side (secure) SQL syntax to express the same, and supports limiting the selected fields, WHERE clauses, and ORDER BY. (and more). Essentially its a more sophisticated way to to the same thing.

I don’t believe we have any API to convert Dynamic Where XML to a DA SQL statement, client side, as they are meant to used exclusionary. How do you generate/get/construct your Dynamic Where now?

Hi marc,

Finally I found the solution,

I generate the Dynamic Where with NSPredicate, then send the where cause to Delphi server ,

And work with the server side before the

On my project,

I want to load part of the table, like “ select top 1000 * from sampleList where Number like ‘2019%’ order by ID”

On this sql string, I want to order the date before the condiction of the dynamic where ,

Anyway, I use the custom method it works now .


Ah, when api;'re using TOP, sorting server side does Mae sense, yeah.