Varchar type column with null value has a '\\' when unloaded

Hi, Would like to ask if you have encountered this before that we have a varchar type column in one of our table with null value that when we do a select statement it will display a blank value but when we do an unload it has a '\\' value. I suspect that there is a special character being inserted. How can I verify and solve the issue? We are using IDS 7.31 on HP-Unix 11. Any feedback would be appreciated. Thank you in advance. Best Regards, Angelito sending to informix-list

angelito_ventula@non.agilent.com wrote: > > Would like to ask if you have encountered this before that we have a > varchar type column in one of our table with null value that when we do a > select statement it will display a blank value but when we do an unload it > has a '\\' value. I suspect that there is a special character being > inserted. How can I verify and solve the issue? > > We are using IDS 7.31 on HP-Unix 11. > > Any feedback would be appreciated. Thank you in advance. If you search the newsgroup, you'll find this question has come up at least a couple of times. The short answer is that DB-Access handles null and zero-length non-null values differently when you are dealing with VARCHAR, NVARCHAR, and LVARCHAR fields. From what I've read, it sounds like you have a zero-length non-null value, not a null. Back on 10 August 2004, Jonathan Leffler described how null and zero-length non-null values are stored on disk. See the thread in comp.databases.informix entitled 'Unexpected backslashes \\ in dbaccess unload output' for Jonathan's complete response. -- June Hunt

Angelito, I will bet you do not have a varchar type column in one of your tables with null value that will display a blank value from a select statement, but has a '\\' value when you do an unload it. Your eyes will deceive you. Try a simple test with concatenation to prove it. CREATE TABLE null_test ( serial_col SERIAL NOT NULL, varchar_col VARCHAR(255), PRIMARY KEY (serial_col) ); INSERT INTO null_test VALUES (0, ''); INSERT INTO null_test VALUES (0, ' '); INSERT INTO null_test VALUES (0, NULL); SELECT serial_col, '-' || varchar_col || '-' FROM null_test; -- RESULTS: -- serial_col 1 -- (expression) >< -- -- serial_col 2 -- (expression) > < -- -- serial_col 3 -- (expression) -- Note that any string concatenated to NULL is NULL. UNLOAD TO null_test.unl SELECT serial_col,varchar_col FROM null_test; -- RESULTS: -- cat null_test.unl -- 1|\\ | -- 2| | -- 3|| You can also select and unload "WHERE varchar_col IS NULL", of course, just to see. This question comes up a lot. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.