IBCPSession2::BCPSetBulkMode
IBCPSession2::BCPSetBulkMode provides an alternative to IBCPSession::BCPColFmt (OLE DB) for specifying the column format. Unlike IBCPSession::BCPColFmt, which sets individual column format attributes, IBCPSession2::BCPSetBulkMode sets all attributes.
Syntax
HRESULT BCPSetBulkMode (
int property,
void * pField,
int cbField,
void * pRow,
int cbRow
);
Arguments
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.
Returns
IBCPSession2::BCPSetBulkMode can return one of the following:
S_OK |
The method succeeded. |
E_FAIL |
A provider specific error occurred, for detailed information use the ISQLServerErrorInfo interface. |
E_UNEXPECTED |
The call to the method was unexpected. For example, the IBCPSession2::BCPInit method was not called before calling IBCPSession2::BCPSetBulkMode. |
E_INVALIDARG |
The argument was invalid. |
E_OUTOFMEMORY |
Out of memory error. |
Remarks
IBCPSession2::BCPSetBulkMode can be used to bulk copy out of either a query or a table. When IBCPSession2::BCPSetBulkMode is used to bulk copy out of a query statement, it must be called before calling IBCPSession::BCPControl(BCP_OPTIONS_HINTS, …) to specify the query statement.
You should avoid combining RPC call syntax with batch query syntax ({rpc func};SELECT * from Tbl, for example) in a single command text. This will cause ICommandPrepare::Prepare to return an error and prevent you from retrieving metadata. Use ODBC CALL syntax ({call func}; SELECT * from Tbl, for example) if you need to combine stored procedure execution and batch query in a single command text.
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 IBCPSession::BCPColFmt with eUserDataType property set to BCP_TYPE_SQLCHARACTER. |
BCP_OUT_WIDE_CHARACTER_MODE |
Specifies Unicode output mode. Corresponds to the –w option in BCP.EXE and IBCPSession::BCPColFmt with eUserDataType property set to BCP_TYPE_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 IBCPSession::BCPColFmt with eUserDataType property set to BCP_TYPE_SQLNCHAR if the column type is a string or BCP_TYPE_DEFAULT if not a string. |
BCP_OUT_NATIVE_MODE |
Specifies native database types. Corresponds to the –n option in BCP.EXE and IBCPSession::BCPColFmt with eUserDataType property set to BCP_TYPE_DEFAULT. |
You can call IBCPSession::BCPControl and IBCPSession2::BCPSetBulkMode for IBCPSession::BCPControl options that do not conflict with IBCPSession2::BCPSetBulkMode. For example, you can call IBCPSession::BCPControl with BCP_OPTION_FIRST and IBCPSession2::BCPSetBulkMode.
You cannot call IBCPSession::BCPControl with BCP_OPTION_TEXTFILE and IBCPSession2::BCPSetBulkMode.
If you attempt to call IBCPSession2::BCPSetBulkMode with a sequence of function calls that includes IBCPSession::BCPColFmt, IBCPSession::BCPControl, and IBCPSession::BCPReadFmt, one of the function calls will return a sequence error failure. If you choose to correct the failure, call IBCPSession::BCPInit to reset the settings and start over.
The following table presents some examples of function calls that result in a function sequence error:
Call sequence |
|---|
|
|
|
|
|
|
|
|
Example
The following sample creates four files using different settings of IBCPSession2::BCPSetBulkMode.
// compile with: sqlncli11.lib oleaut32.lib ole32.lib
#include <stdio.h>
#include "sqlncli.h"
IDBInitialize* g_pIDBInitialize = NULL;
IBCPSession2 * g_pIBcpSession = NULL;
class COLEDBPropSet : public DBPROPSET {
public:
COLEDBPropSet() {
rgProperties = NULL;
cProperties = 0;
};
COLEDBPropSet(const GUID& guid) {
rgProperties = NULL;
cProperties = 0;
guidPropertySet = guid;
};
~COLEDBPropSet() {
for ( ULONG i = 0 ; i < cProperties ; i++ )
VariantClear(&rgProperties[i].vValue);
CoTaskMemFree(rgProperties);
}
void SetGUID(const GUID& guid) {
guidPropertySet = guid;
};
bool AddProperty(DWORD dwPropertyID, bool bValue) {
if (!Add())
return false;
rgProperties[cProperties].dwPropertyID = dwPropertyID;
rgProperties[cProperties].vValue.vt = VT_BOOL;
rgProperties[cProperties].vValue.boolVal = (bValue) ? VARIANT_TRUE : VARIANT_FALSE;
cProperties++;
return true;
};
bool AddProperty(DWORD dwPropertyID, long nValue) {
if (!Add())
return false;
rgProperties[cProperties].dwPropertyID = dwPropertyID;
rgProperties[cProperties].vValue.vt = VT_I4;
rgProperties[cProperties].vValue.lVal = nValue;
cProperties++;
return true;
};
bool AddProperty(DWORD dwPropertyID,LPCWSTR szValue) {
if (!Add())
return false;
rgProperties[cProperties].dwPropertyID = dwPropertyID;
rgProperties[cProperties].vValue.vt = VT_BSTR;
rgProperties[cProperties].vValue.bstrVal = SysAllocString(szValue);
cProperties++;
return true;
};
bool Add() {
DBPROP* p = (DBPROP*)CoTaskMemRealloc(rgProperties, (cProperties + 1) * sizeof(DBPROP));
if (p != NULL) {
rgProperties = p;
rgProperties[cProperties].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[cProperties].colid = DB_NULLID;
rgProperties[cProperties].vValue.vt = VT_EMPTY;
return true;
}
else
return false;
};
};
void OLEDBCleanUp() {
if (g_pIDBInitialize) {
g_pIDBInitialize->Release();
g_pIDBInitialize = NULL;
}
if (g_pIBcpSession) {
g_pIBcpSession->Release();
g_pIBcpSession = NULL;
}
}
BOOL MakeOLEDBConnect(LPWSTR pServer) {
BOOL ret = true;
IDBProperties * pIDBProperties = NULL;
IDBCreateSession * pIDBCreateSession = NULL;
COLEDBPropSet PropSet(DBPROPSET_DBINIT);
COLEDBPropSet BcpProperty(DBPROPSET_SQLSERVERDATASOURCE);
try {
HRESULT hr = CoInitializeEx(NULL,COINIT_MULTITHREADED);
hr = CoCreateInstance(SQLNCLI_CLSID, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (LPVOID *)&g_pIDBInitialize);
if (FAILED(hr)) {
printf("CoCreateInstance failed\n");
return false;
}
PropSet.AddProperty(DBPROP_INIT_DATASOURCE, (LPWSTR)pServer);
PropSet.AddProperty(DBPROP_AUTH_INTEGRATED, L"SSPI");
hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties, (void**) &pIDBProperties);
if (FAILED(hr)) {
printf("g_pIDBInitialize->->QueryInterface(IID_IDBProperties...) failed\n");
throw false;
}
hr = pIDBProperties->SetProperties(1, &PropSet);
if (FAILED(hr)) {
printf("g_pIDBInitialize->->SetProperties(...) failed\n");
throw false;
}
hr = g_pIDBInitialize->Initialize();
if (FAILED(hr)) {
printf("g_pIDBInitialize->->Initialize() failed\n");
throw false;
}
BcpProperty.AddProperty(SSPROP_ENABLEFASTLOAD, true);
BcpProperty.AddProperty(SSPROP_ENABLEBULKCOPY, true);
hr = pIDBProperties->SetProperties(1, &BcpProperty);
if (FAILED(hr)) {
printf("g_pIDBInitialize->->SetProperties() for bcp failed\n");
throw false;
}
hr = g_pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**) &pIDBCreateSession);
if (FAILED(hr)) {
printf("g_pIDBInitialize->QueryInterface(IID_IDBCreateSession..) failed\n");
throw false;
}
hr = pIDBCreateSession->CreateSession(NULL, IID_IBCPSession2, (IUnknown**) &g_pIBcpSession);
if (FAILED(hr)) {
printf("g_pIDBCreateSession->CreateSession() failed\n");
throw false;
}
}
catch(...) {
ret = false;
}
if (pIDBProperties)
pIDBProperties->Release();
if (pIDBCreateSession)
pIDBCreateSession->Release();
return ret;
}
BOOL BCPSetBulkMode(LPWSTR pszServer, LPTSTR pszQureryOut, char BCPType, LPWSTR pszDataFile) {
HRESULThr;
if (!MakeOLEDBConnect(pszServer))
return false;
hr = g_pIBcpSession->BCPInit(NULL, pszDataFile, NULL, BCP_DIRECTION_OUT ); // bcp init for queryout
if (FAILED(hr)) {
printf("BCP init failed\n");
OLEDBCleanUp();
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");
OLEDBCleanUp();
return false;
}
hr = g_pIBcpSession->BCPSetBulkMode(bulkmode, pColTerm, cbColTerm, pRowTerm, cbRowTerm);
if (FAILED(hr)) {
printf("BCPSetBulkMode failed\n");
OLEDBCleanUp();
return false;
}
// set queryout TSQL statement
hr = g_pIBcpSession->BCPControl(BCP_OPTION_HINTS, pszQureryOut);
if (FAILED(hr)) {
printf("BCPControl failed\n");
OLEDBCleanUp();
return false;
}
// bcp copy
DBROWCOUNT nRowsInserted = 0;
hr = g_pIBcpSession->BCPExec(&nRowsInserted);
if (FAILED(hr)) {
printf("BCPExec failed\n");
OLEDBCleanUp();
return false;
}
printf("bcp done\n");
OLEDBCleanUp();
return true;
}
int main() {
BCPSetBulkMode(L"localhost", TEXT("SELECT 'this is a bcp -c test', 1,2") , 'c', L"bcpc.dat");
BCPSetBulkMode(L"localhost", TEXT("SELECT 'this is a bcp -w test', 1,2") , 'w', L"bcpw.dat");
BCPSetBulkMode(L"localhost", TEXT("SELECT 'this is a bcp -c test', 1,2") , 'n', L"bcpn.dat");
BCPSetBulkMode(L"localhost", TEXT("SELECT 'this is a bcp -w test', 1,2") , 'N', L"bcp_N.dat");
}