Re: NULL value

On Tue, 20 Oct 1998 18:22:23 +0100, Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> wrote: Helmut Leininger wrote: > > julie lin wrote: > > > Hello, > > > > I have a little ESQL/C program which fetchs data using dynamic binding > > method (has replacable parameters "?" in the where condition) , my > > problem is it fails to fetch NULL value if I bind the replacable > > parameters with a string containing only '\\0'. Why it doesn't work if > > informix indicates a character field containing a NULL as its value by > > > > storing a zero byte in the first character ? > > > > I'm using INFORMIX-ESQL Version 7.24.UC2 on AIX 4.2.1 > > > > Thanks! > > A NULL value does not match to any distinct value (and that's what you are > trying to do) by definition of SQL. It is only "by chance" that Informix > (unfortunately) indicates NULL values by storing a '\\0' in the leftmost > character (also in INTEGER etc.). > > Regards > >Also, I bet your WHERE clause looks like this: >WHERE mycol = ? >whereas what you should write is: >WHERE mycol IS NULL I know " WHERE mycol IS NULL " will work , but for some reason, it'll be easier for me for using only dynamic SELECT statements (has replacable parameters "?" in the where condition), I just wonder why I can insert NULL data by using the following dynamic INSERT statement ? sprintf(insert,"insert into test values (?)"); EXEC SQL prepare ss from :insert; /* create a sqlda structure bind , execute the query USING bind */ bind=(struct sqlda *)malloc(sizeof(struct sqlda)); bind->sqlvar=(struct sqlvar_struct *)malloc(sizeof(struct sqlvar_struct)); bind->sqld=1; bind->sqlvar->sqltype=CCHARTYPE; bind->sqlvar->sqllen=3; memset(input,'\\0',4); bind->sqlvar->sqldata = input; EXEC SQL execute ss using descriptor bind;