Re: NULL characters in CHAR(n) data type?

--Boundary_(ID_wdQo6omzC864PHNPZDQyMQ) Content-type: text/plain; charset=us-ascii Content-disposition: inline Content-transfer-encoding: 7BIT Jim Why dont you use VARCHAR(n, 0)? HTH Sujit Jim Blye <jimblye@us.bim.com> on 01/14/2000 07:10:48 AM Please respond to Jim Blye <jimblye@us.bim.com> To: informix-list@iiug.org cc: Subject: Re: NULL characters in CHAR(n) data type? --Boundary_(ID_wdQo6omzC864PHNPZDQyMQ) Content-type: text/plain; charset=iso-8859-1 Content-disposition: inline Content-transfer-encoding: quoted-printable Helmut Leininger wrote: > Jim Blye wrote: > > > Is there a way to store NULL characters in a CHAR(n) data type? W= hen I > > try this, the NULL characters (0x00) get converted to spaces (0x20)= . > > Maybe there is an Informix attribute I could set that would change = this > > behavior??? Since the data type is fixed length, I don't underst= and > > why NULL characters are not allowed. > > > > My data for this column is binary (length 40-254 bytes), and the co= lumn > > is the primary key. I don't know of any binary data type in Inform= ix > > that is suitable for a primary key. > > > > My program uses ODBC and works with DB2, Oracle, Sybase, and SQL > > Server. How can I get this to work with Informix? > > Hi Jim, > > Characters 0x00 are not generally forbidden in CHAR type columns. The= > restriction is only they must not be in the first byte because Inform= ix > recognizes a 0x00 in the first byte of a CHAR column as NULL value (i= n > contrary to Oracle where NULL values are stored outside the data part= of the > column). > > Regards > Helmut > > ----------------------------- Helmut, I believe what you say is true for Informix Dynamic Server 7.22. At l= east my code seemed to be working with that version. Now we are using 7.30. I don't have leading zeros. Since my data is variable length and CHAR= (n) is a fixed length data type, I use the first byte as a length field which is= always nonzero. The 7.30 documentation clearly says that NULL characters can not be use= d in the CHAR(n) data type, but I was hoping there was a way around this restri= ction. For example, SQL Server normally strips trailing zeros, but the behavio= r can be changed by SET ANSI_PADDING ON. Here's a quote from the Informix Guide to Database Design and Implement= ation=AE: "A CHAR(n) or NCHAR(n) value can include tabs and spaces but normally contains no other nonprinting characters. When you insert rows with INS= ERT or UPDATE, or when you load rows with a utility program, no means exist= s for entering nonprintable characters. However, when a program that uses= embedded SQL creates rows, the program can insert any character except = the null (binary zero) character." I've found that starting at the first NULL byte, Informix converts the = rest of the data to spaces (0x20). The only solution that I have is to change the data type to BYTE and ad= d an additional column as the key. Unfortunately, such a change would proba= bly be considered too big of a hit, and thus Informix support for this product= would be dropped. Jim Blye = --Boundary_(ID_wdQo6omzC864PHNPZDQyMQ)--