The following list names the various elements of a bcp format file. Use Figure 4-3 as the format file example.
The Tabular Data Stream (TDS) version is always the first line of the file. It specifies the version of TDS that you are using, not the Adaptive Server version, and appears is a literal string without quotation marks. In Figure 4-3, the version is 10.0.
The second line of a bcp format file is the number of columns, which refers to the number of records in the format file, not including lines 1 and 2. Each column in the host table has one line.
One line for each column follows the first and second lines in the database table. Each line consists of elements that are usually separated by tabs, except for the host file datatype and the prefix length which are usually separated by a space. These elements are:
Host file column order
Host file datatype
Prefix length
Host file data length
Terminator
Server column order
Server column name
Column precision
Column scale
The following sections describe the column elements in the format file.
The host file column order is the sequential number of the field in the host data file, which begins numbering at 1.
The host file datatype refers to the storage format of the field in the host data file, not the datatype of the database table column. The host file datatypes and their storage formats are:
SYBCHAR – char, chavarchar (ASCII), nchar, nvarchar
SYBTEXT – text
SYBBINARY – binary, timestamp, unichar, univarchar, varbinary
SYBIMAGE – image
SYBINT1 – tinyint
SYBINT2 – smallint
SYBINT4 – int
SYBINT8 – bigint
SYBFLT8 – float
SYBREAL – real
SYBBIT – bit
SYBNUMERIC – numeric
SYBDECIMAL – decimal
SYBMONEY – money
SYBMONEY4 – smallmoney
SYBDATETIME – datetime
SYBDATETIME4 – smalldatetime
SYBDATE – date
SYBTIME – time
SYBUINT8 – unsigned bigint
SYBUINT4 – unsigned int
SYBUINT2 – unsigned smallint
SYBUNITEXT – unitext
SYBFLT8 – double
Data written to a host file in its native format preserves all of its precision. datetime and float values preserve all of their precision, even when they are converted to character format. Adaptive Server stores money values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places.
See Chapter 1, “System and User-Defined Datatypes” in the Reference Manual: Building Blocks for descriptions and appropriate uses of Adaptive Server datatypes.
Prefix length indicates the number of bytes in the field length prefix. The prefix length is a 0-, 1-, 2-, or 4-byte unsigned integer value embedded in the host data file that specifies the actual length of data contained in the field. Some fields may have a length prefix while others do not.
Table 4-7 shows the allowable prefix length values.
Length (in bytes) |
Range |
|---|---|
0 |
No prefix |
1 |
28-1; 0-255 |
2 |
216-1; 0-65535 |
4 |
232 -1; 0-4,294,967,295 |
Host file data length refers to the maximum number of bytes to copy for the field. To decide how much data to copy in or out, bcp uses one of:
The maximum field length
The prefix length, if any
The field terminator string, if any
If more than one method of field length specification is given, bcp chooses the one that copies the least amount of data.
The terminator can be up to 30 bytes of characters enclosed in quotation marks (" "). The terminator designates the end of data for the host data file field.
The server column order represents the colid (column ID) of the syscolumns column into which the host data file column is to be loaded. Together with the host file column order, this element maps host data file fields to the database table columns.
The server column name is the name of the database table column into which this field is to be loaded.
The column precision is the precision of the database table column into which this field is to be loaded. This element is present only if the storage format is numeric or decimal.
The column scale is the scale of the database table column into which this field is to be loaded. This element is present only if the storage format is numeric or decimal.
