In this session I'm going to demonstrate how to use C++Builder XE6 to build a multi-tier database application with DataSnap framework. The server is a web app that accesses data from the InterBase database and the client is a mobile Android or iOS app that is using secure HTTPS protocol for connecting to the server and exchanging JSON data through REST interfaces.
FireDAC JSON Reflection support has been introduced in RAD Studio XE5 Update 2 and the first person to blog about it was Delphi Product Manager Marco Cantu in this blog post.
I have promised in the session to make the source code of the demo available, so here it is! It is available for download from Embarcadero CodeCentral here.
The demo illustrates best practices for using DataSnap framework in the RAD Studio XE6.
These are the preferred technologies:
- Database: InterBase
- Database Access Framework: FireDAC
- Data format: JSON
- Client/Server Architecture: REST
- Communication Protocol: HTTPS
- Client-side in-memory dataset: FireDAC "TFDMemTable"
- UI technology: Visual LiveBindings
In the RAD Studio XE6 there is an Object Pascal DataSnap demo in C:\Users\Public\Documents\Embarcadero\Studio\14.0\Samples\Object Pascal\DataSnap\FireDACJSONReflect that I have translated to C++. The original demo has the FireMonkey desktop client and in my case it is a mobile client.
The "C++ Mobile Day" DataSnap demo contains two projects. "CPPDepartments_REST_server" and "CPPDepartments".
The server project has been created using the C++Builder XE6 "DataSnap WebBroker Application" wizard. It uses HTTPS for communication, DataSnap encryption and compression transport filters and role-based authentication/authorization. The server exposes two server methods for retrieving JSON data and one method for accepting data updates to the underlying InterBase "EMPLOYEE" sample database.
The application works with two tables in the database: DEPARTMENT and EMPLOYEE. Here is the screenshot from FireDAC Explorer that shows the structure of both tables:
The server module contains the database FireDAC connection component and three query components.
The first query, FDQueryDepartmentNames, returns the list of department numbers and names. It has the following SQL statement:
select DEPT_NO, DEPARTMENT from DEPARTMENT
The result from this query is used at the client to build the list of departments. In order to retrieve this information the client app needs to call the "GetDepartmentNamesJSON" server method that returns data encoded as TJSONObject. The implementation of this method is very interesting.
#include "System.Json.hpp"
#include "Data.FireDACJSONReflect.hpp"
TJSONObject* TServerMethods1::GetDepartmentNamesJSON()
{
FDQueryDepartmentNames->Close();
TFDJSONDataSets *ds = new TFDJSONDataSets();
TFDJSONDataSetsWriter::ListAdd(ds, FDQueryDepartmentNames);
TJSONObject *obj = new TJSONObject();
TFDJSONInterceptor::DataSetsToJSONObject(ds, obj);
return obj;
}
Notice that we do not care about the types of fields returned from the query. The "TFDJSONDataSetsWriter" class provides static "ListAdd" method that is using reflection to convert results of the query into "TFDJSONDataSets" object and the "TFDJSONInterceptor" class uses DataSetsToJSONObject static method to convert the contents of datasets into plain JSON object that is returned from the server method to client.
The next two queries – FDQueryDepartment and FDQueryDepartmentEmployees – are used to retrieve more detailed information for a given department from DEPARTMENT and from EMPLOYEE tables. These are parameterized queries:
select * from DEPARTMENT where DEPT_NO = :DEPT
select * from EMPLOYEE where DEPT_NO = :DEPT
The information returned from both queries is exposed to clients via "GetDepartmentEmployeesJSON" server method. What is interesting here is that we can in just one operation receive data from multiple queries. That's a very nice capability!
const System::String sEmployees = "Employees"
const System::String sDepartment = "Department"
TJSONObject* TServerMethods1::GetDepartmentEmployeesJSON(System::UnicodeString AID)
{
FDQueryDepartmentEmployees->Active = false
FDQueryDepartment->Active = false
FDQueryDepartment->Params->operator [](0)->Value = AID;
FDQueryDepartmentEmployees->Params->operator [](0)->Value = AID;
// Create dataset list
TFDJSONDataSets *ds = new TFDJSONDataSets();
// Add departments dataset
TFDJSONDataSetsWriter::ListAdd(ds, sDepartment, FDQueryDepartment);
// Add employees dataset
TFDJSONDataSetsWriter::ListAdd(ds, sEmployees, FDQueryDepartmentEmployees);
TJSONObject *obj = new TJSONObject();
TFDJSONInterceptor::DataSetsToJSONObject(ds, obj);
return obj;
}
These two methods are used by a client app to receive information about departments. First we get the list of departments names and their IDs. When a client selects a department from the list, then the detailed information about department and its employees is returned from the second server method.
The third server method is used to send data updates from client and updating the underlying database. Here is the source code.
void TServerMethods1::ApplyChangesDepartmentEmployeesJSON(TJSONObject* AJSONObject)
{
TFDJSONDeltas *LDeltas = new TFDJSONDeltas();
TFDJSONInterceptor::JSONObjectToDataSets(AJSONObject, LDeltas);
TFDJSONErrors *errs = new TFDJSONErrors();
// Apply the department delta
TFDJSONDeltasApplyUpdates::ListApplyUpdates(LDeltas, sDepartment, FDQueryDepartment->Command, errs);
// If no errors, apply the employee delta
if (errs->Count == 0) {
TFDJSONDeltasApplyUpdates::ListApplyUpdates(LDeltas, sEmployees, FDQueryDepartmentEmployees->Command, errs);
}
// Raise an exception if any errors.
if (errs->Count > 0) {
throw new Exception(errs->Strings->Text);
}
}
This method does not return any value, but it accepts JSON object with updates to data. Again, this code is quite generic. We do not care about the underlying field types. The "TFDJSONDeltasApplyUpdates::ListApplyUpdates" static method is using reflection to apply updates to the underlying database tables. In just one operation we can update multiple tables!
The client application has been created with "FireMonkey Mobile App – C++Builder" wizard. This project can be compiled as an Android or an iOS app from the very same source code.
The first step is to use "DataSnap REST Client Module" wizard to generate DataSnap client proxy code for accessing the server functionality. "ClientClassesUnit1" and "ClientModuleUnit1" units have been generated with this wizard. In order to have more cleaner design I have also added a data module ("uDMDepartmentsCPP") to the project where I have placed three FireDAC in-memory dataset components for storing data coming from the three queries on the server.
Additionally I had to add the "TFDStanStorageBinLink" component to the form, because otherwise the client app gives an error at runtime. The first dataset is used only for storing the read-only data with the list of departments and their names. The next two datasets are used for working with data coming from the parameterized server queries and the changes made to in-memory data done through user interface are sent back to server. It is important to set "CachedUpdates" property on both table to "True" for things to work.
I'm using visual live bindings for binding data to visual controls. For this reason it is necessary to define field definitions in the FDMemTables to we can bind to them at design time. Field types are not important, but the names of the field definitions need to correspond to fields in datasets received from the server.
The user interface of the client application contains one tab control that occupies the whole screen, with just three tabs. The first tab contains the list view components for the list of department names. When the end user clicks on any department name, then the second tab is displayed with information about selected department. The last tab contains connection settings with host, port, protocol, username and password needed to connect to a specific server.
The first tab has the "refresh" button used for retrieving department names and identifiers. I'm using Visual LiveBindings for binding data from the "FDMemTableDepartmentNames" dataset to the list view. In order to be able to display the DEPT_NO information, you need to change the "ItemAppearance.ItemAppearance" property to "ListItemRightDetail". In this way the "Item.Detail" property is added to list view and we can bind it to DEPT_NO field.
I have encapsulated the logic to retrieve department names in the data module's "GetDepartmentNames" method.
#include "uDMDepartmentsCPP.h"
#include "Data.FireDACJSONReflect.hpp"
#include "DataSnap.DSClientREST.hpp"
#include "ClientModuleUnit1.h"
// ...
void TDMDepartmentsCPP::GetDepartmentNames()
{
try
{
TJSONObject* LJSONObject(ClientModule1->ServerMethods1Client->GetDepartmentNamesJSON());
std::auto_ptr<TFDJSONDataSets> LDataSets(new TFDJSONDataSets());
TFDJSONInterceptor::JSONObjectToDataSets(LJSONObject, LDataSets.get());
FDMemTableDepartmentNames->Active = false
TFDAdaptedDataSet * LDataSet = TFDJSONDataSetsReader::GetListValue(LDataSets.get(), 0);
FDMemTableDepartmentNames->AppendData(*LDataSet);
} catch (TDSRestProtocolException& E)
{
HandleRESTException(ClientModule1->DSRestConnection1, "Get Departments error", &E);
}
}
We start from getting JSON data from the server using "ClientModule1" class that was generated with the "DataSnap REST Client Module" wizard. Next we convert JSON to a dataset and append it to FDMemTableDepartmentNames table. The Visual LiveBindings take care about displaying this data in the list view. That's a very powerful mechanism.
I'm not including the source for the "HandleRESTException" method. You can find in the download.
When the end user clicks on the department names the following code is executed to download department and employee detailed information for a selected department.
void TDMDepartmentsCPP::GetDepartmentDetails(const System::String ADEPTNO)
{
try
{
TJSONObject* LJSONObject(ClientModule1->ServerMethods1Client->GetDepartmentEmployeesJSON(ADEPTNO));
std::auto_ptr<TFDJSONDataSets> LDataSets(new TFDJSONDataSets());
TFDJSONInterceptor::JSONObjectToDataSets(LJSONObject, LDataSets.get());
{
TFDAdaptedDataSet * LDataSet = TFDJSONDataSetsReader::GetListValueByName(LDataSets.get(), sDepartment);
// Update UI
FDMemTableDepartment->Active = False
FDMemTableDepartment->AppendData(*LDataSet);
}
{
TFDAdaptedDataSet * LDataSet = TFDJSONDataSetsReader::GetListValueByName(LDataSets.get(), sEmployees);
// Update UI
FDMemTableEmployees->Active = False
FDMemTableEmployees->AppendData(*LDataSet);
}
} catch (TDSRestProtocolException& E)
{
HandleRESTException(ClientModule1->DSRestConnection1, "Get Departments error", &E);
}
}
The data stored in the "FDMemTableDepartment" and "FDMemTableEmployee" can be modified through the user interface. When the end user clicks on the "Apply Updates" button the following code is used to send the updates back to the server.
void TDMDepartmentsCPP::ApplyUpdates()
{
// Post if editing
if (dsEditModes.Contains(FDMemTableDepartment->State))
{
FDMemTableDepartment->Post();
}
if (dsEditModes.Contains(FDMemTableEmployees->State))
{
FDMemTableEmployees->Post();
}
// Create a delta list
TFDJSONDeltas * LDeltas = new TFDJSONDeltas();
// Add deltas
TFDJSONDeltasWriter::ListAdd(LDeltas, sEmployees, FDMemTableEmployees);
TFDJSONDeltasWriter::ListAdd(LDeltas, sDepartment, FDMemTableDepartment);
TJSONObject * LJSONObject(new TJSONObject());
TFDJSONInterceptor::DataSetsToJSONObject(LDeltas, LJSONObject);
try
{
// Call server method. Pass the delta list.
ClientModule1->ServerMethods1Client->ApplyChangesDepartmentEmployeesJSON(LJSONObject);
}
catch (TDSRestProtocolException& E)
{
HandleRESTException(ClientModule1->DSRestConnection1, "Get Departments error", &E);
}
}
That's it! Using the new FireDAC JSON Reflection functionality in C++Builder XE6 it is super easy to build secure, multitier database applications!
The source code of this demo is available for download from Embarcadero Code Central.