Console

The Commands console sample demonstrates executing remote schema Commands from a client application to insert, update and delete a record from a data table in an instance of Relativity Server.

Getting Started

The Console sample is typically located in /Developer/RemObjects Software/Samples/Data Abstract/Console/Commands, though you may have installed the Data Abstract for Cocoa and it's samples in another location.

To build it, you will of course need Xcode, and like all the samples provided with Data Abstract for Cocoa you will need to be running the Relativity Server with the DASamples Domain and the Simple Schema available.

Lastly this sample makes use of a convenience method (DALogTable) provided by a class extension to NSArray that we ship with Data Abstract for Cocoa. It is actually a #define method that uses one of three new log methods added to NSArray to print the contents of a DADataTable in a pretty fashion to the console using NSLog. To use this in your own code you must add the following to your import statements #import <DataAbstract/NSArray+DADataTableLog.h>. Please note that this class extension is intended for test purposes only.

Running the Sample

The sample when run sets up a connection to an instance of Relativity Server (Step 1). It then builds up a parameter dictionary which is passed to the executeCommand to insert a new record into the Clients table and retrieves a copy of the new row and prints it to the console (Step 2).

A new set of parameters are then created and passed to a command that will update the new record created in Step 2. Again a copy of the table is retrieved where the clientID matches the previously inserted record and displayed to the console so that the changes can be seen (Step 3).

Finally in Step 4 the delete command is executed passing in the clientID from the previous step and the table again retrieved and displayed. As the record has now been deleted there is nothing to print out.

Examining the Code

The code for creating the connection to the server and handling logging in is covered by other samples. In this section we shall focus solely on Steps 2, 3 and 4 that insert a new record using a command, updating that record and then deleting the record. All of the code can be found in the main.m source file.

Looking at the Commands

Before stepping through the code, we shall take a detour and use Server Explorer to examine that commands that are used in the next sections. If you open Server Explorer, navigate to the Simple schema and double click it to open the schema editor. 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 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.

Inserting a record

Before we can execute the Clients_INSERT command, we need to build up a dictionary of parameters (params) that match those defined for the command in the schema. Each of the FIELD_* keys are defined at the top of main.m and their string values match those defined in the Schema. If you fail to provide one of the parameters then a null value will instead be inserted into the database table for that parameter.

We pass the params dictionary to the executeCommand method of the DARemoteAdapter class, along with the name of the command we want to execute as a string; here Clients_INSERT. The executeCommand 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).

The remaining lines generate an SQL select statement to specifically retrieve the new record from the Clients table, specifying that the clientname matches the value for the FIELD_NAME key. The clientId that the database assigns the record is extracted from the first object in the table, and will be used in the following steps to work with that specific record.

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

NSDictionary *params = @{FIELD_NAME:        @"Jane C.",
                                 FIELD_DISCOUNT:    @0.01,
                                 FIELD_EMAIL:       @"janec@mail.com",
                                 FIELD_PASSWORD:    @"temp",
                                 FIELD_BIRTHDATE:   [df dateFromString:@"01/06/1973"],
                                 FIELD_ADDRESS:     @"Somewhere in Texas",
                                 FIELD_POSTALCODE:  @"???",
                                 FIELD_PHONE:       @"UNKNOWN",
                                 FIELD_NOTES:       @"Added by command" };

int result = [dataAdapter executeCommand:@"Clients_INSERT" withParameters:params];
NSLog(@"Done with result: %d", result);

NSString *dasql = [NSString stringWithFormat: @"SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientname = '%@'", @"Jane C."];
table = [dataAdapter getDataTableWithSQL:dasql];
NSNumber *clientID = [[table.rows firstObject] valueForKey:@"ClientId"];
DALogTable(table);

Updating a record

As with the previous step, we create a dictionary that contains all of the parameters defined for the command in the schema. There is an additional parameter which is the clientID we retrieved at the end of the previous step, and this the id assigned to the record by the database. Note that all of the parameters must be supplied otherwise the previous value will be replaced with a null value.

As before the parameters dictionary (params) is passed to the executeCommand of the DARemoteAdapter class, this time with the command name Clients_UPDATE. The result returned is a 1 if the command was successful and a 0 if it failed.

To show the record was correctly updated, we again use getDataTableWithSQL to retrieve a table with only the specific record. The SQL SELECT statement is slightly different this time around, using the clientid rather than clientname.

params = @{FIELD_ID_OLD:      clientID,
           FIELD_NAME:        @"Jane Collins",
           FIELD_DISCOUNT:    @0.05,
           FIELD_EMAIL:       @"janec@mail.com",
           FIELD_PASSWORD:    @"djCkU",
           FIELD_BIRTHDATE:   [df dateFromString:@"01/06/1973"],
           FIELD_ADDRESS:     @"4935 Oliver Street Weatherford, TX 76086",
           FIELD_POSTALCODE:  @"76086",
           FIELD_PHONE:       @"(817)-34-11-361",
           FIELD_NOTES:       @"Updated by command" };

result = [dataAdapter executeCommand:@"Clients_UPDATE" withParameters:params];
NSLog(@"Done with result: %d", result);
dasql = [NSString stringWithFormat:@"SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = %@", clientID];
table = [dataAdapter getDataTableWithSQL:dasql];
DALogTable(table);

Deleting a record

To delete a record from the Clients table, we execute the Clients_DELETE command passing a dictionary that only contains the value clientID which we retrieved in the previous step. The value of FIELD_ID_OLD is defined at the top of main.m and is the string OLD_ClientId which as you saw above was defined as a parameter for the Clients_DELETE command.

This time, when getDataTableWithSQL is called, the table returned will be empty and DALogTable will print that its an empty table.

params = @{FIELD_ID_OLD: clientID};
result = [dataAdapter executeCommand:@"Clients_DELETE" withParameters:params];
NSLog(@"Done with result: %d", result);

table = [dataAdapter getDataTableWithSQL:dasql];
DALogTable(table);

Example Output

This is an example of the output you will see when the sample is run.

Commands sample has been started.
Target URL is http://localhost:7099/bin.
RO SDK layer is configured.
RO DataAbstract layer is configured.
Trying to login with login string User=simple;Password=simple;Domain=DASamples;Schema=Simple...
Login successful

STEP 2. Call command for adding a new client row ...
Done with result: 1
TABLE: clients (1 rows from 1)
-----------------------------------------------------------------------------------
| ClientId| ClientName            | ClientPhone           | ClientNotes           |
-----------------------------------------------------------------------------------
| 28      | Jane C.               | UNKNOWN               | Added by command      |
-----------------------------------------------------------------------------------

STEP 3. Call command for modification client row ...
Done with result: 1
TABLE: clients (1 rows from 1)
-----------------------------------------------------------------------------------
| ClientId| ClientName            | ClientPhone           | ClientNotes           |
-----------------------------------------------------------------------------------
| 28      | Jane Collins          | (817)-34-11-361       | Updated by command    |
-----------------------------------------------------------------------------------  

STEP 4. Call command for removing client row ...
Done with result: 1
Nothing to log. Rows collection is empty.
Done!

Program ended with exit code: 0