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

[James Mills]

On Tue, Jul 15, 2003 at 08:53:12AM -0700, Alan Mead wrote:
> 
> --- 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.

Well the only bad thing is that it returns data where there should be no
data. ie: '""' instead of the expected '', causing the program to treat
'""' as if it were a real string.

James

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