DA SQL sample (desktop) (Delphi)

The DA SQL desktop sample demonstrates using a complex DA SQL statement to selectively retrieve data from tables in the "Simple" schema in an instance of Relativity Server.

Getting Started

The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\DASQL.

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.

Running the Sample

This sample allows you to see the results of running a pre-defined complex DA SQL statement that 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.

When you first run the sample you will see an empty area where the table data will be displayed and a toolbar which contains a combo box and a button. When pressed the "DASQL Report" button will execute the DA SQL statement on the server specified in the combo box, the retrieved results will then be used to populate the table. The combo box contains the address of the server to connect to, which by default points to an instance of Relativity Server running on the local machine. If you enter a different address, then that will be used when you press the "DASQL Report" button.

Examining the Code

The sample is built around 2 classes, ClientDataModule which handles the interaction with the server including creating and sending the DA SQL statement. ClientForm handles creating the user interface and the user interactions.

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 creating and issuing the DA SQL statement.

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.

Creating and Issuing a Complex DA SQL statement

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. 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.

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.

In this particular sample a statement is created that will retrieve a new 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.

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.

//fClientDataModule.pas
c_DASQL    = '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 ...

RemoteDataAdapter.FillWithDASql(table,c_DASQL,nil);