PreviousCreating New Applications Reusing Legacy CodeNext

Chapter 5: Data Access Applications

This chapter describes how you can create a working SQL application in minutes with the Internet Application Wizard. It also explains how you can extend an application once it has been created.

5.1 Overview

The Internet Application Wizard can generate all the HTML forms and server-side code to access an SQL database. The wizard uses the Open ESQL Assistant to generate the code for a SQL query, which it embeds in a server-side program. A data access application generated by the Internet Application Wizard can give the end-user access to any of the following database functions:

You decide when you are creating the application which functions to make available.

You can generate two different views onto the database:

Each view is handled by a separate form and server-side program. The server-side programs generated are symmetric, using the same form for input and output.

When you generate an application which has both a single record view and a list view, the two views link to each other. The form for a single record has a pushbutton to display the list view, and each record displayed on a list view includes a hypertext link to display the single-record view.

For more information about COBOL and SQL applications, please see the online book Database Access.

5.2 Creating a Data Access Application

The easiest way to understand how to create data access applications with the Internet Application Wizard is to try it. What we will do is use the Internet Application Wizard to create two programs. One queries a table of customer information, to provide customer details, and the other queries an order table, to get order details.

Having generated the two basic programs, with all the ESQL code needed to access the data, we will modify them to create an order processing system. This involves linking from a customer record directly to the orders for that customer. This shows you not only how you can generate simple database access applications using the Internet Application Wizard, but how you can customize and extend an application which has been generated for you.

5.2.1 Generating an Application

In this section you will generate two simple applications:

The customer query enables the end-user to query the customer information in the database. The order query and update enables the end-user to retrieve and update orders.

Customer Query

Before you start, close the currently open project in NetExpress (if there is one). The Internet Application Wizard adds all files generated to the current NetExpress project. We want all the files for this application held in a single separate project; if no project is open, the Internet Application Wizard prompts you for the name and location of a new project.

The instructions in this chapter are not at the detailed level of "now click the OK button". They explain the overall process. If you want to get a feel for Form Designer and NetExpress before you start, run the sample sessions in Getting Started.

To generate the Customer Query:

  1. Start the Internet Application Wizard (click New on the File menu, and select Internet Application from the New dialog box).

  2. Fill in the following information to create a new project, then click the Next button:
    Project name orders
    Project path \netexpress\base\workarea\orders

  3. Select SQL Database as the Wizard Input and press Next.

  4. Fill in the following information on the wizard, then click the Next button:
    Base name for files customer
    Title for generated forms Customer Details

    The Internet Application Wizard generates the application. The wizard prompts you if any of the files it is going to create already exist, so that you can choose to keep the file or overwrite it.

  5. Double-click NetExpress Sample2 on the Data Selection dialog box, and then double-click to select the Customer table. Right-click on the Customer table, and click Select All Columns.

    If you want to see the results of the query, click on the Run Query button to see the results.

  6. Click the Next button to display the Application Style dialog box. From this dialog box, you can select which different types of view onto the data you want.

  7. Select the following options, then click the Next button:
    • Display data in single-record view
    • Display data in table view
      • Hot-link items in list to single-record view
      • Enable paging through 10 records per page

  8. The Form Editing dialog box determines whether users of the application can update the database. Click the Yes radio button, and select the Insert, Update and Delete check boxes. Then click the Next button.

  9. On the final dialog box, ensure that both the Save settings as default and Add files to NetExpress project check boxes are selected, then click the Finish button.

The Internet Application Wizard generates the following files:

Note: Two other files (sqlca.cpy and sqlda.cpy) are added to the project. These files are standard copy files for Embedded SQL and you should not need to edit them. For more information, look up sqlca and sqlda in the NetExpress online help index.

At this point you have a complete application which you can run to query the database.

Note: If you decide to regenerate the application with a different SQL query, delete the .cbl files first. To avoid losing any changes you make to the business logic, the generator does not overwrite existing .cbl files. If they aren't present, they are regenerated from scratch.

5.2.2 Running the Basic Application

You can now build and run the application:

  1. Click Rebuild on the Project menu.

  2. Click Run on the Animate menu.

  3. In the Start Animating dialog box, change the executable name to:

    HTTP://127.0.0.1/cgi-bin/customerlist.exe

This runs the customer list server-side program, and displays the first 10 records in the customer database. You can page through 10 records at a time by clicking the < or > buttons, and to the beginning or end by clicking the << or >> buttons.

