Insert null value

Hello All, IDS 11.50 FC8 small doubt > info columns for tab1; Column name Type Nulls name char(5) no > info columns for tab2; Column name Type Nulls id integer no > insert into tab1 values('');1 row(s) inserted. > insert into tab2 values('');391: Cannot insert a null into column (tab2.id). '' is treated as "null" when inserting into int and "empty string" while inserting into char? Is the error due to data type mismatch or '' is treated as null in Informix? Regards,

On 05/09/11 13:33, VIKAS HIVARKAR wrote: > Hello All, > > IDS 11.50 FC8 > > small doubt > >> info columns for tab1; > > Column name Type Nulls > name char(5) no > >> info columns for tab2; > Column name Type Nulls > id integer no > >> insert into tab1 values('');> 1 row(s) inserted. > >> insert into tab2 values('');> 391: Cannot insert a null into column (tab2.id).> > '' is treated as "null" when inserting into int and "empty string" while > inserting into char? Is the error due to data type mismatch or '' is treated > as null in Informix? > > Regards, > '' just represent an empty string (padded with spaces in the case of chars, and not padded in the case of varchars): when you insert it into a char column, that's precisely what you store - an empty string, which is not null. If you are inserting into an integer column, however, the string value must first be converted to an integer. '' converts to a NULL integer, hence the error. If you change your test to use the NULL keyword in place of '', both inserts will fail in the same way. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Data type mismatch and conversion semantics. The empty string when inserted into a CHAR/VARCHAR/LVARCHAR is inserted as just that, a non-null string that is empty. If you are inserting or updating a numeric column with an empty string, what number would you want the string converted to? It cannot be converted into a zero (0) since that is a different string. The only logical choice is to convert it to what it is "no number" or NULL. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Sep 5, 2011 at 8:33 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hello All, > > IDS 11.50 FC8 > > small doubt > > > info columns for tab1; > > Column name Type Nulls > name char(5) no > > > info columns for tab2; > Column name Type Nulls > id integer no > > > insert into tab1 values('');> 1 row(s) inserted. > > > insert into tab2 values('');> 391: Cannot insert a null into column (tab2.id).> > '' is treated as "null" when inserting into int and "empty string" while > inserting into char? Is the error due to data type mismatch or '' is > treated > as null in Informix? > > Regards, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8af484e2c704ac317c53

Thanks Marco and Art, Now I understand how "" works in informix. thanks to fernando for his blog post on handling null. Regards, ================================================================================ = Data type mismatch and conversion semantics. The empty string when inserted into a CHAR/VARCHAR/LVARCHAR is inserted as just that, a non-null string that is empty. If you are inserting or updating a numeric column with an empty string, what number would you want the string converted to? It cannot be converted into a zero (0) since that is a different string. The only logical choice is to convert it to what it is "no number" or NULL. Art Art S. Kagel ================================================================================ = '' just represent an empty string (padded with spaces in the case of chars, and not padded in the case of varchars): when you insert it into a char column, that's precisely what you store - an empty string, which is not null. If you are inserting into an integer column, however, the string value must first be converted to an integer. '' converts to a NULL integer, hence the error. If you change your test to use the NULL keyword in place of '', both inserts will fail in the same way. -- Ciao, Marco ================================================================================ = > Hello All, > > IDS 11.50 FC8 > > small doubt > >> info columns for tab1; > > Column name Type Nulls > name char(5) no > >> info columns for tab2; > Column name Type Nulls > id integer no > >> insert into tab1 values('');> 1 row(s) inserted. > >> insert into tab2 values('');> 391: Cannot insert a null into column (tab2.id).> > '' is treated as "null" when inserting into int and "empty string" while > inserting into char? Is the error due to data type mismatch or '' is treated > as null in Informix? > > Regards, >

Hello Vikas, Got to see your mail only today. Well, I see couple of responses from IIUG site. Guess, that meets an answer to your query. Do let me know, if you need further details on the same. Regards Prasanna Alur Mathada Manyata Business Park, Nagwara Competitive Technology Enablement - Informix Database Bangalore, 560045 India Software Labs - Information Management India IBM Software Group Phone: +91-080-28060954 Mobile: +91-98-86-427876 e-mail: amprasanna@in.ibm.com From: "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> To: ids@iiug.org Date: 05/09/2011 18:05 Subject: Insert null value [24843] Sent by: ids-bounces@iiug.org Hello All, IDS 11.50 FC8 small doubt > info columns for tab1; Column name Type Nulls name char(5) no > info columns for tab2; Column name Type Nulls id integer no > insert into tab1 values('');1 row(s) inserted. > insert into tab2 values('');391: Cannot insert a null into column (tab2.id). '' is treated as "null" when inserting into int and "empty string" while inserting into char? Is the error due to data type mismatch or '' is treated as null in Informix? Regards, ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.