Lotus Geek :: Tech Talk: NotesDocument.ColumnValues, and Null VS Empty String

Category Technical
Bookmark :

Today's entry is really covering two topics, both of which came to me when responding to a forwarded email I received from Duffbert. Here's the relevant part of the email:
Also, you wrote about migration issues from R5 to R6.5. I ran into
trouble with an agent walking a view entry column's values. I had
used entry.ColumnValues(0) <> Null to go through each column 0,1,2 to
gather the appropriate values to fill in an array of the view totals
in R5 and it worked fine. In R6, the agent didn't work anymore. I
had to change the Null to "" in order to get it run. Should I never
have used Null to begin with? Am I missing something?

So, let me break this down into two parts. First, let's take a look at the Null VS Empty String issue.

NULL VS Empty String

The help for NULL states the following:

A special value that represents unknown or missing data. Various operations return a NULL value, but you can only assign the NULL value to a Variant variable. To determine if a variable contains the NULL value, use the IsNull function.

So, to make this clear, NULL does not equal an empty string. NULL is actually Char(0), while Empty String is a string var of zero length. Sara Boucher explained it quite well in this technote about moving nulls and empty strings to relational dbs using LEI, where she states:

Notes has no implicit concept of "NULL". When you create a document through Notes, any fields to which you give no value are saved as zero-length text fields (no matter what their actual datatype). This has its advantages. If a datatype other than text (Number, for example) contains an empty text string, we know that the field was intended to be Null and can transfer the data as such. With a text datatype, we cannot determine whether the absence of a value in the field is intended as a Null or as an empty text string. In the case of a text datatype, the absence of a value will be treated as an empty text string. A workaround option would be to add Notes processing via LotusScript or a formula (@IsUnavailable) to actually remove the relevant field, if it were empty, and then it could be seen as NULL. For example, use the following Input Translation formula for the field: @If(text="";@Unavailable;text)

So, the best thing to do is to test for what you expect - if you expect an item to not have been initialized, then use NULL; if you're testing for an empty string, then use "".

As far as it working in R5 VS ND6.x, I think that the use of NULL did become more restrictive in ND6 (rightly so) - anyone else have any definitive information about this?

Now let's talk about my next topic, columnvalues...

NotesDocument.ColumnValues property

Now, let's talk about the NotesDocument.ColumnValues(x) property. A member of the columnValues property array (where a "member" of the array is a single column, like columvalues(0) is the first column) can return either a scalar (i.e. String, Number, Date, etc.) or an array of scalars. What determines this is what the column contains - if it contains a single value for the document, then it will return a scalar; if it contains multiple values for the document, then it returns an array.

Now this is NOT the same as an item - an item in a NotesDocument always returns an array. I wish that the columnvalues property would work the same way, but it doesn't. So, I have a couple of recommendations for you. First, I would recommend that you always test your columnvalues references to see if they are arrays. I typically use something like this:
Dim tmpval as Variant, fldval as String
tmpval = doc.columnvalues(0)
If isArray(tmpval) then fldval = Cstr(tmpval(0)) else fldval = Cstr(tmpval)

Now, let me point out a couple of things. First, notice that the tmpval var is declared as a Variant. This is so it can hold whatever the columnvalues property returns. I use the tmpval var over and over, and assign the value to my "real" var (in this example, fldval) after figuring out what it is. Second, I believe you should always typecast the results of your columvalues property to the data type you need. The columnvalues property MAY return the data type of the underlying item, or it may return a string. So, I would recommend that the "real" var that will hold your columnvalues result be declared as the desired data type, and then use one of the "C" functions (e.g. Cstr for string, Cint for integer, Cdat for date, Ccur for currency, etc.) to type cast the columnvalues result to the desired data type.

Your comments are welcome!

Rock
**If I ever make it on Wheel of Fortune, I'm sneakin' in my own vowels under my jacket. No way I'm gonna pay $250, especially for a U.

Comments

1 - The original question is even further off. Her agent did this: entry.ColumnValues(0) <> Null, while the documentation says: To determine if a variable contains the NULL value, use the IsNull function.

In other words "NULL" shouldn't be used for comparison operators.

This is pretty similar to the old @function hack of comparing values to NULL. @Functions have no concept of NULL, so the only thing anyone was ever doing was comparing a variable to an implicitly declared variable, which was and is a terrible practice.

Posted by Nathan T. Freeman At 01:31:51 PM On 08/04/2004 | - Website - |

2 - Bob, are you serious? It returns an actual NotesDataTime object? I need to test that - that could be a real PITA (and I have been lucky so far)!

I'll report back what I find...

Rock

Posted by Rock At 09:28:31 AM On 08/06/2004 | - Website - |

3 - Actually, I think you can also get a NotesDateTime object instance in the ColumnValues array, if the colmn contains date values

Posted by Bob Balaban At 11:29:01 AM On 08/05/2004 | - Website - |

4 - Good tip Rock; it constantly infuriates me how inconsistent return values can be in Notes, and ColumnValues are a real gotcha.

Test, trap, and test again!

Posted by Ben Poole At 12:33:31 PM On 08/04/2004 | - Website - |

5 - NEVER assume you're going to get data of the type you expect - we all know what "assume" means right?

"It'll make an ASS out of U and ME".

Posted by Roy Holder At 11:51:47 AM On 08/05/2004 | - Website - |

6 - I think it is a must (at least to try) to cover all possible occurrences. This includes Null and "".
It just depends on what you need in the current situation. E.g. how to find out if an array is empty. OK, there exist many functions in the www for this issue. But this is imho a definition thing. In some cases, I need to know if all array values are "". In some other cases I need to know if the whole array is NOTHING or not. Or I just need to know if it is an array or not (-> IsArray). In some other cases it is sufficient to know if the first entry of the array is "". Sometimes I need to know if all entries of an array are "".
--
I welcome the more restrictive usage of NULL in ND6. Anyhow - I avoided so far the usage of NULL, I simply did not need it so far