Friday, November 28, 2008

Create Distributed DB2 and Delphi Web Apps with DataSnap

Source :


This article shows you how to create distributed applications with thin clients and a database server tier using Borland Delphi/Kylix/C++ Builder and DB2.


In the past few articles, I have written dbExpress applications in DelphiTM to access data from the DB2® SAMPLE database . I have also shown how to migrate this application to Linux (recompiling with KylixTM) and even how to use WebSnapTM to produce a Web server application. This time, you'll enter the world of distributed applications, where you'll split the original Windows application in two different tiers: a thin client and a database server tier. I'll focus especially on the server side, as well as the communication between the (thin) client and the middleware server.


Understanding DataSnap multitier applications

Borland® Delphi and C++BuilderTM contain special wizards and components (collectively known as DataSnapTM) to help developers build multi-tier applications. A DataSnap server is an application that runs on a (server) machine, and is connected to the DBMS-the DB2 SAMPLE database, in this case. For one DataSnap server, you can have multiple DataSnap clients that connect only to the DataSnap server and not to the DBMS itself. In fact, the DataSnap client doesn't even have to know which DBMS it connects to (via the DataSnap server). This approach turns the DataSnap client into a so-called thin-client, since no database-specific drivers are required on the client machines. And the DataSnap server can even migrate from one DBMS to another without having to change the clients at all.

Start by building a DataSnap server in Delphi 7. You can use a number of components from the previous Windows application, but in different places. However, it can be insightful to compare the two different approaches, and see where you split the application in two (right between the TDataSetProvider and TClientDataSet components, placing a special connection component in between).

