Specify the format of a host file for bulk copy purposes, with precision and scale support for numeric and decimal columns.
RETCODE bcp_colfmt_ps (dbproc, host_colnum, host_type,
host_prefixlen, host_collen,
host_term, host_termlen,
table_colnum, typeinfo)
DBPROCESS *dbproc;
int host_colnum;
int host_type;
int host_prefixlen;
DBINT host_collen;
BYTE *host_term;
int host_termlen;
int table_colnum;
DBTYPEINFO *typeinfo;
bcp_colfmt_ps’s
parameters are identical to bcp_colfmt’s,
except that bcp_colfmt_ps has
the additional parameter typeinfo, which contains information
about precision and scale for numeric or decimal columns.
A pointer to the DBPROCESS structure that provides the connection for a particular front-end/Adaptive Server Enterprise process. It contains all the information that DB-Library uses to manage communications and data between the front end and Adaptive Server Enterprise.
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 Enterprise 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 Enterprise 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.
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 shortest 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 smallest 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 smallest 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 types 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.
A pointer to a DBTYPEINFO structure containing information about the precision and scale of decimal or numeric host file columns. An application sets a DBTYPEINFO structure with values for precision and scale before calling bcp_colfmt_ps to specify the host file format of decimal or numeric columns.
If typeinfo is NULL, bcp_colfmt_ps is the equivalent of bcp_colfmt. That is:
If the server column is of type numeric or decimal, bcp_colfmt_ps picks up precision and scale values from the column.
If the server column is not numeric or decimal, bcp_colfmt_ps uses a default precision of 18 and a default scale of 0.
If host_type is not 0, SYBDECIMAL or SYBNUMERIC, typeinfo is ignored.
If host_type is 0 and the corresponding server column is not numeric or decimal, typeinfo is ignored.
A DBTYPEINFO structure is defined as follows:
typedef struct typeinfo {
DBINT precision;
DBINT scale;
} DBTYPEINFO;
Legal values for precision are from 1 to 77. Legal values for scale are from 0 to 77. scale must be less than or equal to precision.
SUCCEED or FAIL.
bcp_colfmt_ps is the equivalent of bcp_colfmt, except that bcp_colfmt_ps provides precision and scale support for numeric and decimal datatypes, which bcp_colfmt does not. Calling bcp_colfmt is equivalent to calling bcp_colfmt_ps with typeinfo as NULL.
bcp_colfmt_ps 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_ps 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_ps 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_ps.
You must call bcp_colfmt_ps 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, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_sendrow
