I just ran into some strange behavior related to temporary tables.
I'm still evaluating edb and have been tied (mostly with pleasure) to dbisam3 for years

The behavior can be reproduced in the Manager:

1. create an in memory database --CREATE DATABASE "Mem" IN MEMORY
2. create a temporary table in the memory database --CREATE temporary TABLE "Temp" (....)
3. check if the newly created table exists --select * from mem.information.temporarytables
where name='Temp'
the result set lists the new table

4. drop the temporary table --drop TABLE "Temp"
5. check if the table exists --select * from mem.information.temporarytables where name='Temp'
the table is gone but the result set still lists the dropped table

6. restart the session
7. check the existence of "Temp" --select * from mem.information.temporarytables where
name='Temp'
the result set is as expected null

8. create the temporary table as before --CREATE temporary TABLE "Temp" (....)
9. check its existence again --select * from mem.information.temporarytables where name='Temp'
the table is there, but the result set remains null

note: every execution is preceded by an "unprepare"

by checking the existence of a temporary table before creating or deleting it, i try to
avoid annoying exceptions.
is there a work around? or am i missing something?

Josef,

> by checking the existence of a temporary table before creating or deleting it, i try to
> avoid annoying exceptions.

I cannot answer you initial question, but  generally I recommend to use
a script or a stored procedure and eat any exception:

BEGIN
  Execute Immediate 'Drop Table "MyTable"';
EXCEPTION
END;
Execute Immediate 'Create Temporary Table "MyTable"....';

So you don't have to care if the table exists or not.

Regards Uli

Josef


I think this is a consequence (probably unintended) of releasing the temporary tables when the database is closed and Tim isn't removing the entry when an individual entry is dropped.

Uli's method will work although I prefer to test for its existence myself.

Final point is that whilst you can there is little point in creating a temporary table in memory. They are all temporary.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Josef


I think this is a consequence (probably unintended) of releasing the temporary tables when
the database is closed and Tim isn't removing the entry when an individual entry is dropped.

Uli's method will work although I prefer to test for its existence myself.

Final point is that whilst you can there is little point in creating a temporary table in
memory. They are all temporary.

Roy Lambert [Team Elevate]
-------------------------------------
Roy,
thank you for your helpful and fast response!
Omitting the TEMPORARY clause did it. I was initially misled after creating a "normal"
table in the memory database and seeing it still existing after closing and reopening the
database and even after disconnecting and reconnecting the affiliated session, which made
me believe its structure had been saved in some physical catalog file.
Regarding the misleading information query results with temporary tables, the same
behavior can also be noticed with temp-tables which are created in conventional databases.

Josef

Josef,

<< I just ran into some strange behavior related to temporary tables. I'm
still evaluating edb and have been tied (mostly with pleasure) to dbisam3
for years >>

Thanks for the report.  I'll make sure that this is corrected in the next
build.

The issue is that the temporary table create/drop executions are not causing
the catalog information to be flagged as modified because the catalog isn't
actually changed on disk, thus the Information schema tables don't get
refreshed properly.

--
Tim Young
Elevate Software
www.elevatesoft.com