Generate and INCLUDE the EMP.cpy Copybook

Walks you through the steps required to use the OpenESQL Assistant to generate copybook that contains the table declaration and appropriate data structures, and an EXEC SQL INCLUDE statement to call the copybook from the program.

Before you can compile and run your new program, you need to add code that declares the table, provides a COBOL record to receive the data, and provides a COBOL record for indicator variables. You can use OpenESQL Assistant to generate a copybook that contains all of this code and an INCLUDE statement to call the copybook. You can also use the OpenESQL Assistant to embed the INCLUDE statement into the program.

Generate the EMP.cpy copybook

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

From the OpenESQL Assistant tree view, click the EMP(dbo) table to select it; then right-click and select Generate DCLGEN from the context menu.
OpenESQL Assistant brings up the Generate Host Variable Declarations dialog box, giving you the opportunity choose a path for the generated file and provide a name.

The default name of the copybook is tablename.cpy, which in this case is EMP.cpy.
Navigate to your OESQLAssistantTutorial project directory, and save EMP.cpy there.

The following shows the code generated in the EMP.cpy 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.

Embed the EXEC SQL INCLUDE statement into the program

On the Auxiliary Code tab, you should see that OpenESQL Assistant has also generated an INCLUDE statement to call the copybook from the program. You need to embed the generated INCLUDE statement into your program.

From the Auxiliary Code tab, click Add Query to temporary file.
In the COBOL Editor, place the cursor on the line just after the EXEC SQL INCLUDE SQLCA statement, in the same column.
Right-click; then select Paste.
The INCLUDE statement is inserted into the program.
Click File > Save to save Program1.cbl.
Save the OESQLAssistantTutorial project. Eclipse automatically rebuilds the project.
Close the OpenESQL Assistant.

Add format code

When you run this application, you want to be able to see the results of the query as easily as you can see them on the Results tab in the OpenESQL Assistant. To achieve this, you 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.

In the COBOL Editor, type the following working-storage record after the EXEC SQL INCLUDE EMP END-EXEC statement:
```
01  EDIT-PAY     PIC Z,ZZ99.99.
```
Scroll down to the following IF statement:
```
IF SQLCODE=0
```
Place your cursor on the next line down and insert the following MOVE statement:
```
MOVE EMP-SALARY TO EDIT-PAY
```

Replace the following DISPLAY statement:

DISPLAY 'ROW FOUND'

with

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

Save the OESQLAssistantTutorial project. Eclipse automatically rebuilds the project.