AW: O$: Null Fields

Joe Maus jmaus at theccgllc.com
Wed Apr 22 11:08:06 EDT 2009


----- Rudolf Bargholz <bargholz at onlinetravel.ch> 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
> 
> till thus not insert any DEFAULT values, but rather NULL in all of the specified columns.

As I mentioned previously, many backends will not store an "empty" string - but will replace them implicitly with a NULL.  I suspect this has to do with searches as you'd have to search "where column is null or column = '' and adding another condition would simply take too much time for no benefit.  That is, if you're almost always looking to see if something exists in that column and an "empty" string (if it were allowed) is not all that different from a null.  Not to mention the additional overhead you'd have to think about in how functions and nulls behaved...

If you're looking to see if something is "initialized" then you'd have to code for it specifically.  For example, a flag of some kind.  But really, you could devise a data structure that handles this for you where you have the ability to track if a column was "inited" by the user (set to empty) and still have the backend be happy about storing nulls in the actual columns that were "inited" by the user. This would be easier on a backend supporting stored procedures, but here's the pseudo code to effect such a thing in a manner that would suffice - if not annoy Codd...

the table myTable is
pk      number(16,0) not null,
col1    varchar(100) null,
col2    varchar(100) null

the table whatIsInited is
pk       number(16,0) not null,
fk       number(16,0) not null,
col_name varchar(30) not null

Say the user "inited" col1 (set it to empty - how or why they would do this is not material to the discussion : )) and entered "I like beer." in col2.  In your code in Omnis you'd have to know that col1 was inited and col2 contained actual data but you'd be able to insert something like this (as part of a transaction):

insert into myTable values (100, null, 'I like beer.')
insert into whatIsInited values (10, 100, 'col1')

So now you have a row of data telling you the column was inited as "the flag" - which is much more flexible, if not a bit more complex...  But you can now do something like this in your select:

select
   a.pk,
   case when a.col1 is not null then
     a.col1
   else when (select count(*) from whatIsInited where fk = a.pk and col_name = 'col1') = 1 then
     '' -- this may be translated to a NULL by the DAM your using - you'd have to "trick" it to return empty
        -- by using some other kind of function (e.g. coalesce()) that would pass though the DAM as desired
   else
      null
   end as col1,
   case when a.col2 is not null then
     a.col2
   else when (select count(*) from whatIsInited where fk = a.pk and col_name = 'col2') = 1 then
     '' -- this may be translated to a NULL by the DAM your using - you'd have to "trick" it to return empty
        -- by using some other kind of function (e.g. coalesce()) that would pass though the DAM as desired
   else
      null
   end as col2
from
   myTable a
where
   ...

where you're using data rows to dynamically show what is "inited" or not.  Of course, once the user has put something in the column you'd have to deleted the appropriate row in whatIsInited but even if you didn't the select would still work.

Perhaps this exercise was useful to someone : )

Regards,

Joe Maus





More information about the omnisdev-en mailing list