Tutorial: Generate a COBOL Wrapper for a SQL Server COBOL Stored Procedure

Prerequisites

• Review the Assumptions and Before you begin sections in the Tutorials: SQL Server COBOL Stored Procedures topic to ensure that your environment is set up properly.
• Before attempting this tutorial, you must first work through Tutorial: Prepare the SQL Server COBOL Environment.

Add COBOL source to the project

We provide a sample COBOL program, GETHIRE.cbl, that receives an employee number, and returns the employee's first name, last name, and hire date. In this phase, you add this COBOL program and its required copybook, EMP.cpy, to your SQL Server Database Project.

1. From the Solution Explorer, right-click the SQLServerSP COBOL project, and click Add > Existing Item.
2. Browse to the %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\sql\sqlclr directory.
3. From the Objects of type drop-down list, click COBOL Files (*.cbl, *.cpy, *.cob).
4. Select the GETHIRE.cbl and EMP.cpy files; then click Add.

Create a stored procedure definition file and skeleton COBOL file

You need to create a stored procedure definition file (.spd file) that contains the stored procedure definition from which to generate the COBOL wrapper program.

1. From the Solution Explorer, right-click the SQLServerSP COBOL project, and click Add > New Item.
2. In the left pane, click COBOL.
3. In the center pane, click Stored Procedure Definition.
4. In the Name field, type SQLServerSPDef.spd; then click Add.

   This adds an empty SQLServerSPDef.spd file to the project and opens it in Visual Studio. It also generates a skeleton COBOL file named SQLServerSPDef.mssp.cbl and adds it to the project.

Define the stored procedure and generate the COBOL wrapper

These two steps are done automatically in one phase.

1. Cut the following Stored Procedure definition code from this tutorial and paste it into the empty SQLServerSPDef.spd file open in Visual Studio:

   CREATE PROCEDURE TEST.GETHIRE
       ( IN    EMPNO       CHAR   (6)
        ,OUT   FIRST_NAME  VARCHAR     (12)
        ,OUT   LAST_NAME   VARCHAR (16)       
        ,OUT   HIRE_DATE   DATE
        ,OUT   OT_SQLCODE  INTEGER
        )
     RESULT SETS 0
     PARAMETER STYLE GENERAL 
     ;

2. Save the SQLServerSPDef.spd file. This automatically generates the COBOL wrapper code, saving it to the SQLServerSPDef.mssp.cbl file.
3. In the Solution Explorer, double-click SQLServerSPDef.mssp.cbl to see the generated COBOL wrapper code.
4. Save and close the SQLServerSPDef.spd and SQLServerSPDef.mssp.cbl files.

Publish the COBOL wrapper application

1. From the Solution Explorer, right-click the SQLServerSP.Publish project, and click Publish.

Execute the stored procedure

1. In Visual Studio, open the SQL Server Object Explorer.
2. Expand the SQLCLR_Test.dbo data connection.
3. Expand Stored Procedures.
4. Right-click GETHIRE, and select Execute.
5. Provide the following Values for the corresponding Names:

   Name	Value
   @lkEMPNO	000200
   @lkFIRST_NAME	<NULL>
   @lkLAST_NAME	<NULL>
   @lkHIRE_DATE	<NULL>
   @lkOT_SQLCODE	0

6. Click OK. The data appears in the Output window:

   Running [dbo].[GETHIRE] ( @lkEMPNO = 000200, @lkFIRST_NAME = <NULL>, @lkLAST_NAME = <NULL>, @lkHIRE_DATE = <NULL>, @lkOT_SQLCODE = 0 ).
   
   No rows affected.
   (0 row(s) returned)
   @lkFIRST_NAME = DAVID
   @lkLAST_NAME = BROWN
   @lkHIRE_DATE = 1966-03-03
   @lkOT_SQLCODE = 0
   @RETURN_VALUE = 
   Finished running [dbo].[GETHIRE].