How to bulk copy data from program variables (ODBC)
To use bulk copy functions directly on program variables
- Allocate an environment handle and a connection handle.
- Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.
- Connect to Microsoft® SQL Server™.
- Call bcp_init to set the following information:
- The name of the table or view to bulk copy from or to.
- Specify NULL for the name of the data file.
- The name of an data file to receive any bulk copy error messages (specify NULL if you do not want a message file).
- The direction of the copy: DB_IN from the application to the view or table or DB_OUT to the application from the table or view.
- The name of the table or view to bulk copy from or to.
- Call bcp_bind for each column in the bulk copy to bind the column to a program variable.
- Fill the program variables with data, and call bcp_sendrow to send a row of data.
- After several rows have been sent, call bcp_batch to checkpoint the rows already sent. It is good practice to call bcp_batch at least once per 1000 rows.
- After all rows have been sent, call bcp_done to complete the operation.
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.
Examples
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 is 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);
// Notify ODBC that 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
// 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 is 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);
}