Hi,

Before I go too much further, I was just wanting to confirm that I'm
doing the right thing with my temporary table.

It's my understanding that temporary tables are 'per user' and can not
be shared? (That I can use them for whatever I want, not worrying about
conflicts), and in comparison Memory tables are like a real table in the
database and can be shared amongst users?

And if correct, I have the following SQL Script below.

SCRIPT
BEGIN
DECLARE Result CURSOR WITH RETURN FOR stmt;
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


The purpose of the script is to:

a) Delete the temporary table if it already exists (as I want to create
a new one).

b) Give me a result set I can work with.

c) Be able to load the temporary table into a TEDBTable component so the
user can edit the data, and

d) Perform queries (insert / update statements) on the data after the
user has changed the data.


My questions are:

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

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?

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?

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.

Is one approach preferred / best practice over another?


Create Temporary Table Tmp123 as
 Select True as Selected, GrowerID, GrowerNGR, Tonnage, CommodityID,
VarietyID, GradeID, Price, N.Code, N.Company
  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

Thanks & Regards

Adam

Adam


>Before I go too much further, I was just wanting to confirm that I'm
>doing the right thing with my temporary table.
>
>It's my understanding that temporary tables are 'per user' and can not
>be shared? (That I can use them for whatever I want, not worrying about
>conflicts), and in comparison Memory tables are like a real table in the
>database and can be shared amongst users?

Almost, you have to add in wether its C/S or F/S. If its C/S you're right, but you can have temporary memory tables as well, if its F/S then all memory tables are per user. Memory is just another database unlike in DBISAM.


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

See above

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

You shouldn't be able to create a temporary table with the same name as another table in the database - try it in EDBManager and see (I just did

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

Once the table is there you can treat it as you would any other table, so since you've created the table you will be able to do with it as you wish

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

>Is one approach preferred / best practice over another?

You can't create a table if its already there so your choice becomes "do I use a script or two queries" - your choice - who cares

Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name.

Roy Lambert [Team Elevate]

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END

Adam,
>>   PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';

And if you want to check for the existence of a temporary table, you would use:
     PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?';

Barry
(Version 2.13 B2)

Barry / Adam


Or if you know you want to drop it if its there just use an exception block.

BEGIN
EXECUTE IMMEDIATE 'DROP table';
EXCEPTION
END;

Roy Lambert [Team Elevate]

Hi Roy,

Thanks for that. So to clarify:

> Almost, you have to add in wether its C/S or F/S. If its C/S you're
> right, but you can have temporary memory tables as well, if its F/S
> then all memory tables are per user. Memory is just another database
> unlike in DBISAM.

- Temporary Tables will always be per user - never shared.
- Memory tables can be shared on C/S, but not on F/S


>> 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?
>
> You shouldn't be able to create a temporary table with the same name as another table in the database - try it in EDBManager and see (I just did

Thanks. So basically - temporary tables and real tables are identical
except that tempory tables are:

- per user, and

- cleaned up when the session finishes.

> Once the table is there you can treat it as you would any other table, so since you've created the table you will be able to do with it as you wish

Excellent

> You can't create a table if its already there so your choice becomes "do I use a script or two queries" - your choice - who cares

Interesting. So, the only thing really stopping me from using a DBISam
type solution is the fact that queries don't support multiple statements
within the same SQL seperated by a semi column?

(As in, I just need multiple phyical TEDBQuery components instead of one?)

> Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name.

Thanks. One day I'm going to learn what everything means in those
scripts, instead of just relying on copy and paste.

Thanks for your help!

Adam.

Thanks Barry and Roy,

I guess when it comes to SQL I like the idea of being able to run the
same SQL in the EDBManager. (For debugging and building).

The Exception (which I have used) stops the script and you have to
continue in EDBManager, so the non-exception script (whilst longer)
might be neater in the end...

Cheers

Adam.

Adam

>Thanks. So basically - temporary tables and real tables are identical
>except that tempory tables are:
>
>- per user, and
>
>- cleaned up when the session finishes.

I'm not sure if its when the session closes or when the app closes. I use memory tables partly from DBISAM habits and partly because even if the app crashes they get "cleaned up".

>Interesting. So, the only thing really stopping me from using a DBISam
>type solution is the fact that queries don't support multiple statements
>within the same SQL seperated by a semi column?

>(As in, I just need multiple phyical TEDBQuery components instead of one?)

Almost The fact that a DBISAM style script wouldn't respond well to exceptions and has no facilities for branching / conditional statements may also have something to do with it.

>> Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name.
>
>Thanks. One day I'm going to learn what everything means in those
>scripts, instead of just relying on copy and paste.

