Stored Procedure Definition (SPD) File

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. Enterprise Developer 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, or optionally generate an SPD file using HCOSS. You can also 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 Enterprise Developer SQL Server Database project. See To create a stored procedure definition (SPD) file and COBOL Wrapper Programs for more information. If using HCOSS to generate an SPD file, see also the Generate an SPD File section below.

An SPD file must contain a CREATE PROCEDURE statement, and optionally, generation options for your COBOL wrapper (see SPD Generation Options below for details).

Generate an SPD File

You can generate an SPD file using the Generate SPD File and Extract Schema HCO for SQL Server (HCOSS) tools. The Generate SPD File tool generates the file, including a CREATE PROCEDURE statement based on a schema that you extract using the HCOSS Extract Schema tool. See Extract Schema Tool and Generate SPD File Tool for more information.

The tool also generates SPD generation options for non-default values you have specified in the SPD Generator Options section of the HCOSS configuration options. See To set HCOSS options for more information.

Important: The Generate SPD File tool does not generate the SPDMSG option. To specify SPDMSG, edit the generated SPD file and add SPDMSG to the generation options. See the SPD Generation Options section below for more information.

If you decide to not use HCOSS, you must create your SPD file manually.

The CREATE PROCEDURE statement

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).

Syntax:

>>--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-|

Parameters

procedure_name

The name of the stored procedure.

parameter_desc

parameter_name

The name of the parameter.

data_type

The data type of the corresponding parameter. Supported data types are:

SMALLINT,INTEGER,INT,BIGINT
DECIMAL(x,y), DEC(x,y), NUMERIC(x,y)
FLOAT,REAL
CHARACTER(x), CHAR(x), CHARACTER VARYING (x), CHAR VARYING (x), VARCHAR (x), CLOB (x)
GRAPHIC (x), VARGRAPHIC (x), DBCLOB (x)
BINARY(x), BINARY VARYING (x), VARBINARY(x), BLOB (x)
DATE, TIME, TIMESTAMP
TINYINT
NCHAR
NVARCHAR, NVARCHAR(MAX)
MONEY
VARCHAR(MAX)
VARBINARY(MAX)
TIMESPAN
DATETIME, SMALLDATETIME, DATETIME2

Tip: To pass COBOL group variables instead of PIC X COBOL variables, specify the BINARY or VARBINARY data type in the SPD file, and specify the BINARY or VARBINARY SQL Type in the client stored procedure code.

option_desc

integer: The number of result sets returned.
string, identifier: The external name of the stored procedure (optional).

SPD Generation Options

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:

PARMPREFIX=value

Sets a prefix for generated wrapper parameter names

value: Any character or numeric string, or omit value to specify no prefix.
Default: lk
Example: PARMPREFIX=;

GENFIXEDLENGTH

Moves the entire length of INPUT VARCHAR parameters

Default: Use string length
Example: GENFIXEDLENGTH;

SPDMSG=value

Generates the MFSQLMESSAGETEXT variable and sets the maximum string length for SQL error messages.

value: Any number over 72.
Default: 72
Example: SPDMSG=400;

DATE_FORMAT=value

Date format for generated DATE SQL CLR stored procedure parameters.

value

Any of the following date format specifications:

ODBC
ISO
USA
EUR
JIS

Note: See DATE for a list of corresponding generated formats.

Default

ISO - generates yyyy-mm-dd (mainframe default) format.

Example

DATE_FORMAT=USA;

TIME_FORMAT=value

Time format for generated TIME SQL CLR stored procedure parameters.

value

Any of the following time format specifications:

ODBC
ISO
USA
EUR
JIS

Note: See TIME for a list of corresponding generated formats.

Default

ISO - generates hh:mm:ss (mainframe default) format.

Example

TIME_FORMAT=USA;

POSTDEPLOY=value

The name and location of a post-deployment script file. This is required if a Java routine calls a stored procedure with a TIMESTAMP parameter. For complete information, see Post-deployment Script Requirements.

value: ScriptPath\Name, representing the full path to and filename of the post-deployment script file.
Default: none
Example: POSTDEPLOY=C:\MYPROJ\SQLD1.SQL;

NAMESPACE=value

The default namespace of the COBOL managed project that includes the SPD file. This is required when you also specify POSTDEPLOY. You can see the default namespace in Enterprise Developer on the Project properties Application tab. For complete information, see Post-deployment Script Requirements.

value: The namespace specified on the Application tab of the project properties.
Default: none
Example: NAMESPACE=X1103670;

Example SPD Files and Generated COBOL Wrapper Programs

The following examples pair an SPD file and its resulting generated COBOL wrapper program.

Example 1 - no generation options

This SPD file:

   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.

Example 2 - PARMPREFIX, DATE_FORMAT, SPDMSG, and POSTDEPLOY generation options

This SPD file:

   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.