DBNull class – something very confusing about it

[beginwithl]

Hi

I just started learning ADO.NET and Sql

1)

From SQL book:
“SQL, however, doesn’t allow for data to hold no value. Fields with no
specified value actually do have a value: NULL. NULL is not the same
thing as nothing; NULL represents the unknown.

But MSDN claims:
“The DBNull class represents a nonexistent value. In a database, for
example, a column in a row of a table might not contain any data
whatsoever. That is, the column is considered to not exist at all
instead of merely not having a value. A DBNull object represents the
nonexistent column.”


a) So MSDN essentially claims that Sql table may have a column in a
row that doesn’t have any value assign to it ( not even NULL ) and
thus column isn’t even considered to exist?

But Sql book claims that all fields in a DB must have a value assigned
to them. So which is true?



b) Perhaps this is just a matter of semantics, but according to SQL
book a column with value of NULL is conceptually still considered to
exist, and NULL here represents the unknown value. In other words,
column has a value, but we don’t know what that value is.
MSDN on the other hand claims DBNull represents non existing column,
that is a column that doesn’t even exist ( just conceptually doesn’t
exist? ) due to not having any value


* So which of the two quotes is correct?
* I assume if Sql book is correct, then DBNull instance represents an
unknown value?





2) “If a database field has missing data, you can use the
DBNull..::.Value property to explicitly assign a DBNull object value
to the field. However, most data providers do this automatically. “



Above quote suggests data providers would automatically assign null
value to the field located in a database itself. When would data
providers do this automatically?

Even when app would simply be just reading from a DB table and upon
encountering a field with no value they would simply put value NULL in
it?
Or would they do this only when app would be adding a row and if no
value was assigned to a particular field, then null will be
automatically added?




3) “DBNull is a singleton class, which means only this instance of
this class can exist.”

What is the reason for only allowing one instance of DBNull class to
exist? Couldn’t that cause potentional problems in multithreaded apps?




thank you

 

[Pavel Minaev]

From SQL book:
“SQL, however, doesn’t allow for data to hold no value. Fields with no
specified value actually do have a value: NULL. NULL is not the same
thing as nothing; NULL represents the unknown.

But MSDN claims:
“The DBNull class represents a nonexistent value. In a database, for
example, a column in a row of a table might not contain any data
whatsoever. That is, the column is considered to not exist at all
instead of merely not having a value. A DBNull object represents the
nonexistent column.”

a) So MSDN essentially claims that Sql table may have a column in a
row that doesn’t have any value assign to it ( not even NULL ) and
thus column isn’t even considered to exist?

But Sql book claims that all fields in a DB must have a value assigned
to them. So which is true?

This is purely a semantical issue. However, the SQL book is definitely
not correct, if you cite it as is, because NULL is not a value. We can
say that "field is NULL", and, according to the SQL spec at least, it
is supposed to mean that "value of this field is unknown" (but can
really mean anything - it's up to the schema designer in practice;
quite often people just use it as a "not specified" marker, for
example). To that extent, it is incorrect to say that "field has a
value of NULL". This is reflected in ANSI SQL syntax in that you
cannot use NULL keyword in all places where a value could go, and you
rather get some special operators (such as "IS NULL" and "IS NOT
NULL") to deal with it.

On the other hand, MSDN description is also quite misleading. I'm not
sure what is meant by the "missing column" there, or how it might be
different from "no value". In any case, SQL boolean semantics make it
very clear that NULL really means "unknown", and nothing else.

* I assume if Sql book is correct, then DBNull instance represents an
unknown value?

DBNull simply represents SQL NULL. What SQL NULL means is another
matter entirely. In practice, whenever you'd get a record from the
database where a field IS NULL, you'll see DBNull.Value as a value of
that field in ADO.NET.

2) “If a database field has missing data, you can use the
DBNull..::.Value property to explicitly assign a DBNull object value
to the field. However, most data providers do this automatically. “

Above quote suggests data providers would automatically assign null
value to the field located in a database itself. When would data
providers do this automatically?

When you're doing an INSERT, and the field in the table is not marked
as IS NOT NULL, and you do not specify any value for that field, then
it will be NULL. This also works for indirect INSERTs, such as when
you use a DataTable/DataAdapter. I don't know any SQL RDBMS that would
behave differently.

When reading from the table, you will _always_ get DBNull for any
NULL.

3) “DBNull is a singleton class, which means only this instance of
this class can exist.”

What is the reason for only allowing one instance of DBNull class to
exist? Couldn’t that cause potentional problems in multithreaded apps?

The reason is that there's no point in having more than one DBNull
instance, and that, since there's only one, you can use referential
equality (==) to check for it. Keep in mind that, as DBNull is a
class, if there could be more than one instance of it, then == would
give false for two DBNull instances, even though logically they both
mean the same thing.

It doesn't cause any problems with multithreading because DBNull is
purely a marker value and has no state. Only shared _mutable_ state
may cause multithreading problems, when two or more threads try to
modify it at the same time, or one thread modifies while another one
reads. For immutable state, there is no concern - and lack of state
is a special case of immutable state, as there's nothing to mutate

 

[Cor Ligthert[MVP]]

In my idea comes the use of Null in SQL from the in my idea wrong use of the
word null in computing.

This is learned at schools and often threaded as a value.

Null is no value at all, it is currently in computing just a word in C
languases and SQL for not assigned.
VB uses for that Nothing, while older languages like Cobol uses High-Values

