Re: char database types blanks and nulls

In article <1992Oct12.225029.2143@advtech.uswest.com> yslas@riverauswest.com ( Rob Yslas) writes: >Using Standard Engine > >This sometimes returns a blank field for the value of column1 > >$string column1[20]; > >$declare test_curs cursor for >select distinct column1 >into $column1 >from test_table >where column1 is not null;>$open test_curs; >$fetch test_curs; > >I know that the informix char data type will blank fill to the end of a field. >If I am using the sting data type for the variable name the trailing blanks are >removed from the value placed into the array. If the field is blank doesn't the >database treat this as a null value? Your code shows retrieving data, so you have to ask what is in the table. In the database, the value "" is not the same as NULL. An empty string is a specific value; NULL means "not known." So if you do this: insert into test_table values ("") you have inserted a non-null value. In fact the engine always pads CHAR columns to their specified length, so the above is the same as inserting " " for however many blanks. > If not what situation will cause the >database to think there is a value in that field. Any value other than the keyword NULL on insert/update. > Does it fill with a different >value for blank then when a user enters a blank character? If it thinks a >blank field is null why would it return with this cursor? Shouldn't the >where disallow the inclusion of this blank field with the other cursor values. A blank column value is a specific column value and it is appropriate to return that specific value in this query. You could write the statement this way: ...where column 1 is not null AND column 1 NOT MATCHES " "