DB2 Version 9.7 for Linux, UNIX, and Windows

SQL data type handling in C and C++ routines

This section identifies the valid types for routine parameters and results, and it specifies how the corresponding argument should be defined in your C or C++ language routine. All arguments in the routine must be passed as pointers to the appropriate data type. Note that if you use the sqludf.h include file and the types defined there, you can automatically generate language variables and structures that are correct for the different data types and compilers. For example, for BIGINT you can use the SQLUDF_BIGINT data type to hide differences in the type required for BIGINT representation between different compilers.

It is the data type for each parameter defined in the routine's CREATE statement that governs the format for argument values. Promotions from the argument's data type might be needed to get the value in the appropriate format. Such promotions are performed automatically by DB2® on argument values. However, if incorrect data types are specified in the routine code, then unpredictable behavior, such as loss of data or abends, will occur.

For the result of a scalar function or method, it is the data type specified in the CAST FROM clause of the CREATE FUNCTION statement that defines the format. If no CAST FROM clause is present, then the data type specified in the RETURNS clause defines the format.

In the following example, the presence of the CAST FROM clause means that the routine body returns a SMALLINT and that DB2 casts the value to INTEGER before passing it along to the statement where the function reference occurs:

     ... RETURNS INTEGER CAST FROM SMALLINT ...

In this case, the routine must be written to generate a SMALLINT, as defined later in this section. Note that the CAST FROM data type must be castable to the RETURNS data type, therefore, it is not possible to arbitrarily choose another data type.

The following is a list of the SQL types and their C/C++ language representations. It includes information on whether each type is valid as a parameter or a result. Also included are examples of how the types could appear as an argument definition in your C or C++ language routine:

SMALLINT
Valid. Represent in C as SQLUDF_SMALLINT or sqlint16.
Example:
```
     sqlint16    *arg1;          /* example for SMALLINT */
```
When defining integer routine parameters, consider using INTEGER rather than SMALLINT because DB2 does not promote INTEGER arguments to SMALLINT. For example, suppose you define a UDF as follows:
```
     CREATE FUNCTION SIMPLE(SMALLINT)...
```
If you invoke the SIMPLE function using INTEGER data, (... SIMPLE(1)...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function might not perceive the reason for the message. In the preceding example, 1 is an INTEGER, so you can either cast it to SMALLINT or define the parameter as INTEGER.
INTEGER or INT
Valid. Represent in C as SQLUDF_INTEGER or sqlint32. You must #include sqludf.h or #include sqlsystm.h to pick up this definition.
Example:
```
     sqlint32 *arg2;          /* example for INTEGER */
```
BIGINT
Valid. Represent in C as SQLUDF_BIGINT or sqlint64.
Example:
```
     sqlint64 *arg3;          /* example for INTEGER */
```
DB2 defines the sqlint64 C language type to overcome differences between definitions of the 64-bit signed integer in compilers and operating systems. You must #include sqludf.h or #include sqlsystm.h to pick up the definition.
REAL or FLOAT(n) where 1 <= n <= 24
Valid. Represent in C as SQLUDF_REAL or float.

Example:
```
     float *result;          /* example for REAL */
```
DOUBLE or DOUBLE PRECISION or FLOAT or FLOAT(n) where 25 <= n <= 53
Valid. Represent in C as SQLUDF_DOUBLE or double.
Example:
```
     double  *result;        /* example for DOUBLE   */
```
DECIMAL(p,s) or NUMERIC(p,s)
Not valid because there is no C language representation. If you want to pass a decimal value, you must define the parameter to be of a data type castable from DECIMAL (for example CHAR or DOUBLE) and explicitly cast the argument to this type. In the case of DOUBLE, you do not need to explicitly cast a decimal argument to a DOUBLE parameter, because DB2 promotes it automatically.

Example:
Suppose you have two columns, WAGE as DECIMAL(5,2) and HOURS as DECIMAL(4,1), and you wish to write a UDF to calculate weekly pay based on wage, number of hours worked and some other factors. The UDF could be as follows:
```
     CREATE FUNCTION WEEKLY_PAY (DOUBLE, DOUBLE, ...)
            RETURNS DECIMAL(7,2) CAST FROM DOUBLE
            ...;
```
For the preceding UDF, the first two parameters correspond to the wage and number of hours. You invoke the UDF WEEKLY_PAY in your SQL select statement as follows:
```
     SELECT WEEKLY_PAY (WAGE, HOURS, ...) ...;
```
Note that no explicit casting is required because the DECIMAL arguments are castable to DOUBLE.
Alternatively, you could define WEEKLY_PAY with CHAR arguments as follows:
```
     CREATE FUNCTION WEEKLY_PAY (VARCHAR(6), VARCHAR(5), ...)
            RETURNS DECIMAL (7,2) CAST FROM VARCHAR(10)
            ...;
```
You would invoke it as follows:
```
     SELECT WEEKLY_PAY (CHAR(WAGE), CHAR(HOURS), ...) ...;
```
Observe that explicit casting is required because DECIMAL arguments are not promotable to VARCHAR.

An advantage of using floating point parameters is that it is easy to perform arithmetic on the values in the routine; an advantage of using character parameters is that it is always possible to exactly represent the decimal value. This is not always possible with floating point.
CHAR(n) or CHARACTER(n) with or without the FOR BIT DATA modifier.
Valid. Represent in C as SQLUDF_CHAR or char...[n+1] (this is a C null-terminated string).
Example:
```
     char     arg1[14];      /* example for CHAR(13)   */
     char    *arg1;          /* also acceptable */
```
Input routine parameters of data type CHAR are always automatically null terminated. For a CHAR(n) input parameter, where n is the length of the CHAR data type, n bytes of data are moved to the buffer in the routine implementation and the character in the n + 1 position is set to the ASCII null terminator character (X'00').

Output parameters of procedures and return values of functions of data type CHAR must be explicitly null terminated by the routine. For a return value of a UDF specified by the RETURNS clause, such as RETURNS CHAR(n), or a procedure output parameter specified as CHAR(n), where n is the length of the CHAR value, a null terminator character must exist within the first n+1 bytes of the buffer. If a null terminator is found within the first n+1 bytes of the buffer, the remaining bytes, up to byte n, are set to ASCII blank characters X'20'). If no null terminator is found, an SQL error (SQLSTATE 39501) results.