To see a single customer record, click the CustID in the left-hand column. This displays the Form View. From the Form View, you can modify the database, and also change filtering options.

For example, to see all the customers in New York state:

  1. Click the Clear screen button.
  2. Type NY in the Region field.
  3. Drop-down the Filter by list, and select Region.
  4. Click the Query button to see the first record for customers in New York.

    You can either page through them one at a time, or click Table View to display them in list form. If you use the Table View, you can see its status line has changed to indicate that filtering is now by Region.

To add a record:

  1. From the Form View, click the Clear screen button.
  2. Enter some customer details.
  3. Click the Insert record button.

    The form should be redisplayed, with a status line telling you that the record was successfully inserted.

To update a record:

  1. Change some data on the form (for example, the address).
  2. Click the Update record button.

    The form should be redisplayed, with a status line telling you that the record was successfully updated.

To delete a record:

  1. From the Form View, click the Delete Record button.

5.2.3 Generate a Second Form Set

Now let's generate a second form set, to query and update order records.

  1. Start the Internet Application Wizard.

  2. Select SQL Database as the Wizard Input and press Next.

  3. Fill in the following information on the wizard, then click the Next button:
    Base name for files order
    Title for generated forms Order Details

  4. Double-click NetExpress Sample2 on the Data Selection dialog box, and then double-click to select the Orders table. Right-click on the Orders table, and click Select All Columns, then click Next.

  5. On the Application Style dialog box, select both single-record and table-view forms, and set the paging options to10 records per page (as before).

  6. On the Form Editing dialog box, give users full access to add, update and delete records, as you did before.

  7. Press Finish on the last page of the wizard.

    The Internet Application Wizard generates a second set of files, for displaying order information.

  8. Rebuild the application

  9. Run the form view for this application.
    • Set the Filter by control to CustID
    • Click the Clear screen button
    • Enter MERRG in the CustID field and click Query.
    • Then click Table view to see a list of the orders for this customer.

In the next section, we are going to modify this application to connect the Customer and Order query forms together.

5.2.4 Modifying the Application

The server-side programs which access the database are written in COBOL, so you can edit them to add extra functionality. And you can edit the HTML forms using Form Designer. In this section, we will show you how to modify the example application.

Currently you have two pairs of forms; one queries the database for customer information, the other queries the database for order information. We are going to add a pushbutton on the customer query form that links you directly to the list of orders for that customer.

The new pushbutton starts the orderform.exe server-side program, and passes it the CustID for the customer currently displayed.


Figure 5-1: Linking the customer query to the order list

Adding this new functionality means modifying the forms and the server-side programs. The next two subsections show you how to do this.

5.2.4.1 Changing the Forms

You need to provide a link on the Customer Query form, which runs the orderlist.exe server-side program. You also need to pass the following information to orderlist.exe:

You can pass information to a server-side program through the link that starts it. The information is passed as name/value pairs, in the same way as the data from the controls on a form. You can make all the changes needed to pass the information across by editing customerform.htm and orderlist.htm with Form Designer.

The changes you need to make are:

To edit customerform.htm:

  1. Double-click customerform.htm on the NetExpress project window to start Form Designer and load the form.

  2. Drop an Input Button on the form (use an Input Button - not a Submit button which will always rerun customerform.exe).

  3. Change the caption on the button to Orders.

  4. Right-click the Input Button, and click Events on the context menu.

  5. Select the onclick event for the Orders Input Button and click New Handler.

  6. Enter the following string between the curly braces in the bottom pane: 
    window.location.href="orderlist.exe?CustID=:A-CustID&Action=cquery"

    Note: The control names, CustID and Action, are case sensitive. Enter them exactly as they are printed here.

    This is equivalent to setting a hypertext link of orderlist.exe?CustID=:f-A-CustID&Action=cquery each time the pushbutton is pressed. The first part of the link, orderlist.exe, is a request to the server for resource orderlist.exe. The question mark indicates that a list of name/value pairs follows. The ampersand (&) is a separator between name/value pairs.

    • CustID=:A-CustID sends through a parameter with the name CustID, and the value :f-A-CustID. This form is output by the customerform.exe server-side program using EHTML, and :f-A-CustID is an EHTML substitution marker which is replaced by the value in COBOL data item f-A-CustID when the form is output. COBOL data item f-A-CustID is used by the COBOL program to set the value of the CustomerID field, so this sets an HTML parameter named CustID to the value of the Customer ID.

      How do we know which COBOL variable is used to set the value of a form field? Click the field for the CustID on the form. Its Name property is set to A_CustID. This generates two variables in the server-side program: A-CustID and f-A-CustID (underscores are converted to hyphens because underscores are not legal in COBOL data names). The variable with the same name as the control is the one you manipulate inside the COBOL program, and has the picture string set by the COBOLPicture property for the control.

      The variable prefixed with "f-" is the one used to move data between the form and the COBOL program, and always has a PIC X(n) picture string - because form data is always a string literal. The generated code always includes a set of routines to move the data between the two data items on input and output, together with any conversions needed.

    • Action=cquery sets an HTML parameter named Action to the value "cquery".

      All the Submit buttons on orderlist.htm have the Group name property set to Action. The Action variable is passed to the server-side program with the value of the Submit button which was clicked to run the program. When orderlist.exe is started by this link, it checks the value of COBOL data item Action to see which button was pressed. This code makes it think that a button with the value of "cquery" was pressed on a form; we can add code to orderlist.cbl to get it to run a query filtering on Customer ID when the action is set to "cquery".

  7. Click OK to close the Script Assistant

  8. Save your changes.

