SqlBCPColumnFormat%

Sets up the column format for the input file in a bulk-copy operation. This is a utility function that combines SqlBCPColumns% and SqlBCPColfmt% in one step.

Syntax

SqlBCPColumnFormat% ( sqlconn%, col( ), numcols% )

where

sqlconn%

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

col( )

Is an array of a user-defined datatype (structure) defined as BCPColData, that contains the following elements:

ftype%

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

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 the bcp utility will determine whether to use a length prefix, set this parameter to -1. In that case, the bcp utility uses a length prefix of whatever length is necessary if the database column length is variable.

When 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), the bcp utility uses the one that results in the shortest amount of data being copied.

One valuable use for length prefixes is to simplify the specifying of null data values in an operating-system file. For example, assume that you have a length prefix of 1 byte 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 an empty string, the length prefix can be set to 0 to indicate that 0 bytes follow.

fcollen&

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 fcollen& to 0 signifies that the data is an empty string. Setting fcollen& 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 for the special case of null values. Therefore, for fixed-length datatypes, fcollen& must always be -1 except when the data is an empty string, in which case fcollen& must be 0. For character, text, binary, and image data, fcollen& can be -1, 0, or any positive value. If fcollen& 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 fcollen& is - 1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If fcollen& is 0, the system assumes that the data is an empty string. If fcollen& is a positive value, the system uses fcollen& as the maximum data length. However, if a positive fcollen& 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 for character, text, binary, and image datatypes, because all other types are of fixed length. To avoid using a terminator, set this parameter to an empty string. To set the terminator to null-terminated, 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). The length of fterm$ is limited to 30 characters.

When 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), the bcp utility 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 -1.

tcol%

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

numcols%

Is the total number of columns to be copied.

Returns

SUCCEED (1) or FAIL (0).

Remarks

SqlBCPColumnFormat% is equivalent to calling SqlBCPColumns% (to set the number of columns) and repeated calls to SqlBCPColfmt% (to set the format).

The size of the col( ) array is equal to the number of columns (numcols%). To access an element of the col structure, you must use the point (.) notation. For example:

col.elementname 

For example, the following statement sets the datatype of the column in the operating-system file:

Col.ftype% = SystemFileType% 

See Also

SqlBCPColfmt%, SqlBCPColumns%