The Tutorials: SQL CLR Stored Procedures Called via COBOL and JCL topics use a demonstration application that includes JCL, native COBOL, procedural COBOL, and SQL CLR stored procedure code. First, a native COBOL program is invoked via JCL. The invoked program calls a SQL Server SQL CLR stored procedure. The called stored procedure calls a second, procedural, COBOL program that then calls a second stored procedure that, unlike the first stored procedure, is nested. This nested stored procedure then calls other COBOL programs. Finally, a count of called programs and stored procedures is returned back to the calling native COBOL program. The count is increased by 1 for every program called and by 10 for every stored procedure called.
The tutorials are divided into single-project and multi-project functionality. The single-project tutorials call all programs and stored procedures from a single assembly. The multi-project tutorials call programs and stored procedures from multiple assemblies.
Each tutorial uses its own provided solution created using the SQL Server Database project template and then modified to include individual tutorial customizations. Each solution contains either three or four projects, depending on whether the tutorial demonstrates single- or multi-project functionality. While each project is slightly different from the others, all solutions contain projects that use the same names and provide essentially the same functionality.
Project organization differs slightly from solution to solution, depending on whether the tutorial demonstrates single- or multi-program functionality.
Putting the programs and stored procedures into a single project is a deployment advantage because everything is in a single assembly. No project references or procedure pointers are required. This organization is best for simple, self-contained applications. Larger, more diverse applications are best organized in a multi-project configuration.
In multi-project solutions such as these, you must define references or procedure pointers that identify the assemblies used, that is, when one assembly calls a program in a second assembly, the first assembly must either reference the second assembly or use a procedure pointer to load the second assembly. This ensures that routines contained in the second assembly are found at run time. One advantage to the multi-project approach is that you can more logically group types of code. For example, you can organize your code by creating projects that include sets of related programs, and sets of related stored procedures. Each project has its own assembly distinct from the others. Because the publishing process includes only assemblies that have changed, this method of organization can improve efficiency with regard to the size of the deployed application and the time required to publish. This method does require knowledge of the code sufficient to configure the appropriate references and procedure pointers.
When you create a solution using the SQL Server Database project template, as has been done for these tutorials, Enterprise Developer automatically generates two projects - a COBOL project and a corresponding .Publish project. The .Publish project automatically references the COBOL project. The COBOL project properties are set to build the project as a verifiable (or safe) assembly. This is required to ensure that any assembly published to SQL Server runs in the SQL CLR environment.
The projects and source code provided in each solution are:
| SPCall.jcl | JCL script |
| SPCall.cbl | Native COBOL program |
RUN PROG (SPCALL) PLAN (SPCALL) LIB ('MY.DBRMLIB')
exec sql
call "SP1" (:countval INOUT)
end-exec
| A.cbl | Procedural COBOL programs |
| B.cbl | |
| C.cbl | |
| SP1.cbl | SQL CLR stored procedures |
| SP2.cbl |
| SP1.cbl | SQL CLR stored procedures |
| SP2.cbl |
The called procedural COBOL programs are part of the LegacyPrograms project.
To ensure that called programs 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 and stored procedures. 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.
| 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.