Adam


>So, temporary + memory is the way to go for full independent tables that
>will be cleaned up automatically at some stage regardless of what the
>application does.

That's my view, but I'm always willing to hear new facts.

>It seems to me as though the only reason to go with the memory side of
>things is because there's no way of dealing with stray temporary tables.
>(ie, that they can't be deleted automatically and there's no way of
>knowing if a table is temporary or not). If it wasn't for that, it seems
>as though temporary tables would be fine.

I thought it should be possible by scanning the temporary directory, cross checking the file names with the temporary tables but I can see no way to get the file name linked back to the temporary table name.

Maybe someone can tell us.

Roy

Adam,

<< 1) Do I have the correct understanding with Memory vs Temporary tables?
>>

Yes.

<< 2) Although it works, how do I know that the "Drop Table" statement is
working on a temporary table, as opposed to a real table - or are they
effectively one and the same as far as each session is concerned? >>

They are one and the same.  Temporary tables and "normal" tables share the
same namespace, so there can't be duplicates.

<< 3) if I remove the 'Prepare Stmt from 'Select *...', etc - from this, can
I simply open up DiaryContM from a TEDBTable component, make changes, and
then run an update script using data in that table as a result? >>

Regardless of what you do with the PREPARE, you can do all of that.
Temporary tables work just like normal tables.

<< 4) I have a statement that can achieve the same result (as below). The
difference (as I understand it) is that the statement will not delete the
table if it already exists. This statement is easier for me to use /
understand, but I'm guessing that I would need to use a script anyway (or
something) to detect if the table already exists to delete it first. >>

Correct.  You can't create a table and overwrite an existing table.

Regarding your script, though, there is one issue with the PREPARE/OPEN at
the end.  As long as the current script is prepared, the contained statement
will stay prepared.  This means that a single prepare of the script followed
by repeated executions of the script will fail to drop the table because it
will still be open on the second and subsequent executions.  To avoid this,
just make the following change:

BEGIN
DECLARE Result CURSOR WITH RETURN FOR stmt;
UNPREPARE stmt;  -- Add this <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
BEGIN
   Execute immediate 'Drop table "DiaryContM"';
   Exception
End;
Execute Immediate 'Create TEMPORARY TABLE "DiaryContM" as (
  Select True as Selected, GrowerID, GrowerNGR, Tonnage, CommodityID,
VarietyID, GradeID, Price
  From Diary D
  left outer join Names N on (N.ID = D.GrowerID)
  where
  (True)
  /*1*/ and ((D.ID = 13) or (D.ID = 14) or (D.ID = 15))
  ) WITH DATA';

Prepare Stmt from 'Select * from DiaryContM' ;
Open Result;
END

It seems weird, but makes sense when you consider that the prepared state of
any contained statements is persistent within the context of the outer
script's prepared state.

Tim Young
Elevate Software
www.elevatesoft.com

Good Morning Tim,

Thanks for answering my questions.

The only other question I have regarding temporary tables is cleaning
them up in the event of an application crash, etc.

As they share the same naming space as normal tables, is there some way
we can identify orphaned temporary tables and delete them?

Roy has suggested that I use memory temporary tables. Is that the best
approach? Should I always use a temporary table in the memory to ensure
that they are cleaned up in the end?

Thanks & Regards

Adam.

> I thought it should be possible by scanning the temporary directory, cross checking the file names with the temporary tables but I can see no way to get the file name linked back to the temporary table name.

Thanks Roy. I'll see what Tim comes back with. Really appreciate your
help. I somehow gather that the transition from DBISAM to EDB is going
to be a far larger learning curve than from the BDE to DBISAM.

Adam,

<< The only other question I have regarding temporary tables is cleaning
them up in the event of an application crash, etc.

As they share the same naming space as normal tables, is there some way we
can identify orphaned temporary tables and delete them? >>

Do you mean in terms of where they are on disk ?  They are normally stored
in the user's temporary files folder, so they will get cleaned up when
Windows does a scheduled or manual free space cleanup.  Frankly, I wouldn't
worry about this situation that much.  Unless you're generating GB+
temporary tables, then you don't have much to worry about.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> Do you mean in terms of where they are on disk ?  They are normally
> stored in the user's temporary files folder, so they will get cleaned up
> when Windows does a scheduled or manual free space cleanup.  Frankly, I
> wouldn't worry about this situation that much.  Unless you're generating
> GB+ temporary tables, then you don't have much to worry about.

Perfect. So there's no real reason to create temporary memory tables
then. Thanks for that!

Cheers

Adam.