Friday, September 12, 2008

Build DB2 and Delphi 7 Internet Applications with WebSnap

Original Source click here

Level: Introductory

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant and Webmaster, Bob Swart Training and Consultancy

14 Nov 2002

With Delphi 7 WebSnap technology, you can quickly take data from an IBM DB2 Universal Database and move it to a Web-based browser application in, well, a snap. In this article, the author shows you step by step how to move the application to the Web.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


© 2002 International Business Machines Corporation. All rights reserved.

Introduction

Delphi®7 Studio is more than a desktop application development tool. With Delphi 7 WebSnapTM technology, you can quickly take data from an IBM® DB2TM Universal Database and move it to a Web-based browser application in, well, a snap. In this article, I'll take the application built in my previous articles in this series (which started with Create a Reusable Component to Connect Delphi 7 to DB2 with dbExpress) and quickly move it to the Web with WebSnap.



Back to top


Start building your Web app

WebSnap was introduced by BorlandTM in Delphi 6, and is part of the Enterprise edition of Delphi 7 Studio. To build a WebSnap application:

  1. Start Delphi and select New->Other from the File menu.
  2. Go to the WebSnap tab of the Object Repository and double-click on the WebSnap Application icon for the New WebSnap Application Wizard (see Figure 1).
  3. Select the Web App Debugger executable target from this dialog box. You can use (and test) this without the need for a Web server. Note the Cross Platform checkbox, which helps produce WebSnap applications that you can compile with Delphi on Windows and KylixTM on Linux®. Whenever you use a Web App Debugger executable target, you must specify a Class Name (eBob42 in this example).
  4. Next, change the starting Page Name to "Home", and then click OK to generate a new WebSnap application with an Application Page Module (the starting page) as well as an empty Web App Debugger form.
  5. Select Save All from the File menu and save the Page Module in pmHome.pas, the form in WADForm.pas, and the project itself in WAD.dpr.

Figure 1. Select the desired type of New WebSnap application.
Select the desired type of New WebSnap application.


Back to top


Building the WebSnap data module

