DA SQL sample (console) (Delphi)
The DA SQL sample demonstrates using DA SQL to use two different SQL statements to selectively retrieve data from tables in the "Simple" schema in an instance of Relativity Server.
The first is a simple statement that retrieves 4 fields from the Clients table for a particular row. The second SQL statement is more complex and retrieves a new (virtual) table whose fields are taken from 4 different tables, where their rows match particular criteria.
Getting Started
The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\DASQL (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 the sample is run it sets up a connection to the server (Step 1), and then creates a simple DA SQL statement to retrieve certain fields from the Clients table for any row where the value in the ClientId matches "20", sends it to the server and prints out the resultant table (Step 2). Finally a more complex DA SQL is created and the results from that are then printed to the console (Step 3).
Examining the Code
All of the code can be found in the uDASQL source file. The schema table "Clients" used for the first DA SQL statement is comprised of 10 fields, which this sample will limit to just 4 using. The more complex SQL statement will pull data from the OrderDetails, Orders, Products and ProductGroups tables.
NOTE If the table name, or the field names do not exist or if there is a mistake in the SQL statement then an exception will be raised and should be handled appropriately.
A Simple DA SQL statement (Step 2)
A DA SQL statement is essentially 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 to use DA SQL, simply put the DA SQL statement in a String object and pass it an as argument to the FillWithDASql method of TDARemoteDataAdapter.
The version of FillWithDASql used here takes three arguments. The first argument should be an instance of TDADataTable, here TDAMemDataTable, that will be used to hold the data returned after executing the DA SQL statement. The second argument is the string containing the DA SQL statement and the last argument is for an optional array of DataParameter objects that would be used as arguments for the SQL statement.
Here a DA SQL statement is created that retrieves the clientid, clientname, clientphone and clientaddress fields from the clients table where the clientid is 20. It is passed along with an instance of TDAMemDataTable and a nil to the FillWithDASql method of TDARemoteDataAdapter.
DASQL1 = 'SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = 20';
... snipped code ...
lRemoteDataAdapter.FillWithDASql(lTable,DASQL1,nil);
A Complex DA SQL statement (Step 3)
DA SQL statements aren't limited to basic requests interacting with a single table, you can compose as complex a SQL statement as you need.
Here the statement retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 4 different tables (OrderDetails, Orders, 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.
As before the DA SQL statement is passed to the server using the FillWithDASql procedure of TDARemoteDataAdapter.
DASQL2 = 'SELECT g.Name AS [Group],'+#10+
' p.Name AS Product,'+#10+
' sum(d.Amount) AS Sold'+#10+
'FROM'+#10+
' OrderDetails d'+#10+
'INNER JOIN Orders o'+#10+
' ON o.Id = d.[Order]'+#10+
'INNER JOIN Products p'+#10+
' ON p.Id = d.Product'+#10+
'INNER JOIN ProductGroups g'+#10+
' ON g.Id = p.[Group]'+#10+
'WHERE'+#10+
' o.Type = 2'+#10+
'AND o.Date > ''2013-01-01'''+#10+
'AND o.Date <= ''2013-12-12'''+#10+
'GROUP BY'+#10+
' g.Id,'+#10+
' g.Name,'+#10+
' p.Name'+#10+
'ORDER BY'+#10+
' 3 Desc ';
... snipped code ...
lRemoteDataAdapter.FillWithDASql(lTable,DASQL2,nil);
Example Output
This is an example of the output you will see when the sample is run. As each step starts the DA SQL statement is printed to the console, followed by the results of the request.
DASQL sample has been started.
Target URL is http://localhost:7099/bin
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. 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 Acre |
---------------------------------------------------------------------
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: MyData (17 rows from 17)
---------------------------------------------------
| Group | Product | Sold |
---------------------------------------------------
| RAM | RAM DDR3 4096Mb | 4 |
| Monitors | Monitor 22"; De | 4 |
| Motherboards | Motherboard INT | 2 |
| Hard-Disk Drive | HDD Seagate Bar | 2 |
| CPU | CPU INTEL i5-46 | 2 |
| Cases | Case CM HAF 912 | 2 |
| Tablets | Apple A1474 iPa | 2 |
| Solid State Dis | SSD Intel 240 G | 1 |
| Video | Video Asus NVid | 1 |
| Mouses | Mouse Logitech | 1 |
| Monitors | Monitor 22"; As | 1 |
| Notebooks | Apple MacBook A | 1 |
| Notebooks | Notebook 15.6"; | 1 |
| Netbooks | Ultrabook 13.3" | 1 |
| Tablets | Tablet 10,1"; A | 1 |
| Players | TRANSCEND T.son | 1 |
| Laser Printers | Printer HP Lase | 1 |
---------------------------------------------------
Done!
Cleanup!
Press Return to exit...