To use bulk copy functions directly on program variables
You can vary the location and length of program variables during a bulk copy operation by calling bcp_colptr and bcp_collen.
Use bcp_control to set various bulk copy options. Use bcp_moretext to send text, ntext, and image data in segments to the server.
The following example shows using bulk copy functions to bulk copy data from program variables to SQL Server using bcp_bind and bcp_sendrow. Error-checking code was removed to simplify this example.
// Sample showing ODBC bulk copy from program variables
// bound with bcp_bind; data sent with bcp_sendrow.
//
// Assumes server has:
//
// CREATE TABLE BCPSource (cola int PRIMARY KEY,
// colb CHAR(10) NULL)
// CREATE TABLE BCPTarget (cola int PRIMARY KEY,
// colb CHAR(10) NULL)
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC, hdbc2 = SQL_NULL_HDBC;
SQLHSTMT hstmt2 = SQL_NULL_HSTMT;
int main() {
RETCODE retcode;
// BCP variables.
char *terminator = "\0";
// bcp_done takes a different format return code
// because it returns number of rows bulk copied
// after the last bcp_batch call.
DBINT cRowsDone;
// Set up separate return code for bcp_sendrow so
// it is not using the same retcode as SQLFetch.
RETCODE SendRet;
// Column variables.
// cbCola and cbColb must be defined right before
// Cola and szColb because they are used as
// bulk copy indicator variables.
struct ColaData{
SQLINTEGER cbCola;
SQLINTEGER Cola;
} ColaInst;
struct ColbData{
SQLINTEGER cbColb;
SQLCHAR szColb[11];
} ColbInst;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
// Let ODBC know this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
// Allocate ODBC connection handle, set bulk copy mode, and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP,
(void *)SQL_BCP_ON, SQL_IS_INTEGER);
retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,
"sa", SQL_NTS, "MyPassWord", SQL_NTS);
// Initialize the bulk copy.
retcode = bcp_init(hdbc1, "pubs..BCPTarget", NULL,
NULL, DB_IN);
// Bind the program variables for the bulk copy.
retcode = bcp_bind(hdbc1, (BYTE *)&ColaInst.cbCola, 4,
SQL_VARLEN_DATA, NULL, (INT)NULL,
SQLINT4, 1);
// Could normally use strlen to calculate the bcp_bind
// cbTerm parameter, but this terminator is a null byte
// (\0), which gives strlen a value of 0. Explicitly give
// cbTerm a value of 1.
retcode = bcp_bind(hdbc1, (BYTE *)&ColbInst.cbColb, 4, 11,
terminator, 1, SQLCHARACTER, 2);
// Allocate second ODBC connection handle so that bulk copy
// and cursor operations do not conflict.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc2);
retcode = SQLConnect(hdbc2, "MyDSN", SQL_NTS,
"sa", SQL_NTS, "MyPassWord", SQL_NTS);
// Allocate ODBC statement handle.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, &hstmt2);
// Bind the SELECT statement to the same program variables
// that were bound to the bulk copy operation.
retcode = SQLBindCol(hstmt2, 1, SQL_C_SLONG, &ColaInst.Cola, 0,
&ColaInst.cbCola);
retcode = SQLBindCol(hstmt2, 2, SQL_C_CHAR, &ColbInst.szColb, 11,
&ColbInst.cbColb);
// Execute a SELECT statement to build a cursor containing
// the data to be bulk copied to the new table.
retcode = SQLExecDirect(hstmt2,
"SELECT * FROM BCPSource",
SQL_NTS);
// Go into a loop fetching rows from the cursor until
// each row has been fetched. Because the bcp_bind calls
// and SQLBindCol calls each reference the same
// variables, each fetch fills the variables used by
// bcp_sendrow, so all you have to do to send the data
// to SQL Server is to call bcp_sendrow.
while ( (retcode = SQLFetch(hstmt2) ) != SQL_NO_DATA) {
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
// Process error.
return(9);
}
if ( (SendRet = bcp_sendrow(hdbc1) ) != SUCCEED ) {
// Process error.
return(9);
}
}
// Signal the end of the bulk copy operation.
cRowsDone = bcp_done(hdbc1);
printf("Number of rows bulk copied after last bcp_batch
call = %d.\n", cRowsDone);
/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLDisconnect(hdbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);
}
bcp_batch | bcp_init |
bcp_bind | bcp_sendrow |
bcp_done | Bulk Copying from Program Variables |