A Stored Procedure Definition (SPD) file defines your stored procedure, and is required for SQL CLR integration when the stored procedure uses COBOL data type parameters. Visual COBOL uses the SPD file to generate a COBOL wrapper program that converts the COBOL data type parameters to the .NET data type parameters required by the SQL CLR environment.
You can create and manually code an SPD file for a SQL Server Database project from the Solution Explorer in Visual Studio. You can manually code an SPD file using any file editor. Save the file with a .spd file extension. If you create an SPD file outside of Visual Studio, add it to your Visual COBOL SQL Server Database project. See To create a stored procedure definition (SPD) file and COBOL Wrapper Programs for more information.
An SPD file must contain a CREATE PROCEDURE statement, and optionally, generation options for your COBOL wrapper (see SPD Generation Options below for details).
The CREATE PROCEDURE statement declares the parameter data types in the stored procedure, and also provides other information about the expected behavior of the stored procedure. An SPD file must contain a CREATE PROCEDURE statement. This statement follows any SPD generation options in the file (see the SPD Generation Options section below for more information).
>>--CREATE PROCEDURE procedure_name (parameter_desc)--.-----------.-->< V | +option_desc+ +---,--------+ V : +---,------+ parameter_desc ::= .-IN-----. >>-+--------+--parameter_name--data_type-->< +--OUT---+ +--INOUT-+ option_desc ::= .-DYNAMIC RESULT SETS 0--------. >--+------------------------------+-------------------------------> '-DYNAMIC RESULT SETS--integer-' >--+-----------------------------------+---+------------------+---> | | |--LANGUAGE COBOL--| '-EXTERNAL NAME--+-'string'---+-----' '-identifier-' >--+--------------------------------------------------------------< |--PARAMETER STYLE---+-GENERAL------------+ |-GENERAL WITH NULLS-|
In addition to a CREATE PROCEDURE statement, the SPD file can contain generation options that specify how you want your COBOL wrapper program generated.
For example, you might want to generate the wrapper program using certain parameter names or types depending on what type of program calls the stored procedure. If you wanted to call the stored procedure from a VB.NET or Java program, you might not want the default "lk" prefix on each parameter. Also, you might want to return a longer error message than what is available in the SQLCA.
In the SPD file, specify your generation options before the CREATE PROCEDURE statement. You can specify multiple options using a semicolon (;) to delineate each one.
The following describes each SPD generation option:
The following examples pair an SPD file and its resulting generated COBOL wrapper program.
CREATE PROCEDURE X1103670 (OUT SRMODRET VARCHAR(650) , IN SKPRMF1AE CHAR(250) , OUT SKPRMF1AS TIMESTAMP ) DYNAMIC RESULT SETS 1 EXTERNAL NAME SKPRMF1A LANGUAGE COBOL PARAMETER STYLE GENERAL;
Generates this COBOL wrapper program:
* $set dialect(mf) charset(ascii) $set sql(dbman=ado behavior=optimized dialect=mainframe nocheck) $set sourceformat(variable) $set ilusing(Microsoft.SqlServer.Server) $set notrunc class-id X1103670_CLASS is partial. method-id X1103670 static attribute SqlProcedureAttribute. local-storage section. 01 lsSRMODRET. 49 lsSRMODRET-LEN PIC S9(4) BINARY. 49 lsSRMODRET-TXT PIC X(650). 01 lsSKPRMF1AE PIC X(250). 01 lsSKPRMF1AS PIC X(26). 01 ts_format string. 01 time_hh binary-long. 01 time_mm binary-long. 01 ls_dt type DateTime. 01 ls_SqlRts type MFClrRts.MFClrRts. procedure division using by output lkSRMODRET as STRING attribute SqlFacet(name MaxSize = 650), by value lkSKPRMF1AE as STRING attribute SqlFacet(name MaxSize = 250), by value lkSKPRMF1AS as STRING attribute SqlFacet(name MaxSize = 26). *> MOVE INPUT data to COBOL parameters MOVE lkSKPRMF1AE TO lsSKPRMF1AE CALL "SKPRMF1A" USING lsSRMODRET, lsSKPRMF1AE, lsSKPRMF1AS. *> MOVE OUTPUT data from COBOL parameters MOVE lsSRMODRET-TXT(1:lsSRMODRET-LEN) TO lkSRMODRET MOVE lsSKPRMF1AS TO lkSKPRMF1AS *> GET SQL Runtime for deadlock detection exec ado get runtime into :ls_SqlRts end-exec if ls_SqlRts::ClrCallAborted display "#@$%:1:" lsSRMODRET end-if goback. end method. end class.
PARMPREFIX=;DATE_FORMAT=USA;SPDMSG=400; POSTDEPLOY=C:\X1103670\X1103670\X1103670.Publish\Script.PD1.sql;NAMESPACE=X1103670; CREATE PROCEDURE X1103670 (OUT SRMODRET VARCHAR(650) , IN SKPRMF1AE CHAR(250) , OUT SKPRMF1AS DATETIME2 ) DYNAMIC RESULT SETS 0 EXTERNAL NAME SKPRMF1A LANGUAGE COBOL PARAMETER STYLE GENERAL;
Generates this COBOL wrapper program:
* $set dialect(mf) charset(ascii) $set sql(dbman=ado behavior=optimized dialect=mainframe nocheck) $set sourceformat(variable) $set ilusing(Microsoft.SqlServer.Server) $set notrunc * $set sql(date=USA TIME=ISO detectdate ) class-id X1103670_CLASS is partial. method-id X1103670 static attribute SqlProcedureAttribute. local-storage section. 01 lsSRMODRET. 49 lsSRMODRET-LEN PIC S9(4) BINARY. 49 lsSRMODRET-TXT PIC X(650). 01 lsSKPRMF1AE PIC X(250). 01 lsSKPRMF1AS PIC X(26). 01 ts_format string. 01 time_hh binary-long. 01 time_mm binary-long. 01 ls_dt type DateTime. 01 ls_SqlRts type MFClrRts.MFClrRts. 01 MFSQLMESSAGETEXT PIC X(400). procedure division using by output SRMODRET as STRING attribute SqlFacet(name MaxSize = 650), by value SKPRMF1AE as STRING attribute SqlFacet(name MaxSize = 250), by output SKPRMF1AS as type System.DateTime attribute SqlFacet(name Scale= 6). *> MOVE INPUT data to COBOL parameters MOVE SKPRMF1AE TO lsSKPRMF1AE CALL "SKPRMF1A" USING lsSRMODRET, lsSKPRMF1AE, lsSKPRMF1AS. *> MOVE OUTPUT data from COBOL parameters MOVE lsSRMODRET-TXT(1:lsSRMODRET-LEN) TO SRMODRET set ts_format to "yyyy-MM-dd-HH.mm.ss.ffffff" set SKPRMF1AS TO type DateTime::ParseExact(lsSKPRMF1AS, ts_format, type System.Globalization.CultureInfo::CurrentCulture) *> GET SQL Runtime for deadlock detection exec ado get runtime into :ls_SqlRts end-exec if ls_SqlRts::ClrCallAborted display "#@$%:1:" lsSRMODRET display "#@$%:3:" lsSKPRMF1AS end-if goback. end method. end class.