FirebirdSQL logo
 DOMAININDEX 

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.

Row Delimiters

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.

External Table Example

For our example, we will define an external log table that might be used by an exception handler in a stored procedure or trigger.The external table is chosen because the messages from any handled exceptions will be retained in the log, even if the transaction that launched the process is eventually rolled back because of another, unhandled exception.For demonstration purposes, it has two data columns, a timestamp and a message.The third column stores the row delimiter:

CREATE TABLE ext_log
  EXTERNAL FILE 'd:\externals\log_me.txt' (
  stamp CHAR (24),
  message CHAR(100),
  crlf CHAR(2) -- for a Windows context
);
COMMIT;

Now, a trigger, to write the timestamp and the row delimiter each time a message is written to the file:

SET TERM ^;
CREATE TRIGGER bi_ext_log FOR ext_log
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (new.stamp is NULL) then
    new.stamp = CAST (CURRENT_TIMESTAMP as CHAR(24));
  new.crlf = ASCII_CHAR(13) || ASCII_CHAR(10);
END ^
COMMIT ^
SET TERM ;^

Inserting some records (which could have been done by an exception handler or a fan of Shakespeare):

insert into ext_log (message)
values('Shall I compare thee to a summer''s day?');
insert into ext_log (message)
values('Thou art more lovely and more temperate');

The output:

2015-10-07 15:19:03.4110Shall I compare thee to a summer's day?
2015-10-07 15:19:58.7600Thou art more lovely and more temperate

Setting a DEFAULT Value

The optional DEFAULT clause allows you to specify the default value for the table column.This value will be added to the column when an INSERT statement is executed and that column was omitted from the INSERT command or DEFAULT was used instead of a value expression.The default value will also be used in UPDATE when DEFAULT is used instead of a value expression.

The default value can be a literal of a compatible type, a context variable that is type-compatible with the data type of the column, or NULL, if the column allows it.If no default value is explicitly specified, NULL is implied.

An expression cannot be used as a default value.