Binds data from a program variable to a table column for bulk copy into Microsoft® SQL Server™.
RETCODE bcp_bind (
HDBC hdbc,
LPCBYTE pData,
INT cbIndicator,
DBINT cbData,
LPCBYTE pTerm,
INT cbTerm,
INT eDataType,
INT idxServerCol );
If indicators are present in the data, they appear in memory directly before the data. The pData parameter points to the indicator variable in this case, and the width of the indicator, the cbIndicator parameter, is used by bulk copy to address user data correctly.
Indicators appear in memory directly before any data. For example, the following structure type definition could be used to insert integer values into a SQL Server table using bulk copy:
typedef struct tagBCPBOUNDINT
{
int iIndicator;
int iValue;
} BCPBOUNDINT;
In the example case, the pData parameter would be set to the address of a declared instance of the structure, the address of the BCPBOUNDINT iIndicator structure member. The cbIndicator parameter would be set to the size of an integer (sizeof(int)), and the cbData parameter would again be set to the size of an integer (sizeof(int)). To bulk copy a row to the server containing a NULL value for the bound column, the value of the instance’s iIndicator member should be set to SQL_NULL_DATA.
Setting cbData to SQL_NULL_DATA signifies that all rows copied to the server contain a NULL value for the column.
Setting cbData to SQL_VARLEN_DATA indicates that the system will use a string terminator, or other method, to determine the length of data copied.
For fixed-length data types, such as integers, the data type indicates the length of the data to the system. Therefore, for fixed-length data types, cbData can safely be SQL_VARLEN_DATA or the length of the data.
For SQL Server character and binary data types, cbData can be SQL_VARLEN_DATA, SQL_NULL_DATA, some positive value, or 0. If cbData is SQL_VARLEN_DATA, the system uses either a length/null indicator (if present) or a terminator sequence to determine the length of the data. If both are supplied, the system uses the one that results in the least amount of data being copied. If cbData is SQL_VARLEN_DATA, the data type of the column is a SQL Server character or binary type, and neither a length indicator nor a terminator sequence is specified, the system returns an error message.
If cbData is 0 or a positive value, the system uses cbData as the data length. However, if, in addition to a positive cbData value, a length indicator or terminator sequence is provided, the system determines the data length by using the method that results in the least amount of data being copied.
The cbData parameter value represents the count of bytes of data. If character data is represented by Unicode wide characters, then a positive cbData parameter value represents the number of characters multiplied by the size in bytes of each character.
If there is no terminator for the variable, set pTerm to NULL.
You can use an empty string (““) to designate the C null terminator as the program-variable terminator. Because the null-terminated empty string constitutes a single byte (the terminator byte itself), set cbTerm to 1. For example, to indicate that the string in szName is null-terminated and that the terminator should be used to indicate the length:
bcp_bind(hdbc, szName, 0, SQL_VARLEN_DATA, "", 1, SQLCHARACTER, 2)
A nonterminated form of this example could indicate that 15 characters be copied from the szName variable to the second column of the bound table:
bcp_bind(hdbc, szName, 0, 15, NULL, 0, SQLCHARACTER, 2)
The bulk copy API performs Unicode-to-MBCS character conversion as required. Make sure that both the terminator byte string and the length of the byte string are set correctly. For example, to indicate that the string in szName is a Unicode wide character string, terminated by the Unicode null terminator value:
bcp_bind(hdbc, szName, 0, SQL_VARLEN_DATA, L"",
sizeof(WCHAR), SQLNCHAR, 2)
If the bound SQL Server column is wide character, no conversion is performed on bcp_sendrow. If the SQL Server column is an MBCS character type, wide character to multibyte character conversion is performed as the data is sent to the SQL Server.
For more information about a list of supported conversions, see the ODBC 3.0 Programmer’s Reference.
The eDataType parameter is enumerated by the SQL Server data type tokens in Odbcss.h, not the ODBC C data type enumerators. For example, you can specify a two-byte integer, ODBC type SQL_C_SHORT, using the SQL Server-specific type SQLINT2.
SUCCEED or FAIL.
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.
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.
Calling bcp_columns when using bcp_bind results in an error.
...
// 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, strlen(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 and carry on.
break;
}
}
// Terminate the bulk copy operation.
if ((nRowsProcessed = bcp_done(hdbc)) == -1)
{
printf("Bulk-copy unsuccessful.\n");
return;
}
printf("%ld rows copied.\n", nRowsProcessed);
// Carry on.
...
| bcp_batch | bcp_done |
| bcp_colfmt | bcp_exec |
| bcp_collen | bcp_init |
| bcp_colptr | bcp_moretext |
| bcp_columns | bcp_sendrow |
| bcp_control | SQLColumns |