The following conventions deal with the various aspects of working with string arguments in DB2 CLI functions.
Input string arguments have an associated length argument. This argument indicates to DB2 CLI, either the exact length of the argument (not including the null terminator), the special value SQL_NTS to indicate a null-terminated string, or SQL_NULL_DATA to pass a NULL value. If the length is set to SQL_NTS, DB2 CLI will determine the length of the string by locating the null terminator.
Output string arguments have two associated length arguments, an input length argument to specify the length of the allocated output buffer, and an output length argument to return the actual length of the string returned by DB2 CLI. The returned length value is the total length of the string available for return, regardless of whether it fits in the buffer or not.
For SQL column data, if the output is a null value, SQL_NULL_DATA is returned in the length argument and the output buffer is untouched. The descriptor field SQL_DESC_INDICATOR_PTR is set to SQL_NULL_DATA if the column value is a null value. For more information, including which other fields are set, see SQL_DESC_INDICATOR_PTR in SQLSetDescField().
If a function is called with a null pointer for an output length argument, DB2 CLI will not return a length, and assumes that the data buffer is large enough to hold the data. When the output data is a NULL value, DB2 CLI can not indicate that the value is NULL. If it is possible that a column in a result set can contain a NULL value, a valid pointer to the output length argument must always be provided. It is highly recommended that a valid output length argument always be used.
Performance Hint
If the length argument (StrLen_or_IndPtr) and the output buffer (TargetValuePtr) are contiguous in memory, DB2 CLI can return both values more efficiently, improving application performance. For example, if the following structure is defined:
struct
{ SQLINTEGER pcbValue;
SQLCHAR rgbValue [BUFFER_SIZE];
} buffer;
and &buffer.pcbValue and buffer.rgbValue is
passed to SQLBindCol(), DB2 CLI would update both values in one
operation.
By default, every character string that DB2 CLI returns is terminated with a null terminator (hex 00), except for strings returned from graphic and DBCLOB data types into SQL_C_CHAR application variables. Graphic and DBCLOB data types that are retrieved into SQL_C_DBCHAR application variables are null terminated with a double byte null terminator. This requires that all buffers allocate enough space for the maximum number of bytes expected, plus the null-terminator.
It is also possible to use SQLSetEnvAttr() and set an environment attribute to disable null termination of variable length output (character string) data. In this case, the application allocates a buffer exactly as long as the longest string it expects. The application must provide a valid pointer to storage for the output length argument so that DB2 CLI can indicate the actual length of data returned; otherwise, the application will not have any means to determine this. The DB2 CLI default is to always write the null terminator.
It is possible, using the PATCH1 CLI/ODBC configuration keyword, to force DB2 CLI to null terminate graphic and DBCLOB strings. This keyword can be set from the CLI/ODBC Settings notebook accessible from the Client Configuration Assistant (CCA). Refer to the "Platform Specific Details for CLI/ODBC Access". The Configure the CLI/ODBC Driver section for your platform will provide the steps required to set the keywords. The description of PATCH1 in "Configuration Keywords" includes how to find the setting required to force the null termination of graphic and DBCLOB strings.
If an output string does not fit into a buffer, DB2 CLI will truncate the string to the size of the buffer, and write the null terminator. If truncation occurs, the function will return SQL_SUCCESS_WITH_INFO and an SQLSTATE of 01004 indicating truncation. The application can then compare the buffer length to the output length to determine which string was truncated.
For example, if SQLFetch() returns SQL_SUCCESS_WITH_INFO, and an SQLSTATE of 01004, at least one of the buffers bound to a column is too small to hold the data. For each buffer that is bound to a column, the application can compare the buffer length with the output length and determine which column was truncated.
Normally, DB2 CLI interprets string arguments in a case-sensitive manner and does not trim any spaces from the values. The one exception is the cursor name input argument on the SQLSetCursorName() function, where if the cursor name is not delimited (enclosed by double quotes) the leading and trailing blanks are removed and case is ignored.