Parameter style SQL C and C++ functions

C and C++ user-defined functions should be created using the PARAMETER STYLE SQL clause in the CREATE FUNCTION statement. The parameter passing conventions of this parameter style should be implemented in the corresponding source code implementation. The C and C++ PARAMETER STYLE SQL signature implementation required for user-defined functions follows this format:

 SQL_API_RC SQL_API_FN function-name ( SQL-arguments,                                         
  SQL-argument-inds,
  SQLUDF_TRAIL_ARGS )

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++ user-defined function, which can vary across supported operating systems. The use of these macros is required for C and C++ routines. The macros are declared in 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 function specified within the corresponding CREATE FUNCTION 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 function declaration within the source code file should be prefixed with extern "C" as shown in the following example: extern "C" SQL_API_RC SQL_API_FN OutLanguage( char *, sqlint16 *, char *, char *, char *, char *);

SQL-arguments

C or C++ arguments that correspond to the set of SQL parameters specified in the CREATE FUNCTION statement.

SQL-argument-inds

For each SQL-argument a null indicator parameter is required to specify whether the parameter value is intended to be interpreted within the routine implementation as a NULL value in SQL. Null indicators must be specified with data type SQLUDF_NULLIND. This data type is defined in embedded SQL routine include file sqludf.h.

SQLUDF_TRAIL_ARGS

A macro defined in embedded SQL routine include file sqludf.h that once expanded defines the additional trailing arguments required for a complete parameter style SQL signature. There are two macros that can be used: SQLUDF_TRAIL_ARGS and SQLUDF_TRAIL_ARGS_ALL. SQLUDF_TRAIL_ARGS when expanded, as defined in sqludf.h, is equivalent to the addition of the following routine arguments:

SQLUDF_CHAR *sqlState, 
SQLUDF_CHAR qualName,
SQLUDF_CHAR specName,
SQLUDF_CHAR *sqlMessageText,

In general these arguments are not required or generally used as part of user-defined function logic. They represent the output SQLSTATE value to be passed back to the function invoker, the input fully qualified function name, input function specific name, and output message text to be returned with the SQLSTATE. SQLUDF_TRAIL_ARGS_ALL when expanded, as defined in sqludf.h, is equivalent to the addition of the following routine arguments:

SQLUDF_CHAR  qualName,
SQLUDF_CHAR  specName,
SQLUDF_CHAR  sqlMessageText,
SQLUDF_SCRAT *scratchpad
SQLUDF_CALLT *callType

If the UDF CREATE statement includes the SCRATCHPAD clause or the FINAL CALL clause, then the macro SQLUDF_TRAIL_ARGS_ALL must be used. In addition to arguments provided with SQLUDF_TRAIL_ARGS, this macro also contains pointers to a scratchpad structure, and a call type value.

The following is an example of a simple C or C++ UDF that returns in an output parameter the value of the product of its two input parameter values:

SQL_API_RC SQL_API_FN product ( SQLUDF_DOUBLE *in1,
                                SQLUDF_DOUBLE *in2,
                                SQLUDF_DOUBLE *outProduct,
                                SQLUDF_NULLIND *in1NullInd,
                                SQLUDF_NULLIND *in2NullInd,
                                SQLUDF_NULLIND *productNullInd,
                                SQLUDF_TRAIL_ARGS )
{

  /* Check that input parameter values are not null 
     by checking the corresponding null indicator values
         0  : indicates parameter value is not NULL 
         -1 : indicates parameter value is NULL 

    If values are not NULL, calculate the product.
    If values are NULL, return a NULL output value. */

  if ((*in1NullInd != -1) &&
       *in2NullInd != -1))
  {
    *outProduct = (*in1) * (*in2);
    *productNullInd = 0;  
  }
  else
  {
    *productNullInd = -1; 
  }
  return (0);
}

The corresponding CREATE FUNCTION statement that can be used to create this UDF could be:

CREATE FUNCTION product( in1 DOUBLE, in2 DOUBLE )
  RETURNS DOUBLE
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL 
  FENCED THREADSAFE
  DETERMINISTIC 
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION
  EXTERNAL NAME 'c_rtns!product'

The preceding SQL statement assumes that the C or C++ function is in a library file in the function directory named c_rtns.