To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy.

Roy Lambert

Hi Roy,

>> Thanks. So basically - temporary tables and real tables are identical
>> except that tempory tables are:
>>
>> - per user, and
>>
>> - cleaned up when the session finishes.
>
> I'm not sure if its when the session closes or when the app closes. I use memory tables partly from DBISAM habits and partly because even if the app crashes they get "cleaned up".

I see. I have a few concerns with EDB memory tables:

1) That memory tables can be shared between other users or instances of
the application if the client is running C/S.

2) That they operate differently between F/S and C/S. I would prefer my
applications to work the same if I switch from one to the other.

These are why I'm looking at using temporary tables.

The requirements I have is for a private temporary result set (or sets)
to be created that I can work with and then have dropped at the end.

I don't want them to remain if the app crashes, and I definitely don't
want them to be available shared amongst multiple instances of the
application or users. (Just incase two users are running the same
procedure but for different data at the same time).

As I understand it, temporary tables are the only tables that I can be
assured won't be shared and will be private to that own session, whether
F/S or C/S which is what I need.

I also expected temporary tables to get cleaned up too, but after
reading your reply now I'm not sure. What happens if someone does crash
with a temporary table? (I'm assuming if they restart, there's a new
session, so a new 'temporary' area - and if so - how does one ever get
to 'clean up' the ghost temporary tables?

You also mentioned temporary memory tables. Maybe this is what I should
be using to have the best of both worlds. (ie, would this ensure since
they are temporary that they will be private per user, but being memory
they will also automatically clean up in an app crash)?

>> Thanks. One day I'm going to learn what everything means in those
>> scripts, instead of just relying on copy and paste.
>
> To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy.

It's my understanding that EDB is supposed to use an industry standard
for SQL? If that's the case, would I potentially just have to find any
examples for other SQL's such as Oracle or MS SQL, or is it not that simple?

I mean - it's great to be able to copy examples and tweak them to my
requirements, but I spend so much time in SQL ever day (currently almost
all with DBISAM) whether it be designing new SQL statements, or doing
once off bulk updates to data, etc  - I see that I'm really going to
need to understand EDB - not just 'get by' for me to be able to be
efficient in it's implementation in my future apps.

Cheers

Adam.

Adam



>1) That memory tables can be shared between other users or instances of
>the application if the client is running C/S.

True unless its created as a temporary table

>2) That they operate differently between F/S and C/S. I would prefer my
>applications to work the same if I switch from one to the other.

Use temporary memory tables and they will

>I also expected temporary tables to get cleaned up too, but after
>reading your reply now I'm not sure. What happens if someone does crash
>with a temporary table? (I'm assuming if they restart, there's a new
>session, so a new 'temporary' area - and if so - how does one ever get
>to 'clean up' the ghost temporary tables?

If you think about it memory tables are released because they are part of the applications memory and are returned to the pool when the application closes, even if they were left around a reboot will fix it. Temporary disk tables I just realised I'm not sure of without either Tim telling us or doing some experimenting. If the temporary tables table is held in memory then when the app crashes its gone and it can no longer know what to delete on a proper close and Tim can't allow ElevateDB to go around deleting files it isn't certain it owns. If the temporary tables table is held on disk then the app could clean up the next time it closes.

>You also mentioned temporary memory tables. Maybe this is what I should
>be using to have the best of both worlds. (ie, would this ensure since
>they are temporary that they will be private per user, but being memory
>they will also automatically clean up in an app crash)?

Yup

>>> Thanks. One day I'm going to learn what everything means in those
>>> scripts, instead of just relying on copy and paste.
>>
>> To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy.
>
>It's my understanding that EDB is supposed to use an industry standard
>for SQL? If that's the case, would I potentially just have to find any
>examples for other SQL's such as Oracle or MS SQL, or is it not that simple?

Yes, that's true for the core SQL unless Tim has added an extension (eg IMPORT TABLE) but I don't know how much of the SQL/PSM language is standard.

My statement above was wrong. I've just found that if you use the contents for the olh rather than the index as I generally do there is a section for PSM giving all the keywords so you should be able to use that to get up to speed reasonably quickly.

Roy Lambert [Team Elevate]

Hi Roy,

Thanks again for the help.

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.

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.

> My statement above was wrong. I've just found that if you use the contents for the olh rather than the index as I generally do there is a section for PSM giving all the keywords so you should be able to use that to get up to speed reasonably quickly.

Excellent - thanks for that. I guess I do the same thing as you (I
normally use context sensitive help, or the index too).

I'll check out the contents help.

Cheers

Adam.