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

[James Mills]

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

Why did Michael say that creating a TDataset was so hard ? If in it's
simplest form it's just a record to hold the data with a boolean
indicating it's null-ness, that doesn't seem so difficult to implement
to me.

I might play around with the mysqldb.pp dataset class in the fcl source
though...

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

My program relies on the fact that some fields (which are string types)
are null. I'm not sure what to say next so I'll leave it at that :P

cheers
James

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