My GoD, ASCII 0 is NULL in my Informix Online Dynamic Server 7.24

My God, my God I have always read that an empty chain is not the same thing that a NULL value in SQL, and that a numeric 0 value is different to a NULL value in SQL. But with my Informix Server (Online Dynamic Server 7.24) when I insert a character ASCII 0 in a field CHAR(1), in fact I insert a NULL value !!!??? I cannot have a table where the primary key is a character, for example to link data to each ASCII character. :-( I have attempted the insert the ASCII 0 character (in a primary key) from an Informix-4GL program and also from a java program using the JDBC driver, but without success. Some idea ? Is this a bug of the database server ? Thanks in advance!

ETHERMI wrote: > My God, my God Deities, especially undefined ones, are not usually invoked in this news group. Regrettably, the producers of IDS are all too human, as are the other denizens of this news group. > I have always read that an empty chain is not the same > thing that a NULL value in SQL, I'm not sure that there are any chains in SQL (beyond the shackles of backwards compatibility). > and that a numeric 0 value is different to a NULL value in SQL. Well, in a numeric context, that is undoubtedly true. > But with my Informix Server (Online Dynamic Server > 7.24) when I insert a character ASCII 0 in a > field CHAR(1), in fact I insert a NULL value !!!??? And in ISQL 2.00 and 2.10, and in Turbo 1.10.03, and in OnLine 4.00, 4.10, 5.0x, 5.1x and 5.2x, and in ODS 6.00, 7.00 and 7.1x, and in IDS 7.2x and 7.3x, and in XPS 8.xy, and in IUS 9.0x and 9.1x, and in IDS 9.2x, 9.30, 9.40 and 10.00. Which version did I forget? No - ISQL 1.10 did not support nulls at all; I didn't forget it. Yes, your observation is correct. You can (if you are very careful) store ASCII NUL characters in other positions in a longer character field, but if the first byte is ASCII NUL, the string is interpreted as an SQL NULL string. > I cannot have a table where the primary key is a > character, for example to link data to each > ASCII character. Correct - but it is possible to do the job with a stored procedure and the other 255 characters. I debugged a piece of SQLCMD recently creating such a table, and such a pair of stored procedures (CHR() and ASCII() - though ASCII() is a misnomer for something that can handle any of the 8th bit set characters). However, the code is at the office. > :-( > > I have attempted the insert the ASCII 0 character > (in a primary key) > from an Informix-4GL program and also from a > java program using the JDBC driver, but without success. > > Some idea? It is expected, documented behaviour. > Is this a bug of the database server ? Debatable, it isn't going to change in a hurry. Does that make it a feature or a bug? Anyway, get used to it - that's the way Informix has always behaved, and will continue to behave. You might also care to note the symmetric range of supported values for SMALLINT, INT, INT8 and meditate on the plausible use of the non-supported value. Bingo! You got it. I didn't say that was good either, though it is much more acceptable than the problem with strings. You might also care to note that an empty VARCHAR is different from a NULL stored for a VARCHAR - one occupies one byte, the other occupies two. However, you still face the same problem - a VARCHAR with ASCII NUL in the first character is NULL. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

Thank you Jonathan for your answer, comments and suggestions. I have the impression that you are a member of the team of development of some informix product. The expression "My God" only indicates surprise, and it should not be censored. Certainly, it surprises that a possible value of a field, be also a NULL value. I suppose that all this is due to a design decision. Surely, the Database Manager gets bigger efficiency, etc. Anyway, certain it is that this matter doesn't represent an obstacle for most of the situations. Informix is a good Database Manager. Then, we conclude that it is not a bug of Informix. Bye Jonathan Leffler escribi': > ETHERMI wrote: > >> My God, my God > > > Deities, especially undefined ones, are not usually invoked in this news > group. Regrettably, the producers of IDS are all too human, as are the > other denizens of this news group. > >> I have always read that an empty chain is not the same >> thing that a NULL value in SQL, > > > I'm not sure that there are any chains in SQL (beyond the shackles of > backwards compatibility). > >> and that a numeric 0 value is different to a NULL value in SQL. > > > Well, in a numeric context, that is undoubtedly true. > >> But with my Informix Server (Online Dynamic Server >> 7.24) when I insert a character ASCII 0 in a >> field CHAR(1), in fact I insert a NULL value !!!??? > > > And in ISQL 2.00 and 2.10, and in Turbo 1.10.03, and in OnLine 4.00, > 4.10, 5.0x, 5.1x and 5.2x, and in ODS 6.00, 7.00 and 7.1x, and in IDS > 7.2x and 7.3x, and in XPS 8.xy, and in IUS 9.0x and 9.1x, and in IDS > 9.2x, 9.30, 9.40 and 10.00. > > Which version did I forget? No - ISQL 1.10 did not support nulls at > all; I didn't forget it. > > Yes, your observation is correct. You can (if you are very careful) > store ASCII NUL characters in other positions in a longer character > field, but if the first byte is ASCII NUL, the string is interpreted as > an SQL NULL string. > >> I cannot have a table where the primary key is a >> character, for example to link data to each >> ASCII character. > > > Correct - but it is possible to do the job with a stored procedure and > the other 255 characters. I debugged a piece of SQLCMD recently > creating such a table, and such a pair of stored procedures (CHR() and > ASCII() - though ASCII() is a misnomer for something that can handle any > of the 8th bit set characters). However, the code is at the office. > >> :-( >> >> I have attempted the insert the ASCII 0 character >> (in a primary key) >> from an Informix-4GL program and also from a >> java program using the JDBC driver, but without success. >> >> Some idea? > > > It is expected, documented behaviour. > >> Is this a bug of the database server ? > > > Debatable, it isn't going to change in a hurry. Does that make it a > feature or a bug? Anyway, get used to it - that's the way Informix has > always behaved, and will continue to behave. > > You might also care to note the symmetric range of supported values for > SMALLINT, INT, INT8 and meditate on the plausible use of the > non-supported value. Bingo! You got it. I didn't say that was good > either, though it is much more acceptable than the problem with strings. > > You might also care to note that an empty VARCHAR is different from a > NULL stored for a VARCHAR - one occupies one byte, the other occupies > two. However, you still face the same problem - a VARCHAR with ASCII > NUL in the first character is NULL. >