Using the EXTERNAL FILE Option

From InterBase

Go Up to Creating Tables (Data Definition Guide)


The EXTERNAL FILE option creates a table for which the data resides in an external table or file, rather than in the InterBase database. External files are ASCII text that can also be read and manipulated by non-InterBase applications. In the syntax for CREATE TABLE, the <filespec> that accompanies the EXTERNAL keyword is the fully qualified file specification for the external data file. You can modify the external file outside of InterBase, since InterBase accesses it only when needed.

Note:
The 2GB external file size limit has been removed from InterBase XE onward.

Use the EXTERNAL FILE option to:

  • Import data from a flat external file in a known fixed-length format into a new or existing InterBase table. This allows you to populate an InterBase table with data from an external source. Many applications allow you to create an external file with fixed-length records.
  • SELECT from the external file as if it were a standard InterBase table.
  • Export data from an existing InterBase table to an external file. You can format the data from the InterBase table into a fixed-length file that another application can use.
Important:
For security reasons, it is extremely important that you not place files with sensitive content in the same directory with external tables.

Restrictions

The following restrictions apply to using the EXTERNAL FILE option:

  • The default location for external files is <InterBase_home>/ext. InterBase can always find external files that you place here. If you want to place them elsewhere, you must specify the location in the ibconfig configuration file using the EXTERNAL_FILE_DIRECTORY entry.
Note:
If you are migrating from InterBase 6.x or older to InterBase 7.x or newer, and your database includes external table files, you must either move these files to <InterBase_home>/ext or note their locations in ibconfig using the EXTERNAL_FILE_DIRECTORY entry.
  • You must create the external file before you try to access the external table inside of the database.
  • Each record in the external file must be of fixed length. You cannot put BLOB or array data into an external file.
  • 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 new-line character. The size of this column must be exactly large enough to contain an EOL symbol of a particular system (usually one or two bytes). For most versions of UNIX, it is 1 byte. For Microsoft Windows, it is 2 bytes.
  • While it is possible to read in numeric data directly from an external table, it is much easier to read it in as character data, and convert using the CAST() function.
  • Data to be treated as VARCHAR in InterBase must be stored in an external file in the following format:
<2-byte unsigned short><string of character bytes>
where the two-byte unsigned short indicates the number of bytes in the actual string, and the string immediately follows. Because it is not readily portable, using VARCHAR data in an external file is not recommended.
  • You can perform only INSERT and SELECT operations on an external table. You cannot perform UPDATE or DELETE operations on it; if you try to do so, InterBase returns an error message.
  • Inserting into and selecting from an external table are not under standard transaction control because the external file is outside of the database. Therefore, changes are immediate and permanent – you cannot roll back your changes. If you want your table to be under transaction control, create another internal InterBase table, and insert the data from the external table into the internal one.
  • 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

The following steps describe how to import an external file into an InterBase table:

  1. Create an InterBase 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 one byte. If the example file was on a Windows platform, you would need two characters for NEW_LINE.
    CREATE TABLE EXT_TBL EXTERNAL FILE 'file.txt'
    (FNAME CHAR(10),
     LNAME CHAR(20),
     HDATE CHAR(8),
     NEWLINE CHAR(1) );
    COMMIT;
    
  2. Create another InterBase table that will eventually be your working table. If you expect to export data from the internal table back to an external file at a later time, be sure to create a column to hold the newline. Otherwise, you do not need to leave room for the newline character(s). In the following example, a column for the newline is provided:
    CREATE TABLE PEOPLE
    (FIRST_NAME CHAR(10),
     LAST_NAME CHAR(20),
     HIRE_DATE CHAR(8),
     NEW_LINE CHAR(1));
    COMMIT;
    
  3. Create and populate the external file. You can create the file with a text editor, or you can create an appropriate file with an application such as ­Paradox for Windows or dBASE for Windows. If you create the file with a text editor, 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 is platform-specific. You need to know how many characters are contained in the EOL of your platform (typically one or two) in order to correctly format the columns of the tables and the corresponding records in the external file. In the following example, the record length is 36 characters. “b” represents a blank space, and “n” represents the EOL: When exporting data to or from an external file, the file must already exist before you begin the operation. Also, you must specify a directory path whenever you reference the external file.
  4. At this point, when you do a SELECT statement from table EXT_TBL, you will see the records from the external file:
    SELECT FNAME, LNAME, HDATE FROM EXT_TBL;
    
    FNAME     LNAME             HDATE
    ========  ================= ===========
    Robert    Brickman          12-JUN-1992
    Sam       Jones             13-DEC-1993
    
  5. Insert the data into the destination table.
    INSERT INTO PEOPLE SELECT FNAME, LNAME, CAST(HDATE AS DATE),
     NEWLINE FROM EXT_TBL;
    

    Now if you SELECT from PEOPLE, the data from your external table will be there.

    SELECT FIRST_NAME, LAST_NAME, HIRE_DATE FROM PEOPLE;
    
    FIRST_NAME LAST_NAME           HIRE_DATE
    ========== =================== ===========
    Robert     Brickman            12-JUN-1992
    Sam        Jones               13-DEC-1993
    
    Note:
    InterBase allows you to store the date as an integer by converting from a CHAR(8) to DATE using the CAST() function.

Exporting InterBase Tables to an External File

If you add, update, or delete a record from an internal table, the changes will not be reflected in the external file. So in the previous example, if you delete the “Sam Jones” record from the PEOPLE table, and do a subsequent SELECT from EXT_TBL, you would still see the “Sam Jones” record.

Note:
When exporting data to or from an external file, the file must already exist before you begin the operation. Also, you must specify a directory path whenever you reference the external file.

This section explains how to export InterBase data to an external file. Using the example developed in the previous section, follow these steps:

  1. Open the external file in a text editor and remove everything from the file. If you then do a SELECT on EXT_TBL, it should be empty.
  2. Use an INSERT statement to copy the InterBase records from PEOPLE into the external file, file.txt. Be sure to specify the file directory.
    INSERT INTO EXT_TBL SELECT FIRST_NAME, LAST_NAME, HIRE_DATE,
     NEW_LINE FROM PEOPLE WHERE FIRST_NAME LIKE 'Rob%';
    
  3. Now if you do a SELECT from the external table, EXT_TBL, only the records you inserted should be there. In this example, only a single record should be displayed:
    SELECT FNAME, LNAME, HDATE FROM EXT_TBL;
    
    FNAME     LNAME             HDATE
    ========  ================= ===========
    Robert    Brickman          12-JUN-1992
    
    Important:
    Make sure that all records that you intend to export from the internal table to the external file have the correct EOL character(s) in the newline column.

Advance To: