Console
The DASQL console sample demonstrates using DA SQL to retrieve data from an instance of Relativity Server. DA SQL is a technology introduced to Data Abstract that gives clients the full flexibility of SQL queries to describe their data needs which will be run against the middle tier rather than against the back-end database(s), using the table and field names that are defined in the schema rather than those defined in the back-end database.
Getting Started
The Console sample is typically located in /Developer/RemObjects Software/Samples/Data Abstract/Console/DASQL, 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
When run, the sample sets up the connection to the server (Step 1). It then generates a basic DA SQL statement to retrieve the clientid, clientname, clientphone and clientaddress fields from the clients table where the clientid is equal to 20 and passes it to the server using getDataTable:withSQL: and prints the results to the console (Step 2). A more complex SQL statement is then generated and executed using the variant getDataTableWithSQL: method (Step 3). Finally the previous complex DA SQL statement is reused but this time executed using the asynchronous method beginGetDataTable:withSQL:withBlock: (Step 4).
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 through 4 which handle the creation of the DA SQL statement and retrieving the records using the getDataTable:withSQL:, getDataTableWithSQL: and beginGetDataTable:withSQL:withBlock: methods of DARemoteDataAdapter.
NOTE If the table name, or the field names do not exist or if there is a mistake in the SQL statement then an ROException will be raised and should be handled appropriately.
Using a simple DA SQL statement (Step 2)
A DA SQL statement is simply a normal SQL statement that uses the table & field names from the schema rather than the ones in the back-end Database. There is no special class required, simply put the DA SQL statement in an NSString and pass it as the second argument to the getDataTable:withSQL: method; the first argument is the name to be assigned to the returned table, while it can be whatever you wish it is recommended that you don't make it the same as other tables in the schema.
Here we create a DA SQL statement that retrieves the clientid, clientname, clientphone and clientaddress fields from the clients table where the clientid is 20. Its passed to getDataTable:withSQL:, along with the table name "MyData". The result of the call is then printed to the console. You can see in the output below that the table has been assigned the name "MyData".
NSLog(@"STEP 2. Simple DASQL ...");
NSString *template = @"SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = %@";
NSString *dasql = [NSString stringWithFormat:template, @20];
NSLog(@"DASQL: %@", dasql);
DADataTable *table = [dataAdapter getDataTable:@"MyData" withSQL:dasql];
DALogTable(table);
Using a complex DA SQL statement (Step 3)
DA SQL statements aren't limited to basic requests, you can compose as complex a request as you need by using a multi-line NSString. Here the statement retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 3 different tables (OrderDetails, Products and ProductGroups) where the order Type was "2", and the order date was between "2013-01-01" and "2013-12-12" and the data is grouped together and ordered by two columns, first by column "3" (i.e. the amount sold) and then by the "Desc" column.
If you aren't interested in assigning your own table name to the retrieved table you can use the method getDataTableWithSQL: instead. This method simply takes the DA SQL statement to be executed and it will assign the table the name "@dasqldata".
NSLog(@"STEP 3. Complex DASQL ...");
NSString *fromDate = @"2013-01-01";
NSString *toDate = @"2013-12-12";
template =
@"\n"
@"SELECT g.Name AS [Group], \n"
@" p.Name AS Product, \n"
@" sum(d.Amount) AS Sold \n"
@"FROM \n"
@" OrderDetails d \n"
@"INNER JOIN Orders o \n"
@" ON o.Id = d.[Order] \n"
@"INNER JOIN Products p \n"
@" ON p.Id = d.Product \n"
@"INNER JOIN ProductGroups g \n"
@" ON g.Id = p.[Group] \n"
@"WHERE \n"
@" o.Type = %d \n"
@"AND o.Date > '%@' \n"
@"AND o.Date <= '%@' \n"
@"GROUP BY \n"
@" g.Id, \n"
@" g.Name, \n"
@" p.Name \n"
@"ORDER BY \n"
@" 3 Desc \n";
dasql = [NSString stringWithFormat:template, 2, fromDate, toDate];
NSLog(@"DASQL: %@", dasql);
table = [dataAdapter getDataTableWithSQL:dasql];
DALogTable(table);
Using a complex DA SQL statement asynchronously (Step 4)
This step makes use of the complex DA SQL statement created for the previous step, and uses it as an argument to the asynchronous method beginGetDataTable:withSQL:withBlock:, which like the method in Step 2 has a first argument that is the name of the table to assign to the returned table. The last argument is a block to execute when the method call is finished.
Here we request a table that will have the name "MyData", whose content will be the records that match the dasql complex DA SQL statement. When the call is complete we execute the anonymous block that prints the table to the console and sets the done bool flag to YES; which will cause the sample to end.
NSLog(@"STEP 4. The same as previous STEP but Asynchronous ...");
static BOOL done = NO;
NSLog(@"Begin to select data ...");
[dataAdapter beginGetDataTable:@"MyData" withSQL:dasql withBlock:^(DADataTable * t) {
NSLog(@"Data has been retrieved");
DALogTable(t);
done = YES;
}];
Example Output
This is an example of the output you will see when the sample is run.
DASQL 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. Simple DASQL ...
DASQL: SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = 20
TABLE: MyData (1 rows from 1)
-----------------------------------------------------------------------------------
| ClientId| ClientName | ClientPhone | ClientAddress |
-----------------------------------------------------------------------------------
| 20 | Anna H. Kugler | (817) 249-9525 | 597 Loving Acres Road |
-----------------------------------------------------------------------------------
STEP 3. Complex DASQL ...
DASQL:
SELECT g.Name AS [Group],
p.Name AS Product,
sum(d.Amount) AS Sold
FROM
OrderDetails d
INNER JOIN Orders o
ON o.Id = d.[Order]
INNER JOIN Products p
ON p.Id = d.Product
INNER JOIN ProductGroups g
ON g.Id = p.[Group]
WHERE
o.Type = 2
AND o.Date > '2013-01-01'
AND o.Date <= '2013-12-12'
GROUP BY
g.Id,
g.Name,
p.Name
ORDER BY
3 Desc
TABLE: @dasqldata (17 rows from 17)
-----------------------------------------------------------
| Group | Product | Sold |
-----------------------------------------------------------
| RAM | RAM DDR3 4096Mb | 4 |
| Monitors | Monitor 22"; Dell | 4 |
| Motherboards | Motherboard INTEL BOXD| 2 |
| Hard-Disk Drives | HDD Seagate Barracuda | 2 |
| CPU | CPU INTEL i5-4670 | 2 |
| Cases | Case CM HAF 912 - Mid | 2 |
| Tablets | Apple A1474 iPad Air 1| 2 |
| Solid State Disks | SSD Intel 240 Gb | 1 |
| Video | Video Asus NVidia R928| 1 |
| Mouses | Mouse Logitech Wireles| 1 |
| Monitors | Monitor 22"; Asus | 1 |
| Notebooks | Apple MacBook Air 11" | 1 |
| Notebooks | Notebook 15.6"; ASUS X| 1 |
| Netbooks | Ultrabook 13.3"FHD Toc| 1 |
| Tablets | Tablet 10,1"; ASUS Viv| 1 |
| Players | TRANSCEND T.sonic 870;| 1 |
| Laser Printers | Printer HP LaserJet P2| 1 |
-----------------------------------------------------------
STEP 4. The same as previous STEP but Asynchronous ...
Begin to select data ...
Waiting for completion asynchronous GetData call...
Data has been retrieved
TABLE: MyData (17 rows from 17)
-----------------------------------------------------------
| Group | Product | Sold |
-----------------------------------------------------------
| RAM | RAM DDR3 4096Mb | 4 |
| Monitors | Monitor 22"; Dell | 4 |
| Motherboards | Motherboard INTEL BOXD| 2 |
| Hard-Disk Drives | HDD Seagate Barracuda | 2 |
| CPU | CPU INTEL i5-4670 | 2 |
| Cases | Case CM HAF 912 - Mid | 2 |
| Tablets | Apple A1474 iPad Air 1| 2 |
| Solid State Disks | SSD Intel 240 Gb | 1 |
| Video | Video Asus NVidia R928| 1 |
| Mouses | Mouse Logitech Wireles| 1 |
| Monitors | Monitor 22"; Asus | 1 |
| Notebooks | Apple MacBook Air 11" | 1 |
| Notebooks | Notebook 15.6"; ASUS X| 1 |
| Netbooks | Ultrabook 13.3"FHD Toc| 1 |
| Tablets | Tablet 10,1"; ASUS Viv| 1 |
| Players | TRANSCEND T.sonic 870;| 1 |
| Laser Printers | Printer HP LaserJet P2| 1 |
-----------------------------------------------------------
Done!
Program ended with exit code: 0
NOTE: For clarity the date and process name and ID have been removed from the sample text above