Unload Question

I am getting an unexpected result in an unload. An empty string is being unloaded as |\\\\ | as demonstrated by the following query. unload to "d:\\\\gisextract\\\\test.unl" select "", '', ' ' FROM systables wheretabid=1 Resulting File \\\\ |\\\\ | | I have not seen this behavior before in Informix. Is there a way to turn this off? I have IDS 9.4 running on a Windows 2000 Advanced Server, not my choice. Thanks. Lennie Informix DBA Information Technology Tax Team Lake County, IL

"Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > >I am getting an unexpected result in an unload. An empty string is being >unloaded as |\\\\ | as demonstrated by the following query. > >unload to "d:\\\\gisextract\\\\test.unl" select "", '', ' ' FROM systables where>tabid=1 > >Resulting File >\\\\ |\\\\ | | > >I have not seen this behavior before in Informix. Is there a way to turn >this off? I have IDS 9.4 running on a Windows 2000 Advanced Server, not my >choice. You are unloading two blank, non-null values and a space. The resulting file looks just as I would expect it to look. Are you expecting the first two fields to appear as though you unloaded NULL values? (That is a very different thing.) -- June Hunt

"June C. Hunt" <june_c_hunt@hotmail.com> wrote on 07/22/2004 01:40:53 PM: > "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > >I am getting an unexpected result in an unload. An empty string is being > >unloaded as |\\\\ | as demonstrated by the following query. > > > >unload to "d:\\\\gisextract\\\\test.unl" select "", '', ' ' FROM systables where> >tabid=1 > > > >Resulting File > >\\\\ |\\\\ | | > > > >I have not seen this behavior before in Informix. Is there a way to turn > >this off? I have IDS 9.4 running on a Windows 2000 Advanced Server, not my > >choice. > > You are unloading two blank, non-null values and a space. The resulting > file looks just as I would expect it to look. Are you expecting the first > two fields to appear as though you unloaded NULL values? (That is a very > different thing.) Actually, it's marginally more complex than that. If you had SQLCMD (shameless plug - see http://www.iiug.org/software) and ran: sqlcmd -d stores -T <<'!' select "", '', ' ' from systables where tabid = 1; ! You would see the information that the first two values are 'VARCHAR(1)' and the third is 'CHAR(1)'. The backslash-space notation is used by DB-Access to indicate an empty but non-null VARCHAR value. You did realize that the empty string is not null, didn't you? If you poke about on disk, you'll find that an empty non-null VARCHAR has a single byte representation - 0x00 - (zero bytes of data follow), whereas a null VARCHAR has a two byte representation - 0x01 0x00 - (one byte of data, and it's the ASCII NUL byte). This outlandish backslash space notation is used to distinguish between the empty but non-null value and the null value - and only in VARCHAR or NVARCHAR. (PS: SQLCMD does not support that notation; it also doesn't handle the DB-Access output with -X specified. BUG - but it hasn't hurt me yet. Note that there is no way to generate backslash space in the output file from data actually in the database. The nitty-gritty details are in the unload.format document in the SQLCMD distribution since 2001 - I just haven't gotten around to implementing them in SQLCMD yet.) -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"