[fpc-pascal]SQLite and NULL Strings...

[Alan Mead]

--- James Mills <prologic at daisy.ods.org> wrote:

> Just reconfirming with you and solidifying my knowledge. 
> The reason it returns "" is because: when working with 
> native pascal types, assigning null to a string results 
> in an empty string "" right ?
> 
> cheers
> James

James,

Pascal is a strongly typed language and there is no Pascal string
precisely equivalent to a NULL.  Someone (either the SQLite authors
or the one who wrote the Pascal wrapper) decided to automatically
translate NULL into empty (zero-length) strings.  And it's impossible
now to distinguish actual null values from fields containing actual
zero-length strings.  

You could solve this in several ways:

1) re-write all the code to return and store a more complex record
(as suggested in a previous post) that include a string and a
boolean, this record completely and precisely captures the data SQL
is returning;

2) re-write the code that does the automatic translation to insert
the string 'NULL' (in which case, you will never be able to
distinguish NULL's from actual fields containing the value
'NULL'--but maybe this is no problem);

3) you could simply avoid ever having empty strings in your database
(in which case, empty strings returned are always NULL's)

4) Live with not being able to distinguish, in many instances, an
empty string is practically the same as a NULL (in other instances,
however, it indicates that some records in a full join failed to find
a match)

HTH,

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com