Tutorial: Call a SQL CLR Stored Procedure Single-project by Literal

Walks you through the process of invoking a native COBOL program using JCL, and calling programs and stored procedures in a single assembly using literals.

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 single-project by literal

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 a SQL CLR stored procedure by a literal. It includes the following projects:

SQLCLRTutorial Project
The SQLCLRTutorial project in this tutorial contains both the required procedural COBOL programs and the stored procedure programs. These are built into one DLL that is deployed to the sample SQLCLR_Test SQL Server database:
A.cbl Procedural COBOL programs
B.cbl
C.cbl
SP1.cbl SQL CLR stored procedures
SP2.cbl
A.cbl, B.cbl, C.cbl
Procedural COBOL programs that each increment the COBOL program counter by 1.
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, is the only assembly; therefore all calls from SP1.cbl to other programs and stored procedures are found in it. For example, when the stored procedure code in SP1.cbl calls program A, it finds A in the loaded SQLCLRTutorial assembly. SP1 calls programs A and C, and calls stored procedure SP2.
SP2.cbl
Stored procedure that calls programs A and B, 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. 
       exec sql
            call "SP1" (:countval INOUT)
       end-exec
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:

  1. Set Up the SQLCLRTutorialSPCall Solution - Single-project Literal
    Guides you through the process of setting up the provided SQLCLRTutorialSPCall Visual Studio solution for use with a literal, including setting SPCall project properties, building the solution, and publishing the stored procedures.
  2. Set Up the JCLSPSL Enterprise Server Region
    Walks you through the process of configuring the imported JCLSPSL enterprise server regions for use with this tutorial.
  3. Debug the SPCall Program and SP1 Stored Procedure - Single-project Literal
    Provides step-by-step instructions to associate and start the JCLSPSL enterprise server region, set debug properties and breakpoints, debug the SPCall and SP1 stored procedure, view the JCL execution results, and stop the JCLSPSL enterprise server region.
Parent topic: Tutorials: SQL CLR Stored Procedures Called via COBOL and JCL
Related concepts
Tutorials: SQL CLR Stored Procedures Called via COBOL and JCL
Tutorial: Prepare the SQL CLR Stored Procedures Environment