Dynamic Order

since .1561

Dynamic Order allows to dynamically change the ORDER BY clause of SELECT statement that goes to the database server.

For example, it is possible to specify sorting like

ORDER BY 
  field1 ASC,
  field2 DESC

Previously, this could only be achieved by hard-coding the parameters in the SQL statement. This was a problem in the case when each call required a different set of data. The advantage of Dynamic Order lies in its flexibility without sacrificing security. It allows to dynamically compose any ORDER BY clause when needed.

Security

Since the table's SQL query is being dynamically changed based on client application request, security can be a concern. Data arriving at the server needs to be well structured in order to allow the server to check/validate the Dynamic Order clause and prevent any attempt of SQL injection or other unauthorized SQL access techniques.

Dynamic Order fields are sent to the server as a semicolon-separated list. As a result the Data Abstract server can validate such requests. For example it is not possible to refer to a field that are not exposed in the server Schema.

Note, that Dynamic Order is activated by default.

If you want to disable it, set the Service's AllowDynamicOrder property to False (True by default).

How it works

Server-side

If you are using AutoSQL mode, no changes on server-side are required

for Manual SQL mode, update your SQL and add ORDERBY macro like

SELECT ...
FROM ...
WHERE {WHERE}
{ORDERBY}

Client-side

The TableRequestInfo structure is used for customizing the GetData process. We decided to extend this structure and provide a new one called TableRequestInfoV7. Most platforms allow to set Dynamic Order via correspondent property of table.

Note: for specifing DESC mode we use ! prefix like !Field.

The following are some examples of using the Dynamic Order feature.

TableRequestInfoV7 ri = new TableRequestInfoV7();
ri.SetDynamicSelect( new string[]
                  {"EmployeeID", "FirstName", "Title", "Birthdate", "HireDate"});
//ri.SetWhereClause(lCondition.ToXmlNode());                  
ri.SetOrderBy(new string[] { "Title", "!FirstName"});
remoteDataAdapter.Fill(newDataset, "Employees", ri);


TableRequestInfoV7 requestInfo = new TableRequestInfoV7();

String[] arrayOfColumnNames =
      new String[] { "EmployeeID", "FirstName", "Title", "Birthdate", "HireDate" };
requestInfo.setDynamicSelect(new StringArray(arrayOfColumnNames));
remoteDataAdapter.setDynamicSelect(true);

DataTable table = new DataTable("Employees");
table.addDynamicOrder("Title");
table.addDynamicOrder("FirstName", false);
remoteDataAdapter.fill(table, requestInfo);

var dynamicSelect = ["EmployeeID", "FirstName", "Title", "Birthdate", "HireDate"];
  var includeSchema = true;
  var maxRecords = -1;      // Load all records
  var userFilter = "";      // No filter
  var parameters = [];      // No additional parameters
  // it's ok for using TableRequestInfoV5. DA/JS will convert it to V7 if needed
  var request = RemObjects.DataAbstract.Util.createRequestInfoV5(includeSchema, maxRecords, userFilter, parameters);
  request.DynamicSelectFieldNames.items = dynamicSelect;
  var table = new RemObjects.DataAbstract.DataTable("Employees");
  table.dynamicOrder = ["Title","!FirstName"];

  // Obtaining the data 
  adapter.getData(table, request, function () {
    drawTable(table, "t");


// simple way, similar to 'Designtime', with manual filling table fields
procedure ConfigureTableForDynamicSelect(aTable: TDADataTable; 
                                         aDynamicSelect: array of string);
var
  i: integer;
  lField: TDAField;
begin
  aTable.Close;
  aTable.Fields.Clear;
  for I := Low(aDynamicSelect) to High(aDynamicSelect) do begin
    lField := aTable.Fields.Add;
    lField.Name := aDynamicSelect[i];
  end;
end;

ConfigureTableForDynamicSelect(tbl_Employees, ['EmployeeID', 'FirstName',
                               'Title', 'BirthDate', 'HireDate']);

tbl_Employees.RemoteDataAdapter := RemoteDataAdapter;
tbl_Employees.DynamicOrder.Add('Title');
tbl_Employees.DynamicOrder.Add('FirstName', True);
RemoteDataAdapter.DynamicSelect:=True;
RemoteDataAdapter.Fill([tbl_Employees],False,True);