Tutorial: Provide Additional Code

Our application will not compile and run unless we provide code that declares our table, provides a COBOL record to receive the data, and provides a COBOL record for indicator variables. We can use OpenESQL Assistant to generate a copybook that contains all of this code and an INCLUDE statement to call the copybook. We can also use OpenESQL Assistant to embed the INCLUDE statement into the program.

We want to send the output from the application to the console in a readable format. This requires additional code we enter into the program manually.

Requirements

Before attempting this tutorial, you must complete the following tutorials in the order listed:

• Tutorial: Create a SQL Server Database
• Tutorial: Create and Configure an Eclipse Project
• Tutorial: Set OpenESQL Assistant Configuration Options
• Tutorial: Catalog a Connection
• Tutorial: Build and Test a Query

Phase 1: Generate a Copybook

We can generate a copybook using the Host Variable Declarations feature on the Auxiliary Code tab of the OpenESQL Assistant.

1. From the OpenESQL Assistant tree view, right-click the EMP table; then select Generate DCLGEN from the context menu.

   OpenESQL Assistant generates the copybook and brings up the Generate Host Variable Declarations dialog box, giving you the opportunity to name it. The default name of the copybook is tablename.cpy. In this case, the name of the generated copybook is emp.cpy.

2. Save emp.cpy to the default folder, which is your current Eclipse project folder.

   On the Auxiliary Code tab, you see that OpenESQL Assistant has also generated an INCLUDE statement to call the copybook from the program.

The following shows the code generated for the copybook:

       *> -------------------------------------------
       *> COBOL HOST VARIABLES FOR TABLE EMP
       *> -------------------------------------------
       01  EMP-EMPNO                          STRING.
       01  EMP-FIRSTNME                       STRING.
       01  EMP-MIDINIT                        STRING.
       01  EMP-LASTNAME                       STRING.
       01  EMP-WORKDEPT                       STRING.
       01  EMP-PHONENO                        STRING.
       01  EMP-HIREDATE                       type System.DateTime.
       01  EMP-JOB                            STRING.
       01  EMP-EDLEVEL                        BINARY-SHORT.
       01  EMP-SEX                            STRING.
       01  EMP-BIRTHDATE                      type System.DateTime.
       01  EMP-SALARY                         DECIMAL.
       01  EMP-BONUS                          DECIMAL.
       01  EMP-COMM                           DECIMAL.
       *> -------------------------------------------
       *> COBOL INDICATOR VARIABLES FOR TABLE EMP
       *> -------------------------------------------
       01  EMP-WORKDEPT-NULL                  BINARY-SHORT.
       01  EMP-PHONENO-NULL                   BINARY-SHORT.
       01  EMP-HIREDATE-NULL                  BINARY-SHORT.
       01  EMP-JOB-NULL                       BINARY-SHORT.
       01  EMP-EDLEVEL-NULL                   BINARY-SHORT.
       01  EMP-SEX-NULL                       BINARY-SHORT.
       01  EMP-BIRTHDATE-NULL                 BINARY-SHORT.
       01  EMP-SALARY-NULL                    BINARY-SHORT.
       01  EMP-BONUS-NULL                     BINARY-SHORT.
       01  EMP-COMM-NULL                      BINARY-SHORT.

Phase 2: Embed the INCLUDE statement

We need to embed the generated INCLUDE statement into our program.

1. From the Auxiliary Code tab, click Add Query to temporary file.
2. In the program editor for Program1.cbl, place the cursor on the line just after the EXEC SQL INCLUDE SQLCA statement.
3. Right-click; then select Paste.

   OpenESQL Assistant inserts the query into the program.

4. Save the OESQLAssistantTutorial project. Eclipse automatically rebuilds the project.

Phase 3: Add Code to Format Output

When we run our program, we want to be able to see the results of the query as easily as we see them when we run the query in the OpenESQL Assistant. To achieve this, we need to add some code to the program that formats SALARY data, and code that sends all data to the console in a readable format.

1. With Program1.cbl showing in the source code editor, type the following working-storage record after the EXEC SQL INCLUDE EMP END-EXEC statement:

   01  EDIT-PAY     PIC Z,ZZ99.99.

2. Scroll down to the following IF statement:

   IF SQLSTATE < "02000"

3. Place your cursor on the next line down and insert the following MOVE statement:

   MOVE EMP-SALARY TO EDIT-PAY

4. Replace the following DISPLAY statement:

   DISPLAY 'ROW FOUND'

   with

   DISPLAY 'NAME: ' EMP-LASTNAME ', ' EMP-FIRSTNME '   SALARY: ' EDIT-PAY UPON CONSOLE

5. Save the OESQLAssistantTutorial project. Eclipse automatically rebuilds the project.

This completes the tutorial. Next, please complete Tutorial: Build and Run the Application.