PreviousCreating a Web Application from a COBOL Application Creating a Windows GUI ApplicationNext

Chapter 11: Creating a Web Database Application

You use Internet Application Wizard to create a Web interface to an existing database.


You need to have read the chapter Start Here for the Tutorials, worked through the first session Using NetExpress, and read the chapter Introduction to Web Applications, before you do this session.


11.1 Overview

You use Internet Application Wizard to create forms and server-side programs so you can access an existing database via the Web.

The database must have been created with a database system for which Open Database Connectivity (ODBC) support is available. This includes most major databases, such as Oracle, Sybase, Informix, DB2, Microsoft SQL (pronounced "sequel") Server, and Microsoft Access.

Using Internet Application Wizard you don't need to do any form design or programming yourself. The Wizard looks at the database structure and creates the forms and server-side programs for you. You just set some options about what kinds of operation are allowed.

The application generated can both query and - if you choose this option - update the database. You can still use your original database tool to query and update the database too.

NetExpress includes several sample databases, including a Microsoft Access database containing details of a wholesaler's customers. The customers are retail outlets. In this session, you create a Web application to query and update this database.

The database is accessed via the data source name (DSN) NetExpress Sample2, which represents the sample Microsoft Access database d:\NetExpress\Base\Demo\SMPLDATA\access\sample.mdb. (For details of ODBC and how DSN's are assigned, see the help for your ODBC Administrator, accessible via the Control Panel on your Windows desktop.)

11.1.1 Terminology

In this session, when talking about the database we will keep to database terminology and use the terms row and column. We will use the corresponding terms record and field when data is displayed in the forms on the Web browser.

11.2 Preparation

If you have closed NetExpress, open it as before. If any project window or text windows are open, close them.

It doesn't matter whether you initially have your Web browser and/or Solo running.

If you're reading this book in your Web browser, you should start a new instance of your browser (use the New function on its File menu) so that you can still see the book during this chapter.

11.3 Sample Session

In this sample session you:

  1. Create the project, forms, and programs
  2. Build the application
  3. Run the application
  4. Navigate the database
  5. Update the database
  6. Filter the database

11.3.1 Creating the Project, Forms, and Programs

You could start by creating a project in the way described in the chapter Using NetExpress, but if you go straight ahead and create the application NetExpress will give you an opportunity to create the project. To create the application:

  1. Click New on the File menu, then select Internet Application on the New dialog box and click OK.

    The first page of the Internet Application Wizard appears. On this page you choose which of the three methods of creating an application, as described in the chapter Introduction to Web Applications, you wish to use.

  2. Select Full Application (HTML Client and Server Program), and in the lower part of the dialog box select SQL Database.

  3. Click Next.

  4. Click Yes on the message asking if you want to create a project.

  5. Make sure Empty Project is selected (it's the default, so it should be). Enter Daw as the name of the project, and d:\NetExpress\Base\Demo\Daw as the folder to contain the project, then click Create.

  6. Click Yes when asked whether or not you want to create this directory. (If this session has been run previously, you will instead be asked whether to overwrite the existing project. Click Yes.)

  7. Enter Retail as the basename for all the files to be generated, and Retail Outlets as the title to appear on all the forms, then click Next.

    The page that now appears lists the data sources on your computer. At the right are a number of tabs. Make sure the Query tab is at the front. (It should be, as it's the default.)

  8. Double-click NetExpress Sample2.

    The list expands to show the tables in this database.

  9. Double-click Customer.

    The list expands to show the columns in the table called Customer.

    A SQL (pronounced "sequel") SELECT statement is generated and displayed on the right. SQL is a language for querying databases. NetExpress includes a feature called OpenESQL, a compiler preprocessor that enables SQL to be included in COBOL source. You don't need to know SQL to use the Internet Application Wizard, as the Wizard generates it for you.

    The statement is not yet complete. It will be completed in the next step, and then used as the basis for the SQL statements included in the COBOL source program that the Internet Application Wizard generates.

  10. Right-click Customer in the tree view and click Select All Columns on the popup menu.

    Checkmarks appear by all the column names, and the statement in the Query field is completed. This statement will cause all columns from the Customer table to be displayed.

    Although you are creating this query for inclusion in a COBOL program, you can run it now, interactively, as follows.

  11. Click Run Query (near the top left of the Internet Application Wizard dialog box).

    The Results tab comes to the front, displaying all the columns from all the rows in the Customer table.

    The SQL statements being generated for inclusion in the source program are equivalent to this one, but they also handle selection criteria that can be specified at run time by the user, to select which rows - that is, which customers' details - will be displayed. These statements will cause data from the table to be read into the program's Working-Storage Section, from where other COBOL statements will move them to the Web form.

    This dialog box is also used by another facility in NetExpress, the OpenESQL Assistant. You use this facility to create SQL queries either to run interactively or to include in a COBOL source program that you write yourself.

    To learn how to create more complex queries than the one we've just created, after finishing this book you should read the tutorial on the OpenESQL Assistant in the online book Database Access.

  12. Click Next.

    On the page that now appears you specify what Web forms you want your application to have.

    Cross-platform specifies the method NetExpress uses to ensure objects in your positional form are positioned as you wish. Cross-platform forms use HTML tables for this. In Dynamic HTML, style attributes are used. HTML Tables are supported by more browsers. Dynamic HTML offers more exact positioning, but is only supported by Internet Explorer 4.0. Remember your forms will be seen by users with different browsers. We will accept this default.

    There are two standard forms. The single-record view (also known as simply "record view") form displays the data from one row of the table; in our case, one customer's details. The table view form displays the data from several rows, in a tabular layout. We will accept the default, which is to have both forms.

  13. Click Next.

    On the page that now appears you specify whether your user can only query the database, or can update it too. We will accept the default, which is to let the user update it.

  14. Click Next.

    The page that now appears shows a summary of what you have selected.

  15. Click Finish.

    The Internet Application Wizard generates the forms and programs, and finishes. The names of the generated files are added to the project, then the project is scanned for dependencies. Internet Application Wizard then closes.

11.3.2 Files Created

You have now created your user interface. The Internet Application Wizard has created the following files for you and added them to the project (you may have to expand the tree view in the project view by clicking the "+" signs to see them all):

The following files are shown in the right-hand pane only:

It has also added to the project the following object files, created when you build the project:

It has also created several copyfiles (extensions .cp*). Their purpose is described in comments in the .cbl file.

Although the filenames are shown above in lower case, some might appear in upper case in the project. Filenames are not case sensitive.

If you ever want to update the forms, you simply double-click on retailform.htm or retaillist.htm in the project window, and Form Designer opens. When you save the updated form, the copyfiles are regenerated, so you should not edit them directly. You can however edit the .cbl programs that the Wizard generated.

11.3.3 Building the Application

To build the application:

  1. Click Rebuild All on the Project menu.

    NetExpress saves and compiles the programs, and builds the executable files. Wait until the message "Rebuild Complete" appears in the Output window before continuing.

11.3.4 Running the Application

To run the application:

  1. Click Run on NetExpress's Animate menu.

  2. Click OK on the Start Animating dialog box.

    This starts Solo and your Web browser, and then runs the program Retailform. The program displays its form, the single-record view form retailform.htm. The IDE is automatically minimized.

    Initially, the form shows the customer details from the first row in the table, the one with CustID = ALWAO.

    Depending on the font size in your Web browser, you may not always see all five characters of the Customer ID in the record view form. For example, ALWAO may appear as ALWA, or MERRG as MERR. You will see the full Customer ID if you scroll the CustID field sideways by clicking in it and using the left and right arrow keys.

  3. Click Table View at the top right of the form.

    The table view form retaillist.htm is loaded into the browser. It shows the first ten customers in the table, ALWAO through CONSH.

  4. Click Record View at the top right of the form.

    The single-record view form retailform.htm is loaded again.

11.3.5 Navigating the database

Both the forms have standard controls for moving backward and forward through the table. The record view form retailform.htm also has functions for updating and querying the table. Some of these are optional when you are creating the forms - we accepted all the defaults to have them all included.

In this section we'll try out some of these standard controls. Make sure you still have the single-record view form displayed. The Order By field has defaulted to CustID. This causes rows from the table to appear in the order of CustID.

You may need to make your Web browser full screen to see the displayed forms properly.

  1. Click > at the top of the form.

    The form shows the next customer in the table, the one with CustID = ANDRC.

  2. Click Table View.

    The table view form is loaded, showing ten customers starting from ANDRC.

  3. Click >>.

    The form shows the end of the table, that is, the last ten customers TOPOF through WITAE.

  4. Click >.

    The form is redisplayed, but it has not changed. In table view, if you try to go beyond the end of the table, you don't move.

  5. Click <<.

    The form shows the beginning of the table, that is, the first ten customers ALWAO through CONSH.

  6. Click >.

    The form shows the next ten customers EASTC through HANOP.

  7. Click Record View.

    The record view is loaded, showing the customer EASTC.

  8. Click >>.

    The form shows the last customer in the table, WITAE.

  9. Click >.

    The form shows the first customer in the table, ALWAO. In record view, if you try to go beyond the end of the table, you go back to the start of the table.

  10. Type "BE" in the CustID field, replacing ALWAO which is currently there, and click Query.

    The form shows the first customer whose CustID is alphabetically greater than or equal to what you put in the CustID field. It is customer BERGS.

  11. Click Table View.

    The table view is loaded, showing ten customers starting from BERGS

  12. Click EASTC in the first column of customer EASTC.

    The single-record form appears showing customer EASTC.

11.3.6 Updating the Database

In creating the application, we accepted the default, which is to let the user update the database. You use the single-record view form to insert, delete, or update records.

  1. In the Address field, after "35 King George" add "Street"; then click Update record at the bottom of the form.

    The record is redisplayed.

  2. Click Table View.

    The table view is loaded again, and you can see that the database has been updated with the change you made.

11.3.7 Filtering the Database

You can use the fields at the top of the record view form to filter out records you don't want to see, and to specify which field you want records ordered on.

To display only customers in Canada, displaying them in order of city:

  1. Click Record View.

    The record view form appears. You could now click Clear Screen to clear the customer data fields before setting the filter, but it's not necessary - only the fields you specify will be looked at anyway.

  2. Using the drop-down lists, set the Order By field to City, the Filter Type field to = (the default), and the Filter By field to Country.

  3. Type Canada in the Country field, replacing what is currently there, and click <<.

    The form shows the first (in order of city) customer in the table who is in Canada. This is MERRG, in East Vancouver.

    The Filter By field has automatically changed to "(existing)". This ensures that in the next few steps (until you change this field) the set of rows already selected will be used - these steps just change the position in the table, they don't set a new filter.

  4. Type T in the City field, replacing what is currently there, and click Query.

    The form shows the first (in city order) Canadian customer in a city whose name is greater than or equal to "T". This is BOTTM, in Tsawassen.

  5. Click Table View.

    The table view form is loaded. It now displays, in city order, Canadian customers from the one in Tsawassen onward. There are three of them.

    Now let's set a new filter. To do this you will change the Filter By field from "(existing)" to a field-name again.

  6. Click Record View.

  7. Change the Filter By field to Country, then type UK in the Country field and click <<.

    The form shows the first (in city order) UK customer, ISLAT in Hedge End.

  8. Click Table View.

    The table view form is loaded. It now displays, in city order, the first ten UK customers.

    Now let's get the full table back, and get it in order of Customer ID again.

  9. Click Record View.

  10. Change the Order By field to CustID and the Filter By field to (none), and click <<.

    The form is redisplayed showing the first (in order of Customer ID) customer in the table.

  11. Click Table View.

    The table view form is loaded showing the first ten customers. You've got the full table displayed again, in order of Customer ID.

  12. Restore the minimized IDE, and click Stop Animating on NetExpress's Animate menu.

11.4 Before Continuing

Close the project.

Close Solo, by right-clicking on the Solo icon in the taskbar tray and clicking Exit on the popup menu.

If you opened a second instance of your Web browser to display the forms, close it.

If you're planning to go straight on to another session, you can keep NetExpress open.

Return to the Tutorials Map in the chapter Start Here for the Tutorials and choose which session to go on to next, depending on your interests.


Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
PreviousCreating a Web Application from a COBOL Application Creating a Windows GUI ApplicationNext