Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Temporary Table column size |
Mon, Nov 23 2009 6:47 PM | Permanent Link |
"Malcolm" | When using:
CREATE TEMORARY TABLE .. AS .... WITH DATA How is the size of text/varchar columns of the temp table determined? Is it taken from the source table, or from the longest string selected, or ..? I ask because I have had some unexpected 'truncation' errors when post-processing the resulting data and I need to decide where/how to avoid them. Malcolm -- |
Tue, Nov 24 2009 3:26 AM | Permanent Link |
Uli Becker | Malcolm,
> How is the size of text/varchar columns of the temp table determined? > Is it taken from the source table, or from the longest string > selected, or ..? If you skip "temporary" and just create a normal table from your sql-statement, you can check the column sizes. Uli |
Tue, Nov 24 2009 4:07 AM | Permanent Link |
"Malcolm" | Uli Becker wrote:
> > If you skip "temporary" and just create a normal table from your > sql-statement, you can check the column sizes. > > Uli Doh! <blush> -- |
Tue, Nov 24 2009 4:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>Doh! <blush> Whilst I totally endorse you reaction (what colour did you go?) I think its still a good question and one I'd be interested in the answer. Roy Lambert [Team Elevate] |
Tue, Nov 24 2009 7:01 AM | Permanent Link |
"Malcolm" | Roy Lambert wrote:
> Whilst I totally endorse you reaction (what colour did you go?) I > think its still a good question and one I'd be interested in the > answer. > > Roy Lambert [Team Elevate] Sorry, no mirror available at the time. It seems to take the structure from the source column (as I originally expected) - or I got a coincidence. So one option for me will be to alter the source column definition to allow for the appended text .. but what if the users just fill up that extra .. Hmm, better all round if I handle the appends outwith the temp table, ie in the report definitions. -- |
Tue, Nov 24 2009 7:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>It seems to take the structure from the source column (as I >originally expected) - or I got a coincidence. That's what I would expect, and where there is no column then the length of the constant text. >So one option for me >will be to alter the source column definition to allow for the >appended text .. but what if the users just fill up that extra .. >Hmm, better all round if I handle the appends outwith the temp table, >ie in the report definitions. What was the post processing that resulted in the truncation error? Roy Lambert |
Tue, Nov 24 2009 8:34 AM | Permanent Link |
"Malcolm" | Roy Lambert wrote:
> > What was the post processing that resulted in the truncation error? > > Roy Lambert Generic problem reported by beta tester. For reports I commonly use the 'create temp table .. with data' to gather and construct the stuff required for the report - then the report runs off a query on the temp table. In some events there may be a guest or two and sometimes a competitor will withdraw part way through an event. So to explain otherwise 'odd' results I append a tag such as '(guest)' or '(withdrew)' to the name - team column. This is also localised so it can be longer than the English version. As I am doing this by updating the temp table it is sometimes causing this error. It didn't seem to be a problem in DBISAM but as I have not yet investigated I think I will find that the memory tables I used to use with INSERT INTO were defined with 'wider' columns. I will probably now add the tags inside FastReport by adding a bunch of IIF() thingies .. or I will concatenate instead of updating. Skin and cat come to mind. Malcolm -- |
Tue, Nov 24 2009 9:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>It didn't seem to be a problem in DBISAM but as I have not yet >investigated I think I will find that the memory tables I used to use >with INSERT INTO were defined with 'wider' columns. The problem is one of those areas in the SQL spec that I loath. With DBISAM if a bit of text was to large to fit it was simply truncated. With ElevateDB the options were (from memory) a warning or raise an error. With Delphi there's apparently no way to issue a warning so Tim raises an error. You'll have to manage all text input if there's a chance of it exceeding the column widths. The other two areas I dislike is that text is no longer right trimmed, and the trailing spaces are handled differently in SQL and in Delphi and of course the famous null vs emptystring (I'm a heretic I like the idea of null and emptystring are one and the same) which is why I subclassed the TEDBTable to handle things the way I want. Roy Lambert |
Tue, Nov 24 2009 10:04 AM | Permanent Link |
"Malcolm" | Roy Lambert wrote:
> The problem is one of those areas in the SQL spec that I loath. > With DBISAM if a bit of text was to large to fit it was simply > truncated. With ElevateDB the options were (from memory) a warning > or raise an error. With Delphi there's apparently no way to issue a > warning so Tim raises an error. You'll have to manage all text > input if there's a chance of it exceeding the column widths. I don't have a problem with this really. In places I am happy with truncated and there I will swallow the error, but in this case it was useful to get the error as truncation is not acceptable. Now that I understand the cause I can solve it before release. As for your other dislikes .. I have lurked hereabouts for many years. <bg> -- |
Tue, Nov 24 2009 11:06 AM | Permanent Link |
"Malcolm" | Roy Lambert wrote:
> The problem is one of those areas in the SQL spec that I loath. > With DBISAM if a bit of text was to large to fit it was simply > truncated. With ElevateDB the options were (from memory) a warning > or raise an error. With Delphi there's apparently no way to issue a > warning so Tim raises an error. You'll have to manage all text > input if there's a chance of it exceeding the column widths. I don't have a problem with this really. In places I am happy with truncated and there I will swallow the error, but in this case it was useful to get the error as truncation is not acceptable. Now that I understand the cause I can solve it before release. As for your other dislikes .. I have lurked hereabouts for many years. <bg> -- |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |