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

[James Mills]

On Mon, Jul 14, 2003 at 03:06:44PM +0200, Michael Van Canneyt wrote:
> 
> 
> On Mon, 14 Jul 2003, James Mills wrote:
> 
> > On Mon, Jul 14, 2003 at 02:26:28PM +0200, Michael Van Canneyt wrote:
> > >
> > >
> > > On Mon, 14 Jul 2003, James Mills wrote:
> > >
> > > > On Mon, Jul 14, 2003 at 09:59:17AM +0200, Michael Van Canneyt wrote:
> > > > >
> > > > >
> > > > > On Mon, 14 Jul 2003, James Mills wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > This is a very weird behaviour I've found of either SQLite, or the Unit.
> > > > > > I'm not sure... I have been discussing this same problem on the SQLite
> > > > > > mailing list but without success there, so perhaps someone here might
> > > > > > know...
> > > > > >
> > > > > > I have attached a test database, which contains 1 database entry, null
> > > > > > strings are inserted using NULL... Check the database schema yourself.
> > > > > >
> > > > > > The problem therein lies in the fact that test3.pas returns "" for the
> > > > > > query: SELECT channelNotice FROM channels WHERE channel LIKE '#ProLogiTech';
> > > > > > It should return a null string as it does in test2.pas and in the sqlite
> > > > > > shell program.
> > > > >
> > > > > The problem is that an implicity conversion from Nil to empty string is
> > > > > done. You cannot solve this as long as stringlists are used to contain
> > > > > the data. (ok, you could set 'Null' as the string, but that is sloppy)
> > > >
> > > > "is done." in what ? The SQLite library or TStringList ?
> > >
> > > sqlite.
> >
> > But you said "You cannot solve this as long as stringlists are used to
> > contain the data." This made me think it was a behaviour of TStringList
> > ... If it truely is a behaviour of SQLite, I'll get them to change it.
> 
> The problem is that there is no way to distinguish between a 'null' and
> an empty string once it is converted to a string. The information is
> simply lost.

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

> 
> >
> > >
> > > >
> > > > >
> > > > > You'd need to have (as a minimum) an array of TField records:
> > > > >   TField = Record
> > > > >     IsNull : Boolean;
> > > > >     Value : String;
> > > > >   end;
> > > > >
> > > > > But this will require major changes, so I suggest trying to find or
> > > > > implement a TDataset descendent for SQLIte, this will solve most of
> > > > > your problems at once.
> > > >
> > > > How major would the changes be ? Otherwise I'll try and search for a
> > > > TDataset descendent ? Couldn't I write a descendent myself ? (I've kinda
> > > > of done something similar in creating a TDatabase descendant..., but it
> > > > doesn't solve my problem)
> > >
> > > Implementing a TDataset descendent is not easy, it'll take you at least
> > > a week (conservative estimate).
> >
> > Why is this a hard thing to do ? Give me some ideas here, you might be
> > conservative, but I don't muck around :)
> 
> Because TDataset is not an easy subject. You need to implement a lot of
> calls in a correct and predefined way.
> 
> 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