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

[Alan Mead]

--- James Mills <prologic at daisy.ods.org> wrote:
> 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 know nothing specifically about SQLite but SQL has many data types,
similar to Pascal.  However, SQL data fields can also have the value
NULL which has no analog with Pascal data.  Pascal data types cannot
simultaneously hold data and this non-data missing value code (unless
you define some convention within your code... like you could arrange
for NULL results to be returned as the string value 'NULL' but then
you would be unable to distinguish between actual strings with the
value 'NULL' and the NULL result... which may be no big deal).

According to Michael, someone arranged for NULL values to be
translated to empty strings, perhaps without even meaning to, because
of how ansistrings act.. they are internally pointers and an empty
string is represented as a nil pointer.  

I have no idea why this behavior would be different between Windows
(?) and Linux. (in fact, are you sure it is?)  

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

See below.

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

I think the idea with the TDataset was to create a record in Pascal
that could hold all the SQL data. Since a string alone cannot hold
it, you need a record with a string and a boolean.  The boolean holds
NULL or not-NULL.  When the boolean holds not-NULL, the string holds
the data (the string should never hold data if the boolean hold
NULL).  By "boolean holds NULL", of course, I mean that you define
the boolean in some way so that it indicates the NULL-ness.  For
example, define it as 'IsNull:boolean;' and then TRUE will indicate
that the result is NULL.

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

Well then why are you worried about distinguishing between NULL's and
empty strings?  All empty strings are NULL's.  (I know originally,
you were compiling test programs... is this really an issue?) 

-Alan


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