[Gambas-user] handling database nulls

[Bruce]

On Wednesday 15 April 2009 18:45:56 Werner wrote:
> Werner wrote:
> > Bruce wrote:
> >> This has sort of been asked before. However, the answers I have found
> >> haven't helped.
> >>
> >> My database has a LOT of integer fields, arranged into sets of fieldwork
> >> results.  The majority of them contain nulls, quite validly as the data
> >> for a particular set may not have been entered or even collected yet.
> >>
> >> My Gambas problem is creating the classes for the various plot types. 
> >> Within Gambas the data items are integers. When I try and read the
> >> existing datum point into the class (as in
> >> dp.rangeheight=$dbresult!rangeheight ) it is complaining that the value
> >> is a null not an integer, as in "wanted integer, got null instead").
> >>
> >> Now, coercing the value of the object data to zero is NOT valid, as in
> >> the above example, the rangeheight is null not zero, as zero means 0m
> >> above the basepoint.  Similarly, coercing it to a negative value wont
> >> work either, as that means meters below the base point.
> >>
> >> Is there any way to set the value of an integer variable to an
> >> "uninitalised" state?
> >>
> >> bruce
> >
> > A solution might be to use the Variant data type instead of Integer.
> > Alternatively, you could define a class that holds a data point plus a
> > flag whether the data is valid.
> >
> > Werner
>
> ...or you could set empty data points to -0 which is most significant
> bit set to 1 all other bits 0
>
> ...or you might be able to use TRY whenever accessing a data point to
> catch the exception.
>
> In any case there needs to be a test whether the data is valid.
>
> As you said, the majority of data fields is empty. If you have a
> humongous amount of data it might be worth looking into "spare matrix"
> compression (what spreadsheets do)
>
> Werner
>
>
> ---------------------------------------------------------------------------
>--- This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user

Werner,

Brilliant!!  Both ideas I like.  The internal representation can be a variant 
that presents a special case externally.  I wil have a look at how this will 
work, but I think you've hit on the solution.

Thanks
bruce

btw: the sparce matrix idea doesn't really apply here as the instantiated 
objects are really quite simple with around 6 to 8 properties each depending 
on the field type.  In any realised instance we only need to be able to 
observe these 6-8 properties.  The database design is not the issue,  it was 
just the problem within the derivative fieldcount classes.  Sometimes it just 
needs the application of a third eye!  Thanks again.