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

Requirements

Before attempting this tutorial, you must first complete the following SQL CLR Integration tutorials to ensure you have an established SQL Server database named SQLCLR_Test and a connection to that database:

• Tutorial: Enable SQL CLR Integration
• Tutorial: Create a Sample Database
• Tutorial: Create and Configure a Database Project

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 SQLCLRTutorial 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 SQLCLRTutorial COBOL project, and click Add > New Item.
2. Under Installed Templates, click COBOL.
3. In the center pane, click Stored Procedure Definition.
4. In the Name field, type SQLCLRStoredProc.spd; then click Add.

   This adds an empty SQLCLRStoredProc.spd file to the project and opens it in Visual Studio. It also generates a skeleton COBOL file named SQLCLRStoredProc.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 SQLCLRStoredProc.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 SQLCLRStoredProc.spd file. This automatically generates the COBOL wrapper code, saving it to the SQLCLRStoredProc.mssp.cbl file.
3. In the Solution Explorer, double-click SQLCLRStoredProc.mssp.cbl to see the generated COBOL wrapper code.
4. Save and close the SQLCLRStoredProc.spd and SQLCLRStoredProc.mssp.cbl files.

Publish the COBOL Wrapper Application

1. From the Solution Explorer, right-click the SQLCLRTutorial.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].
   

This completes the tutorial.