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

As I think about it, the journal would probably be the simplest.
Normally the problem with a journal entry is defining the record
format but in this case you don't want the record format. You just
want to want to look at each record received from a RTVJRNE for a null
character in the record and the journal would tell which program and
user wrote the record.

On Fri, Jun 29, 2012 at 11:19 AM, 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.