Question about NULL's - no-MIME version

Sorry, here comes the no-MIME version... Hi, I have the following problem: A table in my database has some constraint checks. There are also fields with only one space in it (' '). The output from dbschema is quite correct. Output from dbschema: ... check (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )), check (kat IN ('G' ,'U' ,' ' )), check (heck IN ('F' ,'S' ,'G' ,'W' ,' ' ,'L' )), check (antr IN ('A' ,'V' ,'H' ,' ' )), check (blei IN ('B' ,' ' )), check (moto IN ('O' ,'D' ,'R' ,'Z' ,' ' )), check (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )), ... But - if I test the same table in dbaccess (Info - Constraint -Checks) I get the following: Output from inside dbaccess: Constraint name Value c928_8451 (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )) c928_8453 (kat IN ('G' ,'U' ,' ' )) c928_8469 (heck IN ('F' ,'S' ,'G' ,'W' ,'' ,'L' )) c928_8477 (antr IN ('A' ,'V' ,'H' ,' ' )) c928_8482 (blei IN ('B' ,' ' )) c928_8484 (moto IN ('O' ,'D' ,'R' ,'Z' ,'' )) c928_8493 (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )) Note, that some fields have the ''-value - that's a NULL!!! But I don't want have NULLs in my tables! How can I correct this? R. Heydenreich

I have a table created on Informix Dynamic Server Version 7.30.UC8. I have three fields with a unique index. I created a aix shell script that creates an .sql files then does a isql < filename.sql. Amongst other things, it inserts new rows into the table. I want it to attempt an insert each time the script is ran in case there are any new items to be inserted into the database. Is the anyway to not show the "Cannot add due to not unique" type message echoed to the screen? Not a big deal all all but just wondering...... -Tony-

If dbschema is reporting correctly then it is just a dbaccess bug. Verify finally by selecting the contentes of syschecks and make sure. Then report the dbaccess bug to tech support. Art S. Kagel Ralf Heydenreich wrote: > > Sorry, here comes the no-MIME version... > > Hi, > I have the following problem: A table in my database has some constraint > checks. There are also fields with only one space in it (' '). The > output from dbschema is > quite correct. > Output from dbschema: > ... > check (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )), > check (kat IN ('G' ,'U' ,' ' )), > check (heck IN ('F' ,'S' ,'G' ,'W' ,' ' ,'L' )), > check (antr IN ('A' ,'V' ,'H' ,' ' )), > check (blei IN ('B' ,' ' )), > check (moto IN ('O' ,'D' ,'R' ,'Z' ,' ' )), > check (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )), > ... > > But - if I test the same table in dbaccess (Info - Constraint -Checks) I > get the following: > Output from inside dbaccess: > Constraint name Value > c928_8451 (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )) > c928_8453 (kat IN ('G' ,'U' ,' ' )) > c928_8469 (heck IN ('F' ,'S' ,'G' ,'W' ,'' ,'L' )) > c928_8477 (antr IN ('A' ,'V' ,'H' ,' ' )) > c928_8482 (blei IN ('B' ,' ' )) > c928_8484 (moto IN ('O' ,'D' ,'R' ,'Z' ,'' )) > c928_8493 (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )) > > Note, that some fields have the ''-value - that's a NULL!!! But I don't > want have NULLs in my tables! How can I correct this? > > R. Heydenreich

Just wanted to apologize for tacking a completely new msg on to yours as a reply.. twas an accident.. I canceled the msg as soon as I realized, but it already got out of the newguy servers and a lot of places don't honor cancel msgs. etc.. Anyhow.. Sorry, didn't mean to screw up this msg thread. -Tony- On Mon, 28 Aug 2000 15:35:37 +0200, Ralf Heydenreich <heydenreich@delta.de> wrote: >Sorry, here comes the no-MIME version... > >Hi, >I have the following problem: A table in my database has some constraint >checks. There are also fields with only one space in it (' '). The >output from dbschema is >quite correct. >Output from dbschema: <snip>

Ralf Heydenreich wrote: > > Sorry, here comes the no-MIME version... > > Hi, > I have the following problem: A table in my database has some constraint > checks. There are also fields with only one space in it (' '). The > output from dbschema is quite correct. > Output from dbschema: > ... > check (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )), > check (kat IN ('G' ,'U' ,' ' )), > check (heck IN ('F' ,'S' ,'G' ,'W' ,' ' ,'L' )), > check (antr IN ('A' ,'V' ,'H' ,' ' )), > check (blei IN ('B' ,' ' )), > check (moto IN ('O' ,'D' ,'R' ,'Z' ,' ' )), > check (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )), > ... > > But - if I test the same table in dbaccess (Info - Constraint -Checks) I > get the following: > Output from inside dbaccess: > Constraint name Value > c928_8451 (benz IN ('N' ,'S' ,'D' ,'B' ,'P' ,' ' )) > c928_8453 (kat IN ('G' ,'U' ,' ' )) > c928_8469 (heck IN ('F' ,'S' ,'G' ,'W' ,'' ,'L' )) > c928_8477 (antr IN ('A' ,'V' ,'H' ,' ' )) > c928_8482 (blei IN ('B' ,' ' )) > c928_8484 (moto IN ('O' ,'D' ,'R' ,'Z' ,'' )) > c928_8493 (abga IN ('1' ,'2' ,'A' ,'B' ,'C' ,' ' )) > > Note, that some fields have the ''-value - that's a NULL!!! But I don't > want have NULLs in my tables! How can I correct this? The string '' is not null -- it is just empty. So, although it would be preferable if the heck and moto fields listed ' ' instead of '', both are valid and non-null. If I had to guess for a source of the difference, then I'd look at CHAR vs VARCHAR, but I have a feeling I'd be disappointed and it is pure idiosyncrasy on the part of DB-Access. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"