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.
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?
Set StatementType to stAutoSQL or stSQL. If you use stSQL than set SQL to “select … From … Where {WHERE} {ORDERBY}”
Create a param ‘OrderBy’ (DataType=datString) in schema table
This param also must be in client table
Add TDataService.BeforeGetDatasetData event
procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject;
const aDataset: IDADataset; const aIncludeSchema: Boolean;
const aMaxRecords: Integer);
var
P:TDAParam;
ParamList:TStringList;
ParamName, AscModeName:String;
I, J: Integer;
begin
if (aDataset.Name <>'CUSTOMER') then exit; //allow only CUSTOMER table
ParamList:=TStringList.Create();
try
ParamList.Delimiter := ',';
P := aDataset.Params.FindParam('OrderBy');
if Assigned(P) then
begin
ParamList.DelimitedText := P.Value;
aDataset.Params.Delete(P.Inde;
end;
for I := 0 to ParamList.Count - 1 do
begin
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
begin
TableName := '';
FieldName := ParamName;
AscMode := StrToBoolDef(AscModeName, True);
end;
end;
finally
FreeAndNil(ParamList);
end;
end;
On the client side you can use
begin
ClientDataModule.tbl_CUSTOMER.Close;
ClientDataModule.tbl_CUSTOMER.ParamByName('OrderBy').AsString := 'CONTACT_FIRST:1, CUSTOMER:0, CONTACT_LAST';
ClientDataModule.tbl_CUSTOMER.Open;
end;
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’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.
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 10.0.0.1449 ,
it’s any other solutions, that I can use the “dynamicWhere” with “order by” on client side in Cocoa?
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.
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?