Check data from database is this null?

Hello

if I want to check data from database is this null

I’m write code:

if rs.field(“name”).stringValue = “null” then

end if

but error

thank you

sounds like for starters you might want to read up on database basics…

In most modern databases, data elements can have 3 values (for the most part)

a numeric value can be ZERO, non-ZERO, or NULL
a string value can be zero-length ("") , non-zero length (“abc”), or NULL

Null indicates that the data element has NO VALUE. (do not confuse an empty string with NULL)

Trivia… The programmer that came up with the concept of NULL recently said it was the worst design decision he ever made

Various database engines handle things differently
but in SQLlite you can detect null IN SQL STATEMENTS with the IFNULL() operator or IS NULL statement

you can also say

if rs.field(“name”).Value = NIL then

note : VALUE not STRINGVALUE

if rs.Field( "name" ).Value = nil then …

Or

if rs.Field(“name”).Value.IsNull then …

I’ve always thought that, too. I avoid NULL like the plague.

thank …

How?
Do you never use outer joins?

[quote=48009:@Jeff Tullin]How?
Do you never use outer joins?[/quote]

That is why the IFNULL/ISNULL operators exist …a properly crafted query can avoid the problems assoicated with NULLs

Actually I think it is useful. To me it means that data was never stored and so there is no valid value.

Bad assumption in my opinion.
We have tons of code where values are set to NULL instead of giving them a meaningful value…
but the intent was not “that data was never stored and so there is no valid value.”
the intent was to give NULL a “meaning”

A prime example. We have hundreds of tables that contain an EFFECTIVE and TERM date
For an “active” record it has a non-NULL Effective Date and a NULL term date
now this does fit Karens description. But forces queries to be overly complex when attempting
to find records that are or WERE active during a particular time period or particular day

You could say something like

SELECT * from myTABLE
WHERE eff_date is not null
     and eff_date <="somedate"
     and (term_date is NULL or (term_date is NOT null and term_date>="somedate"))

or what we actually do more often as it is less code and more readable (although more expensive from an execution profile)

SELECT * from myTABLE
where NVL(eff_date,'01-Jan-1900')<="somedate"
    and NVL(term_date,"31-Jan-2999')>="somedate"

bearing in mind that code breaks on 01-01-3000… but that will be someone elses problem… not mine :smiley:

NOTE : NVL is ORACLE syntax… your database engine may vary

[quote=48009:@Jeff Tullin]How?
Do you never use outer joins?[/quote]
I meant in the database design.

This.

It makes everything more complicated. To me, a properly designed default value is preferable. But that’s just me.

NULL has a well defined meaning in SQL - no data was entered for this column

[quote=48080:@Tim Hare]
It makes everything more complicated. To me, a properly designed default value is preferable. But that’s just me.[/quote]
NULL is a well defined value

Try this - where NULL makes a lot of sense.
Say you are recording information from someone taking a survey
One of the questions is “Are you Male / Female / Prefer not to answer”
So it requires a trinary value
Personally I’d use M, F, & NULL
Some might decide to insert “NA” or “None” or some other default but NULL is also a good choice
Then queries only need to do a left or right outer join on that column OR they have to explicitly have a special test for whatever the default was (which is about the same in practice)

I tend to agree with Tim. I find NULLs cause more problems than they solve and usually try to avoid them.

Working with Oracle many years ago we ran into issues with doing lookups on large tables with NULL values in columns. I have no idea if it is still true, but at the time, NULL columns would not get indexed by a column index. So in Norman’s example above, if you used NULL for “no answer” and then needed to query the table to get all the “no answer” values by selecting on NULL, a full table scan would be done instead of it hitting the index (as it would to get the M or F values). Of course, these were very large tables; it wouldn’t matter for small data sets.

I quickly get annoyed having to write queries that deal with NULL as a special case.

I find NULL makes it harder to deal with languages that don’t have NULL support in the simple data types.

But they can be helpful with some JOINs as people have said, so they are not all bad.

Anyway, I’d say it depends on your database, your schema design and what you are comfortable with. But I’m on “Team NOT NULL”.

I respectfully disagree - even using Oracle - which we used with a very large SCADA install with hundreds of millions of rows in one table and about 180 tables - in total many 100’s of TB of data.
However, we had composite indexes which Oracle CAN deal with even if some columns are NULL.
In the case of M/F/NULL and index would not be useful as it’s not very selective anyways.
Outer joins worked fine (very fast) and finding all values with NULL again was no issue

Well, no your M/F/NULL example doesn’t have enough different values to matter. I was just trying to explain it in simpler terms.

Anyway, I can’t say I remember all the specifics of our problem. It was 15+ years ago and involved NULLs, indexing and stored procedures. And the solution involved not using NULLs. I’ve happily forgotten most of it as I never enjoyed working with Oracle!