Restrictions

Go Up to Using the EXTERNAL FILE Option

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.