SqlBCPColfmt%

Specifies the format of an operating-system file in a bulk copy into or out of SQL Server.

Syntax

SqlBCPColfmt% ( sqlconn%, fcolumn%, ftype%, fplen%, fclen&, fterm$, ftlen%, tcol% )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

fcolumn%

Is the column number in the operating-system file whose format is being specified. The first column is number 1.

ftype%

Is the datatype of this column in the operating-system file. If the specified datatype is different from the datatype of the corresponding column in the database table (tcol%), the datatype is converted automatically. To specify the same datatype as in the corresponding column of the database table (tcol%), set this parameter to 0.

For a bulk copy out of SQL Server into a file, when ftype% 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 or numeric, the precision and scale of the source column are used.

fplen%

Is the length of the length prefix for this column in the operating-system file. Legal prefix lengths are 1, 2, and 4 bytes. To avoid using a length prefix, set this parameter to 0. So that DB-Library will determine whether to use a length prefix, set this parameter to -1. In that 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 the column length of an operating-system file is used (such as a length prefix and a maximum column length, or a length prefix and a terminator sequence), DB-Library uses the one that results in the shortest amount of data being copied.

One valuable use of length prefixes is to simplify the specification of null data values in an operating-system file. For example, assume that 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.

fclen&

Is the maximum length of this column's data in the operating-system file, not including the length of any length prefix and/or terminator. Setting fclen& to 0 signifies that the data is NULL. Setting fclen& to -1 directs the system to ignore this parameter, indicating that there is no default maximum length.

For fixed-length datatypes such as integers, the length of the data is constant, except in the special case of null values. Therefore, for fixed-length datatypes, fclen& must always be -1 except when the data is NULL, in which case fclen& must be 0. For character, text, binary, and image data, fclen& can be
- 1, 0, or any positive value. When fclen& 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 shorter amount of data being copied.) If fclen& is -1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If fclen& is 0, the system assumes the data is NULL. If fclen& is a positive value, the system uses fclen& as the maximum data length. However, if a positive fclen& and a prefix length and/or terminator sequence are provided, the system determines the data length by using the method that results in the shortest amount of data being copied.

fterm$

Is the terminator sequence to be used for this column. This parameter is useful primarily for character, text, binary, and image datatypes, 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 fterm$ to chr$(0). To make the tab character the terminator, set fterm$ to chr$(9). To make the newline character the terminator, set fterm$ to chr$(13) chr$(10).

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

ftlen%

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.

tcol%

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 (1) or FAIL (0).

Remarks

SqlBCPColfmt% specifies the format of the operating-system file for bulk copies. The format of a bulk-copy operation contains the following parts:

• A mapping from the columns in the operating-system file to columns in the database.
• The datatype of each column in the operating-system file.
• The length of the optional-length prefix of each column.
• The maximum length of the data in a column in the operating-system file.
• The optional terminating-byte sequence in each column.
• The length of the optional terminating-byte sequence in each column.

Each call to SqlBCPColfmt% specifies the format for one column in the operating system file. For example, when you have a table with five columns and want to change the default settings for three of those columns, first call SqlBCPColumns%(5), and then call SqlBCPColfmt% five times, with three of those calls setting your custom format. Set ftype% for the remaining two calls to 0 and fplen%, fclen&, and ftlen% to -1. These settings copy all five columns ¾ three with your customized format and two with the default format.

You must call SqlBCPColumns% before you call SqlBCPColfmt%.

You must call SqlBCPColfmt% once and only once for every column in the operating-system file, regardless of whether those columns use the default format or are skipped.

To skip a column, set tcol% to 0.

See Also

SqlBCPColumnFormat%, SqlBCPColumns%, SqlBCPControl%, SqlBCPExec%, SqlBCPInit%