Re: Nulls and Binary zeros

You do not specify what data type you are using in the Informix database to represent the DB2 data, but judging from the context, you are using CHAR(n), or possibly VARCHAR, NCHAR or NVARCHAR. None of these types accepts an ASCII NUL '\\0' 0 byte in the data. They cannot be used for storing arbitrary bit strings. Regrettably, the only data type that would allow you to store arbitrary bit strings is BYTE (a blob type), and changing the type to use BYTEs has lots of ramifications. It has long irritated me that there is no data type analogous to CHAR for BINARY data; indeed, in feature request FR2087, I said: The existing CHAR and VARCHAR datatypes are fine within their design limitations, but their design limitations are a nuisance. I want to propose a more complete set of data types for handling long fields. Please note that blobs are not always acceptable as alternatives. The full set of data types should be: CHAR(n)..........-- As now. No complaints about this one. VARCHAR(n).......-- As now. No complaints about this, if there is also... LONGVARCHAR(n)...-- Using 2-byte integer to store length, and length up to 32k - 3 bytes. (-3 because max row length is 32k-1 and 2 bytes are needed to store length). Otherwise, semantics as for VARCHAR. BINARY(n)........-- This would be a fixed length field containing arbitrary binary data. This differs from 8-bit transparent CHAR fields in that it does not blank pad things, ever! Max length would be 32k-1, as for CHAR(n). VARBINARY(n).....-- This would be the analogue of VARCHAR(n). The user would have to be able to specify the active length via some mechanism -- logically, it should be the first byte of the data that specifies the size. Max length would be 255, as for VARCHAR(n). LONGVARBINARY(n).-- Similarly, this would be the analogue of LONGVARCHAR(n). Again, there would have to be mechanism to allow the active length to be specified, and again, the first 2 bytes would be appropriate. Max length would be 32k-3, as for LONGVARCHAR(n). Note that one of the binary types (LONGVARBINARY?) would be appropriate for use in storing stored-procedures (SP) in the database. It would eliminate the need for the encoding/decoding steps in using SPs. I was astonished to find that this was not how they were implemented. The BINARY data types would allow an ESQL/C programmer to define and store a complex data-type (e.g. an 8-byte integer, or a complex (real+imaginary) number in the database. A blob is not always suitable, especially if the data type is, say, 16 bytes long. It would be the responsibility of the programmer to ensure that the data was always meaningful -- to worry about byte order and such like. Although these things can sometimes be emulated by embedding a set of columns into a table, it is often inconvenient. Please note that I am not arguing against blobs -- they have a place, especially when the data can be larger than the 32k limit. But blobs are not as convenient as orthodox columns because they have to be located and so on, and because they would be incredibly wasteful for small BINARY types (e.g. BINARY(16) or VARBINARY(56)). Note that the BINARY type can be implemented in SE at zero-cost -- C-ISAM already does anything that is necessary. I assume LONGVARCHAR, VARBINARY and LONGVARBINARY in SE would be vetoed on the same grounds that VARCHAR is vetoed, though in principle, since C-ISAM now supports variable length records, these could also be implemented in SE. The BINARY data types could be defined so that comparision operators could be used on them, but the only built-in comparisons would be byte-by-byte. There could, possibly, be a a BMATCHES (for binary matches) operator, but I would not regard this as essential. The system should also allow these types to be indexed, and for substrings to be selected -- ie, as far as possible, they should be symmetric with CHAR types. There is some evidence that it has been considered, but no official response has been made, even though I entered it in February 1992. The Universal Data Server should actually address these issues, albeit probably in a different way. With regard to 'NOT NULL'; removing that has negligible effect. A CHAR column is regarded as null if its first character is the NUL char. Otherwise it is not null; a VARCHAR is null if it is of length 1 and the character is NUL. But embedding NUL characters in the middle of the string means that string operations won't work as expected -- things like strcmp() and strcpy() will stop too soon, etc. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: wloftis@ix.netcom.com (Wesley Loftis, Jr. ) >Date: 21 Mar 1996 00:01:02 GMT >X-Informix-List-Id: <news.22324> > >I am in the process of moving a DB2 application to Informix 7.12 on HP >UNIX 10.01 platform. > >In DB2(IBM MVS), the tables have each data element defined as NOT NULL. >The actual data contains binary zeros (hex x'00') in several of the >fields. > >I defined the Informix tables also with NOT NULL, but when attempting >to load via dbaccess: load from filename insert into tablename >I am receiving the error cannot load null into NOT NULL field. > >I have removed the NOT NULL constaint from the tables for now to allow >me to load the data. Binary zeros has a specific meaning to the >application. How can I (or can I) load binary zeros into a field >defined as NOT NULL? > >My question? > >Is the presence of binary zeros a NULL or just the representation of a >NULL value? MVS DB2 allows this without error. The presence of LOW >VALUES is a standard MVS COBOL programming method. > >Can any UNIX and/or relational DB expert solve this problem or is it a >problem? > >Wes Loftis >Thanks in advance.