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.

External File Format

The “row” format of the external table is fixed length and binary.There are no field delimiters: both field and row boundaries are determined by maximum sizes, in bytes, of the field definitions.Keep this in mind, both when defining the structure of the external table and when designing an input file for an external table that is to import (or export) data from another application.The ubiquitous CSV format, for example, is of no use as an input file and cannot be generated directly into an external file.

The most useful data type for the columns of external tables is the fixed-length CHAR type, of suitable lengths for the data they are to carry.Date and number types are easily cast to and from strings whereas the native data types — binary data — will appear to external applications as unparseable “alphabetti”.

Of course, there are ways to manipulate typed data to generate output files from Firebird that can be read directly as input files to other applications, using stored procedures, with or without employing external tables.Such techniques are beyond the scope of a language reference.Here, we provide guidelines and tips for producing and working with simple text files, since the external table feature is often used as an easy way to produce or read transaction-independent logs that can be studied off-line in a text editor or auditing application.