bcp_bind
Binds data from a program variable to a table column for bulk copy into SQL Server.
Use bcp_bind for a fast, efficient way to copy data from a program variable into a table in SQL Server.
Call bcp_init before calling this or any other bulk-copy function. Calling bcp_init sets the SQL Server target table for bulk copy. When calling bcp_init for use with bcp_bind and bcp_sendrow, the bcp_init szDataFile parameter, indicating the data file, is set to NULL; the bcp_init eDirection parameter is set to DB_IN.
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. Rebinding a column is not supported.
Whenever you want SQL Server to commit 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.
Control parameter settings, specified with bcp_control, have no effect on bcp_bind row transfers.
If pData for a column is set to NULL because its value will be supplied by calls to bcp_moretext, any subsequent columns with eDataType set to SQLTEXT, SQLNTEXT, SQLXML, SQLUDT, SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, SQLNCHAR, or SQLIMAGE must also be bound with pData set to NULL, and their values must also be supplied by calls to bcp_moretext.
For new large value types, such as varchar(max), varbinary(max), or nvarchar(max), you can use SQLCHARACTER, SQLVARCHAR, SQLVARBINARY, SQLBINARY, and SQLNCHAR as type indicators in the eDataType parameter.
If cbTerm is not 0, any value (1, 2, 4, or 8) is valid for the prefix (cbIndicator). In this situation, SQL Server Native Client will search for the terminator, calculate data length with respect to the terminator (i), and set the cbData to the smaller value of i and the value of prefix.
If cbTerm is 0 and cbIndicator (the prefix) is not 0, cbIndicator must be 8. The 8 byte prefix can take the following values:
-
0xFFFFFFFFFFFFFFFF means a null value for the field
-
0xFFFFFFFFFFFFFFFE is treated as a special prefix value which is used for efficiently sending data in chunks to the server. The format of data with this special prefix is:
-
<SPECIAL_PREFIX> <0 or more DATA CHUNKS> <ZERO_CHUNK> where:
-
SPECIAL_PREFIX is 0xFFFFFFFFFFFFFFFE
-
DATA_CHUNK is a 4 byte prefix containing length of the chunk,followed by the actual data whose length is specified in the 4 byte prefix.
-
ZERO_CHUNK is a 4 byte value containing all zeros (00000000) indicating the end of data.
-
Any other valid 8 byte length is treated as a regular data length.
Calling bcp_columns when using bcp_bind results in an error.
For information about the types used with the eDataType parameter for date/time types, see Bulk Copy Changes for Enhanced Date/Time Types (OLE DB and ODBC).
For more information, see Date/Time Improvements (ODBC).
#include sql.h
#include sqlext.h
#include odbcss.h
// Variables like henv not specified.
HDBC hdbc;
char szCompanyName[MAXNAME];
DBINT idCompany;
DBINT nRowsProcessed;
DBBOOL bMoreData;
char* pTerm = "\t\t";
// Application initiation, get an ODBC environment handle, allocate the
// hdbc, and so on.
...
// Enable bulk copy prior to connecting on allocated hdbc.
SQLSetConnectAttr(hdbc, SQL_COPT_SS_BCP, (SQLPOINTER) SQL_BCP_ON,
SQL_IS_INTEGER);
// Connect to the data source; return on error.
if (!SQL_SUCCEEDED(SQLConnect(hdbc, _T("myDSN"), SQL_NTS,
_T("myUser"), SQL_NTS, _T("myPwd"), SQL_NTS)))
{
// Raise error and return.
return;
}
// Initialize bcp.
if (bcp_init(hdbc, "comdb..accounts_info", NULL, NULL
DB_IN) == FAIL)
{
// Raise error and return.
return;
}
// Bind program variables to table columns.
if (bcp_bind(hdbc, (LPCBYTE) &idCompany, 0, sizeof(DBINT), NULL, 0,
SQLINT4, 1) == FAIL)
{
// Raise error and return.
return;
}
if (bcp_bind(hdbc, (LPCBYTE) szCompanyName, 0, SQL_VARLEN_DATA,
(LPCBYTE) pTerm, strnlen(pTerm, sizeof(pTerm)), SQLCHARACTER, 2) == FAIL)
{
// Raise error and return.
return;
}
while (TRUE)
{
// Retrieve and process program data.
if ((bMoreData = getdata(&idCompany, szCompanyName)) == TRUE)
{
// Send the data.
if (bcp_sendrow(hdbc) == FAIL)
{
// Raise error and return.
return;
}
}
else
{
// Break out of loop.
break;
}
}
// Terminate the bulk copy operation.
if ((nRowsProcessed = bcp_done(hdbc)) == -1)
{
printf_s("Bulk-copy unsuccessful.\n");
return;
}
printf_s("%ld rows copied.\n", nRowsProcessed);
