Paul,

> Syntax:
>
> CREATE TEMPORARY TABLE <table name> <table contents source>
> [ON COMMIT {PRESERVE | DELETE} ROWS]

CREATE GLOBAL TEMPORARY TABLE

for tables that are accessible over connection boundaries.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

> Syntax:
> If the optional ON COMMIT clause is omitted, the contents of the temporary

table are discarded at the end of the transaction that

> created them.
>
> Semantics:
> An entry for relation <table name> is made in the RDB$RELATIONS system

table. Entries are made in RDB$RELATION_FIELDS and

> RDB$FIELDS relations describing the fields listed in the <table contents

source>, as they would be for a permanent table. The

> entries in RDB$RELATIONS, RDB$FIELDS, and RDB$RELATION_FIELDS are

provisional and unusable until the transaction which issued the

> CREATE statement commits.
>
> All connections can see the definition of temporary tables; temporary

tables are part of the global name space. No connection can

> see data stored in a temporary table by another connection.
>
> A temporary table can be either the referenced or referencing

table in a foreign key constraint, but the partner table must

> be a temporary table. If either table is ON DELETE COMMIT, both must be

ON DELETE COMMIT.

>
>
> System table support:
> A new field will be added to RDB$RELATIONS, called RDB$RELATION_TYPE. If

that field value has the value RDB$PERMANENT, the table is

> permanent. If the field value is RDB$TEMPORARY_PRESERVE the table is

temporary and contents are retained until the connection which

> created them terminates. If the value is a RDB$TEMPORARY_DELETE, the

table is temporary and contents are discarded at the end of

> the transaction which created them.
>
> Notes:
> No changes are proposed to ALTER TABLE or DROP TABLE. Changing a table

from permanent to temporary or vice-versa is accomplished

> by dropping the table and recreating it.
>
> The syntax AS <query expression> has been suggested as an alternative to

the <table contents source> listed in the syntax above. If

> that syntax is part of the SQL-99 standard, I can't find it.
>
> Implementation:
> A temporary table is represented internally by a TemporaryRelation

class, which is a subclass of the Relation class. A

> TemporaryRelation instance is created either by dfw, CStatement, or during

the processing of blr. TemporaryRelation classes hang

> off the attachment object as well as the Database object. The

TemporaryRelation object provides the location of the index root

> page for that table instance and it's pointer page list.
>
> Changes to VIO, DPM, NAV, BTR, and IDX would be primarily cleanup and

applying object discipline to the driving structures. Record

> source blocks become a class hierarchy. References to the source

relation would be through either the TemporaryRelation object or

> the Relation.
>
> Data and indexes for a temporary table could be stored on pages in the

active database or in a temporary file created for the

> instance. Each alternative has advantages.
>
> Using the active database leverages the existing structures. Free space

can be located with existing mechanisms completely

> unchanged. Page number references in indexes and blobs can be used

directly. Cleaning up the pages used by a temporary table in

> the case of a system crash is problematic.
>
> Using a temporary file requires that space management and all page

references be redirected to the temporary file, increasing the

> number of layers that must be aware of this change. A temporary file is

automatically destroyed in case of system failure.

>
> Paul Beach
> Main Tel (UK):+44 (0) 1844 354357
> Mobile: (UK): +44 (0) 7837 898072
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>