bcp_colfmt

Specifies the format of a user file for bulk copy into or out of Microsoft® SQL Server™.

Syntax

RETCODE bcp_colfmt (
PDBPROCESS dbproc,
INT file_column,
BYTE file_type,
INT file_ prefixlen,
DBINT file_collen,
LPCBYTE file_term,
INT file_termlen,
INT table_column );

Arguments

dbproc

Is the DBPROCESS structure that is the handle for a particular workstation/
SQL Server process. It contains all the information that DB-Library uses to manage communications and data between the workstation and SQL Server.

file_column

Is the column number in the user file for which the format is being specified. The first column is number 1.

file_type

Is the data type of this column in the user file. If it is different from the data type of the corresponding column in the database table (table_column), the conversion is performed automatically. For a table of allowable data conversions, see dbconvert. If you want to specify the same data type as in the corresponding column of the database table (table_column), set this parameter to 0.

For a bulk copy out of SQL Server into a file, when file_type is SQLDECIMAL or SQLNUMERIC:

• If the source column is not decimal or numeric, the default precision and scale are used.
• If the source column is decimal and numeric, the precision and scale of the source column are used.

file_ prefixlen

Is the length of the length prefix for this column in the user file. Legal prefix lengths are 1, 2, and 4 bytes. To avoid using a length prefix, set this parameter to 0. To let DB-Library determine whether to use a length prefix, set this parameter to -1. In such a case, DB-Library uses a length prefix (of whatever length is necessary) if the database column length is variable.

If more than one means of specifying a user-file column length is used (such as a length prefix and a maximum column length, or a length prefix and a terminator sequence), DB-Library chooses the one that results in the shortest amount of data being copied.

Length prefixes simplify the way null data values are specified in a user file. For instance, assume you have a 1-byte length prefix for a 4-byte integer column. Ordinarily, the length prefix contains a value of 4 to indicate that a 4-byte value follows. However, if the value of the column is NULL, the length prefix can be set to 0 to indicate that 0 bytes follow for the column.

file_collen

Is the maximum length of this column’s data in the user file, not including the length of any length prefix or terminator. Setting file_collen to 0 signifies that the data is NULL. Setting file_collen to -1 indicates that the system should ignore this parameter (that is, there is no default maximum length).

For fixed-length data types, such as integers, the length of the data is constant, except for the special case of null values. Therefore, for fixed-length data types, file_collen must always be -1, except when the data is NULL, in which case file_collen must be 0. For character, text, binary, and image data, file_collen can be -1, 0, or some positive value. If file_collen is -1, the system uses either a length prefix or a terminator sequence to determine the length of the data. (If both are supplied, the system uses the one that results in the shortest amount of data being copied.) If file_collen is -1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If file_collen is 0, the system assumes the data is NULL. If file_collen is a positive value, the system uses file_collen as the maximum data length. However, if, in addition to a positive file_collen, a prefix length or terminator sequence is provided, the system determines the data length by using the method that results in the shortest amount of data being copied.

file_term

Is the terminator sequence to be used for this column. This parameter is useful mainly for character, text, binary, and image data types because all other types are of fixed length. To avoid using a terminator, set this parameter to NULL. To set the terminator to NULL, set file_term to “\0”. To make the tab character the terminator, set file_term to “\t”. To make the newline character the terminator, set file_term to “\n”.

If more than one means of specifying a user-file column length is used (such as a terminator and a length prefix, or a terminator and a maximum column length), the bulk copy chooses the one that results in the shortest amount of data being copied.

file_termlen

Is the length, in bytes, of the terminator sequence to be used for this column. To avoid using a terminator, set this value to 0.

table_column

Is the corresponding column number in the database table. If this value is 0, this column is not copied. The first column number is column 1.

Returns

SUCCEED or FAIL.

Remarks

bcp_colfmt allows you to specify the user-file format for bulk copies. For bulk copy, a format contains the following parts:

• A mapping from user-file columns to database columns.
• The data type of each user-file column.
• The length of the optional length prefix of each column.
• The maximum length of the user-file column’s data.
• The optional terminating byte sequence for each column.
• The length of this optional terminating byte sequence.

Each call to bcp_colfmt specifies the format for one user-file column. For example, to change the default settings for three columns in a five-column table, first call bcp_columns(5), and then call bcp_colfmt five times, with three of those calls setting your custom format. Set file_type of the remaining two calls to 0, and set their file_ prefixlen, file_collen, and file_termlen parameters to -1. This procedure copies all five columns, three with your customized format and two with the default format.

The bcp_columns function must be called before any calls to bcp_colfmt.

You must call bcp_colfmt once and only once for every column in the user file, regardless of whether some of those columns use the default format or are skipped.

To skip a column, set the table_column parameter to 0. If you want to skip a column, you must specify its type.

See Also