Export (0) Print
Expand All

bcp_setbulkmode

bcp_setbulkmode lets you specify the column format in a bulk copy operation, setting all the column attributes in a single function call.

RETCODE bcp_setbulkmode ( 
   HDBC hdbc, 
   INT property, 
   void * pField, 
   INT cbField, 
   void * pRow, 
   INT cbRow 
);

hdbc

The bulk copy-enabled ODBC connection handle.

property

A constant of type BYTE. See the table in the Remarks section for a list of the constants.

pField

The pointer to the field terminator value.

cbField

The length (in bytes) of the field terminator value.

pRow

The pointer to the row terminator value.

cbRow

The length in bytes of the row terminator value.

SUCCEED or FAIL

bcp_setbulkmode can be used to bulk copy out of either a query or a table. When bcp_setbulkmode is used to bulk copy out a query statement, it must be called before calling bcp_control with BCP_HINT.

bcp_setbulkmode is an alternative to using bcp_setcolfmt and bcp_columns, which only let you specify the format of one column per function call.

The following table lists the constants for the property parameter.

Property

Description

BCP_OUT_CHARACTER_MODE

Specifies character output mode.

Corresponds to the –c option in BCP.EXE, and to bcp_setcolfmt with BCP_FMT_TYPE property set to SQLCHARACTER.

BCP_OUT_WIDE_CHARACTER_MODE

Specifies Unicode output mode.

Corresponds to the –w option in BCP.EXE and bcp_setcolfmt with BCP_FMT_TYPE property set to SQLNCHAR.

BCP_OUT_NATIVE_TEXT_MODE

Specifies native types for non-character types and Unicode for character types.

Corresponds to the –N option in BCP.EXE and bcp_setcolfmt with BCP_FMT_TYPE property set to SQLNCHAR if the column type is a string (default if not a string).

BCP_OUT_NATIVE_MODE

Specifies native database types.

Corresponds to the –n option in BCP.EXE and bcp_setcolfmt with BCP_FMT_TYPE property set to the default.

You should not use bcp_setbulkmode with a sequence of function calls that includes bcp_setcolfmt, bcp_control, and bcp_readfmt. For example, you should not call bcp_control(BCPTEXTFILE) and bcp_setbulkmode.

You can call bcp_control and bcp_setbulkmode for bcp_control options that do not conflict with bcp_setbulkmode. For example, you can call bcp_control(BCPFIRST) and bcp_setbulkmode.

If you attempt to call bcp_setbulkmode with a sequence of function calls that includes bcp_setcolfmt, bcp_control, and bcp_readfmt, one of the function calls will return a sequence error failure. If you choose to correct the failure, call bcp_init to reset all the settings and start over.

The following table presents some examples of function calls that result in a function sequence error:

Call sequence

bcp_init(“table”, DB_IN);
bcp_setbulkmode();
bcp_init(“table”, DB_OUT);
bcp_setbulkmode();
bcp_readfmt();
bcp_init(NULL, DB_OUT);
bcp_control(BCPHINTS, “select …”);
bcp_setbulkmode();
bcp_init(“table”, DB_OUT);
bcp_setbulkmode();
bcp_setcolfmt();
bcp_init(“table”, DB_OUT);
bcp_control(BCPDELAYREADFMT, true);
bcp_readfmt();
bcp_setcolfmt();
bcp_init(NULL, DB_OUT);
bcp_control(BCPDELAYREADFMT, true);
bcp_setbulkmode();
bcp_control(BCPHINTS, “select …”);
bcp_readfmt();
bcp_init(“table”, DB_OUT);
bcp_control(BCPDELAYREADFMT, true);
bcp_columns();
bcp_init(“table”, DB_OUT);
bcp_control(BCPDELAYREADFMT, true);
bcp_setcolfmt();

The following sample creates four files using different settings of bcp_setbulkmode.

// compile with: sqlncli11.lib odbc32.lib

#include <windows.h>
#include <stdio.h>
#include <tchar.h>
#include <sqlext.h>
#include "sqlncli.h"

// Global variables
SQLHENV g_hEnv = NULL;
SQLHDBC g_hDbc = NULL;

void ODBCCleanUp() {
   if (g_hDbc) {
      SQLDisconnect(g_hDbc);
      SQLFreeHandle(SQL_HANDLE_DBC, g_hDbc);
      g_hDbc = NULL;
   }
   if (g_hEnv) {
      SQLFreeHandle(SQL_HANDLE_ENV, g_hEnv);
      g_hEnv = NULL;
   }
}

