CHAPTER 5 Moving Data In and Out of Databases
177
For each column, you can specify a column-spec. If you omit this option, the
format information in the load-options applies to this column. The column-
spec and load-options format information tell Adaptive Server IQ what type
of data to expect, and how to convert it into a compatible data format if
necessary.
Syntax for the column-spec is:
column-spec
:
{ ASCII (
input-width
) |
BINARY [ WITH NULL BYTE ] |
PREFIX { 1 | 2 | 4 } |
’
delimiter-string
’ |
DATE (
input-date-format
) |
TIME (
input-time-format
) |
DATETIME (
input-datetime-format
) }
[ NULL ( { BLANKS | ZEROS | ’
literal
’, ... } ) ]
You can specify the following types of data in the column-spec:
• Data with bytes of fixed length. Although specified by the keyword
ASCII, any 8-bit characters may be used, and for 16-bit character sets, two
8-bit characters are used for each 16-bit character. No code conversion is
performed for char and varchar fields except truncation, blank stripping,
or blank padding. ASCII is also used to fill numeric data, time, and date-
time fields. In each case, the conversion is the same if the value were first
inserted as a character field, then cast to the data type of the column in the
table. The input-width value is an integer value indicating the fixed width
in bytes of the input field in every record.
• Binary fields that use a number of
PREFIX bytes (1, 2, or 4) to specify the
length of the binary input. The BINARY keyword indicates that data is
already converted to the internal form (except for when the byte-order load
option is specified.
• Variable-length characters delimited by a separator. You specify the
delimiter-string as a string of one to four ASCII characters, or any 8–bit
hexadecimal ASCII code that represents a single, non-printing character.
The delimiter-string must be enclosed in single quotes. For example, you
specify:
• '\x09' to represent a tab as the terminator.
• '\x00' for a null terminator (no visible terminator as in “C” strings).
• '\x0a' for a newline character as the terminator. You can also use the
special character combination of \n for newline.