To modify orderlist.htm:

  1. Load it into Form Designer.

  2. Drop a Hidden field control anywhere on the form, and set the Name property to CustID. Set the COBOLPicture property to PIC X(5) - this is the picture property for the CustID field on customerform.htm.

    When you save the form, this sets up a data item called CustID, together with the code to read its value from a variable called CustID.

  3. Save the changes to the form.

    This regenerates not only the form, but all the code in the associated server-side program (orderlist.cbl) for input and output of form data, so that a value for CustID is read into COBOL variable CustID if present.

5.2.4.2 Modifying the Server-side Program

You now need to change the server-side program orderlist.cbl. Two things must be added to this program:

To edit the server-side program:

  1. Open orderlist.cbl in a text editing pane.

  2. Find the PROCEDURE DIVISION header, and the EVALUATE statement a few lines below it.

  3. Add a new condition to the EVALUATE statement, just above the WHEN OTHER clause: 
    when "cquery"
    perform DoQueryByCustomer

  4. Now you need to add the code to set up the program to filter by CustID. Add the following section to the program, somewhere after the Main Section: 
     DoQueryByCustomer section.
     move "A.CustID" to s-filter-field *> The application logic 
                                       *> is case sensitive
                                       *> Enter "A.CustID" in same
                                       *> case as shown here
     move "=" to s-filter-op
     move custID to filter-A-Custid 
     move "??" to search-op
     move low-values to sort-spec
     perform Do-SQL-Query
     perform SQL-Open
     perform Next-DataTable
     if no-data
         perform setup-status
         string
            " - no data to display" delimited size
            into frmesStatus pointer stat-index
     end-if
     exit.

  5. Save your changes.

The code above sets up variables so that Do-SQL-Query builds a query to search the database filtering on the CustID field. The variables have the following meanings:

Variable
Description
s-filter-field The column to filter on. The value is "A.columnname". The "A." prefix is to allow for future enhancements in which you can construct queries which join tables
s-filter-op The matching criteria. You can set one of the following: "=", ">=" ">" "<=" "<" or "!="
filter-A-CustID The value of CustID to filter on. There is a filter field corresponding to each column in the query you set up through the data wizard. The filter fields are named "filter-A-columnname".
search-op The direction of the search. "??" is an instruction to find the first match. "?>" and "?<" find the next and previous matches. The next and previous matches are defined by the column for the search order, and the contents of the state record for the application.
sort-spec Specifies the ordering for the table. Set to either DESC or spaces.

For more information, consult the Generated CGI Code Reference. Click Help Topics on the NetExpress Help menu, then on the Contents tab. Select Reference, Generated CGI Code, Data Access CGI.

5.2.4.3 Running the new application

You can now run the modified application:

  1. Click Rebuild on the Project menu to recompile the changed programs.

  2. Click Run on the Animate menu.

  3. Change the field in the Start Animating dialog box, and click OK: 
    http://127.0.0.1/cgi-bin/customerform.exe

    Your Web browser displays the customer form view.

  4. You can query and update customer details using this form, as you could before.

  5. When you want to see the orders for a particular customer, click the Orders button.

    This displays the list of orders for the selected customer.

Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
PreviousCreating New Applications Reusing Legacy CodeNext