Friday, September 12, 2008

DB2 and Delphi 7 -- SOAP and Database Web Services

Original Source click here

Level: Introductory

Bob Swart (, Author, Trainer, Consultant and Webmaster, Bob Swart Training and Consultancy

19 Dec 2002

This article shows you how to create a cross-platform Web service with SOAP using IBM DB2 and Borland Delphi and Kylix.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi

© 2002 International Business Machines Corporation. All rights reserved.


In my last article, you entered the world of distributed applications, where you split the original Windows® application into two tiers: a thin client and a database server tier. The focus was especially on the server side, as well as the communication between the (thin) client and middleware server.

This time, you'll create a similar distributed application, but now based on SOAP as the communication protocol, where the server (using DB2®) will be turned into a cross-platform Web service. As a consequence, you can compile both the server and the client with Delphi® 7 and KylixTM 3 and deploy them on either Windows or Linux; this is a true cross-platform solution.

Back to top

Building the Windows SOAP server application

Start by building the server on Windows and then building a client on Linux. (You can do it the other way around if you want -- or build both on either Windows or Linux.)

  1. For the Windows SOAP server, start Delphi 7 Enterprise, do File > New > Other, and go to the Web Services tab of the Object Repository (see Figure 1).
    Figure 1. Delphi 7 Object Repository
    Delphi 7 Object Repository
  2. To build a DataSnapTM application as cross-platform Web service, double-click on the SOAP Server Application icon, which starts the corresponding wizard (see Figure 2).
    Figure 2. Creating a new SOAP Server Application
    Creating a new SOAP Server Application
  3. Using Delphi 7 (on Windows), you can now select from an ISAPI/NSAPI DLL, Apache 1.x or 2.x DLL, CGI executable, or Web App Debugger executable. You can use the latter to debug the application from within the Delphi IDE (but you cannot deploy it-you must change it to one of the other targets to deploy the Web server application). Using Kylix 3 (on Linux), you can select an Apache Dynamic Shared Object (DSO) file, a CGI executable, or a Web App Debugger executable. For this example, select a CGI executable, but for real-world usage you might want to select a more efficient Apache or ISAPI/NSAPI DLL. After you've made your choice, click OK.
  4. A new SOAP Server Application and SOAP Web Module is generated for you. But before you can take a closer look, you get a popup dialog asking if you want to create an interface for the SOAP module right now (see Figure 3).
    Figure 3. Because you're building a special DataSnap SOAP server, click No
    Answer No in response to this dialog

    Click No, because you're not interested in building a regular Web service application right now. Instead, you want to build a special DataSnap SOAP Server.

    As you can see in Figure 4, the SOAP Web module already contains three components:

    • THTTPSoapDispatcher
    • THTTPSoapPascalInvoker
    • TWSDLHTMLPublish component

    See the online help for more details about these components; basically, they perform all the SOAP and WSDL "plumbing" for your DataSnap SOAP Server, so you can now focus on the database tables and not worry about the rest.

    Figure 4. SOAP Web module with components
    SOAP Web module with components
  5. Save the SOAP Web module in file SWebMod.pas and the entire project in D7DB2CGI.dpr (which will result in D7DB2CGI.exe).
  6. Now add a SOAP Data Module to connect to the DB2 database (using the second icon in the WebServices tab of the Object Repository; see Figure 1 again). In the SOAP Data Module Wizard, you only have to specify the name of your new data module;name it D7DB2SAMPLE to indicate that you're still using the DB2 SAMPLE database like last time (see Figure 5).
    Figure 5. New SOAP data module wizard
    New SOAP data module wizard.
  7. Save the SOAP Data Module in SDataMod.pas. Because you want to mimic the application you've built before, this time as a DataSnap SOAP server, you must again use dbExpressTM data-access components and a TDataSetProvider here, and continue inside the DataSnap SOAP client with a TSOAPConnection and TClientDataSet component.
  8. Using dbExpress again, first drop a TSQLConnection component on the SOAP Data Module. Set its ConnectionName property to DB2Connection, check the connection parameters (see if User_Name and Password have values), and finally set the LoginPrompt property to False. If you can then set the Active property to True without problems, you know that you can make a connection to the DB2 SAMPLE database.
  9. Now drop one TSQLDataSet component (for EMPLOYEE) and three TSQLTable components, one for each of the three DB2 detail tables that you want to use in this multitier application again (EMP_ACT, EMP_PHOTO, and EMP_RESUME).
  10. Call the TSQLDataSet component SQLdsEMP, point its Connection property to SQLConnection1, and set its CommandType property to ctTable and its CommandText property to EMPLOYEE. Drop a TDataSetProvider component from the Data Access tab, call it dspEMPLOYEE, and point its DataSet property to SQLdsEMP.
  11. For each of the EMP_ACT, EMP_PHOTO, and EMP_RESUME tables, set the SQLConnection property as well as the TableName (to the corresponding table).
  12. Now, to build the master-detail relationships, you need a TDataSource component (called dsEMP), pointed to SQLsdEMP. And now all three TSQLTable components must point their MasterSource properties to the DataSource component. Further, click on the MasterFields property of the three detail SQLTables and specify the EMPNO fields to define the master-details relationship again. The SOAP Data Module should look like this now (see Figure 6):
    Figure 6. SOAP data module with dbExpress components
    SOAP data module with dbExpress components

Back to top

Deploying the server application

Save everything and compile the project. This produces D7DB2CGI.exe, which you can deploy in your Web server's scripts or cgi-bin directory. Obviously, after the application is placed in that directory, it still needs to be able to access the DB2 SAMPLE database (in my case, the development machine is also the depoyment machine, but in the real world you might have to change your connection SQLConnection parameters to be able to connect to the DB2 SAMPLE database).

After the application is deployed correctly, run the Web service executable from inside a Web browser. This shows information about the services available in the SOAP server (see Figure 7 for the complete listing).

Figure 7. Service Info for the D7DB2CGI Web service
Service Info for the D7DB2CGI Web service

Note that there are no fewer than four services (or interfaces) implemented by your Web service. The first three point to the same "service," namely the DataSnap SOAP Web module, which exposes itself to the outside world using the IAppServer, IAppServerSOAP, and ID7DB2SAMPLE interfaces.

If you add /WSDL to the URL, you get the WebService Listing itself (see Figure 8). WSDL (Web Services Description Language) is a formal way to let the SOAP server expose its capabilities to SOAP clients that want to use it.

Figure 8. WebService listing for D7DB2CGI
WebService listing for D7DB2CGI

For your SOAP server, you can get the formal WSDL specification if you add the name of the interface after the /WSDL in the URL. So for the IAppServer interface, the URL to get the WSDL is http://localhost/cgi-bin/D7DB2CGI.exe/wsdl/IAppServer.

Back to top

Building the Linux SOAP client

Once you have the WSDL (or the URL to produce the WSDL), you can write a SOAP client to access the SOAP server. In this case, you want to write the client using Kylix 3, and deploy it on Linux. Obviously, you won't be able to use the URL with localhost in it on Linux because that would point to the same machine. This time, you have to use the real IP-address or DNS name of the server machine that hosts the SOAP server. In my case, the IP-address is (within my local intranet).

To build the Linux SOAP client:

  1. Start Kylix 3 Enterprise and create a new application.
  2. To make the connection to the SOAP server, use a TSOAPConnection component from Kylix's Web Services tab. Set the URL property to the URL that you used before, but now specify soap instead of wsdl (if you pass /wsdl, you get the Web Services Description Language; if you pass /soap, you send a request to the server). So the value of the URL property in my case is
  3. Next, drop four TClientDataSet components, and call them cdsEMP, cdsEMP_ACT, cdsEMP_PHOTO, and cdsEMP_RESUME, followed by four TDataSource components called dsEMP, dsEMP_ACT, dsEMP_PHOTO, and dsEMP_RESUME, where each TDataSource component is connected to the corresponding TClientDataSet component.
  4. Now, double-click on the cdsEMP component to start the Fields Editor. Right-click inside the Fields Editor and select 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," because 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 convenient to use to apply updates (which you'll do in a moment again).
  5. After you've added the persistent fields (in the Fields Editor), set the DataSetFields properties to ensure that the three detail TClientDataSets are connected-via the master cdsEMP dataset:
    • Set the DataSetField property of cdsEMP_ACT to cdsEMPSQLtblEMP_ACT
    • Set the DataSetField property of cdsEMP_PHOTO to cdsEMPSQLtblEMP_PHOTO
    • Set the DataSetField of cdsEMP_RESUME to cdsEMPSQLtblEMP_RESUME

    You now need two TDBGrids, a TDBNavigator, a TDBMemo, and a TDBImage to produce the design-time view of Figure 9.

Figure 9. Kylix 3 CLX form and SOAP client at design time
Kylix 3 CLX form and SOAP client at design time.

Note that I've also placed a button that can send all updates (inserts, edits, and deletes) in a call to ApplyUpdates, with this single line of code in the OnClick event handler:

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

Apart from the single call to ApplyUpdates, you should also write some code for the OnCreate and OnDestroy event handlers of the SOAP 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). Just like last time, the code is:

	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, you can compile and run the Kylix SOAP client on Linux (see Figure 10).

Figure 10. Kylix 3 CLX form and SOAP client at run time
Kylix 3 CLX form and SOAP client at run time.

Back to top

Deploying the DataSnap server

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 file on Linux or the MIDAS.dll on Windows (and if you compile your project with the MidasLib unit on Windows, you won't even need to deploy the MIDAS.dll).

Back to top


The techniques in this article result in a cross-platform technique that we can use with Kylix (Delphi and C++ edition) 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). This truly opens up your DB2 database to thin-clients that can run just about anywhere!

Back to top