IDS Forum

Re: Insert null value

Posted By: VIKAS HIVARKAR
Date: Tuesday, 6 September 2011, at 12:14 a.m.

In Response To: Re: Insert null value (Art Kagel)

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,
>

Messages In This Thread

• Insert null value
  VIKAS HIVARKAR -- Monday, 5 September 2011, at 8:33 a.m.
  • Re: Insert null value
    Marco Greco -- Monday, 5 September 2011, at 8:41 a.m.
  • Re: Insert null value
    Art Kagel -- Monday, 5 September 2011, at 9:14 a.m.
    • Re: Insert null value
      VIKAS HIVARKAR -- Tuesday, 6 September 2011, at 12:14 a.m.
  • Re: Insert null value
    Prasanna A Mathada -- Tuesday, 6 September 2011, at 12:46 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.