Bulk Copy Data from Program Variables (ODBC)
This sample shows how to use 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.)
This sample was developed for ODBC version 3.0 or later.
Security Note When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.
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 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.
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.
Example
This sample is not supported on IA64.
You will need an ODBC data source called AdventureWorks, whose default database is the AdventureWorks sample database. (You can download the AdventureWorks sample database from the Microsoft SQL Server Samples and Community Projects home page.) This data source must be based on the ODBC driver that is supplied by the operating system (the driver name is "SQL Server"). If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe.
This sample connects to your computer's default SQL Server instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, SQL Server Express installs to a named instance.
Execute the first (Transact-SQL) code listing to create tables that the sample will use.
Compile the second (C++) code listing with odbc32.lib and odbcbcp.lib. If you built with MSBuild.exe, copy Bcpfmt.fmt and Bcpodbc.bcp from the project directory into the directory with the .exe and then invoke the .exe.
Execute the third (Transact-SQL) code listing to delete the tables that the sample used.
// compile with: odbc32.lib odbcbcp.lib
use AdventureWorks
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPSource')
DROP TABLE BCPSource
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPTarget')
DROP TABLE BCPTarget
GO
CREATE TABLE BCPSource (cola int PRIMARY KEY, colb CHAR(10) NULL)
INSERT INTO BCPSource (cola, colb) VALUES (1, 'aaa')
INSERT INTO BCPSource (cola, colb) VALUES (2, 'bbb')
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;
void Cleanup() {
if (hstmt2 != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
if (hdbc1 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
}
if (hdbc2 != SQL_NULL_HDBC) {
SQLDisconnect(hdbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
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 = 0;
// 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 {
int cbCola;
SQLINTEGER Cola;
} ColaInst;
struct ColbData {
int cbColb;
SQLCHAR szColb[11];
} ColbInst;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
Cleanup();
return(9);
}
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
Cleanup();
return(9);
}
// Allocate ODBC connection handle, set bulk copy mode, and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
// sample uses Integrated Security, create the SQL Server DSN using Windows NT authentication
retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"", SQL_NTS, (UCHAR*)"", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLConnect() Failed\n\n");
Cleanup();
return(9);
}
// Initialize the bulk copy.
retcode = bcp_init(hdbc1, "AdventureWorks..BCPTarget", NULL, NULL, DB_IN);
if ( (retcode != SUCCEED) ) {
printf("bcp_init(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
// Bind the program variables for the bulk copy.
retcode = bcp_bind(hdbc1, (BYTE *)&ColaInst.cbCola, 4, SQL_VARLEN_DATA, NULL, (INT)NULL, SQLINT4, 1);
if ( (retcode != SUCCEED) ) {
printf("bcp_bind(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
// 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, (UCHAR*)terminator, 1, SQLCHARACTER, 2);
if ( (retcode != SUCCEED) ) {
printf("bcp_bind(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
// Allocate second ODBC connection handle so bulk copy and cursor operations do not conflict.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc2);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc2) Failed\n\n");
Cleanup();
return(9);
}
// Sample uses Integrated Security, create SQL Server DSN using Windows NT authentication.
retcode = SQLConnect(hdbc2, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"", SQL_NTS, (UCHAR*)"", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLConnect() Failed\n\n");
Cleanup();
return(9);
}
// Allocate ODBC statement handle.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, &hstmt2);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hstmt2) Failed\n\n");
Cleanup();
return(9);
}
SQLLEN lDataLengthA;
SQLLEN lDataLengthB;
// 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, &lDataLengthA);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLBindCol(hstmt2) Failed\n\n");
Cleanup();
return(9);
}
retcode = SQLBindCol(hstmt2, 2, SQL_C_CHAR, &ColbInst.szColb, 11, &lDataLengthB);
if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
printf("SQLBindCol(hstmt2) Failed\n\n");
Cleanup();
return(9);
}
// Execute SELECT statement to build a cursor containing data to be bulk copied to new table.
retcode = SQLExecDirect(hstmt2, (UCHAR*)"SELECT * FROM BCPSource", SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
printf("SQLExecDirect Failed\n\n");
Cleanup();
return(9);
}
// 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) ) {
printf("SQLFetch Failed\n\n");
Cleanup();
return(9);
}
ColaInst.cbCola = (int)lDataLengthA;
ColbInst.cbColb = (int)lDataLengthB;
if ( (SendRet = bcp_sendrow(hdbc1) ) != SUCCEED ) {
printf("bcp_sendrow(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
}
// Signal the end of the bulk copy operation.
cRowsDone = bcp_done(hdbc1);
if ( (cRowsDone == -1) ) {
printf("bcp_done(hdbc1) Failed\n\n");
Cleanup();
return(9);
}
printf("Number of rows bulk copied after last bcp_batch call = %d.\n", cRowsDone);
// Cleanup.
SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLDisconnect(hdbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
use AdventureWorks
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPSource')
DROP TABLE BCPSource
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPTarget')
DROP TABLE BCPTarget
GO
See Also
Bulk Copying with the SQL Server ODBC Driver How-to Topics (ODBC)
Bulk Copying from Program Variables