Creating New Applications

Reusing Legacy Code

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:

• Query
• Update
• Add
• Delete

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

You can generate two different views onto the database:

• Single-record view

  A form which shows each column in the query in its own entry field. This is the only view from which an end-user can actually update the information in the database.

• List view

  Shows a group of records on the screen in a table. You can decide how many records to display at a time when generating the application. The default is ten.

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:

• Customer query and update
• Order query and update

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 Net Express (if there is one). The Internet Application Wizard adds all files generated to the current Net Express 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 Net Express 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	\Program Files\MERANT\Net Express\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:

• Form view:
  • customerform.cbl - source file with SQL code
  • customerform.cpf - copyfile with variables for form controls
  • customerform.cpy - copyfile with variables for business logic
  • customerform.cpv - copyfile with code to convert between business logic and form control variables
  • customerform.htm - HTML file for the form
  • customerform.mff - Form Designer file for editing the form
• List view:
  • customerlist.cbl - source file with SQL code
  • customerlist.cpf - copyfile with variables for form controls
  • customerlist.cpy - copyfile with variables for business logic
  • customerlist.cpv - copyfile with code to convert between business logic and form control variables
  • customerlist.htm - HTML file for the form
  • customerlist.mff - Form Designer file for editing the form

Notes:

• 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 Net Express online help index.

• When you're generating an application to access a database, if a table has primary keys or a unique index defined, the columns specified in the key or unique index must be used by the generated application. If you do not select them, the Internet Application Wizard includes them and displays a dialog box telling you it has done so

• Avoid using the following characters in the field captioned Please enter a name for the generated files: % # ( ) @. Filenames containing these characters can cause problems for Web browsers and for Net Express.

• Avoid using the following characters in the field captioned Please enter a name for the generated files: % # ( ) @. Filenames containing these characters can cause problems for Web browsers and for Net Express.

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:

• The Customer ID currently on the form
• An instruction to filter by Customer ID

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:

• Add a pushbutton to customerform.htm form, and use the Script Assistant to add JavaScript to make it start orderlist.exe through a hyperlink.

  This link will also send Customer ID and instructions to OrderList to make it filter by customer.

• Add a dummy entry field to orderlist.htm to accept the Customer ID.

  When you save orderlist.htm, Form Designer regenerates all the form variables and code used by orderlist.cbl to accept data from the form. Because data is passed to the server-side program as name/value pairs, the server-side program accepts data sent to it when started through the hyperlink in the same way as data submitted to it by form. By adding a dummy entry field with the same name as the parameter sent through on the command line, you automatically generate all the code needed to accept the customer ID. You will use a hidden field as the dummy, as this isn't displayed on the form.

To edit customerform.htm:

1. Double-click customerform.htm on the Net Express 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:

• An extra condition to the EVALUATE statement, to pick up an action of "cquery".

• A new section to set up the variables needed for a SQL query, filtering by CustID.

  Normally, these are set up by the orderform.exe server-side program, and stored in the state file. But when orderlist.exe is started from the customerform, none of this data is set up.

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:

For more information, consult the Generated CGI Code Reference. Click Help Topics on the Net Express 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.

Creating New Applications

Reusing Legacy Code