Re: How To Distinguish Null Values in UNLOAD ??

> > Hi gurus, Well, I think that I had my "guru" license suspended when I didn't know about sqlca.sqlerrd[1], but I'll give it a shot! :-) > > I need to dump data from an Informix 2.10 database into an ASCII file. > Fine. No problem. But, the program which uses the ASCII file needs to know > which values are NULL. The straight UNLOAD statement does not seem to > provide any way to distinguish a NULL value in a column from a value which > has 4 spaces. Yes, it does. > In either case, the output ASCII is a bunch of blanks. No: For a null value, the output is a _zero-length_ string, and for a non-null value it isn't. A text field that contains all blanks is output as a string of length one containing a blank. To illustrate, running this bit of sql: create table test(x serial, a char(10), b char(10) not null); insert into test (b) values ("hi"); insert into test (b) values (" "); insert into test (b) values (""); unload to "/tmp/test" select * from test (i.e. x isn't null, a is, and b isn't) produces the following in /tmp/test: 1||hi| 2|| | 3|| | Notice that the blank, non-null text fields are of length 1, and the null text fields are of zero length. The way Informix does it is better than putting a '.' in the ASCII file for null values: There is no way a non-null field can produce the same output as a null field, regardless of contents. I assume that Informix also outputs a zero-length string for a null value of any other type. -- Bill Foote bill@pds.com MIS Director uunet!promdist!bill PDS Corp, Los Angeles, CA USA