For input and output parameters of procedures or function return values of data type CHAR that also specify the FOR BIT DATA clause, which indicates that the data is to be manipulated in its binary form, null terminators are not used to indicate the end of the parameter value. For either a RETURNS CHARn) FOR BIT DATA function return value or a CHAR(n) FOR BIT DATA output parameter, the first n bytes of the buffer are copied over regardless of any occurrences of string null terminators within the first n bytes. Null terminator characters identified within the buffer are ignored as null terminators and instead are simply treated as normal data.

Exercise caution when using the normal C string handling functions in a routine that manipulates a FOR BIT DATA value, because many of these functions look for a null terminator to delimit a string argument and null terminators (X'00') can legitimately appear in the middle of a FOR BIT DATA value. Using the C functions on FOR BIT DATA values might cause the undesired truncation of the data value.
When defining character routine parameters, consider using VARCHAR rather than CHAR as DB2 does not promote VARCHAR arguments to CHAR and string literals are automatically considered as VARCHARs. For example, suppose you define a UDF as follows:
```
     CREATE FUNCTION SIMPLE(INT,CHAR(1))...
```
If you invoke the SIMPLE function using VARCHAR data, (... SIMPLE(1,'A')...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function might not perceive the reason for the message. In the preceding example, 'A' is VARCHAR, so you can either cast it to CHAR or define the parameter as VARCHAR.
VARCHAR(n) FOR BIT DATA or LONG VARCHAR with or without the FOR BIT DATA modifier.
Valid. Represent VARCHAR(n) FOR BIT DATA in C as SQLUDF_VARCHAR_FBD. Represent LONG VARCHAR in C as SQLUDF_LONG. Otherwise represent these two SQL types in C as a structure similar to the following from the sqludf.h include file:
```
     struct sqludf_vc_fbd
     {
        unsigned short length;         /* length of data */
        char           data[1];        /* first char of data */
     };
```
The [1] indicates an array to the compiler. It does not mean that only one character is passed; because the address of the structure is passed, and not the actual structure, it provides a way to use array logic.

These values are not represented as C null-terminated strings because the null-character could legitimately be part of the data value. The length is explicitly passed to the routine for parameters using the structure variable length. For the RETURNS clause, the length that is passed to the routine is the length of the buffer. What the routine body must pass back, using the structure variable length, is the actual length of the data value.

Example:
```
     struct sqludf_vc_fbd *arg1;  /* example for VARCHAR(n) FOR BIT DATA */ 
     struct sqludf_vc_fbd *result; /* also for LONG VARCHAR FOR BIT DATA */
```
VARCHAR(n) without FOR BIT DATA.
Valid. Represent in C as SQLUDF_VARCHAR or char...[n+1]. (This is a C null-terminated string.)

For a VARCHAR(n) parameter, DB2 will put a null in the (k+1) position, where k is the length of the particular string. The C string-handling functions are well suited for manipulation of these values. For a RETURNS VARCHAR(n) value or an output parameter of a stored procedure, the routine body must delimit the actual value with a null because DB2 will determine the result length from this null character.

Example:
```
     char     arg2[51];      /* example for VARCHAR(50)   */
     char    *result;        /* also acceptable */
```
DATE
Valid. Represent in C same as SQLUDF_DATE or CHAR(10), that is as char...[11]. The date value is always passed to the routine in ISO format:
```
yyyy-mm-dd
```
Example:
```
     char     arg1[11];      /* example for DATE       */ 
     char    *result;        /* also acceptable */ 
```
Note: For DATE, TIME and TIMESTAMP return values, DB2 demands the characters be in the defined form, and if this is not the case the value could be misinterpreted by DB2 (For example, 2001-04-03 will be interpreted as April 3 even if March 4 is intended) or will cause an error (SQLCODE -493, SQLSTATE 22007).
TIME
Valid. Represent in C same as SQLUDF_TIME or CHAR(8), that is, as char...[9]. The time value is always passed to the routine in ISO format:
```
hh.mm.ss
```
Example:
```
     char    *arg;           /* example for TIME          */ 
     char     result[9];     /* also acceptable */
```
TIMESTAMP
Valid. Represent in C as SQLUDF_STAMP or as CHAR(19) - CHAR(32), that is, as char[20] to char[33]. The timestamp value has the following format:
```
yyyy-mm-dd-hh.mm.ss.nnnnnnnnnnnn
```
where:
yyyy

Represents the year.

mm

Represents the month.

dd

Represents the day.

hh

Represents the hour.

mm

Represents the minutes.

ss

Represents the seconds.

nnnnnnnnnnnn

Represents the fractional seconds. The fractional seconds of the TIMESTAMP data type can be optionally specified with 0-12 digits of timestamp precision.
When a timestamp value is assigned to a timestamp variable with a different number of fractional seconds, the value is either truncated or padded with 0's to match the format of the timestamp variable.
The character string can be from 19 - 32 bytes in length depending on the number of fractional seconds specified. The fractional seconds of the TIMESTAMP data type can be optionally specified with 0-12 digits of timestamp precision.
For example:
```
(VALUES(CURRENT TIMESTAMP(0))
1               
-------------------
2008-07-09-14.48.36

  1 record(s) selected.

LENGTH (VALUES(CURRENT TIMESTAMP(0))
1               
-------------------
19

  1 record(s) selected.
(VALUES(CURRENT TIMESTAMP(12))
1               
-------------------
2008-07-09-14.48.36.123456789012

  1 record(s) selected.

LENGTH (VALUES(CURRENT TIMESTAMP(0))
1               
-------------------
32
```
The following are variable declarations that can hold a TIMESTAMP(12) value:
```
     char     arg1[33];      /* example for TIMESTAMP  */ 
     char    *result;        /* also acceptable */ 
```
GRAPHIC(n)
Valid. Represent in C as SQLUDF_GRAPH or sqldbchar[n+1]. (This is a null-terminated graphic string). Note that you can use wchar_t[n+1] on operating systems where wchar_t is defined to be 2 bytes in length; however, sqldbchar is recommended.

For a GRAPHIC(n) parameter, DB2 moves n double-byte characters to the buffer and sets the following two bytes to null. Data passed from DB2 to a routine is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option. For a RETURNS GRAPHIC(n) value or an output parameter of a stored procedure, DB2 looks for an embedded GRAPHIC null CHAR, and if it finds it, pads the value out to n with GRAPHIC blank characters.

When defining graphic routine parameters, consider using VARGRAPHIC rather than GRAPHIC as DB2 does not promote VARGRAPHIC arguments to GRAPHIC. For example, suppose you define a routine as follows:
```
     CREATE FUNCTION SIMPLE(GRAPHIC)... 
```
If you invoke the SIMPLE function using VARGRAPHIC data, (... SIMPLE('graphic_literal')...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function might not understand the reason for this message. In the preceding example, graphic_literal is a literal DBCS string that is interpreted as VARGRAPHIC data, so you can either cast it to GRAPHIC or define the parameter as VARGRAPHIC.

Example:
```
     sqldbchar   arg1[14];      /* example for GRAPHIC(13)   */ 
     sqldbchar  *arg1;          /* also acceptable */ 
```
VARGRAPHIC(n)
Valid. Represent in C as SQLUDF_GRAPH or sqldbchar[n+1]. (This is a null-terminated graphic string). Note that you can use wchar_t[n+1] on operating systems where wchar_t is defined to be 2 bytes in length; however, sqldbchar is recommended.

For a VARGRAPHIC(n) parameter, DB2 will put a graphic null in the (k+1) position, where k is the length of the particular occurrence. A graphic null refers to the situation where all the bytes of the last character of the graphic string contain binary zeros ('\0's). Data passed from DB2 to a routine is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option. For a RETURNS VARGRAPHIC(n) value or an output parameter of a stored procedure, the routine body must delimit the actual value with a graphic null, because DB2 will determine the result length from this graphic null character.

Example:
```
     sqldbchar   args[51],    /* example for VARGRAPHIC(50) */ 
     sqldbchar   *result,     /* also acceptable  */
```
LONG VARGRAPHIC
Valid. Represent in C as SQLUDF_LONGVARG or a structure:
```
     struct sqludf_vg 
     {
        unsigned short length;         /* length of data */ 
        sqldbchar      data[1];        /* first char of data */ 
     };
```
Note that in the preceding structure, you can use wchar_t in place of sqldbchar on operating systems where wchar_t is defined to be 2 bytes in length, however, the use of sqldbchar is recommended.

The [1] merely indicates an array to the compiler. It does not mean that only one graphic character is passed. Because the address of the structure is passed, and not the actual structure, it provides a way to use array logic.

These are not represented as null-terminated graphic strings. The length, in double-byte characters, is explicitly passed to the routine for parameters using the structure variable length. Data passed from DB2 to a routine is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option. For the RETURNS clause or an output parameter of a stored procedure, the length that is passed to the routine is the length of the buffer. What the routine body must pass back, using the structure variable length, is the actual length of the data value, in double byte characters.

Example:
```
     struct sqludf_vg *arg1;  /* example for VARGRAPHIC(n)   */ 
     struct sqludf_vg *result; /* also for LONG VARGRAPHIC   */
```
BLOB(n) and CLOB(n)
Valid. Represent in C as SQLUDF_BLOB, SQLUDF_CLOB, or a structure:
```
     struct sqludf_lob
     {
         sqluint32     length;      /* length in bytes */
         char          data[1];      /* first byte of lob */ 
     }; 
```
The [1] merely indicates an array to the compiler. It does not mean that only one character is passed; because the address of the structure is passed, and not the actual structure, it provides a way to use array logic.

These are not represented as C null-terminated strings. The length is explicitly passed to the routine for parameters using the structure variable length. For the RETURNS clause or an output parameter of a stored procedure, the length that is passed back to the routine, is the length of the buffer. What the routine body must pass back, using the structure variable length, is the actual length of the data value.

Example:
```
     struct sqludf_lob *arg1;  /* example for BLOB(n), CLOB(n) */ 
     struct sqludf_lob *result;
```
DBCLOB(n)
Valid. Represent in C as SQLUDF_DBCLOB or a structure:
```
     struct sqludf_lob 
     {
         sqluint32 length;      /* length in graphic characters */ 
         sqldbchar data[1];         /* first byte of lob */ 
     }; 
```
Note that in the preceding structure, you can use wchar_t in place of sqldbchar on operating systems where wchar_t is defined to be 2 bytes in length, however, the use of sqldbchar is recommended.

The [1] merely indicates an array to the compiler. It does not mean that only one graphic character is passed; because the address of the structure is passed, and not the actual structure, it provides a way to use array logic.

These are not represented as null-terminated graphic strings. The length is explicitly passed to the routine for parameters using the structure variable length. Data passed from DB2 to a routine is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option. For the RETURNS clause or an output parameter of a stored procedure, the length that is passed to the routine is the length of the buffer. What the routine body must pass back, using the structure variable length, is the actual length of the data value, with all of these lengths expressed in double byte characters.
Example:
```
     struct sqludf_lob *arg1;  /* example for DBCLOB(n) */ 
     struct sqludf_lob *result; 
```

Distinct Types

Valid or invalid depending on the base type. Distinct types will be passed to the UDF in the format of the base type of the UDT, so can be specified if and only if the base type is valid.

Example:

     struct sqludf_lob *arg1;  /* for distinct type based on BLOB(n) */ 
     double            *arg2;  /* for distinct type based on DOUBLE  */ 
     char              res[5]; /* for distinct type based on CHAR(4) */

XML
Valid. Represent in C as SQLUDF_XML or in the way as a CLOB data type is represented; that is with a structure:
```
     struct sqludf_lob
     {
         sqluint32     length;      /* length in bytes */
         char          data[1];      /* first byte of lob */ 
     }; 
```
The [1] merely indicates an array to the compiler. It does not mean that only one character is passed; because the address of the structure is passed, and not the actual structure, it provides a way to use array logic.

These are not represented as C null-terminated strings. The length is explicitly passed to the routine for parameters using the structure variable length. For the RETURNS clause or an output parameter of a stored procedure, the length that is passed back to the routine, is the length of the buffer. What the routine body must pass back, using the structure variable length, is the actual length of the data value.

Example:
```
     struct sqludf_lob *arg1;  /* example for XML(n) */ 
     struct sqludf_lob *result;
```
The assignment and access of XML parameter and variable values in C and C++ external routine code is done in the same way as for CLOB values.
Distinct Types AS LOCATOR, or any LOB type AS LOCATOR
Valid for parameters and results of UDFs and methods. It can only be used to modify LOB types or any distinct type that is based on a LOB type. Represent in C as SQLUDF_LOCATOR or a four byte integer.

The locator value can be assigned to any locator host variable with a compatible type and then be used in an SQL statement. This means that locator variables are only useful in UDFs and methods defined with an SQL access indicator of CONTAINS SQL or higher. For compatibility with existing UDFs and methods, the locator APIs are still supported for NOT FENCED NO SQL UDFs. Use of these APIs is not encouraged for new functions.

Example:
```
     sqludf_locator       *arg1;  /* locator argument */ 
     sqludf_locator       *result; /* locator result */ 
```
```
 
EXEC SQL BEGIN DECLARE SECTION;
   SQL TYPE IS CLOB LOCATOR arg_loc;
   SQL TYPE IS CLOB LOCATOR res_loc;
EXEC SQL END DECLARE SECTION;

/* Extract some characters from the middle */
/* of the argument and return them         */
*arg_loc = arg1;
EXEC SQL VALUES SUBSTR(arg_loc, 10, 20) INTO :res_loc;
*result = res_loc;
```
Structured Types
Valid for parameters and results of UDFs and methods where an appropriate transform function exists. Structured type parameters will be passed to the function or method in the result type of the FROM SQL transform function. Structured type results will be passed in the parameter type of the TO SQL transform function.