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

[James Mills]

On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote:
> 
> --- 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.  

I think I'm slowly understanding this bit now. I don't claim to be an
SQL expert. But SQL (sqlite anyway) is capable of storing any data
types, strings, integers, boolean etc, including NULL values. I hope I'm
correct here...

I don't believe the authors of the sqlite unit do translate NULL into
empty (zero-length) strings. I did check the source, check yourself if
I'm wrong.

Why could I not simply check for '""' in my sql return functions and
simply return '' instead (a pascal empty string) ? Would this be
terribly wrong ? Or am I still going to have to follow Michael's
suggestion in finding a TDataset desendant ? (I don't understand what a
TDataset desendant really is and why I need to use one but anyway...)

> 
> 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)
There should be no empty string in my database anyway. If a field is
empty it's value is NULL.

> 
> 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)

cheers
James

> 
> HTH,
> 
> -Alan
> 
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> 
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal

-- 
-
- James Mills
Zero Defect Software Engineers Group - ZDSEG