Examples Of Global Temporary Tables

Examples of Global Temporary Tables

Creating a connection-scoped global temporary table.

CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
  ID  INTEGER NOT NULL PRIMARY KEY,
  TXT VARCHAR(32),
  TS  TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
ON COMMIT PRESERVE ROWS;

Creating a transaction-scoped global temporary table that uses a foreign key to reference a connection-scoped global temporary table.The ON COMMIT sub-clause is optional because DELETE ROWS is the default.

CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
  ID        INTEGER NOT NULL PRIMARY KEY,
  PARENT_ID INTEGER NOT NULL REFERENCES MYCONNGTT(ID),
  TXT       VARCHAR(32),
  TS        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT DELETE ROWS;

External Tables

The optional EXTERNAL [FILE] clause specifies that the table is stored outside the database in an external text file of fixed-length records.The columns of a table stored in an external file can be of any type except BLOB or ARRAY, although for most purposes, only columns of CHAR types would be useful.

All you can do with a table stored in an external file is insert new rows (INSERT) and query the data (SELECT).Updating existing data (UPDATE) and deleting rows (DELETE) are not possible.

A file that is defined as an external table must be located on a storage device that is physically present on the machine where the Firebird server runs and, if the parameter ExternalFileAccess in the firebird.conf configuration file is Restrict, it must be in one of the directories listed there as the argument for Restrict.If the file does not exist yet, Firebird will create it on first access.

Important

The ability to use external files for a table depends on the value set for the ExternalFileAccess parameter in firebird.conf:

If it is set to None (the default), any attempt to access an external file will be denied.
The Restrict setting is recommended, for restricting external file access to directories created explicitly for the purpose by the server administrator.For example:
- ExternalFileAccess = Restrict externalfiles will restrict access to a directory named externalfiles directly beneath the Firebird root directory
- ExternalFileAccess = d:\databases\outfiles; e:\infiles will restrict access to just those two directories on the Windows host server.Note that any path that is a network mapping will not work.Paths enclosed in single or double quotes will not work, either.
If this parameter is set to Full, external files may be accessed anywhere on the host file system.This creates a security vulnerability and is not recommended.