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

[Alan Mead]

--- James Mills <prologic at daisy.ods.org> wrote:
> On Tue, Jul 15, 2003 at 08:35:44AM -0700, Alan Mead wrote:
> > 
> > --- James Mills <prologic at daisy.ods.org> wrote:
> > 
> > > 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
> > 
> > If your database never contains an empty string, then your
> program
> > can rely on the empty strings you retrieve being NULL values in
> the
> > database.
> 
> This case is true and the database will never contain empty strings
> only
> NULL values. The program has strict ways of doing this, unless of
> course
> you modify the databases externally, then bam (you're right as per
> below)...

So if you do nothing, your program will work unless someone accesses
the database externally (or there is a bug in whatever code ensures
against inserting empty strings).  You need to decide how likely and
bad that is.

But maybe there is a simple work-around.  Surely you can execute SQL
select statements using SQLite, right?  If so, then you can exploit
the fact that SQL itself is well aware of the distinction between
empty strings and NULL's to check for this error condition.  

If you're checking one table called MYTABLE with a key called IDX and
a couple fields to check are ADDRESS1 and ADDRESS2 then you would
execute this select:

select IDX from MYTABLE where ADDRESS1='' or ADDRESS2=''

If you get no results, then there is no error condition (you can
safely assume empty strings are NULL's).  If you get any results,
then you know which records need to be fixed.

You could execute this query at the start of your application's run. 
Or you could run it periodically (e.g., nightly) and email yourself
the results.

-Alan

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