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

[James Mills]

On Tue, Jul 15, 2003 at 05:27:42PM +0200, Michael Van Canneyt wrote:
> 
> 
> On Wed, 16 Jul 2003, James Mills wrote:
> 
> > 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.
> 
> It is not that, it's much more than that. TDataset manages such
> 'records' and much more.
> 
> You can write whole books about the TDataset class and it's descendents.
> I wouldn't advise someone with no experience with TDatasets to start
> writing a descendent.

You obviously know a lot about this topic as I've seen in the mysql
dataset you created and the generic dataset. I've looked at it and
shaken my head! (I'll admit I don't know a lot about databases, but am
learning, SQL is my only knowledge thus far).

Having said that though, I'm going to try and modify Eric Jourde's
sqlite unit to accomadate for NULLs (try being the operative word :P)

cheers
James

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