Subject Re: [IBO] Secret of #NULL# needed
Author Helen Borrie
At 12:02 AM 15/11/2005 +0000, you wrote:
>I have to handle exported data from an Access db.
>Sometimes a field in the tab delimited data has the
>six characters #NULL#
>Using IB_Import, and assigning a field where
>this #NULL# might occur seems to work ok. Is this
>to be expected?
>Searching all of my references, and all of Google,
>and what I have access to in these Yahoo groups, I can
>find no mention of #NULL#
>References to NULL abound.
>Why I have I been left out of this secret
>communication? <g>
>Is this #NULL# a universal substitute for NULL data?
>Is this handled by IBO or by the server?
>Can anyone provide me with any references to #NULL#?
>Not NULL.
>Searching on Goggle for "#NULL#" (quotes included)
>returned 54,900,000 hits.
>But of the first 30, none of them actually referred
>to #NULL# but instead contained only the word NULL.
>I don't think I'll have time to look at the rest.
>!#@%$^& ????

You will need to post-process these by going through and updating them so

Access can't store nulls, so it stores a variety of "zero defaults" in
place of it, which is flagged by the M$ output convention #NULL#. It's
meaningless in terms of standard SQL, just a hackish way of representing
the concept in text output.

The best way to deal with it, so you properly store nulls rather than some
possibly wrong default -- like the string 'NULL" that you are getting -- is
to have your import routine look out for that string and clear the value
before storing it. TIB_Import is a bit of a blunt instrument for this.

In fact, when dealing with Access data, I always prefer to import it into a
staging table of all chars and then post-process that table by pumping
using TIB_Datapump and fixing the warts en route. That way I have absolute
control over the hacks that are done to data by Access...