Tutorial: Provide Additional Code

Your new application will not compile and run unless you provide 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 OpenESQL Assistant to embed the INCLUDE statement into the program.

You want to send the output from the application to the console in a readable format. This requires additional code that you 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 a Visual Studio Project
• Tutorial: Set OpenESQL Assistant Configuration Options
• Tutorial: Catalog a Connection
• Tutorial: Build and Test a Query

Generate a Copybook

You 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, click the EMP(dbo) table to select it; then right-click and 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 Visual Studio 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 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 INCLUDE statement

You need to embed the generated INCLUDE statement into your program.

1. In the COBOL Editor, place the cursor on the line just after the EXEC SQL INCLUDE SQLCA statement, in the same column.
2. On the OpenESQL Assistant, with the Auxiliary Code tab showing in the right pane, click Insert query into program () from the toolbar.
3. Switch back to Program1.cbl in the COBOL Editor.

   You can see that the generated code now appears in Program1.cbl.

4. Click Save () to save the program file. Do not close the COBOL Editor.
5. Close the OpenESQL Assistant.

Add Code to Format Output

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.

1. 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.

2. Scroll down to the following IF statement:

   IF SQLCODE=0

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. Click Save () to save the program file. Do not close the COBOL Editor.