Parameter style SQL C and C++ procedures

     SQL_API_RC SQL_API_FN function-name ( 
                                           SQL-arguments,
                                           SQL-argument-inds,
                                           sqlstate,
                                           routine-name,
                                           specific-name,
                                           diagnostic-message )

SQL_API_RC SQL_API_FN

SQL_API_RC and SQL_API_FN are macros that specify the return type and calling convention for a C or C++ procedure, which can vary across supported operating systems. The use of these macros is required for C and C++ routines. The macros are declared in the embedded SQL application and routine include file sqlsystm.h.

function-name

Name of the C or C++ function within the code file. This value does not have to be the same as the name of the procedure that is specified within the corresponding CREATE PROCEDURE statement. This value in combination with the library name however must be specified in the EXTERNAL NAME clause to identify the correct function entry point within the library to be used. For C++ routines, the C++ compiler applies type decoration to the entry point name. Either the type decorated name needs to be specified in the EXTERNAL NAME clause, or the entry point must be defined as extern "C" in the user code. The function name must be explicitly exported.

SQL-arguments

C or C++ arguments that correspond to the set of SQL parameters that are specified in the CREATE PROCEDURE statement. IN, OUT, and INOUT mode parameters are passed using individual pointer values.

SQL-argument-inds

C or C++ null indicators that correspond to the set of SQL parameters that are specified in the CREATE PROCEDURE statement. For each IN, OUT, and INOUT mode parameter, there must be an associated null-indicator parameter. Null indicators can be passed as individual arguments of type SQLUDF_NULLIND or as part of a single array of null indicators that are defined as SQLUDF_NULLIND*.

sqlstate

Input-output parameter value that is used by the routine to signal warning or error conditions. Typically this argument is used to assign a user-defined SQLSTATE value that corresponds to an error or a warning that can be passed back to the caller. SQLSTATE values of the form 38xxx, where xxx is any numeric value, are available for user-defined SQLSTATE error values. SQLSTATE values of the form 01Hxx, where xx is any numeric value, are available for user-defined SQLSTATE warning values.

routine-name

Input parameter value that contains the qualified routine name. This value is generated by the database manager and passed to the routine in the form <schema-name>.<routine-name> where <schema-name> and <routine-name> correspond respectively to the ROUTINESCHEMA column value and ROUTINENAME column value for the routine within the SYSCAT.ROUTINES catalog view. This value can be useful if a single routine implementation is used by multiple different routine definitions. When the routine definition name is passed into the routine, logic can be conditionally executed based on which definition was used. The routine name can also be useful when you formulate diagnostic information such as error messages, or log file entries.

specific-name

Input parameter value that contains the unique routine specific name. This value is generated by the database manager and passed to the routine. This value corresponds to the SPECIFICNAME column value for the routine in the SYSCAT.ROUTINES view. It can be useful in the same way as the routine-name.

diagnostic-message

Output parameter value that is optionally used by the routine to return message text to the caller application or routine. This parameter is intended to be used as a complement to the SQLSTATE argument. It can be used to assign a user-defined error-message to accompany a user-defined SQLSTATE value, which can provide more detailed diagnostic error or warning information to the caller of the routine.

/****************************************************************
 Routine:   cstp

  Purpose:  Returns an output parameter value based on an input
            parameter value

            Shows how to:
             - define a procedure using PARAMETER STYLE SQL
             - define NULL indicators for the parameter
             - execute an SQL statement
             - how to set a NULL indicator when parameter is
               not null

  Parameters:

  IN:       inParm
  OUT:      outParm

            When PARAMETER STYLE SQL is defined for the routine
            (see routine registration script spcreate.db2), in
            addition to the parameters passed during invocation,
            the following arguments are passed to the routine
            in the following order:

             - one null indicator for each IN/INOUT/OUT parameter
               ordered to match order of parameter declarations
             - SQLSTATE to be returned to database (output)
             - qualified name of the routine (input)
             - specific name of the routine (input)
             - SQL diagnostic string to return an optional
               error message text to database (output)

            See the actual parameter declarations below to see
            the recommended datatypes and sizes for them.

            CODE TIP:
            --------
            Instead of coding the 'extra' parameters:
              sqlstate, qualified name of the routine,
              specific name of the routine, diagnostic message,
            a macro SQLUDF_TRAIL_ARGS can be used instead.
            This macro is defined in database include file sqludf.h

            TIP EXAMPLE:
            ------------
            The following is equivalent to the actual prototype
            used that makes use of macro definitions included in
            sqludf.h.  The form actually implemented is simpler
            and removes datatype concerns.
           
   extern "C"  SQL_API_RC SQL_API_FN OutLanguage(
                                                 sqlint16 *inParm,
                                                 double *outParm,
                                                 sqlint16 *inParmNullInd,
                                                 sqlint16 *outParmNullInd,
                                                 char sqlst[6],
                                                 char qualName[28],
                                                 char specName[19],
                                                 char diagMsg[71])
                                                )

*****************************************************************/

extern "C" SQL_API_RC SQL_API_FN cstp ( sqlint16 *inParm,
                                        double *outParm,
                                        SQLUDF_NULLIND *inParmNullInd,
                                        SQLUDF_NULLIND *outParmNullInd,
                                        SQLUDF_TRAIL_ARGS )
{
  EXEC SQL INCLUDE SQLCA;

  EXEC SQL BEGIN DECLARE SECTION;
     sqlint16 sql_inParm;
  EXEC SQL END DECLARE SECTION;

  sql_inParm = *inParm;

  EXEC SQL DECLARE cur1 CURSOR FOR
   SELECT value
   FROM table01
    WHERE index = :sql_inParm;

  *outParm = (*inParm) + 1;
  *outParmNullInd = 0;

  EXEC SQL OPEN cur1;

  return (0);
}

 CREATE PROCEDURE cproc( IN inParm INT, OUT outParm INT )
 LANGUAGE c
 PARAMETER STYLE sql 
 DYNAMIC RESULT SETS 1
 FENCED 
 THREADSAFE
 RETURNS NULL ON NULL INPUT
 EXTERNAL NAME 'c_rtns!cstp'