File format for input and output
Derby Server and Administration Guide
69
Exporting LOB data to a separate external file from the other column data
You can use the SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE and
SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE procedures to write LOB
data to a separate external file. These procedures include the lobFileName parameter,
which specifies the name of the external file for the LOB data.
When you use these procedures, the location of the LOB data is written to the main
export file. The format of the reference to the LOB stored in the main export file is
lobsFileName.Offset.length/.
· Offset is the position in the external file in bytes
· length is the size of the LOB column data in bytes
If a LOB column value is NULL, length is written as -1. No data conversion is performed
when you export LOB data to an external file. BLOB data is written in binary format, and
CLOB data is written using the codeset that you specify.
See
for examples using each of the import and
export procedures.
File format for input and output
There are specific requirements for the format of the input and output files when you
import and export data.
The default file format is a delimited text file with the following characteristics:
· Rows are separated by a newline
· Fields are separated by a comma (
,
)
· Character-based fields are delimited with double quotes (
"
)
Restriction: Before you perform import or export operations, you must ensure that
the chosen delimiter character is not contained in the data to be imported or exported.
If you chose a delimiter character that is part of the data to be imported or exported,
unexpected errors might occur. The following restrictions apply to column and character
delimiters:
· Delimiters are mutually exclusive
· A delimiter cannot be a line-feed character, a carriage return, or a blank space
· The default decimal point (
.
) cannot be a character delimiter
· Delimiters cannot be hexadecimal characters (0-9, a-f, A-F).
The record delimiter is assumed to be a newline character. The record delimiter should
not be used as any other delimiter.
Character delimiters are permitted with the character-based fields (CHAR, VARCHAR,
and LONG VARCHAR) of a file during import. Any pair of character delimiters found
between the enclosing character delimiters is imported into the database. For example,
suppose that you have the following character string:
"What a ""great"" day!"
The preceding character string gets imported into the database as:
What a "great" day!
During export, the rule applies in reverse. For example, suppose you have the following
character string:
"The boot has a 3" heel."
The preceding character string gets exported to a file as:
