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.