Now that I've built the application skeleton, it's time to add a data module containing the dbExpressTM components that connect to DB2:

  1. Select New->Other from the File menu, go to the WebSnap tab again and double-click on the WebSnap Data Module icon. This will display a dialog box: click OK to produce a new WebSnap Data Module.
  2. Save the generated unit in the file wDataMod.pas.
  3. Drop a TSQLConnection component (from the dbExpress tab of the Component Palette) to make a connection to the DB2 SAMPLE database (for more information on this procedure see my article on the same topic at http://www.ibm.com/developerworks/db2/library/techarticle/0210swart/0210swart.html). Select DB2Connection as the value for the ConnectionName property of the TSQLConnection component.
  4. Right-click on the TSQLConnection component to edit the Connection Properties. Make sure to specify a value for the Database (SAMPLE in this case) as well as the User_Name and Password fields (you wouldn't want your Web server application to display a login prompt on the Web server); see Figure 2 for details.
    Figure 2. Various connection settings of the WebSnap data module
    WebSnap Data Module
  5. Then, test the connection by setting the Connected property of the TSQLConnection component to True.
  6. Once you've verified that the connection can be made, drop a TSQLDataSet component on the form. Name the component SQLDataSetEMP, point its SQLConnection property to the TSQLConnection component, set the CommandType to ctTable, and select the EMPLOYEE table in the CommandText property.
  7. Next, drop a TDataSource component on the form, call it dsEMP, and connect its DataSet property to SQLDataSetEMP. You need this DataSource to build the master-detail relationships.
  8. Drop another two TSQLDataSet components-- call them SQLDataSetEMP_PHOTO and SQLDataSetEMP_RESUME -- and connect their SQLConnection properties to the SQLConnection component.
  9. Then, add this SQL query to the CommandText property of SQLDataSetEMP_PHOTO:
     select EMPNO, PICTURE from EMP_PHOTO WHERE (EMPNO = :EMPNO)

    This query uses a parameter called :EMPNO. Click on the ellipsis for the Parameter property of SQLDataSetEMP_PHOTO and specify ftFixedChar as the DataType for this parameter. In order to 'feed' the parameter with the current EMPNO field from the SQLDataSetEMP, the DataSource property of SQLDataSetEMP_PHOTO must point to dsEMP.
  10. Do the same thing with SQLDataSetEMP_RESUME, but this time use this SQL query:
  11. Now, go to the WebSnap tab of the Component Palette, and drop three TDataSetAdapter components onto the form (see Figure 3). Call them dsaEMP, dsaEMP_PHOTO, and dsaEMP_RESUME and connect their DataSet properties to the corresponding TSQLDataSet components. In order to specify (again) that dsaEMP_PHOTO and dsaEMP_RESUME are detail views of the dsaEMP DataSetAdapter, you must also specify dsaEMP as MasterAdapter for the dsaEMP_PHOTO and dsa_EMP_RESUME DataSetAdapters.

Figure 3. Components of the WebSnap data module
WebSnap Data Module


Back to top


Display the data in a WebSnap page module

It's time to display the data in a WebSnap Page Module.

  1. Select New->Other from the File menu, go to the WebSnap tab, and double-click on the WebSnap Page Module icon.
  2. In the new dialog (see Figure 4), select an AdapterPageProducer as Producer Type, and change the Page Name to Employees, before you click on OK to generate a new WebSnap Page Module.
    Figure 4. WebSnap page module
    WebSnap Page Module
  3. Press Alt+F11 to add the wDataMod unit to the uses clause (so you can use all TSQLDataSets in the WebSnap Page Module), and save the WebSnap Page Module in file pmEmployees.pas.
  4. Double-click on the AdapterPageProducer component to start the Web Page Editor.
  5. Right-click on the AdapterPageProducer node in the Web Page Editor, and pick New Component to add new Web Components, starting with an AdapterForm.
  6. Right-click on the AdapterForm and add an AdapterGrid component to show all employees. A design-time warning reminds you that you're not yet finished (see Figure 5).
    Figure 5. Employee page module at design time
    Employee Page Module
  7. Fix this design-time warning by pointing the Adapter property of the AdapterGrid component to WebDataModule1.dsaEMP -- the dsaEMP DataSetAdapter from the WebSnap Data Module.
  8. Delphi 7 now returns a second design-time warning: "dsaEMP: Dataset SQLDataSetEMP not active", caused by the fact that the TSQLDataSet components on the WebSnap Data Module are not opened yet. To ensure they are opened when needed - such as when the WebSnap Data Module is created - return to the WebSnap Data Module, and add this code to the OnCreate event handler:
      procedure TWebDataModule1.WebDataModuleCreate(Sender: TObject);
    begin
    SQLDataSetEMP.Active := True; // Connects to DB2 SAMPLE Database
    SQLDataSetEMP_PHOTO.Active := True;
    SQLDataSetEMP_RESUME.Active := True;
    end;

    It's always good to test things at design time. You can set the Active property of these SQLDataSets to True at design time to preview your data at design time. I would recommend resetting them to False when you want to deploy your project.

    When you set the Active property of SQLDataSetEMP to True, you get a third design-time warning: "dsaEMP: DataSet SQLDataSetEMP unknown keyfields". Avoiding this requires explicitly defining which of the EMPLOYEE table fields are to be used as keyfields (to uniquely identify records of this table in case it's required to locate this record in a find, edit, or delete operation).

  9. To fix this last design-time error, return to the wDataMod unit, double-click on the SQLDataSetEMP component to start the Fields Editor, right-click in the Fields editor and do "Add All Fields".
  10. In the list of fields, select EMPNO and set its pmInKey subproperty of the ProviderFlags property to True. This indicates that the value of the EMPNO field should be sent along whenever the application makes requests to the DataSetProvider. This makes sure the application can uniquely identify each record.
  11. Repeat steps 9 and 10 with the SQLDataSetEMP_PHOTO and SQLDataSetEMP_RESUME fields (set the pfInKeys of their EMPNO fields to True).


Back to top


Viewing the Employee data

And now, after fixing three design-time hints, we see data from the Employee table at design time (see Figure 6).


Figure 6. Employees page module with data at design time
Employees Page Module with Data at Design Time

There are too many fields to show in one big horizontal row in the grid, so right-click on the AdapterGrid component and select "Add Columns". This presents you with a dialog to select the columns you want to be displayed in the Employees overview, based on the fields in the original DB2 employee table, such as EMPNO, FIRSTNME, LASTNAME, HIREDATE, and JOB (see Figure 7):


Figure 7. Adding columns for display in the application.
Adding columns for display in the application.

Adding a detail view command
Apart from these five fields, I also want to have a special command button to go to a detail view of a specific employee, including the photo and resume:

  1. Right-click on the AdapterGrid again and select New Component.
  2. In the dialog, choose an AdapterCommandColumn. Right-click on the AdapterCommandColumn component, and select Add Commands to add the BrowseRow command.
  3. In order to indicate that BrowseRow produces a detail view, set the Caption property to Details, and in the PageName property specify "Employee" (without the ending "s") to indicate the name of the forthcoming WebSnap Page Module that will contain the Employee Detail view. Figure 8 shows the design-time view of the Web Page Editor:
    Figure 8. Employees page module with data and button at design-time
    Employees Page Module with Data and Button at design-time.


Back to top


Adding the employee detail view

Time to add the Employee Detail View.

  1. Do File->New->Other, go to the WebSnap tab, and double-click on the WebSnap Page Module icon again.
  2. Select an AdapterPageProducer again, specify Employee as name (without the "s"), and this time uncheck the Published option (so the Page Module won't be visible in the WebSnap menu).
    Figure 9. New WebSnap page module
    Employees Page Module with Data and Button at design-time.
  3. Press Alt+F11 to add the wDataMod unit to the uses clause, and save the WebSnap Page Module in pmEmployee.pas.
  4. Double-click on the AdapterPageProducer to start the Web Page Editor.
  5. Right-click on the AdapterPageProducer in the Web Page Editor and add a new AdapterForm.
    • Under the AdapterForm, first add a LayoutGroup and set its DisplayColumns property to 2.
    • Under the LayoutGroup add three AdapterFieldGroups, resulting in three design-time warnings. Assign the Adapter property of the first AdapterFieldGroup to WebDataModule1.dsaEMP, the second one to WebDataModule1.dsaEMP_PHOTO, and the last one to WebDataModule1.dsaEMP_RESUME.
    • To further customize the output, you can right-click on the AdapterFieldGroups to add only those fields you want to see (like only the PICTURE and RESUME, without the additional EMPNO field again; see Figure 10.

Figure 10. Browsing Employee page module at design time
Browsing Employee Page Module at design time.


Back to top


Testing your app at runtime

For a runtime test:

  1. Close all Web Page Editors, return to the WebSnap Data Module, and close all SQLDataSets.
  2. Save all files, compile, and run the project. This will show the empty form to indicate that the Web App Debugger project is running.
  3. Select Web App Debugger from the Tools menu to start the Web App Debugger itself. Click on Start to activate it, and then click on the URL on the right of the Start button to start the default browser. This displays a list of Web App Debugger executables on your machine.
  4. Select the completed project (CGI.eBob42) and click Go to run it.

In the first page, you only see the WebSnap menu (with Home and Employees), but if you click on the Employees menu the page with the grid of employees is shown (similar to Figure 10, but now at run time). Only a few employees have a corresponding picture and resume in the DB2 sample database, for example, such as Bruce Adamson.

If you click on the Details button of employee Bruce Adamson, you get the Employee detail view of Figure 11 (note that the formatted memo is now shown as plain text inside the browser -- the formatting itself is lost).


Figure 11. Employee page module in action!
Employee Page Module in Action!

You can always click on the Employees page to view the full grid of employees and select another employee again.



Back to top


Deployment

For real-world deployment, you cannot use the Web App Debugger executable, but must use a CGI executable, ISAPI/NSAPI, or Apache DLL. Fortunately, it's easy to reuse the work that you've done so far: just start the Project Manager and create a new WebSnap application (for example, for a CGI target), and remove the new empty page module.

Inside the Project Manager, drag the WebSnap data modules (just one) and Page Modules (three -- pmHome, pmEmployees, and pmEmployee) to the new project. Everything except for the Web App Debugger form, which is of no use in a Web server application. Now you can recompile and deploy the new target (together with the three generated .html files for the Page Modules) on the Web server machine that has access to the DB2 sample database. The DEPLOY.TXT file in the Delphi directory contains two additional WebSnap files that you need to deploy and register as well.

Finally, inside the OnCreate event handler of the WebSnap Data Module, you can add the same few lines of code (click here for an article on this topic) to make sure the SQLConnection component compiles with Kylix and can be deployed on a Linux Web server.




Back to top


0 Comments: