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

Not sure that "burned by DDS" is a fair comment in this case. I am not aware of any way to prevent insertion of x'00' into a character field, and SQL will be just as happy inserting it into a DDL-described table, as RPG is with inserting it into DDS-described.

What aspect of DDL were you hoping would prevent this from occurring?
++
Dennis
++
We're deep into the holiday gift-giving season, as you can tell from the fact that everywhere you look, you see jolly old St. Nick urging you to purchase things, to the point where you want to slug him right in his bowl full of jelly.
-- Dave Barry


Bravely sent from my Galaxy tablet phone.
++


Matt Olson <Matt.Olson@xxxxxxxx> wrote:

Didn't work. There is actually other data in the field. It's just that one character is a NUL.

I'm thinking that SQL isn't designed to look for this type of stuff as Alan Campin pointed out that SQL (DDL) described tables would have never allowed this bad data to be entered in the first place. This is only a problem with DDS described tables.

Adding this as yet another reason to kick off our database modernization project! Burned by DDS again!


-----Original Message-----
From: AJordan@xxxxxxxxxxxxxx [mailto:AJordan@xxxxxxxxxxxxxx]
Sent: Friday, June 29, 2012 12:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: Dealing with NUL (hex code 00) characters in files/tables

Matt

Try

Select * from tablename where fieldname is null


Kind Regards
Alan Jordan

RODIN by Coglin Mill | 507.282.4151 x 103 | 507.261.4495 Mobile | Rochester, Minnesota USA | www.thinkrodin.com




From: Matt Olson <Matt.Olson@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>,
Date: 06/29/2012 12:07 PM
Subject: Dealing with NUL (hex code 00) characters in files/tables
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Folks,

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.

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: select * from tablename where fieldname like '%' || chr(00)
|| '%' to no avail.

Matt