bcp_bind

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

Syntax

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

Arguments

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 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 data types, such as integers, the data type itself indicates to the system the length of the data. Therefore, for fixed-length data types, 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 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, because 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 data type 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 more information about a list of supported conversions, see dbconvert. For the list of valid SQL Server data types, see DB-Library for C Data Types.

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 file name 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.

Examples

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 structure. login = dblogin(); DBSETLUSER(login, "user"); DBSETLPWD(login, "my_passwd"); DBSETLAPP(login, "example"); BCP_SETL(login, TRUE); dbproc = dbopen(login, "my_server"); // Initialize bulk copy. 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);