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.

Generally, external files are more useful if rows are separated by a delimiter, in the form of a “newline” sequence that is recognised by reader applications on the intended platform.For most contexts on Windows, it is the two-byte 'CRLF' sequence, carriage return (ASCII code decimal 13) and line feed (ASCII code decimal 10).On POSIX, LF on its own is usual.There are various ways to populate this delimiter column.In our example below, it is done by using a BEFORE INSERT trigger and the internal function ASCII_CHAR.