Client Access ODBC Driver Truncates Character Fields That Contain a Null

Resolving The Problem

Character fields on the IBM System i system should contain only valid characters for the specified CCSID. When a field contains characters that are not valid (typically control characters with a value less than x20), the behavior of Client Access ODBC is determined by the data source's Allow unsupported character setting. When Allow unsupported character is set, the driver will not report an error but rather attempts to translate all characters to the corresponding CCSID. For example, the ASCII line feed character (0x0A) is translated to the EBCDIC line feed character (0x25). Embedded nulls remain x00.


If Allow unsupported character is not set, the warning or error CWBNL0107 is reported, for example, Column2: CWBNL0107 - Converted 7 bytes, 1 errors found beginning at offset 2. For the warning condition, data is truncated at the first null.

When Allow unsupported character is set, the driver attempts translation of the string, including null characters. The specified STRLEN number of characters are converted. Note that to view the data correctly the user may need to use the hexadecimal view of the DSPPFM operating system command.

Note: This behavior is different than previous releases (r450 and previous). Applications that specify incorrect STRLEN (pcbValue) on a bind will not have the string truncated at the first null. These applications should be updated to specify the correct STRLEN value. If required, APAR SE07095 also includes a setting to allow backward compatibility. Contact IBM support for further details.

Versions R313, R320, R440, and R450 of Client Access Express ODBC :

If Allow unsupported character is not set, Client Access ODBC will report an error such as invalid pcbValue.

When set, the driver will ignore translation errors and attempt to translate all characters in the string. The exception to this is a null character (0x00). The Client Access ODBC driver truncates parameter and column CHAR and VARCHAR data at the null. This also applies to parameters bound with a fixed length rather than SQL_NTS (null terminated string). All CCSIDs are affected except 65535 (binary fields).

To access data after the null, one possible circumvention is to use the SQL function SUBSTR to select parts of the field that do not contain the null.