Dynamic Where sample (console) (Delphi)
The Dynamic Where sample demonstrates 2 different ways to use Dynamic Where to selectively retrieve data from a table in an instance of Relativity Server.
Getting Started
The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\DynamicWhere (Console).
To build it, you will need a Delphi RAD Studio and like all the samples provided with Data Abstract for Delphi you will need to be running Relativity Server with the DASamples Domain and the Simple Schema available.
Included with the sample is a uLogSupport class which has a LogTable procedure that takes an instance of TDADataTable and creates a nicely formatted string representation of the table that it then prints to the Console which can be useful for debugging purposes. (An example of its output can be seen at the end of this document)
Running the Sample
When run the sample sets up a connection to an instance of Relativity Server and logs in (Step 1). It then creates a simple Dynamic Where condition that retrieves all rows in the "Users" table where the Role field contains the word "Manager" and prints the results to the console (Step 2). Finally a more complex condition is created that matches against rows where the Role field contains "Manager" and the Name field begins with "J", or the Id value is either 20, 22 or 24 (Step 3).
Examining the Code
All of the code can be found in the uDynamicWhere source file. The "Users" schema table used in this sample is comprised of 8 fields, which this sample will limit to just 4 using Dynamic Select. There are 25 rows in the table which will be filtered down to just 9 or 5 depending on the dynamic where condition.
Defining the Data Table
The sample uses a Data Table to store the data retrieved from the server, and also to inform the server about the table we are interested in.
In Step 1 the sample creates an instance of TDAMemDataTable and assigns the newly created and the configured TDARemoteDataAdapter to its RemoteDataAdapter property. The table will now use the data adapter for any operations that require interacting with the server.
Finally to retrieve particular table data from a schema, we need to assign the name of the table we want to the retrieve to the LogicalName property of the TDAMemDataTable object.
lTable := TDAMemDataTable.Create(nil);
lTable.RemoteDataAdapter := lRemoteDataAdapter;
lTable.LogicalName := 'Users';
Using a simple DynamicWhere condition (Step 2)
This section covers the building and executing a simple Dynamic Where expression which will retrieve all records from the "Users" table where the Role field contains the word "Manager".
The first thing the sample does in Step 2 is to configure the table to use Dynamic Select to limit the fields/columns that will be retrieved when the table data is requested. The ConfigureTableForDynamicSelect procedure can be found in uDynamicWhere and is a helper procedure that takes two arguments; the first an instance of TDADataTable and the second argument is an array that contains the string names of the fields we want to retrieve. The procedure basically empties the table of all existing fields, and then adds new fields for each element of the array of field names.
To build the simple Dynamic Where expression we need to create a Binary Expression and assign that expression to the data table.
The TDAMemDataTable provides a handy helper class, an instance of TDAWhereBuilder, that can be used to build WHERE statements. We use its NewBinaryExpression function to create a Binary Expression. The function takes four arguments; a table name, the field name, a binary operator and the value we are looking for. The result is assigned to the Expression property of the TDAWhereBuilder object.
Here as we want a basic expression "Role is like 'Manager'", the table object already has a name so we pass an empty string as the first argument. The second argument is assigned the name of the field we are interested in which is "Role". The third argument is an enumeration provided by TDABinaryOperator, here we want dboLike. The last argument is the value we are looking for.
To execute the Dynamic Where expression we call the Fill procedure of TDARemoteDataAdapter passing it the table object, a False to indicate that cursor should not be saved, and True that the schema should be included. Note there is an optional fourth boolean argument which has a default value of false and does not need to be supplied.
ConfigureTableForDynamicSelect(lTable,['Name','Type','Role']);
lTable.DynamicWhere.Expression := lTable.DynamicWhere.NewBinaryExpression('','Role',dboLike,'%manager%');
lRemoteDataAdapter.Fill([lTable],False,True);
Using a complex DynamicWhere condition (Step 3)
Building off the previous step, this section covers creating a more complex Dynamic Where expression that will retrieve all records from the "Users" table where the Role field contains the word "Manager" and the Name field begins with a "J", or any records where the Id value is equal to 20, 22 or 24. To put it another way:
Condition: (Role CONTAINS "manager" AND Name BEGINSWITH "J") OR Id IN {20, 22, 24}
Like in Step 2, the first thing the sample does is configure the table to use Dynamic Select to limit the fields/columns that will be retrieved when the table data is requested. This time the array of field names passed to the ConfigureTableForDynamicSelect procedure is slightly expanded to include the Id field so you can see that the Dynamic Where expression did match the rows with specific Id values.
Building the actual Dynamic Where expression is a bit more complex. We actually need to create 5 Binary Expressions to cover the whole Dynamic Where expression. As before we use the TDAWhereBuilder helper class to create those binary expressions.
The first Binary Expression is a container for the other binary expressions. The first argument is the binary expression that retrieves those fields where the Role field contains "Manager" and the Name field begins with "J"; lets call this expression A. The second argument is the binary expression that searches for particular values in the id field, we'll call this expression B. The last argument supplied is an dboOR operator from the TDABinaryOperator enumerator which is used to indicate that the expression should evaluate to true if either the first or second argument returns a match.
Expression A is comprised of two more binary expressions and a dboAnd operator to indicate that both of the expressions must evaluate to true for expression A to be considered true. The first binary expression is exactly the same as the one created in Step 2. The second changes the second argument to use the Name field, and the last argument to "J%".
Looking closer at expression B uses an alternative form of the NewBinaryExpression procedure that takes two instances of TDAWhereExpression (which is the base class for any of the where expression classes) and a TDABinaryOperator operator. For the first argument of the expression we create an instance of TDAFieldExpression using the NewField function which takes the field name we are interested in as the second argument, as we don't need to set the table name the first argument is an empty string. The second argument of the expression is an instance of TDAListExpression which takes an array of TDAConstantExpression objects; one for each id value we want to match against. Finally as we want to test if the id value is in the list, we supply the dboIn TDABinaryOperator operator.
Finally, as before, to execute the Dynamic Where expression we call the Fill procedure of TDARemoteDataAdapter passing it the table object, and the required boolean values.
ConfigureTableForDynamicSelect(lTable,['Id','Name','Type','Role']);
ld :=lTable.DynamicWhere;
ld.Expression :=
ld.NewBinaryExpression(
ld.NewBinaryExpression( /// Expression A
ld.NewBinaryExpression('','Role',dboLike,'%manager%'),
ld.NewBinaryExpression('','Name',dboLike,'J%'),
dboAnd
),
ld.NewBinaryExpression( /// Expression B
ld.NewField('','id'),
ld.NewList(
[ld.NewConstant(20),
ld.NewConstant(22),
ld.NewConstant(24)]),
dboIn),
dboOR);
lRemoteDataAdapter.Fill([lTable],False,True);
Example Output
This is an example of the output you will see when the sample is run. You can see in Step 2 that all of the returned rows have a value in the Role field that contains the word "Manager" and then all the rows that match the more complex condition used in Step 3.
Dynamic Where sample has been started.
Target URL is http://localhost:7099/bin
RO SDK layer is configured.
RO DataAbstract layer is configured.
STEP 1: Login to DataService
Connecting to Relativity server: http://localhost:7099/bin
Login string is :
User Id="simple";Password="simple";Domain="DASamples";Schema="Simple"
Login has been successfull. Going further...
STEP 2. Using Simple Condition ...
Select Managers
Condition: Role CONTAINS "manager"
Table: Users (9 rows from 9)
------------------------------------------------------------
| Name | Type | Role |
------------------------------------------------------------
| Gale Dalton | 1 | Income Manager |
| Matthew Decker | 1 | Income Manager |
| Joseph Henson23 | 1 | Income Manager |
| Jennifer Kent | 1 | Sales Manager |
| Sandy Manning | 1 | Sales Manager |
| Glenn Cunningha | 1 | Sales Manager |
| Marcy Collins | 1 | Sales Manager |
| Kirsten Rosario | 1 | Sales Manager |
| Merle Frank | 1 | Sales Manager |
------------------------------------------------------------
STEP 3. Using Complex Condition ...
Select Managers with name started with 'J' OR users with Id 20, 22, 24
Condition (Role CONTAINS "manager" AND Name BEGINSWITH "J") OR Id IN {20, 22, 24}
Table: Users (5 rows from 5)
--------------------------------------------------------------------------
| Id | Name | Type | Role |
--------------------------------------------------------------------------
| 4 | Joseph Henson23 | 1 | Income Manager |
| 5 | Jennifer Kent | 1 | Sales Manager |
| 20 | Anna H. Kugler | 3 | Guest |
| 22 | Kenny S. Lay | 3 | Guest |
| 24 | Lillie R. Schro | 3 | Guest |
--------------------------------------------------------------------------
Done!
Cleanup!
Press Return to exit...