AW: O$: Null Fields

[Alan Davey]

Hi,
This is the way the default is supposed to work.  If you supply a value for
a column (including a null value) you are telling the database to override
the default value for that column.  If you want to have the default value
then you need to either use the 'default' keyword or exclude that
column/value combination from your insert clause.

Regards,

Alan Davey

On Wed, Apr 22, 2009 at 5:39 PM, Bastiaan Olij <lists at basenlily.nl> wrote:

> Hi Rudolf and Cliff,
>
> This is also true for Sybase, I'm guessing that means also MS SQL will
> hold true to this. If you supply a value, the database will ignore the
> default, even if that value is NULL.
>
> Never tried this on any other databases though.
>
> Greetz,
>
> Bas
>
> Rudolf Bargholz wrote:
> > Hi Cliff,
> >
> > Just spent a couple of days thinking I was very smart and attempting to
> implement what you describe below. I went through all our schemas and
> assigned an additional property to the schema column in order to set a
> DEFAULT value, only to find out in the end if you explicitly insert a
> NULL into a column (at least with DB2), then DB2 will insert the NULL,
> irrespective of the value you supply as a default. Only if you do not
> supply the column in your insert statement will the default value be
> inserted.
> >
> > In your example below, could you try:
> >
> > insert into test (id, foo, bar) values (default, cast(NULL as char(1)),
> 99);
> >
> > and have a look what you get returned from the select? In DB2, to my
> dismay, it was NULL and not the expected empty value. Could have kicked
> myself following this white elephant and wasting so much time without
> testing thoroughly first. A
> >
> > Do Row.$definefromsqlclass('MySchema')
> > Do Row.$insert()
> >
> > till thus not insert any DEFAULT values, but rather NULL in all of the
> specified columns.
> >
> > Regards
> >
> > Rudolf Bargholz
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>