Commands sample (console) (Delphi)

The Commands sample demonstrates executing remote schema commands from a client application that will insert, update and delete a record from a table in an instance of Relativity Server. The sample also demonstrates using DA SQL to selectively retrieve data from the schema table.

Getting Started

The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\Commands (Console).

To build it, you will need 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 collection of DataParameters that are passed to the server along with the command name to execute to insert a new row into the table, after printing the result of the command a DA SQL statement is used to retrieve the new row from the Clients table (Step 2). The values in a number of the DataParameters are updated and passed to the server to update the values there using the update command (Step 3). Finally the Id value from the new row is used to delete the new row from the table using the delete command (Step 4).

Examining the Code

All of the code can be found in the uCommands source file. The "Clients" schema table used in this sample is comprised of 10 fields, which this sample will limit to just 4 using DA SQL when it retrieves the new row from the server.

Looking at the Commands

Before stepping through the code, we shall take a detour and use Schema Modeler to examine that commands that are used in the following sections. If you open Relativity Admin Tool, navigate to the Simple schema and press the "Open in Schema Modeler" button. This opens Schema Modeler with the selected schema. There you can see a folder titled "Commands" which contains all of the commands that are available in this schema.

Each command's name describes the table it affects and what the action is to ensure that is no confusion. Clicking the disclosure triangle beside the command name allows you to drill down into the details of the command; including the parameters that the command takes and the statement object that defines the mapping between the schema table and the database table.

In the figure below you can see that the Clients_DELETE command takes a single parameter OLD_ClientId and uses a custom SQL statement that uses that parameter to try and match against the table's Id field and then delete that table from the Agents table (The Clients schema table is mapped to the Agents database table)

Next we shall look at using the commands.

Using the INSERT command

Before we can execute the Clients_INSERT command, we need to create an array of DataParameter objects that match the parameters defined for the command in the schema. Each DataParameter is comprised of a Name that must match one of the parameter names, and a Value object that we want to set the data to. If you fail to provide one of the parameters defined in the schema then a null value will instead be inserted for that parameter in the database table.

The array of parameters is passed to the Execute function of TDARemoteCommand, along with the name of the command we want to execute as a string; here "Clients_INSERT". The Execute function will return an integer value which indicates if the command was successfully executed (It returns a 1) or if it failed (It returns a 0).

Having executed the command, the sample then uses DA SQL to retrieve the newly added row, passing in a pre-created SQL select statement along with some of the values assigned to the newly created row to the FillWithDASql function of TDARemoteDataAdapter. The ClientId of the newly created row is extracted from the table and stored for later use.

Lastly the newly retrieved table is printed to the console so you can see that the command was successfully executed.

daSQL      = 'SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM Clients '+
               'WHERE ClientName = ''%s'' AND ClientEmail = ''%s'' AND ClientPassword = ''%s''';
... snipped code ...

Log('STEP 2. Call command for adding a new client row ...');
ldp:= DataParameterArray.Create;
{0}lp := ldp.Add; lp.Name := UTF8Encode('ClientName');lp.Value := 'Jane C.';
{1}lp := ldp.Add; lp.Name := UTF8Encode('ClientDiscount');lp.Value := 0.01;
{2}lp := ldp.Add; lp.Name := UTF8Encode('ClientEmail');lp.Value := 'test@gmail.com';
{3}lp := ldp.Add; lp.Name := UTF8Encode('ClientPassword');lp.Value := 'temp';
{4}lp := ldp.Add; lp.Name := UTF8Encode('ClientBirthdate');lp.Value := EncodeDate(1973,6,1);
{5}lp := ldp.Add; lp.Name := UTF8Encode('ClientPhone');lp.Value := 'Unknown yet';
{6}lp := ldp.Add; lp.Name := UTF8Encode('ClientPostalCode');lp.Value := '???';
{7}lp := ldp.Add; lp.Name := UTF8Encode('ClientAddress');lp.Value := 'Somewhere in Texas...';
{8}lp := ldp.Add; lp.Name := UTF8Encode('ClientNotes');lp.Value := 'Added by command';

lResultExCommand := lCommand.Execute('Clients_INSERT',ldp);
Log('Done with result: '+IntToStr(lResultExCommand));

lRemoteDataAdapter.FillWithDASql(lTable,Format(daSQL,[
      ldp[0].Value, //ClientName
      ldp[2].Value, //
      ldp[3].Value]),
      nil);
lClientID :=lTable.FieldByName('ClientId').asInteger;
LogTable(ltable);

Using the UPDATE command

