bcp_bind

Binds data from a program variable to a table column for bulk copy into SQL Server.

Syntax

RETCODE bcp_bind (
PDBPROCESS
dbproc,
LPCBYTE
varaddr,
INT
prefixlen,
DBINT
varlen,
LPCBYTE
terminator,
INT
termlen,
INT
type,
INT
table_column );

where

dbproc
Is the DBPROCESS structure that is the handle for a particular workstation/ SQL Server process. It contains all the information that DB-Library uses to manage communications and data between the workstation and SQL Server.
varaddr
Is the address of the program variable from which the data is copied. If type is SQLTEXT or SQLIMAGE, varaddr can be NULL. A NULL varaddr indicates that text and image values are sent to SQL Server in chunks by bcp_moretext, rather than all at once by bcp_sendrow.
prefixlen
Is the length, in bytes, of any length prefix this column can have; valid length prefixes are 0, 1, 2, or 4 bytes. For example, strings in some non-C programming languages are made up of a 1-byte length prefix, followed by the string data itself. If the data does not have a length prefix, set prefixlen to 0.
varlen
Is the length of the data in the program variable, not including the length of any length prefix and/or terminator. Setting varlen to 0 signifies that the data is NULL. Setting varlen to -1 indicates that the system should ignore this parameter.

For fixed-length datatypes, such as integers, the datatype itself indicates to the system the length of the data. Therefore, for fixed-length datatypes, varlen must always be -1 except when the data is NULL, in which case varlen must be 0.

For character, text, binary, and image data, varlen can be -1, 0, or some positive value. If varlen is -1, the system uses either a length prefix or a terminator sequence to determine the length. (If both are supplied, the system uses the one that results in the shortest amount of data being copied.) If varlen is -1 and neither a prefix length nor a terminator sequence is specified, the system returns an error message. If varlen is 0, the system assumes the data is NULL. If varlen is some positive value, the system uses varlen as the data length. However, if, in addition to a positive varlen, 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.

terminator
Is a pointer to the byte pattern, if any, that marks the end of this program variable. For example, C strings usually have a 1-byte terminator whose value is 0. If there is no terminator for the variable, set terminator to NULL. If you want to designate the C null terminator as the program variable terminator, use an empty string (" ") as terminator and set termlen to 1, since the null terminator constitutes a single byte. For instance, to use a C null terminator:
bcp_bind (dbproc, co_name, 0, -1, "", 1, 0, 2)

If there is no terminator:

bcp_bind (dbproc, co_name, 0, -1, NULL, 0, 0, 2)
termlen
Is the length of this program variable's terminator, if any. If there is no terminator for the variable, set termlen to 0.
type
Is the datatype of your program variable. The data in the program variable is automatically converted to the type of the database column. If this parameter is 0, no conversion is performed. For a list of supported conversions, see "dbconvert." For the list of valid SQL Server datatypes, see Appendix B: DB-Library Datatypes.
table_column
Is the column number in the database table to which the data is copied. Column numbers start at 1.

Returns

SUCCEED or FAIL.

Remarks

Use bcp_bind for a fast, efficient way to copy data from a program variable into a table in SQL Server without first putting the data into a user file or using the Transact-SQL INSERT statement.

Call bcp_init before calling this or any other bulk copy functions.

Make a separate bcp_bind call for every column in the SQL Server table into which you want to copy. After the necessary bcp_bind calls have been made, then call bcp_sendrow to send a row of data from your program variables to SQL Server. Call bcp_init to set the table to be copied into.

Whenever you want SQL Server to checkpoint the rows already received, call bcp_batch. For example, call bcp_batch once for every 1000 rows inserted or at any other interval.

When there are no more rows to be inserted, call bcp_done. Failure to do so results in an error.

When using bcp_bind, the user filename parameter, set hfile, in the call to bcp_init, to NULL, and set direction, the direction parameter, to DB_IN.

Control parameter settings, specified with bcp_control, have no effect on bcp_bind row transfers.

Calling bcp_columns when using bcp_bind results in an error.

Example

The following example shows how to use bcp_bind:

LOGINREC    *login;
DBPROCESS    *dbproc;
char        co_name[MAXNAME];
DBINT        co_id;
DBINT        rows_sent;
DBBOOL        more_data;
char        *terminator = "\t\t";

// Install error-handler and message-handler. 
dberrhandle(err_handler);
dbmsghandle(msg_handler);

// Open a DBPROCESS. 
login = dblogin();
DBSETLUSER(login, "user");
DBSETLPWD(login, "my_passwd");
DBSETLAPP(login, "example");
BCP_SETL(login, TRUE);
dbproc = dbopen(login, "my_server");

// Initialize bcp. 
if (bcp_init(dbproc, "comdb..accounts_info", (BYTE *)NULL,
    (BYTE *)NULL, DB_IN) == FAIL)
    exit(ERREXIT);
// Bind program variables to table columns. 
if (bcp_bind(dbproc, (BYTE *)&co_id, 0, (DBINT)-1, (BYTE *)NULL, 0, 0,     1)     == FAIL)
{
    fprintf(stderr, "bcp_bind, column 1, failed.\n");
    exit(ERREXIT);
}

if (bcp_bind
    (dbproc, co_name, 0, (DBINT)-1, terminator, strlen(terminator), 0,     2)     == FAIL)
{
    fprintf(stderr, "bcp_bind, column 2, failed.\n");
    exit(ERREXIT);
}

while (TRUE)
{
    // Process and retrieve program data. 
    more_data = getdata(&co_id, co_name);

    if (more_data == FALSE)
        break;
        // Send the data. 
    if (bcp_sendrow(dbproc) == FAIL)
        exit(ERREXIT);
}

// Terminate the bulk copy operation. 
if ((rows_sent = bcp_done(dbproc)) == -1)
    printf("Bulk-copy unsuccessful.\n");
else
    printf("%ld rows copied.\n", rows_sent);

See Also

bcp_batch, bcp_colfmt, bcp_collen, bcp_colptr, bcp_columns, bcp_control, bcp_done, bcp_exec, bcp_init, bcp_moretext, bcp_sendrow, dbconvert; DB-Library Datatypes