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.
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.
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.
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.
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.
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:
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:
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.