Include file required for C and C++ routine development (sqludf.h)

The sqludf.h include file contains structures, definitions, and values that are required when coding routine implementations.

Although the file has 'udf' in its name, (for historical reasons) it is also useful for stored procedures and methods. When compiling your routine, you need to reference the include directory of the database installation that contains the sqludf.h file.

Use of objects in this file is recommended to ensure that the correct C data type for your specific operating system and operating system bit-width are used.

The sqludf.h file contains structure definitions and descriptions of the structure definitions. The following is a brief summary of its content:

  • Macro definitions for SQL data types that are supported as parameters to external routines that do not require representation as a C or C++ structure. In the file, the definitions have name formats like: SQLUDF_x and SQLUDF_x_FBD where x is an SQL data type name and FBD represents FOR BIT DATA for those data types that are stored in binary form.

    Also included is a C language type for an argument or result that is defined with the AS LOCATOR clause. This is applicable only to UDFs and methods.

  • C structure definitions required to represent the following SQL data types and special parameters:
    • VARBINARY
    • VARCHAR FOR BIT DATA data type
    • LONG VARCHAR data type
    • LONG VARCHAR FOR BIT DATA data type
    • LONG VARGRAPHIC data type
    • BLOB data type
    • CLOB data type
    • DBCLOB data type
    • scratchpad structure
    • dbinfo structure

    Each of these is represented by a structure with more than one field value rather than by a simple C data type.

    The scratchpad structure defines a buffer, that is passed to a user-defined function for use during the function invocation. Unlike a variable, however the data stored in a scratchpad is persistent between multiple user-defined function calls within a single invocation. This can be useful both for functions that return aggregated values and for functions that require initial setup logic.

    The dbinfo structure is a structure that contains database and routine information that can be passed to and from a routine implementation as an extra argument if and only if the DBINFO clause is included in the CREATE statement for the routine.

  • Definition of C language types for the scratchpad and call-type arguments. An enum type definition is specified for the call-type argument.

    External user-defined functions are invoked multiple times for a set of values. Call-types are used to identify individual external function invocations. Each invocation is identified with a call-type value that can be referenced within the function logic. For example there are special call-types for the first invocation of a function, for data fetching calls, and for the final invocation. Call-types are useful, because specific logic can be associated with a particular call-type. Examples of call-types include: FIRST call, FETCH call FINAL call.

  • Macros for defining the standard trailing arguments required in user-defined function (UDF) prototypes. The trailing arguments include the SQL-state, function-name, specific-name, diagnostic-message, scratchpad, and call-type UDF invocation arguments. Also included are definitions for referencing these constructs, and the various valid SQLSTATE values. There are various macro definitions provided that differ in their inclusion or exclusion of the scratchpad and call-type arguments. These corresponds to the presence or absence of the use of the SCRATCHPAD clause and FINAL CALL clause in the function definition.
    In general when defining a user-defined function, it is recommended to use the macro SQLUDF_TRAIL_ARGS to simplify the function prototype as shown in the following example: 
    void SQL_API_FN ScalarUDF(SQLUDF_CHAR *inJob,
                          SQLUDF_DOUBLE *inSalary,
                          SQLUDF_DOUBLE *outNewSalary,
                          SQLUDF_SMALLINT *jobNullInd,
                          SQLUDF_SMALLINT *salaryNullInd,
                          SQLUDF_SMALLINT *newSalaryNullInd,
                          SQLUDF_TRAIL_ARGS)

  • Macro definitions that can be used to test whether or not SQL arguments have null values.

To see how the various definitions, macros, and structures that are defined in the file sqludf.h are used, see the C and C++ sample applications and routines.