Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 11 to 17 of 17 total |
Clarification on Nulls |
Tue, May 31 2011 4:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>> UPDATE table SET field = '1234567890' >> >> will fail if field is a VARCHAR with a length less than 10 > >Shouldn't it fail? (ie - the value given is larger than the size of the >varchar field)? It doesn't in DBISAM, or using a table component, they crop the value so it fits. ie with a VARCHAR(5) it would insert '12345' >> will leave the trailing blanks in place. > >As I would expect too... Why? >But shouldn't Update table SET field = '12345' work on a varchar(10)? yes it does >> They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison! > >Not sure I follow you by what you mean with a 'Delphi Comparison'? In code if tablefield.asstring = '12345' will fail you need if tablefield.asstring = '12345 ' So someone's working from a printed report and types what they see into the computer ..... In earlier incarnations of ElevateDB its SQL would also have failed the test. Trailing blanks in a VARCHAR are just stupid (they're stupid in a CHAR as well but I can understand the logic of leaving them in there). So for VARCHARS we now have the situation of the database stores the trailing blanks but ignores then in comparisons but the programming language used in the UI part of an app takes notice of them - YUCK!!!!!! Roy Lambert [Team Elevate] |
Tue, May 31 2011 8:06 PM | Permanent Link |
Adam H. | Hi Roy,
>>> will fail if field is a VARCHAR with a length less than 10 >> >> Shouldn't it fail? (ie - the value given is larger than the size of the >> varchar field)? > > It doesn't in DBISAM, or using a table component, they crop the value so it fits. ie with a VARCHAR(5) it would insert '12345' > >>> will leave the trailing blanks in place. >> >> As I would expect too... > > Why? Well - I guess that auto-trimming would be OK, but I'm happy for an exception to be raised if the value won't fit in the field. (So the user knows that they have lost data). Admitidally I would prefer it more like DBISam (as you do) becuase that's what I'm used to - but I can live with it either way. >>> They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison! >> >> Not sure I follow you by what you mean with a 'Delphi Comparison'? > > In code if tablefield.asstring = '12345' will fail you need if tablefield.asstring = '12345 ' You almost gave me a heart attack Roy! I just tried that in Delphi, and it's working fine. (On VARCHAR fields). ie: I have the value "MIC" in a VarChar(10) field, and typed in if MyTable1Field1.value = 'MIC' then beep; Which works OK. Alternatively, if I type in if MyTable1Field1.value = 'MIC ' then beep; Then beep does NOT occur (which I would expect). Is this possibly something that happened in earlier versions of EDB that has now been corrected? Cheers Adam. |
Wed, Jun 1 2011 10:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Well - I guess that auto-trimming would be OK, but I'm happy for an >exception to be raised if the value won't fit in the field. (So the user >knows that they have lost data). I'd agree for some things BUT where you're catenating stuff together and you don't care if a bit falls off the end its a pain. >Admitidally I would prefer it more like DBISam (as you do) becuase >that's what I'm used to - but I can live with it either way. As can I, especially since with my modded components it does it the DBISAM way >You almost gave me a heart attack Roy! I'll test again in case things have changed, but you have what I said the wrong way round. Data in field = "MIC " ie trailing space here Test in Delphi field.asstring = 'MIC' no trailing space in the data entered to test against Roy Lambert |
Wed, Jun 1 2011 7:06 PM | Permanent Link |
Adam H. | Hey Roy,
>> Well - I guess that auto-trimming would be OK, but I'm happy for an >> exception to be raised if the value won't fit in the field. (So the user >> knows that they have lost data). > > I'd agree for some things BUT where you're catenating stuff together and you don't care if a bit falls off the end its a pain. Indeed... >> Admitidally I would prefer it more like DBISam (as you do) becuase >> that's what I'm used to - but I can live with it either way. > > As can I, especially since with my modded components it does it the DBISAM way It's great we can do those things, ey. > >> You almost gave me a heart attack Roy! > > I'll test again in case things have changed, but you have what I said the wrong way round. > > Data in field = "MIC " ie trailing space here > > Test in Delphi field.asstring = 'MIC' no trailing space in the data entered to test against Sorry - I'm really confused now. Are you saying rather that if you want the value stored as "MIC " that it trims it to "MIC" - coz that's the one thing I didn't try. Cheers mate Adam. |
Thu, Jun 2 2011 2:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Sorry - I'm really confused now. Are you saying rather that if you want >the value stored as "MIC " that it trims it to "MIC" - coz that's the >one thing I didn't try. This is where it gets confusing I may be wrong but my understanding is that if you use a TDataset derived component it will be trimmed, if you use SQL (eg unbound controls) then it won't trim it. field.asstring := 'MIC ' will be trimmed UPDATE table SET field = 'MIC ' will not be trimmed I'm sure Tim will correct me if I'm wrong. Roy Lambert |
Fri, Jun 3 2011 12:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< This is where it gets confusing I may be wrong but my understanding is that if you use a TDataset derived component it will be trimmed, if you use SQL (eg unbound controls) then it won't trim it. >> You're mixing up "trimming" with "truncating". If you put a value into a TStringField in Delphi/C++Builder that is longer than the defined length of the field, then the VCL layer will truncate the value before it ever reaches the EDB engine. Therefore, you won't see a truncate error. However, if you try to do the same with SQL (or .NET/ODBC/PHP), EDB will raise a truncation error message. None of the layers in EDB automatically right-trim CHAR/VARCHAR columns. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jun 3 2011 1:50 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You're mixing up "trimming" with "truncating". I was actually mixing up languages - English and Delphi so I was using trimming in the sense of "trimming off the fat" as opposed to Delphi's "remove training blanks" >If you put a value into a >TStringField in Delphi/C++Builder that is longer than the defined length of >the field, then the VCL layer will truncate the value before it ever reaches >the EDB engine. Therefore, you won't see a truncate error. However, if you >try to do the same with SQL (or .NET/ODBC/PHP), EDB will raise a truncation >error message. > >None of the layers in EDB automatically right-trim CHAR/VARCHAR columns. Thank you. That explains it a lot better than I could. Roy Lambert |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |