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

[Michael Van Canneyt]

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.

Michael.