Using External Files As Tables - Firebird

In the current SQL argot, Firebird supports the external virtual table, or EVT. Filesystem files in ASCII text format can be read and manipulated by Firebird as if they were tables, albeit with considerable limitations arising from the fact that they are not internal database objects. Other applications can thus exchange data with a Firebird database, independent of any special transforming mechanism. External tables can be converted to internal tables.

The EXTERNAL FILE clause enables a table to be defined with its row structure mapping to fixed-length “fields” in “records” (usually delimited by line feeds) that reside in an external file. Firebird can select from and insert into such a file as if it were a regular table. It cannot, however, perform update or delete operations on external tables.

The text file containing the data must be created on or copied to a storage device that is physically under the control of the server—as usual, no NFS devices, shares, or mapped drives. Shared access by Firebird and other applications at the file level is not possible. Firebird requires exclusive access during times when it has the file open in a transaction. At other times, the file can be modified by other applications.

Syntax for CREATE TABLE...EXTERNAL FILE

The CREATE TABLE statement for an external file defines both the external file specification (local location and file name) and the typed Firebird columns represented by the structure of the contained records.

Filespec is the fully qualified local path and file specification for the external data file. The file need not exist at the time the table is created. However, from Firebird 1.5 onward, the CREATE statement will fail if the filespec refers to an unconfigured external file location.

Columndef is an ordinary Firebird column definition. Non-character data types can be specified, provided every string extracted from the column’s location in the external record is capable of being cast implicitly to that type.

line _delimiter is an optional final column or pair of columns that can be defined to read the characters used by the file system to mark the end of a line of text. Although it makes reading the file easier for humans, it is not a requirement in a fixed-format record unless programs that are going to read the data require it.

  • On Linux/UNIX, this is the single character ASCII 10, the line feed character.
  • On Windows, it is the ordered pair ASCII 13 (carriage return) followed by ASCII 10.
  • On Mac OS, it is ASCII 10 followed by ASCII 13.
  • Other operating systems may use other variations or other characters.

Restrictions and Recommendations

Securing External Files

With all versions of Firebird, a list of directories can be configured to restrict the locations where Firebird will search for or create external files. By default, Firebird 1.5 is installed with no access to external files, whereas 1.0. x gives open access to any file in the local filesystem.

Format of External Data

Firebird will create the external file itself if it does not find it in the location specified in the CREATE EXTERNAL TABLE '<filespec>' specification. If the file already exists, each record must be of fixed length, consisting of fixed-length fields that exactly match the byte length of the column specifications in the table definition. If the application that created the file uses hard line breaks (e.g., the 2-byte carriage return and line break sequence in Windows text files), include a column to accommodate this sequence. See the upcoming section titled “End-of-Line Characters.”

BLOB and ARRAY data cannot be read from or written to an external file.

Most well-formed number data can be read directly from an external table and, in most cases, Firebird will be able to use its internal casting rules to interpret it correctly. However, it may be easier and more precise to read the numbers into character columns and, later, convert them using the CAST(..) function.

Make sure you allow enough width to accommodate your data.

CHAR vs.VARCHAR

Using VARCHAR in the column definition of an external string field is not recommended because it is not a readily portable format:

VARCHAR requires the initial 2-byte unsigned short to include the number of bytes in the actual string, and the string immediately follows. This is difficult or impossible for many external applications to achieve and it simply is not worth the trouble. For this reason, favor CHAR over VARCHAR for string fields and ensure that the feeding application pads the strings to full length.

End-of-Line Characters

When you create the table that will be used to import the external data, you must define a column to contain the end -of -line (EOL) or newline character if the application that created the file included it. The size of this column must be exactly large enough to contain a particular system’s EOL symbol (usually 1 or 2 bytes). For most versions of UNIX, it is 1 byte. For Windows and Macintosh, it is 2 bytes.

Tips for Inserting Non-Printable Characters

When inserting to an external file, the external function ASCII_CHAR(decimal_ASCII_code) from the ib_udf function library can be used to pass the non-printable characters as an expression to the line-delimiter columns in the SQL statement. For example, the following inserts a carriage return and line feed into a column:

An alternative is to create a table to store any non-printable characters your applications might need to store. Simply create a regular text file on the same platform as the server, using an editor that “displays” non-printable characters. Open your “NPC” table using an interactive tool and copy and paste the characters directly to the table. Here we are talking about Intel architecture. Alignment may be different on some architectures.

For statements performing inserts to the external file, the character can be subqueried from the table.

Operations

Only INSERT and SELECT operations can be performed on the rows of an external table. Attempts to update or delete rows will return errors.

Because the data is outside the database, operations on an external table are not under Firebird’s record version control. Inserts therefore take effect immediately and cannot be rolled back.

If you use DROP DATABASE to delete the database, you must also remove the external file—it will not be automatically deleted as a result of DROP DATABASE.

Importing External Files to Firebird Tables

To import an external file into a Firebird table, begin by making sure that you have set up the appropriate access conditions.

  1. Create a Firebird table that allows you to view the external data. Declare all columns as CHAR. The text file containing the data must be on the server. In the following example, the external file exists on a UNIX system, so the EOL character is 1 byte.
  2. Create another Firebird table that will eventually be your working table. Include a column for the EOL character if you expect to export data from the internal table back to an external file later:
  3. Using a text editor, or an application that can output fixed-format text, create and populate the external file. Make each record the same length, pad the unused characters with blanks, and insert the EOL character(s) at the end of each record.

    The number of characters in the EOL string is platform-specific—refer to the previous notes.

    The following example illustrates a fixed-length record length is 41 characters. b represents a blank space, and n represents the EOL:

  4. A SELECT statement from table EXT_TBL returns the records from the external file:
  5. Insert the data into the destination table:

    Now, when you perform a SELECT from PERSONNEL, the data from your external table will appear in converted form:

Exporting Firebird Tables to an External File

Carrying on with the example illustrated in the previous section, the steps for exporting data to our external table are similar:

  1. Open the external file in a text editor and remove everything from the file. Exit from the editor and again perform the SELECT query on EXT_TBL. It should be empty.
  2. Use an INSERT statement to copy the Firebird records from PERSONNEL into the external file, file.txt:
  3. Now query the external table:

Converting External Tables to Internal Tables

It is possible to convert the current data in external tables to an internal table. The means to do this is to back up the database using the gbak utility with the –convert switch (abbreviation –co). All external tables defined in the database will be converted to internal tables by restoring the backup. Afterward, the external table definition will be lost.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics