Re: Dealing with NUL (hex code 00) characters in files/tables

On 29 Jun 2012 12:07, Matt Olson wrote:

We found an issue with the IBM DB2 Driver for .NET (also occurs if
you try to do the Data Transfer from IBM i) when dealing with
characters in a CHAR field (CCSID 37) that contain a NUL (hex code
00) character in the field. In both cases (.NET driver and Data
Transfer from IBM i) we get a character code conversion error and the
whole SELECT SQL statement fails.

SQL uses return codes SQLCODE and SQLSTATE which could better define what "fails" means. Since the Control Character NUL is unchanging between each encoding [ASCII and EBCDIC], I would not expect an error, at least nothing beyond a warning such as with a substitution [substitute] character +335\01517 condition. Is 0x00 data known to be the origin for the [unstated] error, or merely presumed to be an issue?

Any easy way to find these values in all tables, and hopefully we can
track down how the heck this bad data is getting entered.

I attempted to no avail:
select * from tablename
where fieldname like '%' || chr(00) || '%'

While I have no idea why that query would not find the NUL Control Character, i.e. the 0x00 character, having used the CHR scalar function seems unnecessary and even somewhat confusing. The data being searched is presumably the server hex code point x'00', so I would have composed the request to ask instead, for the rows meeting the following selection [the LOCATE scalar to identify where within the field data is the first hex zero character]:

select dec(locate(x'00',fieldname), 6) loc0x00
, t.*
from tablename t
where fieldname like '%' || x'00' || '%'

Regards, Chuck