HTML5 Builder - DataSnap REST Client Module for DataBase Tutorial
By Embarcadero Technologies, Inc.
Purpose: This tutorial shows how to create and use a HTML5 Builder DataSnap REST Client Module to access a DataSnap server and call database access functions exposed by the DataSnap server to access backend database information and display the data on a HTML5 Grid.
Introduction: DataSnap is a technology that enables RAD creation of multi-tier web applications. You can use HTML5 Builder to create client-side web applications that interact with DataSnap. A DataSnap REST client module is a data module that lets you access a DataSnap server. You can then include that data module in another data module or a webpage to be able to call the functions exposed by the DataSnap server.
This tutorial covers:
1. How to create a DataSnap REST Application Server.
2. How to create a DataSnap Server function to access the backend database. For this example, I'll use the InterBase database, but any supported database will work also.
3. How to create a PHP Client Application using HTML5 Builder.
4. How to display the InterBase data on a HTML5 Grid.
Part 1 - Create the DataSnap REST Application Server.
Using either Delphi XE3 or C++ Builder XE3. For this example, I'll use Delphi XE3.
File > New > Other > Delphi Projects > DataSnap Server > DataSnap REST Application
Click OK.
Select "Stand-alone VCL application". Click Next.
Change the HTTP Port to 81, or whatever port you want to use that's available on your machine.
Click 'Test Port" and verify "Test port succeeded". Click OK.
Click Next.
Select Server Methods Class, Sample Methods and Sample Web Files. We'll use these Sample Methods to test our DataSnap Server is working corectly.
Click Next.
Select TDataModule. This will give us a container for the MySQL connection to connect to our MySQL database.
Click Next.
Provide a directory for the project, such as: H5BDataSnapRESTApplication
Click Finish.
Our DataSnap REST Application is created!
Run (F9) to test the DataSnap Server.
Hide image
Hide image
Hide image
Hide image
Click Start. Click Open Browser. Click Server Functions. Expand TServerMehods functions. Test ReverseString. Enter Value. Click Execute. Verify Result.
STOP and close the DataSnap Server.
Next, let's open the ServerMethodsUnit and add a connection to our MySQL database. The ServerMethodsUnit is a TDataModule since we chose "TDataModule" as the server methods ancestor class (from the DataSnap Wizard).
Hide image
Hide image
Hide image
From the Tool Palette, add a TSQLConnection to the TDataModule.
Using the Object Inspector, add your connection properties for the InterBase database. I'm using the sample employee.gdb database:
Driver = InterBase
Database = C:\ProgramData\Embarcadero\InterBase\gds_db\examples\database\employee.gdb
UserName = sysdba
Password = masterkey
LoginPrompt = false
Connected = True
Name= cnt1
Our DataSnap Server is now connected to our InterBase database! Congratulations!
Next, we'll create a method for the client application to call to access the InterBase data.
Open the code for the ServerMethodsUnit file:
Add a public function to GetData from the database. This function takes in a Table Name and a SQL string, and it returns a JSON array, like this:
function GetData(TableName: string; SQL: string): TJSONArray;
Add the Data.DBXJSON unit to the uses clause, for the TJSONArray.
Next, let's implement this function. Highlight the GetData function, and Ctrl+Shift+C (gives you function completion).
Since this is a query, we'll create a TSQLQuery to execute the SQL query the function will receive, and let's dynamically create the query, get the SQL, and establish the connection to the database, like this:
Now that we receive the SQL, let's determine how we will encode the data in our response. The result will be a JSON array, so the array has two objects. The first object will have key as "tablename", and the value of that key will be the names of the fields of the table, like f1name, f2name, ....
The second object should contain the data. The key should be "data" and the data should be an array of rows, and each row should be an array of fields, and the value of the fields should be inside the last array. So this is how we will encode the data in that format.
We will first encode the "tablename" object.
Create variable called "FieldsObj" that's a TJSONObject.
FieldsObj := TJSONObject.Create;
We'll also create a variable FieldNameArray that's a TJSONArray.
FieldNameArray := TJSONArray.Create;
Next, we'll enumerate all the field names.
The For I loop, gives us all the field names from the JSON array.
Lastly, we'll take the FieldsObj and Add Pairs, and the first thing to add is the Table Name we received from the GetData function, and next we'll add the Fields (FieldNameArray). This should give us the data encoded how we want it.
To test, we can create the TJSONArray and add the first FieldsObj element.
Save ALL. Run (F9).
To test our GetData function, START the DataSnap Server, Click the Open Browser, expand the TServerMethods. You will now see our GetData function.
Enter any of the Employee database tables for TableName (such as country). Enter a SQL, such as "select * from country".
Click Execute.
The Result is an array, and the first object of the array is the tablename "country" (the input TableName we supplied in the GetData function), and the value of the object is all the fields [COUNTRY, CURRENCY], from the table country. EXCELLENT!
STOP and close of the DataSnap Server.
The next step is to create the object for the data.
DataObj : TJSONObject;
DataRows : TJSONArray;
RowFields: TJSONArray;
Next, let's create the DataObj and the DataRows array which is an array of data. And once we create the DataRows array, we can query (qry) through all the rows of data.
DataObj := TJSONObject.Create;
DataRows := TJSONArray.Create;
qry.First
For each row we want the value, so we can re-use the same code for the FieldNameArray.
and we create every row for each value in the array, and display its AsString value.
Once the row is created, we'll add it to our DataRows.
Lastly, we'll add all the rows to our DataObj.
Finally, we get our Results := TJSONArray.Create(FieldsObj, DataObj);
The code for our GetData() function is:
function TServerMethods1.GetData(TableName, SQL: string): TJSONArray;
var
qry: TSQLQuery;
FieldsObj: TJSONObject;
FieldNameArray: TJSONArray;
I: Integer;
DataObj: TJSONObject;
DataRows: TJSONArray;
RowFields: TJSONArray;
begin
qry := TSQLQuery.Create(Self);
qry.SQL.Text := SQL;
qry.SQLConnection := cnt1;
qry.Open;
FieldsObj := TJSONObject.Create;
FieldNameArray := TJSONArray.Create;
for I := 0 to qry.FieldCount - 1 do
FieldNameArray.Add(qry.Fields[I].FieldName);
FieldsObj.AddPair(TableName, FieldNameArray);
DataObj := TJSONObject.Create;
DataRows := TJSONArray.Create;
qry.First;
while not qry.Eof do
begin
RowFields := TJSONArray.Create;
for I := 0 to qry.FieldCount - 1 do
RowFields.Add(qry.Fields[I].AsString);
DataRows.Add(RowFields);
qry.Next;
end;
DataObj.AddPair('data', DataRows);
Result := TJSONArray.Create(FieldsObj, DataObj);
qry.Free;
end;
SAVE ALL. Run (F9). Test the GetData function.
An array gets returned. The first object is the "fields" information. The second object is "data", and the value is also an array, and each element of the array is an array of field values. Very cool!
Our DataSnap Server and GetData function is now complete!
NOTE: Keep the DataSnap Server running. You can exit out of Delphi, but keep the DataSnap Server running. The DataSnap server needs to be running to create the DataSnap REST Client module in Part 3.
Next, we'll use HTML5 Builder to create a client web app to invoke the GetData function on the DataSnap Server and return results to the web client.
Part 2 - HTML5 Builder: Create a Server Web Application
Using HTML5 Builder, we’ll create a Server Web Application, like this:
A server web application is a web application that runs in a web server, allowing you to use both server-side and client-side web technologies, and built-in support for database interaction and AJAX is provided. Advanced interface localization features are also available. Once deployed to the server, it can be accessed through a web browser from any device.
Do: Home > New > HTML5 Builder Projects > Server Web Application.
Your new Web Server Project will then be created with an empty server page (unit1.php), which will be opened on the Design view.
A Server Web Application consist mainly of server pages, often along with server data modules and server units, although they might have any type of file. To add new files to your project, use the Project Manager.
Add Some Controls
The first step in creating a server web application with HTML5 Builder is designing the user interface. There are many components available by default for creating user interfaces. Move your cursor over to the Tool Palette (the widget in the top-right corner) and expand the Standard category by clicking the plus (+) icon. Then select the Edit component and drop it onto the Designer. An instance of the component will be displayed on the Designer.
Repeat these steps for adding three Label, one Button and another Edit component onto the Designer.
Now you should see six components on the Designer. Use the mouse pointer to rearrange the components like this:
You can view and change a component’s properties using the Object Inspector and selecting the component on the Designer.
Note: To change a property of a component, select the component on the Designer (or the drop-down list in the top of the Object Inspector), change the value of the target property and press Enter to apply the change.
For Button, change the Caption = ‘Get Data’.
For Label1, change the Caption = “HTML5 Builder DataSnap Web Client”.
For Edit1, change Name = eTableName.
For Edit2, change Name = eSqlQuery.
For Label2, change Caption = 'Table Name'. Change Name=lTableName.
For Label3, change Caption = 'SQL Query'. Change Name = lSqlQuery.
Execute your Application
Let's run the web page to test it works OK. You can click the Run button Hide image
in the main toolbar or press F9.
You will be asked to SAVE the application. Create a new folder, like "DataSnapWebServerClient" and save the application and project.
Rename unit1.php to main.php.
Save the project, like "DataSnapWebServerClientProject.h5bprj"
Once you have executed the application, the webpage will be loaded on your default web browser.
Part 3 - DataSnap REST Client module.
Now that we have a Web Server Application, we’ll add our DataSnap REST Client module.
A DataSnap REST client module is a data module that lets you access a DataSnap server. You can then include that data module in another data module or a webpage to be able to call the functions exposed by the DataSnap server.
Note: If you don’t already have a DataSnap Server, please use either Delphi or C++ Builder and create a new DataSnap REST Application (stand-alone VCL app, http, localhost, port 81, sample server methods, TComponent).
You MUST have your DataSnap Server started before creating the DataSnap REST Client Module.
In HTML5 Builder, Home >> Other Projects >> Other Files >> DataSnap REST Client
On the New DataSnap REST Client Module dialog, you can fill these properties as needed.
- Set the Protocol to either http (faster) or https (more secure).
- Choose a Programming Language for the module. It can be either PHP or JavaScript. If you choose PHP, the connection will be setup with RPCL components; if you want it to be generated with pure PHP code, just check the Generate pure PHP code option.
- Set the Host and the Port to those used by your DataSnap server.
- You might need to also fill URL Path, Context, User name and Password fields.
For this example, our DataSnap Server (Delphi or C++ Builder created) uses http, localhost, and port 81. We will also use PHP for the language.
Click OK.
Two files will be added to your Web Server Project, by default: ClientClassUnit1.php and ClientModuleUnit1.php.
The module (ClientModuleUnit1) contains a DSRestConnection component, which will be responsible for the connection with the DataSnap server. It also includes a read method, readServerMethods1Client(), which returns an instance of a class, TServerMethods1. This class is defined in the ClientClassUnit1 file and contains the methods on the DataSnap server you will be able to call from your web client.
ClientClassUnit has our GetData() method:
ClientModuleUnit has our DSRestConnection method:
SAVE ALL in the same folder "DataSnapWebServerClient".
Call DataSnap Methods from Web Client
In the Code view, open the main.php of the WebServerApp project to call the DataSnap server methods.
Add: require_once("ClientModuleUnit1.php");
You can use the Use Unit icon 
and select the ClientModuleUnit1.php file.
This adds the require_once("ClientModuleUnit1.php"); to your main.php file.
Now, wherever you want to call a DataSnap method in your code:
- Include the global variable for the module: global $ClientModuleDataModule1;
- Call the method from its
ServerMethods1Clientproperty:
$ClientModuleDataModule1->ServerMethods1Client->methodName()
Write a Response for a Button Click
For web applications, any response to users’ actions such as button clicks and text field entries can be implemented as a response to an event. In HTML5 Builder, such responses are referred to as event handlers.
For the Button component, the most typical event is a button click.
Double-click the button on the Designer, HTML5 Builder creates skeleton code to implement an event handler for the button click event.
Now you can implement responses between the braces.
In our Web Client (main.php), we will code the function bGetDataClick to display the result of the DataSnap call for the function GetData() to our web client.
First, we prepare to use $ClientModuleDataModule1 global variable, by adding:
global $ClientModuleDataModule1;
And then we can call the GetData() method from its ServerMethods1Client property. We will pass the TableName from EditBox (eTableName) and the SQL from EditBox (eSqlQuery) to the GetData() method.
GetData($this->eTableName->Text, $this-eSqlQuery-Text);
Since the results gets returned in a array, we'll declare a new variable called $result to store the return value.
To quickly view the returned results, we'll use the var_dump($result); function.
Our bGetDataClick function looks like this:
function bGetDataClick($sender, $params)
{
// Prepare to use $ClientModuleDataModule1 global variable.
global $ClientModuleDataModule1;
$result = null;
// Run DataSnap ReverseString() method, passing it input field content.
$result = $ClientModuleDataModule1->ServerMethods1Client->GetData($this->eTableName->Text, $this->eSqlQuery->Text)->result;
// Print result using var_dump
var_dump($result);
}
Note: In HTML5 Builder, while you are typing code, some hints indicating the kind of parameter you need to specify will appear. Also, hints will indicate the kinds of members that are supported in a given object, that is, the properties and methods of your components.
Run the application (F9).
The output should be your JSON array, like this:
Great!
Next, let's create a function to convert the results to a HTML5 Grid.
function JSONarrayToHTML5Grid($tablename, $jsonArray)
{
$fields = null;
$fields = $jsonArray[0]->$tablename;
$htmlgrid = null;
$htmlgrid = '<table> <tr>';
for($i = 0; $i < count($fields); $i++)
{
$htmlgrid = $htmlgrid . '<td>' . $fields[$i] . '</td>';
}
$htmlgrid = $htmlgrid . '</tr> </table>';
return $htmlgrid;
}
Next, on our main.php form, add a new Label (Label2). We will use the Caption property of the Label to display the database data.
We will replace our "var_dump($result);" function with:
// This displays the fieldnames of the TableName
$this->Label2->Caption = $this->JSONarrayToHTML5Grid($this->eTableName->Text, $result);
and to see the border in the display we'll change:
$htmlgrid = '<table> <tr>'; to $htmlgrid = '<table border = 1> <tr>';
Run (F9) the app, and verify you get your fieldnames of the TableName, like this:
Lastly, lets add all the data to our output HTML Grid.
To our function JSONarrayToHTML5Grid, add:
$rows = null;
$rowvalues = null;
Next, get all the rows from the JSON array, the second element of our RESULT:
$rows = $jsonArray[1]->data;
Next we need a "for loop" to iterate through all the rows.
The complete function looks like this:
function JSONarrayToHTML5Grid($tablename, $jsonArray)
{
$fields = null;
$fields = $jsonArray[0]->$tablename;
$htmlgrid = null;
$rows = null;
$rowvalues = null;
$htmlgrid = '<table border =1> <tr>';
for($i = 0; $i < count($fields); $i++)
{
$htmlgrid = $htmlgrid . '<td><b>' . $fields[$i] . '</b></td>';
}
$htmlgrid = $htmlgrid . '</tr>';
$rows = $jsonArray[1]->data;
for ($i = 0; $i < count($rows); $i++)
{
$htmlgrid = $htmlgrid . '<tr>';
$rowvalues = $rows[$i];
for ($j = 0; $j < count($rowvalues); $j++)
{
$htmlgrid = $htmlgrid . '<td>' . $rowvalues[$j] . '</td>';
}
$htmlgrid = $htmlgrid . '</tr>';
}
$htmlgrid = $htmlgrid . '</tr>';
return $htmlgrid;
}
Save ALL.
Run (F9).
We see the FieldNames get returned on the first row, and the values are underneath each FieldName. AWESOME!
The function converted a JSON Array to a HTML5 Grid, which is returned from our DataSnap Server.
Some modifications we can make to the output are:
1) Make the fieldnames BOLD with the <b> tag:
$htmlgrid = $htmlgrid . '<td><b>' . $fields[$i] . '</b></td>';
Concluding Remarks
Embarcadero’s HTML5 Builder is the definitive software for Rapid Application Development with Web Technologies. HTML5 Builder and its library, the RAD PHP Component Library (RPCL), includes a lot of new features and improvements to make your life easier, and take the RAD methodology even further into the web development. HTML5 Builder features a brand-new interface that adapts itself to your workflow as you take care of the different aspects of your application: writing the logic, designing the Interface, managing databases, and so much more. For HTML5, the RPCL now supports HTML5, the new version of the web standard that is revolutionizing the way you write webpages! To learn more about HTML5 Builder, or to download a trial, visit http://www.embarcadero.com/products/HTML5-Builder
Tags: REST HTML5 Builder DataSnap HTML5 Builder Client Module Grid
