NULL characters in CHAR(n) data type?

Is there a way to store NULL characters in a CHAR(n) data type? When 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 understand why NULL characters are not allowed. My data for this column is binary (length 40-254 bytes), and the column is the primary key. I don't know of any binary data type in Informix 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?

Jim Blye wrote: > Is there a way to store NULL characters in a CHAR(n) data type? When 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 understand > why NULL characters are not allowed. > > My data for this column is binary (length 40-254 bytes), and the column > is the primary key. I don't know of any binary data type in Informix > 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 Informix recognizes a 0x00 in the first byte of a CHAR column as NULL value (in contrary to Oracle where NULL values are stored outside the data part of the column). Regards Helmut -- Helmut Leininger Bull AG / Vienna Open Systems Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.

Helmut Leininger wrote: > Jim Blye wrote: > > > Is there a way to store NULL characters in a CHAR(n) data type? When 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 understand > > why NULL characters are not allowed. > > > > My data for this column is binary (length 40-254 bytes), and the column > > is the primary key. I don't know of any binary data type in Informix > > 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 Informix > recognizes a 0x00 in the first byte of a CHAR column as NULL value (in > 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 least 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 used in the CHAR(n) data type, but I was hoping there was a way around this restriction. For example, SQL Server normally strips trailing zeros, but the behavior can be changed by SET ANSI_PADDING ON. Here's a quote from the Informix Guide to Database Design and Implementation®: "A CHAR(n) or NCHAR(n) value can include tabs and spaces but normally contains no other nonprinting characters. When you insert rows with INSERT or UPDATE, or when you load rows with a utility program, no means exists 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 add an additional column as the key. Unfortunately, such a change would probably be considered too big of a hit, and thus Informix support for this product would be dropped. Jim Blye