Demonstration Application

The HCOSS SQL CLR Stored Procedures tutorials use a demonstration application that includes JCL, native 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 that returns data to the calling program. You build the stored procedure into a DLL that is deployed to the sample SQLCLR_Test SQL Server database.

The solution provided for these tutorials contains three projects.

SPCall Project

A COBOL/JCL project that contains a native COBOL program that initiates the first call to a SQL CLR stored procedure, and the JCL that invokes it the program.

SPCall.jcl	JCL script
SPCall.cbl	Native COBOL program

SPCall.jcl

Invokes SPCall.cbl:

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

SPCall.cbl

Invoked by SPCall.jcl, this program calls the GETHIRE program in the SQLCLRTutorial project.

exec sql
     call "GETHIRE" (:EMPID INOUT, :FIRSTNAME OUT, :LASTNAME OUT, :HIREDATE OUT, :SQLXCODE OUT)
end-exec

SQLCLRTutorial Project

Created from the SQL Server Database project template, the SQLCLRTutorial project contains the following:

GETHIRE.cbl	Native COBOL program
SQLCLRStoredProc.spd	Stored Procedure Definition (SPD) file
SQLCLRStoredProc.mssp.cbl	Stored procedure

GETHIRE.cbl

A native COBOL program that runs the SQL Server stored procedure code. SQL Server has meta-data that identifies and loads the assembly that contains the stored procedure.

SQLCLRStoredProc.spd

A file that contains stored procedure definition code.

SQLCLRStoredProc.mssp.cbl

SQLCLRStoredProc.mssp.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. The call from GETHIRE.cbl to the stored procedure is found in this loaded assembly.

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 procedure that calls the into the DLL that SQL Server executes in a SQL CLR runtime environment. The SQL CLR run time works with the COBOL runtime. Publishing includes deploying both of these runtimes.