Specify the format of a host file for bulk copy purposes.
RETCODE bcp_colfmt (dbproc, host_colnum, host_type,
host_prefixlen, host_collen,
host_term, host_termlen,
table_colnum)
DBPROCESS *dbproc;
int host_colnum;
int host_type;
int host_prefixlen;
DBINT host_collen;
BYTE *host_term;
int host_termlen;
int table_colnum;
A pointer to the DBPROCESS structure that provides the connection for a particular front-end/Adaptive Server process. It contains all the information that DB-Library uses to manage communications and data between the front end and Adaptive Server.
The column in the host file whose format is being specified. The first column is number 1.
The datatype of this column in the host file, expressed as an Adaptive Server datatype. If it is different from the datatype of the corresponding column in the database table (table_colnum), the conversion will be performed automatically. See the dbconvert reference page for a table of allowable data conversions. That reference page also contains a list of Adaptive Server datatypes.
If you want to specify the same datatype as in the corresponding column of the database table (table_colnum), this parameter should be set to 0.
bcp_colfmt does
not offer precision and scale support for numeric and decimal types.
When setting the format of a numeric or decimal host
column, bcp_colfmt uses
a default precision and scale of 18 and 0, respectively. To specify
a different precision and scale, an application can call bcp_colfmt_ps.
The length of the length prefix for this column in the host file. Legal prefix lengths are 1, 2, and 4 bytes. To avoid using a length prefix, this parameter should be set to 0. To let bcp decide whether to use a length prefix, this parameter should be set to -1. In such a case, bcp will use a length prefix (of whatever length is necessary) if the database column length is variable.
If more than one means of specifying a host file column length is used (such as a length prefix and a maximum column length, or a length prefix and a terminator sequence), bcp will look at all of them and use the one that results in the smallest amount of data being copied.
One valuable use for length prefixes is to simplify the specifying of null data values in a host file. For instance, assume you have a 1-byte length prefix for a 4-byte integer column. Ordinarily, the length prefix will contain 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.
The maximum length of this column’s data in the host file, not including the length of any length prefix and/or terminator. Setting host_collen to 0 signifies that the data is NULL. Setting host_collen to -1 indicates that the system should ignore this parameter (that is, there is no default maximum length).
For fixed-length datatypes, such as integer, the length of the data is constant, except for the special case of null values. Therefore, for fixed-length datatypes, host_collen must always be -1, except when the data is null, in which case host_collen must be 0.
For char, text, binary, and image datatypes, host_collen can be -1, 0, or some positive value. If host_collen is -1, the system will use either a length prefix or a terminator sequence to determine the length of the data. (If both are supplied, the system will use the one that results in the shortest amount of data being copied.) If host_collen is -1 and neither a prefix length nor a terminator sequence is specified, the system will return an error message. If host_collen is 0, the system assumes the data is NULL. If host_collen is some positive value, the system uses host_collen as the maximum data length. However, if, in addition to a positive host_collen, a prefix length and/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.
The terminator sequence to be used for this column. This parameter is mainly useful for char, text, binary, and image datatypes, because all other datatypes are of fixed length. To avoid using a terminator, set this parameter to NULL. To set the terminator to the NULL character, set host_term to “\0”. To make the tab character the terminator, set host_term to “\t”. To make the newline character the terminator, set host_term to “\n”.
If more than one means of specifying a host file column length is used (such as a terminator and a length prefix, or a terminator and a maximum column length), bcp will look at all of them and use the one that results in the smallest amount of data being copied.
The length, in bytes, of the terminator sequence to be used for this column. To avoid using a terminator, set this value to -1.
The corresponding column in the database table. If this value is 0, this column will not be copied. The first column is column 1.
SUCCEED or FAIL.
bcp_colfmt allows you to specify the host file format for bulk copies. For bulk copy purposes, a format contains the following parts:
A mapping from host file columns to database columns
The datatype of each host file column
The length of the optional length prefix of each column
The maximum length of the host 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 host file column. For example, if you have a table with five columns and want to change the default settings for three of those columns, you should first call bcp_columns(dbproc, 5), and then call bcp_colfmt five times, with three of those calls setting your custom format. The remaining two calls should have their host_type set to 0, and their host_prefixlen, host_collen, and host_termlen parameters set to -1. The result of this would be to copy all five columns—three with your customized format and two with the default format.
bcp_columns must be called before any calls to bcp_colfmt.
You must call bcp_colfmt for every column in the host 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.
bcp_batch, bcp_bind, bcp_colfmt_ps, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_sendrow
