Delphi Labs: DataSnap XE - jQueryMobile Web Frontend

By: Pawel Glowacki

Abstract: In this article we are going to use Delphi XE to build a jQueryMobile web frontend application for displaying InterBase XE “Employees” database data from a standalone Delphi DataSnap server.

    Introduction

In this lab exercise we are going to use Delphi XE to build a web application for displaying database data coming from a DataSnap server using jQuery Mobile JavaScript library.

At the time of this writing the jQuery Mobile is still in its alpha phase of development, so the functionality available today (March 2011), may differ from the final release.

In this article I am using jQuery Mobile Alpha 3 release available at http://jquerymobile.com/demos/1.0a3/.

The end-result of this tutorial is a sample, multitier, scalable DataSnap system consisting of a Delphi XE DataSnap standalone data server providing data from the underlying InterBase XE sample “Employees” database and a client - a WebBroker Delphi XE standalone web application that will use jQuery Mobile for displaying data.

The starting point for this tutorial is “WebBroker jQueryMobile Boilerplate” project discussed in the previous Delphi Labs episode (http://edn.embarcadero.com/article/41322).

Hide image
Click to see full-sized image

expand view>>

    Understanding jQueryMobile

Coding in pure JavaScript can be very tedious and there are plenty of frameworks and libraries to make this task easier. One of the most popular JavaScript libraries is jQuery (http://jquery.com/), which is “a fast and concise” and “simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development”.

One of the most recent projects within the world of jQuery is a “mobile” version of this popular library that is optimized for web browsers available on smartphones, like iPhone, Android and others. It is a layer on top of the jQuery core that is using the latest web standards, like HTML5 and CSS3, for building web pages that have this “mobile” look-and-feel.

“WebBroker JQueryMobile Boilerplate” project available at the Embarcadero Code Central (http://cc.embarcadero.com/item/28254) is just the starting point. Now it’s the time to build a DataSnap server application that will provide client data to our jQueryMobile to be displayed dynamically.

    Building the Data Server Application

We are going to create a standalone Delphi DataSnap VCL Forms application server that will read data from the InterBase XE sample “Employees” database “Customers” table. We are going to make sure that customer records are sorted by “company name”, so we do not need to perform sorting on the client side.

Right-click on the project group node in the Project Manager and select “Add New Project” and select “DataSnap Server” icon from the “Delphi Projects – DataSnap Server” category in the “New Items” dialog.

Hide image
New DataSnap Server wizard

Keep the default “VCL Forms Application” on the first screen of the wizard.

On the second screen uncheck the option for generating sample methods as we are not going to use them.

Hide image
Click to see full-sized image

On the next screen keep the default port number.

expand view>>

It is critical to make sure that you select the last, not default server methods ancestor class, which is “TDSServerModule”.

Hide image
Click to see full-sized image

Click on “Finish” button.

expand view>>

Click on “File – Save All” menu option. Save the main form unit as “FormServerUnit”, keep the default names for server container unit and server methods unit. Save the project as “DataServerApp” and the project group as “jQueryScalableSystem”.

Now we need to configure database connection to InterBase sample database.

In the “Data Explorer” window select “INTERBASE” node and right click to add a new connection and give it a name “IBEMPLOYEE”.

Right click on the “IBEMPLOYEE” connection and select “Modify Connection” option.

Enter the path to the database file, which is the default InterBase XE installation: “C:\Embarcadero\InterBase\examples\database\employee.ib”.

Click on the “Test Connection” button to make sure that the database server is running and the connection can be established.

The implementation of our Data Server will not require any coding. We are going to use a “TSQLConnection” component which provides access to a selected RDBMS instance, a “TSQLQuery” component with the query to return customer information from the database ordered by company name, and a “TDataSetProvider” component, that our client application will be connecting to.

To quickly add “TSQLConnection” component to the server methods module, just drag it from the Data Explorer onto the server methods unit. This is the fastest way as all necessary properties are automatically configured for you. Alternatively you can manually add “TSQLConnection” component and set its properties accordingly.

The actual database connection information is now embedded in “Params” property of the database connection object.

Press F6 to display “IDE Insight” and add a “TSQLQuery” component to the server methods unit.

Set its “SQLConnection” property to point to the connection object already on the data module.

Now we need to provide SQL statement for retrieving customers from “CUSTOMERS” table ordered by the company name.

Right click on the “IBEMPLOYEE” database in the Data Explorer and select “SQL Window” to author the SQL statement interactively.

Drag “CUSTOMERS” table from the list of the right hand side onto the main window in the middle.

Check all the fields in the table to include them in the query.

Select “Ascending” sorting order for the “CUSTOMER” field that contains the company name.

Select the text of the query and copy it to the clipboard right clicking on the selection and selecting “Copy”.

Close the “SQL Window”.

Hide image
Click to see full-sized image

Open “SQL” property of the query component and paste the SQL statement there.

expand view>>

Click on “Active” property to see if the SQL statement is correct and make sure to uncheck this property after verifying that it can be set to “true”.

Add “TDataSetProvider” component to the module.

Rename the provider component to “dspCustomersByCompanyName”.

Set its “DataSet” property to point to “sqlqCustomersByCompanyName” query component.

Hide image
Click to see full-sized image

That’s it! Our Delphi DataSnap data server is now ready.

expand view>>

Click on the green triangle icon to run the server application.

It has to be running until the end of this tutorial, so we could finish developing the client app and run it.

    Adding DataSnap client data access

If you see right now the code of the employees’ data module in the code editor, just press F12 to switch to the form designer view where you can add non-visual components to the data module. Unlike VCL Forms data modules are not designed for displaying their content. They have a surface only at design time that make it possible to drop different non-visual components, typically for data access functionality.

Press F6 to display “IDE Insight”. Just start typing “TSQLConnection” to find it and then add a connection to our employees data.

I have renamed this new connection to “DSSQLConnectionEmployees” to help it stand out;-)

It is always a good idea to uncheck “LoginPrompt” property at this moment, so there is no “Login” dialog displayed.

Change its “Driver” property to “DataSnap”. This will open access to sub-properties in “Driver” property specific for DataSnap connectivity. I am going to keep “localhost” as “HostName” property as I am running both applications on the same computer. In a more realistic scenario it would be some IP address of the actual machine or a DNS name.

OK… How do we connect to the remote data server now?

On the server side there is a “TDataSetProvider” component on the form ready to receive client requests from VCL “TClientDataSet” components.

The base class for the server side implementation that we have chosen in the wizard was ”TDSServerModule” that implements “IAppServer” interface from MIDAS times. This interface contains methods that data provider and client dataset components know how to use in order to send data packets back and forth over the network.

In this scenario there is no need of generating client proxy code to access the server, because the server-side functionality is already defined by “IAppServer” interface.

  IAppServer = interface(IDispatch)
    ['{1AEFCC20-7A24-11D2-98B0-C69BEB4B5B6D}']
    function  AS_ApplyUpdates(const ProviderName: WideString; Delta: OleVariant;
                              MaxErrors: Integer; out ErrorCount: Integer; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_GetRecords(const ProviderName: WideString; Count: Integer; out RecsOut: Integer;
                            Options: Integer; const CommandText: WideString;
                            var Params: OleVariant; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_DataRequest(const ProviderName: WideString; Data: OleVariant): OleVariant; safecall;
    function  AS_GetProviderNames: OleVariant; safecall;
    function  AS_GetParams(const ProviderName: WideString; var OwnerData: OleVariant): OleVariant; safecall;
    function  AS_RowRequest(const ProviderName: WideString; Row: OleVariant; RequestType: Integer;
                            var OwnerData: OleVariant): OleVariant; safecall;
    procedure AS_Execute(const ProviderName: WideString; const CommandText: WideString;
                         var Params: OleVariant; var OwnerData: OleVariant); safecall;
  end;

Add a “TDSProviderConnection” component to the data module.

I have renamed it to “DSProviderConnectionEmployees” and set its “SQLConnection” property to our connection component “DSSQLConnectionEmployees”.

It would be nice if you could now select the name of available server classes from a dropdown right now, but it is necessary to enter the actual name of the server class on the server we want to connect with. In our case this is: “TServerMethodsIBEmployees”.

Add “TClientDataSet” component to the data module.

Set its “RemoteServer” property to “DSProviderConnectionEmployees” and if everything is properly configured and connected you should see dataset provider names available as a dropdown in the “ProviderName” property.

Note that you can have multiple providers on the server and multiple provider connection components sharing the same connection to the server. Select “dspCustomersByCompanyName” as the provider name.

Rename the client dataset component to “cdsIBCustomers” and try set its “Active” property to “true” to make sure that everything is properly connected but keep it set to “false”. We do not want it to be left “Active” at design-time. We will just make it active when we need data and inactive again afterwards.

In order to simplify data access code it is a good idea to persist client dataset fields. Just double-click on the “cdsIBCustomers” client dataset to display its “Fields Editor” dialog. Right click in the dialog and select “Add All Fields”.

Now we need to provide the actual implementation of the “GetHtmlBody” method that will open the client dataset, take customer data from the client and format it as HTML using jQueryMobile specific features.

    Generating jQueryMobile code dynamically in Delphi

My main focus in this tutorial is Delphi and DataSnap, so I am not going to go into the details of jQueryMobile.

There are plenty of interesting and informative jQueryMobile resources in the web. I have found very useful “Simple development with jQuery Mobile” multimedia tutorial from http://net.tutsplus.com that was included in the Feb2011 issue of “Practical Web Design” magazine. The other resource that provides basic info on jQueryMobile that I have found was this tutorial: http://miamicoder.com/2011/creating-a-website-using-jquery-mobile-part1/.

Please find below the source code for the final version of the data module responsible for dynamic creation of our HTML page.

Just select the whole code below, copy and paste into Delphi editor.

unit DataModuleEmployeesUnit;

interface

uses
  SysUtils, Classes, DBXDataSnap, DBXCommon, DB, SqlExpr, DBClient, DSConnect;

type
  TCustomerData = record
    CUST_NO: Integer;
    CUSTOMER: String;
    CONTACT_FIRST: String;
    CONTACT_LAST: String;
    PHONE_NO: String;
    ADDRESS_LINE1: String;
    ADDRESS_LINE2: String;
    CITY: String;
    STATE_PROVINCE: String;
    COUNTRY: String;
    POSTAL_CODE: String;
    ON_HOLD: String;
  end;

  TDataModuleEmployees = class(TDataModule)
    DSSQLConnectionEmployees: TSQLConnection;
    DSProviderConnectionEmployees: TDSProviderConnection;
    cdsIBCustomers: TClientDataSet;
    cdsIBCustomersCUST_NO: TIntegerField;
    cdsIBCustomersCUSTOMER: TStringField;
    cdsIBCustomersPHONE_NO: TStringField;
    cdsIBCustomersCONTACT_LAST: TStringField;
    cdsIBCustomersCONTACT_FIRST: TStringField;
    cdsIBCustomersADDRESS_LINE1: TStringField;
    cdsIBCustomersADDRESS_LINE2: TStringField;
    cdsIBCustomersCITY: TStringField;
    cdsIBCustomersSTATE_PROVINCE: TStringField;
    cdsIBCustomersCOUNTRY: TStringField;
    cdsIBCustomersPOSTAL_CODE: TStringField;
    cdsIBCustomersON_HOLD: TStringField;
  private
    FCustDetailPages: string;
    function GetCustList: string;
    function GetCustDetailPages: string;
    procedure OutputCustomerDetailPage(const c: TCustomerData);
  public
    function GetHtmlBody: string;
  end;

//var
//  DataModuleEmployees: TDataModuleEmployees;

implementation

{$R *.dfm}

{ TDataModuleEmployees }

function TDataModuleEmployees.GetHtmlBody: string;
begin
  Result :=
 '<!-- Start of main page -->'
+'<div data-role="page" id="main">'

+'  <div data-role="header">'
+'       <h1>Delphi in the Cloud</h1>'
+'  </div><!-- /header -->'

+'  <div data-role="content">'

+'<h2>Welcome to DelphiLabs!</h2>'
+'<p>Press on the button below to find information'
+' about customers of a fictional company.</p>'

+'        <ul data-role="listview" data-theme="c" data-inset="true">'
+'          <li><a href="#customersMain"></a>Customers</li>'
+'          <li><a href="#about"></a>About</li>'
+'        </ul>'
+'  </div><!-- /content -->'

+'  <div data-role="footer">'
+'       <h6>DelphiLabs DataSnap Tutorial</h6>'
+'  </div><!-- /footer -->'
+'</div><!-- /page -->'

+'<!-- Start of about page -->'
+'<div data-role="page" id="about">'

+'  <div data-role="header">'
+'       <h1>About</h1>'
+'  </div><!-- /header -->'

+'  <div data-role="content">'

+'<h2>Welcome to DelphiLabs!</h2>'
+'<p>The page you just see has been created as a demo'
+' project for DelphiLabs "DataSnap" jQueryMobile tutorial'
+' and deployed to a virtual machine running in <a href="http://aws.amazon.com/">Amazon EC2</a>.</p>'
+'<p>Sample "Customers" data comes from <a href="http://www.embarcadero.com/products/interbase">InterBase XE</a> demo database'
+' that is accessed through <a href="http://www.embarcadero.com/products/delphi">Delphi XE</a> standalone DataSnap server application.</p>'
+'<p>The HTML markup that you just see right now has been generated dynamically in Delphi code'
+' using <a href="http://jquerymobile.com/2011/02/jquery-mobile-alpha-3-released/">jQueryMobile (alpha3)</a>.'
+'<hr/>'
+'<p>Visit <a href="http://www.embarcadero.com/rad-in-action/delphi-labs">'
+'Embarcadero RAD-in-Action Delphi-Labs page</a> for more details!</p>'
+'  </div><!-- /content -->'
+'  <div data-role="footer">'
+'       <h6>DelphiLabs DataSnap Tutorial</h6>'
+'  </div><!-- /footer -->'
+'</div><!-- /page -->'


+'<!-- Start of customers summary page -->'
+'<div data-role="page" id="customersMain">'

+'  <div data-role="header">'
+'       <h1>Customers</h1>'
+'  </div><!-- /header -->'

+'  <div data-role="content" data-theme="b">'

+ GetCustList

+'  </div><!-- /content -->'
+'  <div data-role="footer">'
+'       <h6>DelphiLabs DataSnap Tutorial</h6>'
+'  </div><!-- /footer -->'

+'</div><!-- /page -->'

+ GetCustDetailPages

end;

function TDataModuleEmployees.GetCustList: string;
var s: string; ch: char; c: TCustomerData;
begin
  s := '<ul data-role="listview" data-inset="true" data-theme="c" data-filter="true">';

  ch := ' ';

  cdsIBCustomers.Active := true;
  try
    while not cdsIBCustomers.Eof do
    begin
      c.CUST_NO := cdsIBCustomersCUST_NO.AsInteger;
      c.CUSTOMER := cdsIBCustomersCUSTOMER.AsString;
      c.CONTACT_FIRST := cdsIBCustomersCONTACT_FIRST.AsString;
      c.CONTACT_LAST := cdsIBCustomersCONTACT_LAST.AsString;
      c.PHONE_NO := cdsIBCustomersPHONE_NO.AsString;
      c.ADDRESS_LINE1 := cdsIBCustomersADDRESS_LINE1.AsString;
      c.ADDRESS_LINE2 := cdsIBCustomersADDRESS_LINE2.AsString;
      c.CITY := cdsIBCustomersCITY.AsString;
      c.STATE_PROVINCE := cdsIBCustomersSTATE_PROVINCE.AsString;
      c.COUNTRY := cdsIBCustomersCOUNTRY.AsString;
      c.POSTAL_CODE := cdsIBCustomersPOSTAL_CODE.AsString;
      c.ON_HOLD := cdsIBCustomersON_HOLD.AsString;


      if ch <> c.CUSTOMER[1] then
      begin
        ch := c.CUSTOMER[1];
        s := s + '<li data-role="list-divider" data-theme="b">' + ch + '</li>';
      end;

      s := s +
      '<li><a href="#cust' + IntToStr(c.CUST_NO) + '">' + cdsIBCustomersCUSTOMER.AsString + '</a></li>';

      OutputCustomerDetailPage(c);

      cdsIBCustomers.Next;
    end;
  finally
    cdsIBCustomers.Active := false;
  end;

  s := s + '</ul>';
  Result := s;

end;

function TDataModuleEmployees.GetCustDetailPages: string;
begin
  Result := FCustDetailPages;
end;

procedure TDataModuleEmployees.OutputCustomerDetailPage(const c: TCustomerData);
var s: string; aStatus: string;
begin
  if c.ON_HOLD <> '' then
    aStatus := '<B> (ON HOLD)</B>'
  else
    aStatus := '';

  s :=
 '<!-- Start of customer detail page -->'
+'<div data-role="page" id="cust' + IntToStr(c.CUST_NO) + '">'

+'  <div data-role="header">'
+'       <h1>Customer Details</h1>'
+'  </div><!-- /header -->'
+'  <div data-role="content">'

+'<p><h1>' + c.CUSTOMER + aStatus + '</h1></p>'

+'<div data-role="collapsible" data-theme="b">'
+'<h3>Contact</h3>'
+'<p><h2>' + c.CONTACT_FIRST + ' ' + c.CONTACT_LAST + '</h2></p>'
+'<p>' + c.PHONE_NO + '</p>'
+'</div>'

+'<div data-role="collapsible" data-theme="b">'
+'<h3>Address</h3>'
+'<p>' + c.ADDRESS_LINE1 + '</p>'
+'<p>' + c.ADDRESS_LINE2 + '</p>'
+'<p>' + c.CITY + '</p>'
+'<p>' + c.POSTAL_CODE + ' ' + c.CITY +'</p>'
+'<p>' + c.STATE_PROVINCE + '</p>'
+'<p><b>' + c.COUNTRY + '</b></p>'
+'</div>'

+'  </div><!-- /content -->'
+'  <div data-role="footer">'
+'       <h6>DelphiLabs DataSnap Tutorial</h6>'
+'  </div><!-- /footer -->'

+'</div><!-- /page -->';

  FCustDetailPages := FCustDetailPages + s;
end;

end.

The running version of this web application can be accessed through this URL: http://79.125.25.31:8080/

In my Chrome web browser it looks like this:

Hide image
Click to see full-sized image

If you click on “Customers” button we will have a nice transition to customer listing ordered by company name. Check out the filter field at the top.

expand view>>

Hide image
Click to see full-sized image

If you click on the customer name, you should see more details.

expand view>>

Hide image
Click to see full-sized image

That’s it! Feel free to use techniques described here to build your own, great looking web sites for mobile devices in Delphi XE!

expand view>>

    Summary

In this Delphi Labs DataSnap tutorial we have looked into practical steps of building a multitier, scalable system for displaying database data on mobile devices. We have used InterBase XE database as a source of data, intermediate standalone DataSnap server for data access and WebBroker web application for displaying data using modern jQueryMobile JavaScript libraries optimized for mobile web browsers.

Note that we are using a pre-release version of jQueryMobile library, so the final functionality may differ from what has been described here.

DataSnap is a feature of Delphi, C++Builder and RAD Studio Enterprise and Architect editions. It is not available in Starter and Professional editions.

    References

    A Little Disclaimer

In “Delphi Labs” I’m focusing on Delphi development, but all of the applications described here, could be also built using C++Builder, which is a part of RAD Studio.

Delphi and C++Builder are deeply integrated. Both are different “personalities” of the same underlying Integrated Development Environment (IDE) and they share the Visual Component Library (VCL).

It is just my personal preference to use Delphi Pascal programming language. If you are more comfortable with C++ you should definitely give the C++Builder XE (http://www.embarcadero.com/products/cbuilder) a try!

Server Response from: ETNASC02