Edit

Share via

bcp_colfmt

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Syntax

  
RETCODE bcp_colfmt (  
        HDBC hdbc,  
        INT idxUserDataCol,  
        BYTE eUserDataType,  
        INT cbIndicator,  
        DBINT cbUserData,  
        LPCBYTE pUserDataTerm,  
        INT cbUserDataTerm,  
        INT idxServerCol);

Arguments

hdbc
Is the bulk copy-enabled ODBC connection handle.

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

eUserDataType
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 (idxServerColumn), bulk copy converts the data if possible.

SQL Server 2005 (9.x) introduced support for SQLXML and SQLUDT data type tokens in the eUserDataType parameter.

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

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 eUserDataType 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.

cbIndicator
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, 4, or 8.

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.

cbUserData
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 cbUserData to SQL_NULL_DATA indicates that all values in the data file column are, or should be set to NULL.

Setting cbUserData 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, cbUserData can be SQL_VARLEN_DATA, SQL_NULL_DATA, 0, or some positive value. If cbUserData 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 cbUserData 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 cbUserData is 0 or a positive value, the system uses cbUserData as the maximum data length. However, if, in addition to a positive cbUserData, 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 cbUserData value represents the count of bytes of data. If character data is represented by Unicode wide characters, then a positive cbUserData parameter value represents the number of characters multiplied by the size, in bytes, of each character.

pUserDataTerm
Is the terminator sequence 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.

cbUserDataTerm
Is the length, in bytes, of the terminator sequence to be used for this column. If no terminator is present or desired in the data, set this value to 0.

idxServerCol
Is the ordinal position of the column in the database table. The first column number is 1. The ordinal position of a column is reported by SQLColumns.

If this value is 0, bulk copy ignores the column in the data file.

Returns

SUCCEED or FAIL.

Remarks

The bcp_colfmt 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_colfmt 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_colfmt five times, with three of those calls setting your custom format. For the remaining two calls, set eUserDataType to 0, and set cbIndicator, cbUserData, and cbUserDataTerm 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.

For cbIndicator, a value of 8 to indicate a large value type is now valid. If the prefix is specified for a field whose corresponding column is a new max type, it can only be set to 8. For details, see bcp_bind.

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

You must call bcp_colfmt once for each column in the user file.

Calling bcp_colfmt more than once for any user-file column causes an error.

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 idxServerCol 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.

bcp_colfmt Support for Enhanced Date and Time Features

For information about the types used with the eUserDataType parameter for date/time types, see Bulk Copy Changes for Enhanced Date and Time Types (OLE DB and ODBC).

For more information, see Date and Time Improvements (ODBC).

See Also

Bulk Copy Functions