Re: [fpc-pascal]SQLite and NULL Strings...
--- James Mills <[EMAIL PROTECTED]> 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. You could solve this in several ways: 1) re-write all the code to return and store a more complex record (as suggested in a previous post) that include a string and a boolean, this record completely and precisely captures the data SQL is returning; 2) re-write the code that does the automatic translation to insert the string 'NULL' (in which case, you will never be able to distinguish NULL's from actual fields containing the value 'NULL'--but maybe this is no problem); 3) you could simply avoid ever having empty strings in your database (in which case, empty strings returned are always NULL's) 4) Live with not being able to distinguish, in many instances, an empty string is practically the same as a NULL (in other instances, however, it indicates that some records in a full join failed to find a match) HTH, -Alan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > --- James Mills <[EMAIL PROTECTED]> 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 don't believe the authors of the sqlite unit do translate NULL into empty (zero-length) strings. I did check the source, check yourself if I'm wrong. 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 ? 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...) > > You could solve this in several ways: > > 1) re-write all the code to return and store a more complex record > (as suggested in a previous post) that include a string and a > boolean, this record completely and precisely captures the data SQL > is returning; > > 2) re-write the code that does the automatic translation to insert > the string 'NULL' (in which case, you will never be able to > distinguish NULL's from actual fields containing the value > 'NULL'--but maybe this is no problem); > > 3) you could simply avoid ever having empty strings in your database > (in which case, empty strings returned are always NULL's) There should be no empty string in my database anyway. If a field is empty it's value is NULL. > > 4) Live with not being able to distinguish, in many instances, an > empty string is practically the same as a NULL (in other instances, > however, it indicates that some records in a full join failed to find > a match) cheers James > > HTH, > > -Alan > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, 15 Jul 2003, James Mills wrote: > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > --- James Mills <[EMAIL PROTECTED]> 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 don't believe the authors of the sqlite unit do translate NULL into > empty (zero-length) strings. I did check the source, check yourself if > I'm wrong. They do. But implicitly, by converting a pchar to a string. A nil will be converted to an empty string. Michael. ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 03:15:58PM +0200, Michael Van Canneyt wrote: > > > On Tue, 15 Jul 2003, James Mills wrote: > > > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > > > --- James Mills <[EMAIL PROTECTED]> 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 don't believe the authors of the sqlite unit do translate NULL into > > empty (zero-length) strings. I did check the source, check yourself if > > I'm wrong. > > They do. But implicitly, by converting a pchar to a string. A nil will > be converted to an empty string. Oh :) It's funny how I haven't been able to actually reproduce this in some test programs on Linux using FPC. cheers James > > Michael. > > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Mon, Jul 14, 2003 at 03:39:36PM +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) > > > > > > 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. > > > > Where might I find such a descendent that using SQLite ? I'm not quite > > sure what I'm looking for here... An initial search on google provides > > some delphi units that are probably not what I'm looking for. > > On the contrary, on Torry's pages there are some units that do exactly > what I described. Not having much luck with Torry's pages I'm afraid. However I've looked at the FCL source, there is a mysqldb.pp in the db directory... Couldn't that be used for sqlite (obviously with changes) ? cheers James > > Michael. > > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, 15 Jul 2003, James Mills wrote: > On Mon, Jul 14, 2003 at 03:39:36PM +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) > > > > > > > > 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. > > > > > > Where might I find such a descendent that using SQLite ? I'm not quite > > > sure what I'm looking for here... An initial search on google provides > > > some delphi units that are probably not what I'm looking for. > > > > On the contrary, on Torry's pages there are some units that do exactly > > what I described. > > Not having much luck with Torry's pages I'm afraid. However I've looked > at the FCL source, there is a mysqldb.pp in the db directory... Couldn't > that be used for sqlite (obviously with changes) ? Yes. Michael. ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
--- James Mills <[EMAIL PROTECTED]> wrote: > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > --- James Mills <[EMAIL PROTECTED]> 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. > > 3) you could simply avoid ever having empty strings in your > database > > (in which case, empty strings returned are always NULL's) > There should be no empty string in my database anyway. If a field > is > empty it's value is NULL. Well then why are you worried about distinguishing between NULL's and empty strings? All empty strings are NULL's. (I know originally, you were compiling test programs... is this really an issue?) -Alan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 07:04:05AM -0700, Alan Mead wrote: > > --- James Mills <[EMAIL PROTECTED]> wrote: > > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > > > --- James Mills <[EMAIL PROTECTED]> 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. I might play around with the mysqldb.pp dataset class in the fcl source though... > > > > 3) you could simply avoid ever having empty strings in your > > database > > > (in which case, empty strings returned are always NULL's) > > There should be no empty string in my database anyway. If a field > > is > > empty it's value is NULL. > > Well then why are you worried about distinguishing between NULL's and > empty strings? All empty strings are NULL's. (I know originally, > you were compiling test programs... is this really an issue?) 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 cheers James > > -Alan > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Wed, 16 Jul 2003, James Mills wrote: > On Tue, Jul 15, 2003 at 07:04:05AM -0700, Alan Mead wrote: > > > > --- James Mills <[EMAIL PROTECTED]> wrote: > > > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > > > > > --- James Mills <[EMAIL PROTECTED]> 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. ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
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 <[EMAIL PROTECTED]> wrote: > > > > On Tue, Jul 15, 2003 at 04:36:24AM -0700, Alan Mead wrote: > > > > > > > > > > --- James Mills <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
--- James Mills <[EMAIL PROTECTED]> 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. But if an empty string ever crept into a database field, then your program would think the field is null when it's not. If this would cause a grevious error, then you should find a better way. -Alan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 08:35:44AM -0700, Alan Mead wrote: > > --- James Mills <[EMAIL PROTECTED]> 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)... cheers James > > But if an empty string ever crept into a database field, then your > program would think the field is null when it's not. If this would > cause a grevious error, then you should find a better way. > > -Alan > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
--- James Mills <[EMAIL PROTECTED]> wrote: > On Tue, Jul 15, 2003 at 08:35:44AM -0700, Alan Mead wrote: > > > > --- James Mills <[EMAIL PROTECTED]> 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 ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 08:53:12AM -0700, Alan Mead wrote: > > --- James Mills <[EMAIL PROTECTED]> wrote: > > On Tue, Jul 15, 2003 at 08:35:44AM -0700, Alan Mead wrote: > > > > > > --- James Mills <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal]Re: SQLite and NULL Strings...
> 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... Just a thought, but maybe you could return the single-character string: #0 __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]Re: SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 09:26:29AM -0700, Jeff Pohlmeyer wrote: > > > 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... > > Just a thought, but maybe you could return > the single-character string: #0 Thanks mate :) I'm so lost anyway, all these people with much deeper knowledge of databases... cheers James > > > > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
--- James Mills <[EMAIL PROTECTED]> wrote: > > 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. All the better. The string '""' is unlikely to exists you your database naturally, right? So when you see it, you have a NULL. Or you could, as you suggested earlier, just convert it to an empty string. -Alan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal]SQLite and NULL Strings...
On Tue, Jul 15, 2003 at 09:41:11AM -0700, Alan Mead wrote: > > --- James Mills <[EMAIL PROTECTED]> wrote: > > > > 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. > > > All the better. The string '""' is unlikely to exists you your > database naturally, right? So when you see it, you have a NULL. That's correct. What I've learnt over the past 2 days though is that I'll be leave blinded by all this unless I go pickup a book on database and read it! :) > > Or you could, as you suggested earlier, just convert it to an empty > string. I might do that if I have no luck with a TDataset desendant... Thanks Alan, James > > -Alan > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ___ > fpc-pascal maillist - [EMAIL PROTECTED] > http://lists.freepascal.org/mailman/listinfo/fpc-pascal -- - - James Mills Zero Defect Software Engineers Group - ZDSEG ___ fpc-pascal maillist - [EMAIL PROTECTED] http://lists.freepascal.org/mailman/listinfo/fpc-pascal
