The Old Joel on Software Forum: Part 4 (of 5)

What is NULL

Developing an APP in VFP now for a couple of years, and it works great in SQL Server and VFP databases, and we have been tasked with making it work with Oracle.

So we are getting it working, and have discovered that apparently an empty string in Oracle is == NULL, but on every other DB system it seems that empty string == empty string, and NULL == NULL. So to get around this we have to store ' ' instead of '' in empty strings, or change what tables and fields accept NULLs.

So basically this is causing us some issues because some other parts of our system rely on the fact that NULL == NULL and '' == empty string != NULL (wether that is good programming or not, that is just what was easiest for us at the time considering how the VFP ISNULL() function works) so my questions are:

1. Is the NULL behaviour we are seeing correct?

2. Is it configurable?

Chris Ormerod
Tuesday, March 2, 2004

AFAIK null != null, nor does null = '' and cannot be used in comparisons.

You can use something like "WHERE column_name IS [NOT] NULL".

Steve Jones (UK)
Tuesday, March 2, 2004

Chris,

Steve is correct. The correct NULL checking syntax in Oracle.

NULL denotes "nothing" or "empty" in Oracle, so you can have constructs like this for example:

select foo into foo_var from table where id=1
if foo_var is null
...
...
end if;

One word of advice, if you are porting to Oracle, stay away from the datatype CHAR. Use VARCHAR2. In the above example, had foo been declared as CHAR it would never be empty since CHARS is space padded to their declared length.

Patrik
Tuesday, March 2, 2004

Steve,

Yeah, I miss-stated what I meant by NULL = NULL, what I really meant is just that NULL is NULL and not an empty string or 0 or a date of 1/1/1899 etc.

Patrik,

Thanks for that tip on the CHAR fields.

Now my question - but how do I insert EMPTY STRINGs into a character field on Oracle where that character field is set to not allow nulls? At the moment we have had to resort to putting in a single space character for empty strings, but we were hoping for a more sane solution.

Thanks for the info so far guys.

Chris Ormerod
Tuesday, March 2, 2004

You don't. Oracle doesn't believe in empty strings. If you want empty strings, declare your fields nullable and adjust accordingly.

That's the way Oracle decided things are. If you don't like it, don't use Oracle. :/

Sum Dum Gai
Tuesday, March 2, 2004

>single space character for empty strings

There is nothing that annoys me than ' ' in varchar fields. Use NULL in your insert statements.

Consider the following example:

select nvl(t.field,'nothing found') from table t where t.id=1;

This would return 'nothing found' if t.field is null, otherwise it returns t.field. Very handy function to use in SQL when querying the DB. Those ' ' fields roally mess up this function, unless you use TRIM() on the field first and stuff.

Also, when having space filled character fields in Oracle you end up using LTRIM or RTRIM and friends in your WHERE-clauses because Oracle is picky say you have the following:

SELECT * FROM PERSON WHERE FNAME='Patrik';

and

SELECT * FROM PERSON WHERE FNAME='Patrik ' ;

is different, and you will not get a hit unless you match the number of spaces. Its ugly. So you normally see stuff like

SELECT * FROM PERSON WHERE TRIM(FNAME)='Patrik';

to work around this how-many-spaces-are-there problem.

When using functions like this in your WHERE clauses to simplify the SQL writing, you disable the indexes, because indexes are built case sensetively, and the index scanner will compare 'Patrik' and 'Patrik ' or 'Patrik' to 'PATRIK' or whatever.

I guess one line would have been enough,

Avoid spaces in character fields at all cost.

Good luck.

Patrik
Tuesday, March 2, 2004

So I suppose we are best to make all fields allow null then?

Will have to test that and see what issues it causes on some of our older reports that all work fine on other DBs. Hopefully it won't be too much of an issue.

Chris Ormerod
Tuesday, March 2, 2004

"When using functions like this in your WHERE clauses to simplify the SQL writing, you disable the indexes"

Not necessarily. Oracle can do indexes based on a function.

Sum Dum Gai
Tuesday, March 2, 2004

"Not necessarily. Oracle can do indexes based on a function. "

Only later versions of Oracle. When was this introduced - v8.1.7 or so?

Also, if you define a function-based index in Oracle, it will only be used if you use the exact same function in the WHERE clause in your SQL statement.

Philip Dickerson
Tuesday, March 2, 2004

In some places I've seen:

var = var & ""

After they retrieve data... just to avoid the NULL item.

//jorge
Tuesday, March 2, 2004

While we're on the subject of NULLs in databases... this http://www.snopes.com/autos/law/noplate.asp is probably the best (humorous) example of why you need NULL.

Jetguy
Tuesday, March 2, 2004

Stay away from empty strings!!! People use them to mean "empty" or "nothing" which is what Nulls where invented for.

The only reason to keep using them is if you have legacy systems that don't support Nulls or you are lazy and don't want to deal with nulls.

DJ
Tuesday, March 2, 2004

"Stay away from empty strings!!! People use them to mean "empty" or "nothing" which is what Nulls where invented for."

NULLs are a necessary evil. But empty-strings are a common programming language concept. If you don't have empty strings in your database, then you just have to constantly convert between empty strings and NULLs and it's a waste of time and effort.

My philosophy is to avoid NULLs whereever possible -- but if you need a NULL, use it.

Almost Anonymous
Tuesday, March 2, 2004

"Stay away from empty strings!!! People use them to mean "empty" or "nothing" which is what Nulls where invented for."

There can be an important difference between "string that was supplied, but is empty", and "no value supplied" or "invalid value". Oracle makes it impossible to express this difference.

rwh
Wednesday, March 3, 2004

rwh

>Oracle makes it impossible to express this difference.

This is not a problem with nulls and/or empty strings. This is more of a data model problem.

Most times you dont want to save invalid stuff in your database, you simply reject it and have the user fix the problem.

If you for some reason need to save invalid values in your database you can have lookup tables that states which value(s) are valid for each input field and have a field to denote validity...That extra field denotes things like Empty or Invlaid or whatever.

This way you can still have NULLs in your data entry fields and have the invalid_flag tell you why it was invalid on entry.

Patrik
Wednesday, March 3, 2004

Oracle's nulls-only system doesn't allow capturing the difference between "I don't know the value that goes in this field" and "This field intentionally left blank". You end up having to find workarounds - using "n/a" or some similar notation to mean "intentionally blank". Of course, that leads to situations like the license plate story, or people getting mail addressed to Mr. John NMI Smith.

Martha
Wednesday, March 3, 2004

This works on every database I've tried but Oracle:
create table test ( teststr varchar(20) NOT NULL );
insert into test values ('');

Having to program special cases for Oracle for multiple database support is a waste of time. Life is too short for stupid differences like this.

Chris
Friday, March 5, 2004