String handling in CLI applications

CLI functions have conventions for passing strings as input that determine what information is returned from the function call. Strings are a series of characters ending with a null terminator. The Db2® CLI has mechanisms for detecting the length of string arguments.

Length of string arguments

The following conventions deal with the various aspects of string arguments in CLI functions.

Input strings can have an associated length argument which indicates either the exact length of the string (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, CLI will determine the length of the string by locating the null terminator.

Output strings 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 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 the descriptor FieldIdentifier argument values.

If a function is called with a null pointer for an output length argument, CLI will not return a length. When the output data is a NULL value, CLI cannot 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, 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(), CLI would update both values in one operation.

Null-termination of strings

By default, every character string that 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. Also, string data retrieved into SQL_C_WCHAR are terminated with the Unicode null terminator 0x0000. 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 CLI can indicate the actual length of data returned; otherwise, the application will not have any means to determine this. The CLI default is to always write the null terminator.

It is possible, using the Patch1 CLI/ODBC configuration keyword, to force CLI to null terminate graphic and DBCLOB strings.

String truncation

If an output string does not fit into a buffer, 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, it means 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. You can also call SQLGetDiagField() to find out which column failed.

Interpretation of strings

Normally, 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: if the cursor name is not delimited (enclosed by quotation marks) the leading and trailing blanks are removed and case is ignored.

Blank padding of strings

Db2 Universal Database Version 8.1.4 and later do not pad strings with blanks to fit the column size, as was the behavior in releases of Db2 from Version 8.1 through to Version 8.1.4. With Db2 Version 8.1.4 and later, a string might have a length which differs from the length defined for the CHAR column if code page conversion occurred. For releases of Db2 before Version 8.1.4, strings are padded with blanks to fill the column size; these blanks would be returned as part of the string data when the string was fetched from the CHAR column.