Using the EXTERNAL FILE Option
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.
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.
SELECTfrom 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.
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
ibconfigconfiguration file using the
- 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
- Data to be treated as
VARCHARin 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
VARCHARdata in an external file is not recommended.
- You can perform only
SELECToperations on an external table. You cannot perform
DELETEoperations 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 DATABASEto delete the database, you must also remove the external file – it will not be automatically deleted as a result of
Importing External Files
The following steps describe how to import an external file into an InterBase table:
- 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
CREATE TABLE EXT_TBL EXTERNAL FILE 'file.txt' (FNAME CHAR(10), LNAME CHAR(20), HDATE CHAR(8), NEWLINE CHAR(1) ); COMMIT;
- 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;
- 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.
- At this point, when you do a
SELECTstatement 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
- Insert the data into the destination table.
INSERT INTO PEOPLE SELECT FNAME, LNAME, CAST(HDATE AS DATE), NEWLINE FROM EXT_TBL;
Now if you
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
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
EXT_TBL, you would still see the “Sam Jones” record.
This section explains how to export InterBase data to an external file. Using the example developed in the previous section, follow these steps:
- Open the external file in a text editor and remove everything from the file. If you then do a
EXT_TBL, it should be empty.
- Use an
INSERTstatement to copy the InterBase records from
PEOPLEinto 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%';
- Now if you do a
SELECTfrom 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