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

--

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

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>

--

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]

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.

--

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

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
--

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

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>

--

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>

--