Format Files

After gathering information about each field in the table, bcp asks if you want to save the information to a format file and prompts for the file name.

Using a format file created for the data to be copied with bcp allows you to copy data in or out noninteractively without being prompted by bcp for information, since the format file supplies the information that bcp needs. Use this newly created format file at any other time to copy the data back into the SAP ASE server or to copy data out from the table.

The figure illustrates the format of the bcp format files. It shows the publishers table from the pubs2 database, with all the host file columns in character format, with no prefix, and using the default data length, a newline terminator at the end of the final column of a row, and tabs as terminators for all other columns.

bcp format file
The format of the bcp format file includes the TDS level, number of columns, host file comn order, host file datatype, prefix length, host file data length, terminator, server column order, and server column name.
Using this format file example, the names of the various elements of a bcp format file are:
Elements Description

Host file column order

The host file column order is the sequential number of the field in the host data file, which begins numbering at 1.

Host file datatype

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. See the next table for the list of host file datatypes and their storage formats.

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. The SAP ASE 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 System and User-Defined Datatypes in the Reference Manual: Building Blocks for descriptions and appropriate uses of SAP ASE datatypes.

Prefix length

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.

The allowable prefix length values in bytes, and their ranges are:
  • 0. Range: No prefix

  • 1. Range: 28-1; 0-255

  • 2. Range: 216-1; 0-65535

  • 4. Range: 232 -1; 0-4,294,967,295

Host file data length

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.

Terminator

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.

Server column order

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.

Server column name

The server column name is the name of the database table column into which this field is to be loaded.

Column precision

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.

Column scale

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.

Host File Datatype Storage Formats
   
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