Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

<snip>
p.s. NULL in databases is evil.... it just makes things harder and
forces you to scatter your defaulting logic all over.  Think in terms
of the NullObject pattern... where a specific row/value encodes the
_behavior_ of what you think NULL would mean.  It's more explicit and
easier to follow
</snip>

Admittedly, null may act weird sometime.  Maybe like intoxicants, null is
evil to those who abuse it. I think it is a thing of beauty, really.
If you loose null, you loose lots of things that null enables in advanced
designing. For instance, null enables exclusive OR associations for
non-binary type (loosely speaking). A practical application is that Null
let's you have optional foreign/unique keys. Null actually makes unique keys
practical. You cannot have a default value on a unique constraint. Some
people (and am one of them) even think that null is elegant. So much so that
Microsoft had to yield to the practical pressure to bubble nullity into the
.NET  valueType space (Two or so years after people whipped up their own
.NET Nullable types). Null is not 'empty string' because for then, it would
have an address. Null has no address. I have no other way of representing a
memory address that does not exist because it has to be an unsigned integer
and 0 is the first address. And sometimes, I need to store that fact in a
database somewhere, relational or not, indicating that the address does not
exist.

And by the way, I think the NullObject pattern was created to help prevent
people from throwing NullReferenceException in the application layer. It is
not intended for storing crazy stuff in the database, otherwise, all types
may need to be reduced to some magic value. Magic values are a poor
replacement for 'nothingness', probably because the magic value in the first
place is not a type of 'nothingness'. The null object pattern is too much
work, if you ask me, compared to other options. I still prefer to test
before you 'dot' and that works for the goose.quark() as well as
gander->quark() because, let's face it, gander.quark() is undefined and
could be left as such until we approach the UI. These are personal opinions,
however, and am sure that there are cuter opinions out there. The cutest of
it all for me is that null unifies everything for all types, meaning one
thing and only one thing: no value is appropriate because in there's no
place for this thing in the space of all possible members of the alphabet of
this set.

And anytime you set a magic value with the magical powers of indicating a
loss of existence. It, too, looses logical existence. It cannot occur in the
set. It and all variants of it! And when you talk variants, localization
people, Codebase people and binary people begin to shiver because that's a
can of worms out there ... as in many ways to represent a magic number and
it looks as if it's not the same as the chosen variant that. Use of magic
value to represent nullity may actually be a genocide of all the possible
forms of that value. It can only work if you have specific problem (or
domain) context!! And that is why it cannot work for most people who do
context-agnostic work.

No doubt, the null enigma needs some taming ...  so also do some of the
steps in nuclear fission research. Well, then, maybe that's why it takes a
college degree before people get unleashed to go trying these kinds of stuff
on their own.

P


--------------------------------------------------
From: "Marc Brooks" <[email protected]>
Sent: Tuesday, November 27, 2007 3:54 PM
To: <[email protected]>
Subject: Re: [DOTNET-CLR] Strange Wierdness with SQLServer Bit fields and
SQLProvider