To build a DataSnap server in Delphi Enterprise,

  1. Do File > New Application to start a new project. The main form will only identify that the DataSnap server is running, so you may want to resize it and add a label to it in order to easily identify it (once it's running). Save the main form in DataSnapForm.pas and the project in DataSnapServer.dpr.
  2. A DataSnap server requires a data module that can communicate with external client applications. Such a data module is called a Remote Data Module, and you can create one using the wizard found in the Object Repository: do File > New > Other, go to the Multitier tab, and double-click on the Remote Data Module icon. This results in the Remote Data Module Wizard, where you only have to specify the CoClass Name of your DataSnap server (see Figure 1).
    Figure 1.Remote Data Module Wizard
    Remote Data Module Wizard

  3. The options for Instancing and Threading Model are already set to the most common values (see the online help for more details about the alternative choices), so leave them set. Clicking on OK generates a new Remote Data Module for you. Save this unit in file RDataMod.pas.

Adding the remote data module

You want to mimic the application you've built before, but this time as a DataSnap server, which means you must "stop" after the TDataSetProvider (and continue inside the DataSnap client with a connection component and TClientDataSet).

Because you want to use dbExpressTM again, first drop a TSQLConnection component on the Remote Data Module. Set its ConnectionName property, check the connection parameters (see if User_Name and Password have a value), and finally make sure that the LoginPrompt property is set to False. If you can set the Active property to True without problems, you know for sure that you can make a connection to the DB2 SAMPLE database (see the previous articles or this article's source code for more details).

You now need to drop four TSQLDataSet components, one for each of the four DB2 tables that you want to use in this application again (EMPLOYEE, EMP_ACT, EMP_PHOTO, and EMP_RESUME). Here are the steps to configure the components:

  1. Drop a TSQLDataSet component from the dbExpress tab onto the Remote Data Module, call it SQLdsEMP, point its Connection property to SQLConnection1, and set its CommandType property to ctTable and its CommandText property to EMPLOYEE.
  2. Drop a TDataSetProvider component from the Data Access tab, call it dspEMPLOYEE, and point its DataSet property to SQLdsEMP.
  3. Do the same thing for EMP_ACT, EMP_PHOTO, and EMP_RESUME, but this time use TSQLTable components (so you do not have to set the CommandType and CommandText, but can directly select a TableName). The Remote Data Module should now look like this (see Figure 2):
    Figure 2. DataSnap Remote Data Module
    DataSnap Remote Data Module.

  4. Save your project, recompile the DataSnap server application, and run it once to register it on the server machine.

Compared to the dbExpress application you built earlier, the difference is that you can use the DataSetProvider components not only to connect to a local TClientDataSet (residing in the same tier), but also to connect to a remote TClientDataSet-in a remote client application, as you'll now build.

Create the DataSnap client

A DataSnap client application can be any kind of application-from regular GUI to Web server. Since the Remote Data Module inside the DataSnap server is a COM server, you are limited to Windows clients, however (although it's possible to create DataSnap servers and clients using SOAP as communication protocol-transforming the server into a Web service and the client into a consumer of the Web service-but that's a story for another day).The steps to building a basic DataSnap client are:

  1. Build a regular application again-the DataSnap client-using File > New Application. Save the main form in ClientForm.pas, and the application project in DataSnapClient.dpr.
  2. Make a connection from the DataSnap client to the DataSnap server, using one of the available connection components from the DataSnap tab of the Component Palette. Available choices are: TDCOMConnection, TSocketConnection, and TWebConnection. The first uses DCOM as the communication protocol; the second, TCP/IP sockets; and the last one, HTTP. Note that the second and third solutions require additional software (at the DataSnap server machine): the Borland Socket Server is required for the TCP/IP socket connection, while a Web server (like IIS) and the httpsrvr.dll ISAPI DLL are required to allow the Web connection to work. For now, use the TDCOMConnection component (and refer to the online help for details about using the other communication protocols).
  3. Drop a TDCOMConnection component on the DataSnap client form, and open the drop-down combobox for the ServerName property. This should show a list of all registered DataSnap servers with their Remote Data Modules that are available on your machine (which may be only one: DataSnapServer.RemoteDataModuleDB2). Select DataSnapServer.RemoteDataModuleDB2, and set the Connected property to True to verify that you can start the DataSnap server from the DataSnap client application. Set Connected back to False. Note that for a connection to a remote DataSnap server, you also need to specify the ComputerName property of the TDCOMConnection component (without this specified, you'll be talking to localhost itself).
  4. For each of the four TSQLDataSet components on the DataSnap server side, drop a TClientDataSet component here on the client side. Call them cdsEMP, cdsEMP_ACT, cdsEMP_PHOTO, and cdsEMP_RESUME. Connect their RemoteServer properties to DCOMConnection1 (the connection component that forms the gateway to the DataSnap server). Next, open up the drop-down combobox for their ProviderName properties, and select dspEMPLOYEE, dspEMP_ACT, dspEMP_PHOTO, and dspEMP_RESUME, respectively. When you activate the TClientDataSet components, they will use the TDCOMConnection component to connect to the DataSnap server, and request (and receive) their data from the corresponding TDataSetProvider component. Very convenient.

    And again, this time you're using DCOM, but you can also use TCP/IP sockets or HTTP as the communication protocol between the DataSnap clients and the DataSnap server.

  5. Finish the DataSnap client application by dropping four TDataSource components on the form, name them dsEMP, dsEMP_ACT, dsEMP_PHOTO, and dsEMP_RESUME, and connect each of them to one of the TClientDataSet components (with a corresponding name, of course).

Establish master-detail relationships

Before you can drop data-aware controls and build a similar GUI to what you've done before (with the plain dbExpress components), you must first add the master-detail relationships between the EMPLOYEE (master) table and the other three (detail) tables. You can build the master-detail relationship in one of two ways: at the server or at the client. Since you already retrieved four individual datasets from the server, it's easier now to build the master-detail relationships on the client using the existing TClientDataSet components. To build this:

  1. In the cdsEMP master table, you must look at cdsEMP_ACT, cdsEMP_PHOTO, and cdsEMP_RESUME and point their MasterSource properties to dsEMP (the DataSource connected to the cdsEMP master table).
  2. Once the connection exists, specify the MasterFields property for each of the three detail tables, and connect the EMPNO fields from both the master and the detail table(s) using the Field Link Designer dialog.
  3. Drop a TDBNavigator (connect its DataSource to dsEMP), two TDBGrids (connected to dsEMP and dsEMP_ACT), a TDBMemo (connected to dsEMP_RESUME and the RESUME field), and finally a TDBImage component (connected to dsEMP_PHOTO and the PICTURE field). This results in the following client form for me (see Figure 3):

While Figure 3 may look nice, and may look similar to the single-tier dbExpress application you wrote earlier (as well as the WebSnap Web server application you wrote later), you'll have some problems if you actually want to use this application to save changes and update the (remote) database again.

Applying updates to DB2

Just like the single-tier dbExpress application, you have to use a call to ApplyUpdates (a method from the TClientDataSet component) to apply any updates-inserts, edits, and deletes-back to the DataSnap server so it can generate a SQL Update statement that will be sent to the DB2 SAMPLE database. However, having four TClientDataSet components means that you have to be careful in which order you can apply the updates. When deleting an employee, for example, you must first delete all detail records, and hence call ApplyUpdates on the detail TClientDataSet components before you can call ApplyUpdates on the master TClientDataSet component.

However, when adding a new employee, including activities, resume, and photo, you should first call ApplyUpdates on the master before trying to call ApplyUpdates on the details (otherwise there won't be a corresponding employee for which you enter activities, a photo, and resume). And if that doesn't get complex enough, consider the case where an error occurs and you want to roll back some changes. With four separate TClientDataSet components, you may find this a burden that you don't want to deal with.

And the good thing is that you don't have to deal with it, because you can "package" all four tables in one atomic entity, and call a single ApplyUpdates action instead of four separate ones. The technique to do this involves defining the master-detail relationship at the DataSnap server (instead of at the client), and thereby receiving and unpacking a so-called nested table at the DataSnap client.

  1. Start with the master-detail relationships at the server side, so return to the DataSnap server project. First of all, remove all DataSetProvider components except the one that points to the SQLdsEMP master table.
  2. Next, you need one TDataSource component, pointed to SQLsdEMP. And now all three TSQLTable components must point their MasterSource properties to the DataSource component (this is actually the reason why I used TSQLTable components for the three detail tables, since a TSQLDataSet has no MasterSource property).
  3. Click on the MasterFields property of the three detail SQLTables and specify the EMPNO fields to define the master-details relationship again. The modified Remote Data Module should look like this now (see Figure 4):
    Figure 4. Modified DataSnap Remote Data Module
    Modified DataSnap Remote Data Module

    Note that you have only one DataSetProvider component left: connected to the Employee table. But together with the Employee data, the other three tables will be packaged along, as you'll see in a moment.

  4. Before you can return to the DataSnap client, recompile the DataSnap server application again.
  5. Back at the DataSnap client, you should clear the RemoteProvider properties of the cdsEMP_ACT, cdsEMP_PHOTO, and cdsEMP_RESUME TClientDataSet components, because the three referring TDataSetProvider components have been removed from the DataSnap server (something that will not result in a compiler error, but only an error message at runtime, by the way).
  6. Besides clearing the MasterSource property, you must also clear the MasterSource and MasterFields properties.
  7. Double-click on the cdsEMP component to start the Fields Editor. Right-click inside the Fields Editor and do "Add All Fields". As a result, you not only see all fields from the Employee table, but also three special fields called SQLtblEMP_RESUME, SQLtblEMP_PHOTO, and SQLtblEMP_ACT. These three fields are of type TDataSetField, and are in fact "nested datasets," since they contain the detail records (for RESUME, PHOTO, and ACT) that belong to the current master employee record. They're inside that same record, packaged into one entity-making it convenient to receive, and use to apply updates (which you'll do in a moment again).
  8. Once you have added the persistent fields (in the Fields Editor), select the cdsEMP_ACT and set its DataSetField property to cdsEMPSQLtblEMP_ACT. Next, set the DataSetField property of cdsEMP_PHOTO to cdsEMPSQLtblEMP_PHOTO, and the DataSetField of cdsEMP_RESUME to cdsEMPSQLtblEMP_RESUME. This will make sure that the three detail TClientDataSets are connected-via the master cdsEMP dataset.

You don't have to change anything in the DataSnap client application; just recompile and run it. The data will show just as in Figure 3 before. Only this time, you can add a button that can send all updates (inserts, edits, deletes) in a single ApplyUpdates call instead of four separate calls. So, drop a TButton component, call it btnApplyUpdates, set its Caption property to Apply Updates, and write this single line of code in the OnClick event handler:

    procedure TForm3.btnApplyUpdatesClick(Sender: TObject);    begin      cdsEMP.ApplyUpdates(0)    end;  

See the online help for more details on the MaxErrors argument as well as the ability to respond to so-called Reconcile Errors when other users have modified the same records and applied their updates before you.

Apart from the single call to ApplyUpdates, you should also write some code for the OnCreate and OnDestroy event handlers of the DataSnap client's form, to explicitly open the cdsEMP TClientDataSet component (when the application starts), and to check if any changes have been made to make sure ApplyUpdates is called (when the application is shut down again). You may want to ask your end-user for confirmation, but I leave that as an exercise for the reader.

    procedure TForm3.FormCreate(Sender: TObject);    begin      cdsEMP.Active := True    end;      procedure TForm3.FormDestroy(Sender: TObject);    begin      if cdsEMP.ChangeCount > 0 then        cdsEMP.ApplyUpdates(0) // force any pending updates!    end;  

After this code has been written, you can set the Connected property of the TDCOMConnection component to False (at design time), as well as the Active property of the four TClientDataSet components. This is important because when you load the DataSnap client project and either the TDCOMConnection or the TClientDataSet components are "active" at design time, then Delphi will try to establish a connection with the DataSnap server. This will work just fine on my development machine (where the DataSnap server has been compiled and registered), but it may not work on your machine where the DataSnap server is probably unknown (until you compile the project and run the executable). This will cause design-time delays and errors-deactivating the offending connection component or datasets before you can continue.

Deploying the DataSnap server

Finally, in order to deploy the DataSnap server, you need a machine from where it can access the DB2 SAMPLE database, of course, as well as the dbExpress DB2 driver and the MIDAS.DLL. For a DataSnap client, you have to do less work: all it needs is the client executable and the MIDAS.DLL (and if you compile your project with the MidasLib unit, you won't even need to deploy the MIDAS.DLL, resulting in a standalone executable as DataSnap client).

There used to be a deployment license with runtime fees associated with DataSnap, but as of Delphi 7, anyone with a developer license of Delphi 7 Enterprise or later (the version required to build DataSnap servers and clients in the first place) can freely deploy DataSnap applications-both servers and clients. For Delphi 6 or C++Builder 6 Enterprise, there's still a runtime fee required of about $295 (USD) per DataSnap server machine. See the Borland Web site for details.


If you are interested in DataSnap server applications, to make the tables and data from a DB2 DBMS available to DataSnap thin clients, then wait until you see the next article, which combines DataSnap with SOAP. This combination results in a cross-platform technique that you can use with Kylix (Delphi and C++ editions) on Linux as well as Delphi and C++Builder on Windows, where either the DataSnap server or client (or both) can run on Linux instead of Windows. You can even mix clients-having one set running on Windows and another on Linux, with DataSnap servers that can run on both Windows and Linux (only a simple recompile is needed to migrate them).


Download source code here

Yahoo! sekarang memiliki alamat Email baru
Dapatkan nama yang selalu Anda inginkan di domain baru @ymail dan @rocketmail. br> Cepat sebelum diambil orang lain!