Tutorial: Call a SQL CLR Stored Procedure Multi-project by Data Name

Walks you through the process of invoking a native COBOL program using JCL, and calling programs and stored procedures in multiple assemblies using data names.

Prerequisites

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

Demonstration solution - SQLCLRTutorialSPCall multi-project by data name

Note: For an overview of the base application used in this and other SQL CLR tutorials in this series, see the Demonstration solutions section in the Tutorials: SQL CLR Stored Procedures Called via COBOL and JCL topic.

This tutorial uses a version of the SQLCLRTutorialSPCall solution created using the SQL Server Database project template and then modified to demonstrate calling programs and stored procedures in multiple assemblies using data names. It includes the following projects:

SQLCLRTutorial Project

Created from the SQL Server Database project template, the SQLCLRTutorial project contains just the stored procedures. The called procedural COBOL programs are part of the LegacyPrograms project.

SP1.cbl

SP1.cbl contains stored procedure code. At run time, SQL Server meta-data identifies the assembly containing this stored procedure code, and then loads the assembly into the SQL Server database. This loaded assembly, SQLCLRTutorial, does not contain the called COBOL programs.

To ensure that the programs called by stored procedures are found, SP1.cbl defines a procedure pointer that loads the LegacyProgram assembly before calling any programs.

set assembly-to-load to entry "LegacyPrograms"

The LegacyPrograms assembly contains the called programs. For example, when the stored procedure code in SP1.cbl calls program A, it finds A in the loaded LegacyPrograms assembly.

SP1 then calls programs A and D, and calls stored procedure SP2.

SP2.cbl

Stored procedure that calls programs A, B, and C, and also increments the stored procedure counter by 10.

SPCall Project

A COBOL/JCL project added to the solution as a customization. It contains a native COBOL program that initiates the first call to a SQL CLR stored procedure, and the JCL that invokes the program.

SPCall.jcl

Invokes SPCall.cbl:

 RUN PROG (SPCALL) PLAN (SPCALL) LIB ('MY.DBRMLIB')

SPCall.cbl

Invoked by SPCall.jcl, this program calls the SP1 stored procedure in the SQLCLRTutorial project.

       move "SP1" to progname
       exec sql
            call :progname (:countval INOUT)
       end-exec

LegacyPrograms Project

LegacyPrograms contains just the procedural COBOL programs:

A.cbl	Procedural COBOL programs
B.cbl
C.cbl
D.cbl

The LegacyPrograms assembly is loaded by a procedure pointer defined in SP1. Each COBOL program in LegacyPrograms increments the COBOL program counter by 1.

SQLCLRTutorial.Publish Project

Also created by default from the SQL Server Database project template, the SQLCLRTutorial.Publish project does not contain any application code. Its purpose is to create the deployment package for the solution and to deploy it. The publishing process creates the stored procedures that call the into the DLL that SQL Server executes in a SQL CLR run-time environment. The SQL CLR run time works with the COBOL run-time. Publishing includes deploying both of these run-times.

Sequence

To complete this tutorial, progress through these topics in the order presented here. The bottom of each topic provides Next topic and Previous topic navigational links to help you proceed in the proper sequence: