Re: Is hex 00000 in a string field equal to null?

On 06-Dec-2011 12:03 , Mike Wills wrote:

I have been trying to figure out this bug in my .NET program for the
last couple months. For background, it is for signing time sheets.
The .NET program calls a stored procedure that executes a RPG
program that marks the time sheet as signed. Occasionally, something
happens (a couple times per cycle for about 100 employees or so) that
causes my program to crash (please avoid the .NET jokes here). I
might be able to handle the bug okay now that I know what to expect,
but I am trying to determine what that data is and why it is in
there.

I ran the following statement:

SELECT EM_FULL_N
, HEX(EMP_COMMENTS), HEX(SUPV_COMMENTS)
FROM MPRTCSP
WHERE EMP_COMMENTS<>'' OR SUPV_COMMENTS<>''

and got the following result (edited for length):

4040404040404040404040404040404040404040....40
0000000000000000000000000000000000000000....00

I know x40 is blanks but what is x00. Is that null?

For the selected rows, neither column expressed with HEX() should be the database NULL value, because the selection in the WHERE clause would have precluded any NULL values from those columns. To include the NULL values:

SELECT EM_FULL_N
, HEX(EMP_COMMENTS), HEX(SUPV_COMMENTS)
FROM MPRTCSP
WHERE EMP_COMMENTS is distinct from ''
OR SUPV_COMMENTS is distinct from ''

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafynulls.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafznull.htm

Regards, Chuck