In this step a number of the fields in the previously created DataParameterArray are updated with new values, two of the parameters are deleted, and an additional one is added which is the ClientID we retrieved at the end of the previous step.

As before the array of parameters is passed to the Execute function of the TDARemoteCommand, this time with the command name Clients_UPDATE. A result of 1 is returned if the command was successful and a 0 if it failed.

To show the record was correctly updated, we again use FillWithDASql of TDARemoteDataAdapter to retrieve a table with only the specific record. The SQL SELECT statement is slightly different this time around, using the lClientId we retrieved at the end of Step 2.

daSQL1     = 'SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = %d';

... snipped code ...

Log('STEP 3. Call command for modification client row ...');
ldp[0].Value := 'Jane Colins'; // ClientName
ldp[1].Value := 0.05; //ClientDiscount
ldp[2].Value := 'janec@gmail.com'; //ClientEmail
ldp[3].Value := 'djCkU'; //ClientPassword
ldp[5].Value := '(095) 636-19-63'; //ClientPhone
ldp[7].Value := '4935 Oliver Street Weatherford, TX 76086'; //ClientAddress
ldp[8].Value := 'Updated by command'; //ClientNotes

// remove unneeded parameters
ldp.Delete(6);//ClientPostalCode
ldp.Delete(4);//ClientBirthdate
lp := ldp.Add; lp.Name := UTF8Encode('OLD_ClientId');lp.Value := lClientID;

lResultExCommand := lCommand.Execute('Clients_UPDATE',ldp);
Log('Done with result: '+IntToStr(lResultExCommand));

lRemoteDataAdapter.FillWithDASql(lTable,Format(daSQL1,[lClientID]),nil);
LogTable(ltable);

Using the DELETE command

To delete a record from the Clients table, the sample uses the Execute function passing the name of the delete command (Clients_DELETE) as well as an array that contains only the previously retrieved ClientID value. The DataParameterArray from earlier is reused, however its contents are first cleared before adding a single DataParameter that is required as an argument by the schema's Clients_DELETE command.

This time, when FillWithDASql is called, the table returned will be empty as the row has been deleted.

daSQL1     = 'SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = %d';

... snipped code ...

Log('STEP 4. Call command for removing client row ...');
ldp.Clear;
lp := ldp.Add; lp.Name := UTF8Encode('OLD_ClientId');lp.Value := lClientID;

lResultExCommand := lCommand.Execute('Clients_DELETE',ldp);
Log('Done with result: '+IntToStr(lResultExCommand));

lRemoteDataAdapter.FillWithDASql(lTable,Format(daSQL1,[lClientID]),nil);
LogTable(ltable);

Log('Done!');

Using DA SQL to retrieve data

Data Abstract provides a technology called DA SQL that allows you to execute an SQL statement against the schema data rather than directly against the backend table.

To use it create a string that contains the SQL clause you wish to use. Then call the FillWithDASql function passing in the TDADataTable that will contain the table data as the first argument, then the DA SQL statement as the second argument, and finally an optional array of DataParameter objects that would be needed for arguments to the DA SQL statement.

This sample uses DA SQL 3 times to (try and) retrieve the newly added record from the Clients table.

daSQL1     = 'SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = %d';

... snipped code ...

lRemoteDataAdapter.FillWithDASql(lTable,Format(daSQL1,[lClientID]),nil);

Example Output

This is an example of the output you will see when the sample is run. You can see in Step 2 the table contains only the newly added row (thanks to using DA SQL to selectively retrieve data). Then in Step 3 that the values for the ClientName, ClientPhone and ClientNotes fields have been updated. Finally as the row is deleted in Step 4, the DA SQL statement fails to find a matching row, so the resultant table is empty.

Commands
:Commands 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. Call command for adding a new client row ...
Done with result: 1
Table: Data (1 rows from 1)
--------------------------------------------------------------------------
| ClientId    | ClientName      | ClientPhone     | ClientNotes          |
--------------------------------------------------------------------------
| 26          | Jane C.         | Unknown yet     | Added by command     |
--------------------------------------------------------------------------
STEP 3. Call command for modification client row ...
Done with result: 1
Table: Data (1 rows from 1)
--------------------------------------------------------------------------
| ClientId    | ClientName      | ClientPhone     | ClientNotes          |
--------------------------------------------------------------------------
| 26          | Jane Colins     | (095) 636-19-63 | Updated by command   |
--------------------------------------------------------------------------
STEP 4. Call command for removing client row ...
Done with result: 1
Table: Data (0 rows from 0)
--------------------------------------------------------------------------
| ClientId    | ClientName      | ClientPhone     | ClientNotes          |
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Done!
Cleanup!

Press ENTER to continue...