Parameter style SQL C and C++ procedures
C and C++ procedures can be created with the PARAMETER STYLE SQL clause in the CREATE PROCEDURE statement.
The C and C++ PARAMETER STYLE SQL signature for a procedure must
be in the following format.
SQL_API_RC SQL_API_FN function-name (
SQL-arguments,
SQL-argument-inds,
sqlstate,
routine-name,
specific-name,
diagnostic-message )
SQL_API_RC
andSQL_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
, wherexxx
is any numeric value, are available for user-defined SQLSTATE error values. SQLSTATE values of the form01Hxx
, wherexx
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.
SQL_API_RC SQL_API_FN
Remember: You can use the macro definition
SQLUDF_TRAIL_ARGS, which is defined in the sqludf.h file,
in place of using individual arguments for implementing the non-SQL
data type arguments to simplify the writing of C and C++ procedure signature.
The following sample C or C++ procedure accepts a single input
parameter, and returns a single output parameter and a result set:
/****************************************************************
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);
}
The corresponding CREATE PROCEDURE statement for this procedure
follows:
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'
The create procedure statement cproc
assumes
that the C or C++ procedure implementation is in a library file that
is named c_rtns and a function named cstp.