> I suspect you're not talking to the database you think you are...
> perhaps working with a cached connection string to some other server?
>
> But, onto the rest of the post...
>
>> the database uses ref_isvalid as a tristate variable, true, false ,
>> dbnull
>> (not used)
>
> What is the business meaning of "not used"?  What does it default to?
> This is the problem with NULL columns... somewhere, sometime, you have
> to codify the meaning of the NULL selection... why not do so in the
> database where everyone gets to see and benefit from that?
>
>> For this example ref_isvalid defines if we're in debug mode, ( there are
>> 200+ ref tables like this , so changing NOT NULL is not possible.)
>
> Ummm, sure it is... given your schema, this will actually take less
> room (since a single bit in a row will be allocated to an INT and
> waste the rest of the bits)
>
> create table REF_STATE (
>  ref_id int identity(1,1),
>  ref_label varchar(25),
>  ref_isvalid TINYINT CHECK CONSTRAINT [CK_REF_STATE_ref_isValid]
> CHECK  ([ref_isvalid]=0 OR [ref_isvalid]=1 OR [ref_isvalid]=2)
> )
>
> Then "null" == 2. You could easily add the non-null column, set the
> correct valid and drop the old column.  If you (even better) get rid
> of the tristate logic, you can use a column default and non-null it
> in-place.
>
>> to get information from this we use a stored procedure
>>
>> create procedure SP_GetState(@debug bit) as
>> begin
>>   select * from ref_state where ref_state = 1 or @debug = 1
>> end
>
> Eeew, surely you are actually NOT using SELECT * (right?)  Does anyone
> actually care about those audit columns you add down the road?  Never
> use *.  Never.
>
>> when we loop over the dataset ,  we use this function
>>
>>  public T DefaultValue<T>(object o , object defaultValue){
>>  if(o == null || o == DBNull.Value) return defaultValue;
>>  return (T)o;
>>  }
>
> Seems like you should really check if "o" is an instance of DBNull
> before blindly comparing it's value against DBNull.Value, right?  So
>
> public T DefaultValue<T>(object o , object defaultValue)
> {
>   if (o == null)
>      return defaultValue;
>
>   if (o.GetType == typeof(DBNull) && (DBValue)o == DBNull.Value)
>     return defaultValue;
>
>   return (T)o;
> }
>
>>  refItem.IsValid = DefaultValue<Boolean>(dr["ref_isValid"],false);
>
> When, exactly, would you pass anything other than false to such a read
> (of the REF_* tables).
>
>> Selects direct on the database return the correct data, Executing the
>> storedprocedure returns the correct value , Somewhere between the
>> DataAdapter.Fill(ds) and the   dr["ref_isvalid"]  it's either loosing the
>> value or not setting it.  Any Ideas how to debug this or references to an
>> example please?
>
> I will state that you are NOT looking at the same tables, or not using
> the same provider, or something like that....  Got an index on the
> table that excludes NULL (not that you should EVER have a BIT in an
> index (unless it's really late in the column list)).
>
> Marc
>
> p.s. NULL in databases is evil.... it just makes things harder and
> forces you to scatter your defaulting logic all over.  Think in terms
> of the NullObject pattern... where a specific row/value encodes the
> _behavior_ of what you think NULL would mean.  It's more explicit and
> easier to follow... For example:
>
> CREATE TABLE [dbo].[Profile](
> [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
> [DF_Profile_ID]  DEFAULT (newid()),
> [Type] [char](1) NOT NULL CONSTRAINT [DF_Profile_Type]  DEFAULT ('U'),
> [UserName] [nvarchar](150) NOT NULL,
> [Password] [nvarchar](50) NOT NULL,
> [FriendlyName] [nvarchar](50) NOT NULL CONSTRAINT
> [DF_Profile_FriendlyName]  DEFAULT (''),
> [OwnerId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Profile_OwnerId]
> DEFAULT ('00000000-0000-0000-0000-000000000000'),
> [ValidFrom] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidFrom]
> DEFAULT (getutcdate()),
> [ValidTo] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidTo]
> DEFAULT ('9999-12-31 23:59:59.997'),
> CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT
> [FK_Profile_Owner] FOREIGN KEY([OwnerId])
> REFERENCES [dbo].[Profile] ([ID])
> ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_Owner]
>
> ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT
> [CK_Profile_Type] CHECK  (([Type]='R' OR [Type]='U' OR [Type]='P'))
> ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [CK_Profile_Type]
>
> INSERT INTO [dbo].[Profile]
>        ([ID], [Type], [UserName], [Password], [FriendlyName],
> [ValidFrom], [ValidTo])
> VALUES('00000000-0000-0000-0000-000000000000', 'R', '', '', '',
> '1753-01-01 00:00:00.000', '1753-01-01 00:00:00.000')
>
> Now I can always code knowing that valid Profile rows are returned
> when GetUTCDate()  BETWEEN [ValidFrom] AND [ValidTo].  I can always
> code knowing that the [OwnerId] references a Profile row. I can
> special case in my code only against the [Type] being 'R' for Root,
> etc... thus the query to get all valid profiles with owner names and
> type...
>
> SELECT
>   p.[Id]
>   , p.[FriendlyName]
>   , o.[FriendlyName] AS OwnerName
>   , o.[Type] AS OwnerType
> FROM [dbo].[Profile] AS p
> INNER JOIN [dbo.Profile] AS o
>   ON p.OwnerId = o.OwnerId
> WHERE GetUtcDate() BETWEEN p.[ValidFrom] AND p.[ValidTo]
>
> The alternative where a typically nullable schema looks like this:
>
> SELECT
>   p.[Id]
>   , p.[FriendlyName]
>   , ISNULL(o.[FriendlyName], '') AS OwnerName
>   , ISNULL(o.[Type], 'R') AS OwnerType
> FROM [dbo].[Profile] AS p
> LEFT JOIN [dbo.Profile] AS o
>   ON p.OwnerId = o.OwnerId
> WHERE
>   (p.[ValidFrom] <= GetUtcDate() OR p.[ValidFrom] IS NULL)
>   AND (p.[ValidTo] >= GetUtcDate() OR p.[ValidTo] IS NULL)
>
> It's just so much easier in the later schema to "forget" the NULL
> issues, plus you end up preventing INDEX use...
>
> --
> "He uses statistics as a drunken man uses lamp-posts… for support
> rather than illumination." Andrew Lang
>
> Marc C. Brooks
> http://musingmarc.blogspot.com
>

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com