bcp_bind
Binds data from a program variable to a table column for bulk copy into Microsoft® SQL Server™.
Syntax
RETCODE bcp_bind (
HDBC hdbc,
LPCBYTE pData,
INT cbIndicator,
DBINT cbData,
LPCBYTE pTerm,
INT cbTerm,
INT eDataType,
INT idxServerCol );
Arguments
hdbc
Is the bulk copy-enabled ODBC connection handle.
pData
Is a pointer to the data copied. If eDataType is SQLTEXT, SQLNTEXT, or SQLIMAGE, pData can be NULL. A NULL pData indicates that long data values will be sent to SQL Server in chunks using bcp_moretext.
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.
cbIndicator
Is the length, in bytes, of a length or null indicator for the column's data. Valid indicator length values are 0 (when using no indicator), 1, 2, or 4.
Indicators appear in memory directly before any data. For example, the following structure type definition could be used to insert integer values into an 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.
cbData
Is the count of bytes of data in the program variable, not including the length of any length or null indicator or terminator.
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 an 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.
pTerm
Is a pointer to the byte pattern, if any, that marks the end of this program variable. For example, ANSI and MBCS C strings usually have a 1-byte terminator (\0).
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.
cbTerm
Is the count of bytes present in the terminator for the program variable, if any. If there is no terminator for the variable, set cbTerm to 0.
eDataType
Is the C data type of the program variable. The data in the program variable is 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 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.
idxServerCol
Is the ordinal position of the column in the database table to which the data is copied. The first column in a table is column 1. The ordinal position of a column is reported by SQLColumns.
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.
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.
Example
...
// 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.
...