Dynamic Select

Dynamic Select allows you to request data only from the columns you need.

In earlier versions of Data Abstract, if the client application requested data from a table, it received all the fields regardless of whether they were all needed. Thus, in order to provide such selective requests, the user needed to have several instances of the same table (but with different sets of fields) in the DA schema.

Now, since version 5, the client can dynamically specify the set of the fields needed and, as a result, only a subset of field values will be returned to the client.

The benefit of this feature is obvious - it allows you to reduce your network traffic between the database server and your middle tier and between your middle tier and client. It also reduces the complexity of your DA schema.

How it works

The TableRequestInfo structure is used for customizing the GetData process. For version 5 we decided to extend this structure and provide a new one called TableRequestInfoV5. It has a new DynamicSelectFieldNames field, that can hold the requested field names as an array of string values.

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

 

TableRequestInfoV5 ri = new TableRequestInfoV5();
ri.DynamicSelectFieldNames = new string[]
                  {"employeeID", "firstName", "title", "birthDate", "hireDate"};
remoteDataAdapter.Fill(newDataset, "Employees", ri);


var requestInfo := new TableRequestInfoV5();
requestInfo.DynamicSelectFieldNames := [
  "EmployeeID",
  "FirstName",
  "Title",
  "BirthDate",
  "HireDate"];

// execute request
remoteDataAdapter.fill("Employees", requestInfo);

 


let ri = RemObjects.DataAbstract.Server.TableRequestInfoV5()
ri.DynamicSelectFieldNames = ["employeeID", "firstName", "title", "birthDate", "hireDate"]
dataAdapter.Fill(newDataset, "Employees",ri)

 


Dim lRequestInfo As New RemObjects.DataAbstract.Server.TableRequestInfoV5()
lRequestInfo.DynamicSelectFieldNames =
    New String() {"employeeID", "firstName", "title", "birthDate", "hireDate"}
Me.DataAdapter.Fill(lClientsData, lRequestInfo, True)

 


// this code snippet implies using ARC
[rda beginGetDataTable:@"Workers"
                select:@[@"EmployeeID", @"FirstName", @"Title", @"BirthDate", @"HireDate"]
                 where:nil
             withBlock:^(DADataTable *table) {
                self.workersTable = table;
                [tableView reloadData];
             }];

 


rda.beginGetDataTable("Workers",
    select: ["EmployeeID", "FirstName", "Title", "Birthdate", "HireDate"],
    `where`: nil,
    withBlock: { table in
      self.workersTable = table
      tableView.reloadData()
})

 


rda.beginGetDataTable("Workers")
  &select(NSArray.arrayWithObjects("EmployeeID","FirstName", "Title", "Birthdate". "HireDate"))
  &where(NULL) withBlock((table) -> begin
    self.workersTable := table;
    tableView.reloadData();
  end);

 


rda.beginGetDataTable("Workers") @select(
  NSArray.arrayWithObjects("EmployeeID", "FirstName", "Title", "Birthdate", "HireDate"))
                                 @where(null) withBlock((table) =>
                                 {
                                   this.daTable = table;
                                   tableView.reloadData();
                                 });

 


TableRequestInfoV5 requestInfo = new TableRequestInfoV5();

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

DataTable table = new DataTable("Workers");
remoteDataAdapter.fill(table, requestInfo);

 


var requestInfo := new TableRequestInfoV5();

var arrayOfColumnNames := ["EmployeeID", "FirstName","Title","BirthDate","HireDate"];
requestInfo.DynamicSelectFieldNames := new StringArray(arrayOfColumnNames);
remoteDataAdapter.setDynamicSelect(true);

var table := new DataTable("Workers");
remoteDataAdapter.fill(table, requestInfo);

 


TableRequestInfoV5 requestInfo = new TableRequestInfoV5();

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

DataTable table = new DataTable("Workers");
remoteDataAdapter.fill(table, requestInfo);


var requestInfo = TableRequestInfoV5();

var arrayOfColumnNames = ["EmployeeID", "FirstName","Title","BirthDate","HireDate"];
requestInfo.DynamicSelectFieldNames = StringArray(arrayOfColumnNames);
remoteDataAdapter.setDynamicSelect(true);

var table = DataTable("Workers");
remoteDataAdapter.fill(table, requestInfo);

 


// 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;
RemoteDataAdapter.DynamicSelect:=True;
RemoteDataAdapter.Fill([tbl_Employees],False,True);

When the server processes such a request, two outcomes are possible, depending on the table's statement type.

  • If the table is based on an AutoSQL statement, information regarding the requested fields will be used during the SQL generation and the DA server will only request the required fields from the database. This will decrease the network traffic between the database server, DA server and client.
  • If the table is based on a standard SQL statement, all of the data will be requested from the database (according to the table's SQL statement) but the DA server outputs to result stream to the client only those fields actually requested by the client. This will only decrease network traffic between the DA server and client.

Security

Note, that in order to use Dynamic Select, you need to activate it:

  • On the client - set the Remote Data Adapter's new boolean DynamicSelect property to True. By default it is set to False (i.e. Dynamic Requests are not allowed). Once set to True, the list of the fields to select will be created every time you request data, even if you didn't specify any fields in TableRequestInfoV5.
  • On the server - set the Service's AllowDynamicSelect property to True (False by default).

See Also