BOOL MakeODBCConnection(TCHAR * pszServer) {
   TCHAR szConnectionString[500];
   TCHAR szOutConnectionString[500];
   SQLSMALLINT iLen;
   SQLRETURN rc;

   _sntprintf_s(szConnectionString, 500, TEXT("DRIVER={SQL Server Native Client 11.0};Server=%s;Trusted_connection=yes;"), pszServer);
   rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,&g_hEnv);
   if (SQL_SUCCESS != rc && SQL_SUCCESS_WITH_INFO != rc) {
      printf("SQLAllocHandle(SQL_HANDLE_ENV...) failed\n");
      return false;
   }
   rc = SQLSetEnvAttr(g_hEnv,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
   if (SQL_SUCCESS != rc && SQL_SUCCESS_WITH_INFO != rc) {
      printf("SQLSetEnvAttr failed\n");
      SQLFreeHandle(SQL_HANDLE_ENV, g_hEnv);
      return false;
   }
   rc = SQLAllocHandle( SQL_HANDLE_DBC, g_hEnv , &g_hDbc);
   if (SQL_SUCCESS != rc && SQL_SUCCESS_WITH_INFO != rc) {
      printf("SQLAllocHandle(SQL_HANDLE_DBC...) failed\n");
      SQLFreeHandle(SQL_HANDLE_ENV, g_hEnv);
      return false;
   }
   // Enable BCP
   rc = SQLSetConnectAttr(g_hDbc, SQL_COPT_SS_BCP, (SQLPOINTER)SQL_BCP_ON, SQL_IS_INTEGER);
   if (SQL_SUCCESS != rc && SQL_SUCCESS_WITH_INFO != rc) {
      printf("SQLSetConnectAttr(.. SQL_COPT_SS_BCP, (SQLPOINTER)SQL_BCP_ON ...) failed\n");
      ODBCCleanUp();
      return false;
   }
   // connecting ...
   rc = SQLDriverConnect(g_hDbc,NULL, (SQLTCHAR*)szConnectionString, SQL_NTS, (SQLTCHAR*)szOutConnectionString, 500, &iLen, SQL_DRIVER_NOPROMPT);
   if (SQL_SUCCESS != rc && SQL_SUCCESS_WITH_INFO != rc) {
      printf("SQLDriverConnect(SQL_HANDLE_DBC...) failed\n");
      ODBCCleanUp();
      return false;
   }
   return true;
}

BOOL BCPSetBulkMode(TCHAR * pszServer, TCHAR * pszQureryOut, char BCPType, TCHAR * pszDataFile) {
   SQLRETURN rc;

   if (!MakeODBCConnection(pszServer))
      return false;
   rc = bcp_init(g_hDbc, NULL, pszDataFile, NULL, DB_OUT);   // bcp init for queryout
   if (SUCCEED != rc) {
      printf("bcp_init failed\n");
      ODBCCleanUp();
      return false;
   }
   // setbulkmode
   char ColTerm[] = "\t";
   char RowTerm[] = "\r\n";
   wchar_t wColTerm[] = L"\t";
   wchar_t wRowTerm[] = L"\r\n";
   BYTE * pColTerm = NULL;
   int cbColTerm = NULL;
   BYTE * pRowTerm = 0;
   int cbRowTerm = 0;
   int bulkmode = -1;

   if (BCPType == 'c') {   // bcp -c
      pColTerm = (BYTE*)ColTerm;
      pRowTerm = (BYTE*)RowTerm;
      cbColTerm = 1;
      cbRowTerm = 2;
      bulkmode = BCP_OUT_CHARACTER_MODE;
   }
   else
      if (BCPType == 'w') {   // bcp -w 
         pColTerm = (BYTE*)wColTerm;
         pRowTerm = (BYTE*)wRowTerm;
         cbColTerm = 2;
         cbRowTerm = 4;
         bulkmode = BCP_OUT_WIDE_CHARACTER_MODE;
      }
      else
         if (BCPType == 'n')   // bcp -n
            bulkmode = BCP_OUT_NATIVE_MODE;
         else
            if (BCPType == 'N')   // bcp -n
               bulkmode = BCP_OUT_NATIVE_TEXT_MODE;
            else {
               printf("unknown bcp mode\n");
               ODBCCleanUp();
               return false;
            }
            rc = bcp_setbulkmode(g_hDbc, bulkmode, pColTerm, cbColTerm, pRowTerm, cbRowTerm);
            if (SUCCEED != rc) {
               printf("bcp_setbulkmode failed\n");
               ODBCCleanUp();
               return false;
            }
            // set queryout TSQL statement
            rc = bcp_control(g_hDbc, BCPHINTS , pszQureryOut);
            if (SUCCEED != rc) {
               printf("bcp_control(..BCP_OPTION_HINTS..) failed\n");
               ODBCCleanUp();
               return false;
            }
            // bcp copy
            DBINT nRowsInserted = 0;
            rc = bcp_exec(g_hDbc, &nRowsInserted);
            if (SUCCEED != rc) {
               printf("bcp_exec failed\n");
               ODBCCleanUp();
               return false;
            }
            printf("bcp done\n");
            ODBCCleanUp();
            return true;
}

int main() {
   BCPSetBulkMode(TEXT("localhost"), TEXT("SELECT 'this is a bcp -c test', 1,2") , 'c', TEXT("bcpc.dat"));
   BCPSetBulkMode(TEXT("localhost"), TEXT("SELECT 'this is a bcp -w test', 1,2") , 'w', TEXT("bcpw.dat"));
   BCPSetBulkMode(TEXT("localhost"), TEXT("SELECT 'this is a bcp -c test', 1,2") , 'n', TEXT("bcpn.dat"));
   BCPSetBulkMode(TEXT("localhost"), TEXT("SELECT 'this is a bcp -w test', 1,2") , 'N', TEXT("bcp_N.dat"));
}
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft