bcp_setcolfmt

  • Article

The bcp_setcolfmt function supersedes the bcp_colfmt. In specifying the column collation, bcp_setcolfmt function must be used.

This function provides a flexible approach to specifying the column format in a bulk copy operation. It is used to set individual column format attributes. Each call to bcp_setcolfmt sets one column format attribute.

The bcp_setcolfmt function specifies the source or target format of the data in a user file. When used as a source format, bcp_setcolfmt specifies the format of an existing data file used as a data source of data in a bulk copy to a table in SQL Server. When used as a target format, the data file is created using the column formats specified with bcp_setcolfmt.

Syntax

RETCODE bcp_setcolfmt (
        HDBC hdbc,
        INT field,
        INT property,
        void* pValue,
        INT cbValue);

Arguments

  • hdbc
    Is the bulk copy-enabled ODBC connection handle.
  • field
    Is the ordinal column number for which the property is being set.

  • property
    Is one of the property constants. Property constants are defined in this table.

    Property Value Description

    BCP_FMT_TYPE

    BYTE

    Is the data type of this column in the user file. If different from the data type of the corresponding column in the database table, bulk copy converts the data if possible.

    The BCP_FMT_TYPE parameter is enumerated by the SQL Server data type tokens in sqlncli.h, rather than the ODBC C data type enumerators. For example, you can specify a character string, ODBC type SQL_C_CHAR, using the SQLCHARACTER type specific to SQL Server.

    To specify the default data representation for the SQL Server data type, set this parameter to 0.

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

    BCP_FMT_INDICATOR_LEN

    INT

    Is the length in bytes of the indicator (prefix).

    It is the length, in bytes, of a length/null indicator within the column data. Valid indicator length values are 0 (when using no indicator), 1, 2, or 4.

    To specify default bulk copy indicator usage, set this parameter to SQL_VARLEN_DATA.

    Indicators appear in memory directly before any data, and in the data file directly before the data to which they apply.

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

    Data files generated by bulk copy when no user intervention adjusts the format of the data contain indicators when the column data can vary in length or the column can accept NULL as a value.

    BCP_FMT_DATA_LEN

    DBINT

    Is the length in bytes of the data (column length)

    It is the maximum length, in bytes, of this column's data in the user file, not including the length of any length indicator or terminator.

    Setting BCP_FMT_DATA_LEN to SQL_NULL_DATA indicates that all values in the data file column are, or should be set to, NULL.

    Setting BCP_FMT_DATA_LEN to SQL_VARLEN_DATA indicates that the system should determine the length of data in each column. For some columns, this could mean that a length/null indicator is generated to precede data on a copy from SQL Server, or that the indicator is expected in data copied to SQL Server.

    For SQL Server character and binary data types, BCP_FMT_DATA_LEN can be SQL_VARLEN_DATA, SQL_NULL_DATA, 0, or some positive value. If BCP_FMT_DATA_LEN is SQL_VARLEN_DATA, the system uses either the length indicator, if present, or a terminator sequence to determine the length of the data. If both a length indicator and a terminator sequence are supplied, bulk copy uses the one that results in the least amount of data being copied. If BCP_FMT_DATA_LEN is SQL_VARLEN_DATA, the data type is an SQL Server character or binary type, and neither a length indicator nor a terminator sequence is specified, the system returns an error message.

    If BCP_FMT_DATA_LEN is 0 or a positive value, the system uses BCP_FMT_DATA_LEN as the maximum data length. However, if, in addition to a positive BCP_FMT_DATA_LEN, a length indicator or terminator sequence is provided, the system determines the data length by using the method that results in the least amount of data being copied.

    The BCP_FMT_DATA_LEN value represents the count of bytes of data. If character data is represented by Unicode wide characters, then a positive BCP_FMT_DATA_LEN parameter value represents the number of characters multiplied by the size, in bytes, of each character.

    BCP_FMT_TERMINATOR

    LPCBYTE

    Pointer to the terminator sequence (either ANSI or Unicode as appropriate) to be used for this column. This parameter is useful mainly for character data types because all other types are of fixed length or, in the case of binary data, require an indicator of length to accurately record the number of bytes present.

    To avoid terminating extracted data, or to indicate that data in a user file is not terminated, set this parameter to NULL.

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

    The bulk copy API performs Unicode-to-MBCS character conversion as required. Care must be taken to ensure that both the terminator byte string and the length of the byte string are set correctly.

    BCP_FMT_SERVER_COL

    INT

    Ordinal position of the column in the database

    BCP_FMT_COLLATION

    LPCSTR

    Collation name.
  • pValue
    Is the pointer to the value to associate to the property. It allows each column format property to be set individually.
  • cbvalue
    Is the length of the property buffer in bytes.

Returns

SUCCEED or FAIL.

Remarks

This function supersedes the bcp_colfmt function. All the functionality of bcp_colfmt is provided in bcp_setcolfmt function. In addition, support for column collation is also provided. It is recommended that the following column format attributes be set in the order given below:

BCP_FMT_SERVER_COL

BCP_FMT_DATA_LEN

BCP_FMT_TYPE

The bcp_setcolfmt function 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 indicator for each column.
  • The maximum length of data per user-file column.
  • The optional terminating byte sequence for each column.
  • The length of the optional terminating byte sequence.

Each call to bcp_setcolfmt specifies the format for one user-file column. For example, to change the default settings for three columns in a five-column user data file, first call bcp_columns(5), and then call bcp_setcolfmt five times, with three of those calls setting your custom format. For the remaining two calls, set BCP_FMT_TYPE to 0, and set BCP_FMT_INDICATOR_LENGTH, BCP_FMT_DATA_LEN, and cbValue to 0, SQL_VARLEN_DATA, and 0 respectively. 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 calling bcp_setcolfmt.

You must call bcp_setcolfmt once for each property of each column in the user file.

You do not need to copy all data in a user file to the SQL Server table. To skip a column, specify the format of the data for the column, setting the BCP_FMT_SERVER_COL parameter to 0. If you want to skip a column, you must specify its type.

The bcp_writefmt function can be used to persist the format specification.

See Also

Reference

Bulk Copy Functions

Help and Information

Getting SQL Server 2005 Assistance