C languages are uses since the micro processor became the leading processor,
I don't know it, but I assume that it initialize 000000 as unassigned.

That it is used in SQL the same is probably because that they took over the
word from the C languages because there was nothing better to describe it in
a short way

In Net languages is the SQL DBNull.Value a type of column, not a value.

Cor

Hi

I just started learning ADO.NET and Sql

1)

From SQL book:
“SQL, however, doesn’t allow for data to hold no value. Fields with no
specified value actually do have a value: NULL. NULL is not the same
thing as nothing; NULL represents the unknown.

But MSDN claims:
“The DBNull class represents a nonexistent value. In a database, for
example, a column in a row of a table might not contain any data
whatsoever. That is, the column is considered to not exist at all
instead of merely not having a value. A DBNull object represents the
nonexistent column.”


a) So MSDN essentially claims that Sql table may have a column in a
row that doesn’t have any value assign to it ( not even NULL ) and
thus column isn’t even considered to exist?

But Sql book claims that all fields in a DB must have a value assigned
to them. So which is true?



b) Perhaps this is just a matter of semantics, but according to SQL
book a column with value of NULL is conceptually still considered to
exist, and NULL here represents the unknown value. In other words,
column has a value, but we don’t know what that value is.
MSDN on the other hand claims DBNull represents non existing column,
that is a column that doesn’t even exist ( just conceptually doesn’t
exist? ) due to not having any value


* So which of the two quotes is correct?
* I assume if Sql book is correct, then DBNull instance represents an
unknown value?





2) “If a database field has missing data, you can use the
DBNull..::.Value property to explicitly assign a DBNull object value
to the field. However, most data providers do this automatically. “



Above quote suggests data providers would automatically assign null
value to the field located in a database itself. When would data
providers do this automatically?

Even when app would simply be just reading from a DB table and upon
encountering a field with no value they would simply put value NULL in
it?
Or would they do this only when app would be adding a row and if no
value was assigned to a particular field, then null will be
automatically added?




3) “DBNull is a singleton class, which means only this instance of
this class can exist.”

What is the reason for only allowing one instance of DBNull class to
exist? Couldn’t that cause potentional problems in multithreaded apps?




thank you

 

[beginwithl]

hi

Hi

I just started learning ADO.NET and Sql [...]

You should probably post your ADO.NET and SQL questions to a newsgroup
that has something to do with ADO.NET and SQL.

Sorry about that. I thought that perhaps ADO.NET questions would fit
in here, and having that thoughts, I decided to ask here, since most
of other forums suck. Getting there an answer longer than three
sentences is a pipe dream ( or I ask stupid questions … I don’t rule
that one out ). Anyways, won’t do it again


thank you all for your help

 

[Bob Milton]

Cor,
One quick note: SEQUEL (the original SQL) contained nulls and was
documented in 1974 - 4 years BEFORE Kernigan and Ritchie first published
their C book. So, if anything, C took the name null from SQL.
Bob

 

[Arne Vajhøj]

From SQL book:
“SQL, however, doesn’t allow for data to hold no value. Fields with no
specified value actually do have a value: NULL. NULL is not the same
thing as nothing; NULL represents the unknown.

But MSDN claims:
“The DBNull class represents a nonexistent value. In a database, for
example, a column in a row of a table might not contain any data
whatsoever. That is, the column is considered to not exist at all
instead of merely not having a value. A DBNull object represents the
nonexistent column.”

a) So MSDN essentially claims that Sql table may have a column in a
row that doesn’t have any value assign to it ( not even NULL ) and
thus column isn’t even considered to exist?

But Sql book claims that all fields in a DB must have a value assigned
to them. So which is true?

They are trying to communicate the same message and picking
very different ways of explaining it.

It is much easier with an example:
- you need to enter data about persons in a databse
- you create a table with 20 columns
- you create an app with a form with 20 fields
- you start entering data
- you save a NULL where you do not have the info about the person

NULL means missing info.

Whether you consider that a value or not a value does not really
mean anything for the database or the app.

Note that for text types NULL is not the same as the empty string '',
which can sometimes be a bit confusing because '' in many real world
cases will be considered missing. But it is much more logical for
numeric types. There is an obvious different between missing NULL and
the value of zero.

Arne

 

[Arne Vajhøj]

Null is no value at all, it is currently in computing just a word in C
languases and SQL for not assigned.
VB uses for that Nothing, while older languages like Cobol uses High-Values

C languages are uses since the micro processor became the leading
processor, I don't know it, but I assume that it initialize 000000 as
unassigned.

That it is used in SQL the same is probably because that they took over
the word from the C languages because there was nothing better to
describe it in a short way

SQL NULL and C# null/VB.NET Nothing are obviously somewhat
related.

But SQL NULL carries a lot of special baggage that does not
apply to C#/VB.NET.

Comparision, aggregated functions, sorting etc..

Arne

 

[Arne Vajhøj]

3) “DBNull is a singleton class, which means only this instance of
this class can exist.”

What is the reason for only allowing one instance of DBNull class to
exist?

What would you use more than one instance for ?

Couldn’t that cause potentional problems in multithreaded apps?

Something that never change is thread safe.

Arne

 

[Arne Vajhøj]

One quick note: SEQUEL (the original SQL) contained nulls and was
documented in 1974 - 4 years BEFORE Kernigan and Ritchie first published
their C book. So, if anything, C took the name null from SQL.

C is from 1972.

The SQL people may not have had the book, but they may have had
the compiler.

Arne