Board index » delphi » Temporary tables in v7.5

2005-03-23 02:07:03 AM
delphi94
Hi,
How can I create a temporary table with Interbase 7.5.
I read that it is possible to create temporary tables with this version,
visible
until the transaction is active.
What is the syntax to create such a table?
Thanks,
Carlos
 

Carlos Matos writes:
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
blogs.teamb.com/craigstuntz/articles/403.aspx
 

Global temporary tables
This feature implements the functionality of SQL global temporary tables
in InterBase 7.5. Previously InterBase developers simulated temporary
tables with permanent base tables. The developer was responsible for the
application dropping those tables and performing any housekeeping to empty
those tables if the application or database server abnormally terminated.
With this new SQL temporary table feature in InterBase 7.5 all of the
namespace and life cycle issues are transparently managed once the
temporary table is declared to the database schema, thus making
application development much easier.
An SQL global temporary table is declared to a database schema via the
normal CREATE TABLE statement with the following syntactic modifications:
CREATE GLOBAL TEMPORARY <table>(
table-element-comma-list )
[ON COMMIT { PRESERVE | DELETE } ROWS]
The ON COMMIT clause describes whether the rows of the temporary table are
deleted on each transaction commit or are left in place. If the ON COMMIT
clause is not specified then the default behavior is to DELETE ROWS on
transaction commit.
An SQL global temporary table is dropped from a database schema using the
normal DROP TABLE statement. There are no additional clauses necessary to
drop a temporary table.
A temporary table can be altered in the same way as a permanent base table
although there is no official support to toggle the behavior of the ON
COMMIT clause. This specification offers an ALTER TABLE syntax to toggle
that behavior.
ALTER TABLE <table>ON COMMIT {PRESERVE | DELETE} ROWS
{RESTRICT| CASCADE}
RESTRICT will report an error if there are dependencies by other
temporary tables on the current table scope. CASCADE will automatically
propogate this table scope change to other temporary tables to maintain
compliance. The default action is RESTRICT.
For example, assume that TT1 is a temporary table with ON COMMIT PRESERVE
and has a foreign reference to temporary table TT2 which is also ON COMMIT
PRESERVE. If an attempt is made to modify TT2 to ON COMMIT DELETE, an
error is raised because an ON COMMIT PRESERVE table is not allowed by the
SQL standard to have a referential constraint on an ON COMMIT DELETE
table. RESTRICT returns this error while CASCADE would also alter TT1 to
have ON COMMIT DELETE. Thus, CASCADE implements transitive closure when ON
COMMIT behavior is modified.
Note specifically that this ALTER TABLE extension does not allow a table
to be toggled between temporary and persistent. This would provide too
great a risk of accidently deleting a persistent table's rows.
--
Bill Todd (TeamB)
TeamB cannot answer questions received via email
Carlos Matos writes:

Ok, just one thing: must we drop the table or the table goes away after
disconnect from the database?
Thanks,
Carlos
 

TEMPORARY TABLES are permanent tables, with temporary
data, as specified by the SQL standard.
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
www.upscene.com