![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 19 of 19 total |
![]() |
Fri, Jul 4 2014 8:38 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Matthew
>> How long do you think it will be before you start defaulting strings >> to ''? > >You already showed me the light with defaults, so "never"! 8-) I understand each word in that sentence, unfortunately I cannot make them combine in a way which imparts anything comprehensible! Roy Lambert |
Fri, Jul 4 2014 8:43 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> >> How long do you think it will be before you start defaulting > strings >> to ''? > > > > You already showed me the light with defaults, so "never"! 8-) > > I understand each word in that sentence, unfortunately I cannot make > them combine in a way which imparts anything comprehensible! Hmm, I can't find the thread, but I was sure it was you that helped me with my conversion from DBISAM to ElevateDB. One thing I was falling over on was the empty string not being the same as NULL, and that the fix for it was to set a default when creating a new record so that the field would have an empty string, and not therefore be NULL. -- Matthew Jones |
Fri, Jul 4 2014 9:08 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Matthew
Roy Lambert "Matthew Jones" <matthew@mattNOSP-AMhew-jones.com> wrote on Fri, 4 Jul 2014 08:43:36 -0400 >Roy Lambert wrote: > >> >> How long do you think it will be before you start defaulting >> strings >> to ''? >> > >> > You already showed me the light with defaults, so "never"! 8-) >> >> I understand each word in that sentence, unfortunately I cannot make >> them combine in a way which imparts anything comprehensible! > >Hmm, I can't find the thread, but I was sure it was you that helped me >with my conversion from DBISAM to ElevateDB. One thing I was falling >over on was the empty string not being the same as NULL, and that the >fix for it was to set a default when creating a new record so that the >field would have an empty string, and not therefore be NULL. So you've already started. That certainly contradicts the never <vbg> Roy Lambert |
Fri, Jul 4 2014 10:04 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> So you've already started. That certainly contradicts the never <vbg> I made a super-hash of that post didn't I! What I meant was, I don't see myself ever enabling the automatic option to do that. I have got columns with NULLs that are indeed different from empty strings. -- Matthew Jones |
Fri, Jul 4 2014 11:05 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Matthew
>> So you've already started. That certainly contradicts the never <vbg> > >I made a super-hash of that post didn't I! What I meant was, I don't >see myself ever enabling the automatic option to do that. I have got >columns with NULLs that are indeed different from empty strings. Sorry about this but I have to ask - what are they. I'm still desperately seeking a real use case that makes sense (to me) Roy |
Fri, Jul 4 2014 12:05 PM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> Sorry about this but I have to ask - what are they. I'm still > desperately seeking a real use case that makes sense (to me) They are things like "middle name" and "organisation". Nothing fancy, but NULL simply means that they haven't been specified. I have fields like VATOverride, filenames, that are NULL if there is no valid value. I guess you could argue that an empty string also indicates the same, but NULL is much clearer. I'll have to look at my code to see where I've done conditionals on it. -- Matthew Jones |
Fri, Jul 4 2014 3:35 PM | Permanent Link |
Eryk Bottomley | Roy
>>How did your system address this issue? Single name CLOB column so they can have as long a name as they >>wanted (or as short obviously)? How did you notify users on screen (or on printout) of the difference between >>NULL and emptystring? In practice a VARCHAR(255) was enough. Arabs can exceed that by dumping their entire genealogy as a surname via a huge list of "ibn" but in practice no-one does that except Royalty in very formal cases. The general solution in the app I'm discussing is that all fields besides fixed combos and the like respond to a context menu and Ctrl+N and Ctrl+U as hot keys. The users are familiar with this as it crops up in other cases like middle names, maiden names and so on. On a new record the TEdits (etc.) default to blank/clWindow, press Ctrl+N (None/Not Applicable) and the TEdit will switch to clBtnFace/clGrayText and read "<N/A>. Press Ctrl-U (Unknown/Undefined) and the field will revert to initial state. At table level the former case is EmptyStr and the latter is NULL. >>I'm really hoping that you're is a real example and has been designed correctly because I'd love to believe that >>there is a justification for null <> emptystring and all of the additional messing around that has to be done >>because of it. As per my original example, the 'messing around' would occur if they were the same. I used Burmese surnames as an example just for variety: a much more common case would be NULL maiden name vs. EmptyStr maiden name (former "No idea", latter "Never married or refuses to answer"). >>Also while you're at it please tell me what's wrong with special "flag" fields? Isn't it better to have something >>there with a definitive meaning. eg this person is Burmese and doesn't need a surname (but one would be nice if >>we could get it) rather than "oh I just can't be bothered to enter anything here - its easier and the system won't >>care"! Flag fields add complexity and in most real world cases aren't required if you apply trivalent (Value, No value, NULL) logic consistently. Also, many people won't implement flag fields correctly - they will bolt them in to the same table and break normalisation rules (the flag is not dependent on the key, the whole key and nothing but the key). A related example, one of my applications tracks the status of the door of a cabinet in a datacentre. One might think this is a simple boolean - Open/Closed. Not in reality: dsUnknown: Door sensor malfunctioning. dsOpen: Door is positively known to be open. dsClosed: Door is positively known to be closed. This matters because a security check report might otherwise end up as: SELECT * FROM RACKS WHERE DoorOpen=True ...which would falsely imply that everything that isn't Open is known to be Closed. It should be: SELECT * FROM RACKS Where DoorOpen<>False ...which will give you both the known to be Opens and the Unknown/Defective sensors. You can also run a sensor maintenance report by querying for IS NULL. Obviously if you default to the logical equivalent of EmptyStr for Booleans (i.e. 'False' in Pascal) then you have a whacking great security hole as all dodgy sensors appear as positive secure confirmations. Eryk |
Sat, Jul 5 2014 2:32 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Matthew
>I guess you could argue that an empty string also indicates the same, >but NULL is much clearer. I'll have to look at my code to see where >I've done conditionals on it. I don't know about you but I certainly could (and would) ![]() Roy |
Sat, Jul 5 2014 2:52 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Eryk
Roy Lambert Eryk Bottomley wrote on Fri, 4 Jul 2014 15:35:52 -0400 >Roy > >>>How did your system address this issue? Single name CLOB column so they can have as long a name as they >>wanted (or as short obviously)? How did you notify users on screen (or on printout) of the difference between >>NULL and emptystring? > >In practice a VARCHAR(255) was enough. Arabs can exceed that by dumping their entire genealogy as a surname via a huge list of "ibn" but in practice no-one does that except Royalty in very formal cases. > >The general solution in the app I'm discussing is that all fields besides fixed combos and the like respond to a context menu and Ctrl+N and Ctrl+U as hot keys. The users are familiar with this as it crops up in other cases like middle names, maiden names and so on. On a new record the TEdits (etc.) default to blank/clWindow, press Ctrl+N (None/Not Applicable) and the TEdit will switch to clBtnFace/clGrayText and read "<N/A>. Press Ctrl-U (Unknown/Undefined) and the field will revert to initial state. At table level the former case is EmptyStr and the latter is NULL. At least that conforms to my definition of user friendly system = what I'm used to ![]() >>>I'm really hoping that you're is a real example and has been designed correctly because I'd love to believe that >>there is a justification for null <> emptystring and all of the additional messing around that has to be done >>because of it. > >As per my original example, the 'messing around' would occur if they were the same. I used Burmese surnames as an example just for variety: a much more common case would be NULL maiden name vs. EmptyStr maiden name (former "No idea", latter "Never married or refuses to answer"). Here I must disagree with you. The messing around is in the interface because you present (None/Not Applicable) and (Unknown/Undefined) as different to the user so you are adding complexity to a system by having to translate between the database and the human being. >>>Also while you're at it please tell me what's wrong with special "flag" fields? Isn't it better to have something >>there with a definitive meaning. eg this person is Burmese and doesn't need a surname (but one would be nice if >>we could get it) rather than "oh I just can't be bothered to enter anything here - its easier and the system won't >>care"! > >Flag fields add complexity and in most real world cases aren't required if you apply trivalent (Value, No value, NULL) logic consistently. Also, many people won't implement flag fields correctly - they will bolt them in to the same table and break normalisation rules (the flag is not dependent on the key, the whole key and nothing but the key). Firstly I disagree about adding complexity but I'm very puzzled about tristate fields unless you're specifically referring to booleans being true/false/unknown. >A related example, one of my applications tracks the status of the door of a cabinet in a datacentre. One might think this is a simple boolean - Open/Closed. Not in reality: > >dsUnknown: Door sensor malfunctioning. > >dsOpen: Door is positively known to be open. > >dsClosed: Door is positively known to be closed. > >This matters because a security check report might otherwise end up as: > >SELECT * FROM RACKS WHERE DoorOpen=True > >..which would falsely imply that everything that isn't Open is known to be Closed. > >It should be: > >SELECT * FROM RACKS Where DoorOpen<>False > >..which will give you both the known to be Opens and the Unknown/Defective sensors. You can also run a sensor maintenance report by querying for IS NULL. Obviously if you default to the logical equivalent of EmptyStr for Booleans (i.e. 'False' in Pascal) then you have a whacking great security hole as all dodgy sensors appear as positive secure confirmations. So what you seem to say here is that you are using a boolean column and treating NULL as though it has a meaning of dsUnknown. Is that right? If my assumption is correct I would much prefer an integer column with distinct values. I think one of our differences is that I much prefer a positive mechanism to identify things whereas you are imputing meaning to NULL as a substitute for such a positive mechanism. Roy |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, August 15, 2024 at 12:21 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |