1. 概述
使用c++访问数据库的方法很多,由于我访问的是sqlserver数据库,于是上MSDN查了一下有哪些访问技术,主要有以下几种:
- ODBC
- OLEDB
- ADO
ADO是OLEDB的封装,使用起来比OLEDB方便。由于ADO比OLEDB多了一层,其速度可能不及OLEDB,所以就不考虑ADO了。
ODBC访问sqlserver有一个好处,可以在linux上使用。linux上可以使用FreeTDS作为sqlserver的ODBC驱动。OLEDB则只能在windows上运行。
1.1 ODBC(Open Database Connectivity)
ODBC是微软弄出来的一个可以访问各种数据库的接口。ODBC存在时间比较长,是一个比较稳定的接口。它既然能够访问各种接口,所以它是一个最小公共集的接口。这个接口里面可能会缺少某些数据库的特定功能。
ODBC只是一个接口,想要使用ODBC必须提供驱动。 sqlserver 提供SQL Server Native Client ODBC driver来支持ODBC接口。
1.2 OLEDB
OLEDB是一组基于COM的接口。OLEDB提供统一的接口,访问各种形式的数据。说到OLEDB就必须提交两个概念:
consumer(使用接口的程序)、provider(接口的实现者)。OLEDB是定义一组接口,每个数据库供应商如果要是自己的数据库能通过OLEDB访问,就必须提供OLEDB Provider。
sqlserver的的provider是The SQL Server Native Client OLE DB provider。
2. 使用ODBC插入数据
sqlserver使用ODBC的MSDN地址:
https://msdn.microsoft.com/en-us/library/ms131415(v=sql.105).aspx
ODBC插入数据速度快捷的方法主要有两种:
1. SQLBulkOperations
#include <windows.h>
#include <sqlext.h>
#include <stdio.h>
#include <time.h>
SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHSTMT hstmt = NULL;
SQLRETURN retcode;
#define COUNT (100000)
#define ROW_ARRAY_SIZE 1000
typedef struct{
SQLINTEGER rec_num;
SQLINTEGER rec_numInd;
SQLCHAR date[9];
SQLINTEGER dateInd;
SQLCHAR time[9];
SQLINTEGER timeInd;
SQLCHAR reff[11];
SQLINTEGER reffInd;
SQLCHAR acc[11];
SQLINTEGER accInd;
SQLCHAR stock[7];
SQLINTEGER stockInd;
SQLCHAR bs[2];
SQLINTEGER bsInd;
SQLCHAR price[9];
SQLINTEGER priceInd;
SQLCHAR qty[9];
SQLINTEGER qtyInd;
SQLCHAR status[2];
SQLINTEGER statusInd;
SQLCHAR owflag[4];
SQLINTEGER owflagInd;
SQLCHAR ordrec[9];
SQLINTEGER ordrecInd;
SQLCHAR firmid[6];
SQLINTEGER firmidInd;
SQLCHAR branchid[6];
SQLINTEGER branchidInd;
SQLSCHAR checkord[16];
SQLINTEGER checkordInd;
} ORDWTH;
typedef struct{
SQLINTEGER id;
SQLCHAR date[20];
SQLCHAR abbr[10];
//
SQLINTEGER idInd;
SQLINTEGER dateInd;
SQLINTEGER abbrInd;
} Test;
Test test_array[ROW_ARRAY_SIZE];
ORDWTH ordwth_array[ROW_ARRAY_SIZE];
int rec_num = 1;
void main()
{
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
retcode = SQLConnect(hdbc, (SQLCHAR*) "ctp2_lx", SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123456", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
}
else
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, state, &error_id, msg, 128, &text);
printf("db connect fail, sqlstate=%s, errormsg=%s\n", state, msg);
system("pause");
return;
}
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
printf("Inserting...\n");
time_t begin;
time(&begin);
//设定SQL_ATTR_ROW_ARRAY_SIZE属性,bulk的长度
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), 0);
SQLUSMALLINT ParamStatusArray[ROW_ARRAY_SIZE] = { 0 };
//设定状态数组
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ParamStatusArray, 0);
SQLINTEGER nBindOffset = 0;
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, (SQLPOINTER)&nBindOffset, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
//进行一次查询,得到result set
retcode = SQLExecDirect(hstmt, (SQLCHAR*)"select rec_num, date, time, reff, acc, stock, bs, price, qty, status, owflag, ordrec, firmid, branchid, checkord from ashare_ordwth", SQL_NTS);
//retcode = SQLExecDirect(hstmt, (SQLCHAR*)"select id, date, abbr from test", SQL_NTS);
retcode = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
/*SQLBindCol(hstmt, 1, SQL_C_SHORT, &test_array[0].id, 0, &test_array[0].idInd);
SQLBindCol(hstmt, 2, SQL_C_CHAR, &test_array[0].date, 20, &test_array[0].dateInd);
SQLBindCol(hstmt, 3, SQL_C_CHAR, &test_array[0].abbr, 10, &test_array[0].abbrInd);*/
retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &ordwth_array[0].rec_num, 0, &ordwth_array[0].rec_numInd);
SQLBindCol(hstmt, 2, SQL_C_CHAR, &ordwth_array[0].date, sizeof(ordwth_array[0].date), &ordwth_array[0].dateInd);
SQLBindCol(hstmt, 3, SQL_C_CHAR, &ordwth_array[0].time, sizeof(ordwth_array[0].time), &ordwth_array[0].timeInd);
SQLBindCol(hstmt, 4, SQL_C_CHAR, &ordwth_array[0].reff, sizeof(ordwth_array[0].reff), &ordwth_array[0].reffInd);
SQLBindCol(hstmt, 5, SQL_C_CHAR, &ordwth_array[0].acc, sizeof(ordwth_array[0].acc), &ordwth_array[0].accInd);
SQLBindCol(hstmt, 6, SQL_C_CHAR, &ordwth_array[0].stock, sizeof(ordwth_array[0].stock), &ordwth_array[0].stockInd);
SQLBindCol(hstmt, 7, SQL_C_CHAR, &ordwth_array[0].bs, sizeof(ordwth_array[0].bs), &ordwth_array[0].bsInd);
SQLBindCol(hstmt, 8, SQL_C_CHAR, &ordwth_array[0].price, sizeof(ordwth_array[0].price), &ordwth_array[0].priceInd);
SQLBindCol(hstmt, 9, SQL_C_CHAR, &ordwth_array[0].qty, sizeof(ordwth_array[0].qty), &ordwth_array[0].qtyInd);
SQLBindCol(hstmt, 10, SQL_C_CHAR, &ordwth_array[0].status, sizeof(ordwth_array[0].status), &ordwth_array[0].statusInd);
SQLBindCol(hstmt, 11, SQL_C_CHAR, &ordwth_array[0].owflag, sizeof(ordwth_array[0].owflag), &ordwth_array[0].owflagInd);
SQLBindCol(hstmt, 12, SQL_C_CHAR, &ordwth_array[0].ordrec, sizeof(ordwth_array[0].ordrec), &ordwth_array[0].ordrecInd);
SQLBindCol(hstmt, 13, SQL_C_CHAR, &ordwth_array[0].firmid, sizeof(ordwth_array[0].firmid), &ordwth_array[0].firmidInd);
SQLBindCol(hstmt, 14, SQL_C_CHAR, &ordwth_array[0].branchid, sizeof(ordwth_array[0].branchid), &ordwth_array[0].branchidInd);
SQLBindCol(hstmt, 15, SQL_C_BINARY, &ordwth_array[0].checkord, sizeof(ordwth_array[0].checkord), &ordwth_array[0].checkordInd);
//关闭auto commit
SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
for (int j = 0; j < COUNT / ROW_ARRAY_SIZE; j++)
{
for (int i = 0; i < ROW_ARRAY_SIZE; i++)
{
ordwth_array[i].rec_num = rec_num++;
ordwth_array[i].ordrecInd = 0;
strcpy((char*)ordwth_array[i].date, "20150120");
ordwth_array[i].dateInd = SQL_NTS;
strcpy((char*)ordwth_array[i].time, "13:20:10");
ordwth_array[i].timeInd = SQL_NTS;
strcpy((char*)ordwth_array[i].reff, "1234567890");
ordwth_array[i].reffInd = SQL_NTS;
strcpy((char*)ordwth_array[i].acc, "0000011111");
ordwth_array[i].accInd = SQL_NTS;
strcpy((char*)ordwth_array[i].stock, "123456");
ordwth_array[i].stockInd = SQL_NTS;
strcpy((char*)ordwth_array[i].bs, "B");
ordwth_array[i].bsInd = SQL_NTS;
strcpy((char*)ordwth_array[i].price, "1.000");
ordwth_array[i].priceInd = SQL_NTS;
strcpy((char*)ordwth_array[i].qty, "1000");
ordwth_array[i].qtyInd = SQL_NTS;
strcpy((char*)ordwth_array[i].status, "R");
ordwth_array[i].statusInd = SQL_NTS;
strcpy((char*)ordwth_array[i].owflag, "ORD");
ordwth_array[i].owflagInd = SQL_NTS;
strcpy((char*)ordwth_array[i].ordrec, "1");
ordwth_array[i].ordrecInd = SQL_NTS;
strcpy((char*)ordwth_array[i].firmid, "123");
ordwth_array[i].firmidInd = SQL_NTS;
strcpy((char*)ordwth_array[i].branchid, "20201");
ordwth_array[i].branchidInd = SQL_NTS;
::memset(ordwth_array[i].checkord, 0, sizeof(ordwth_array[i].checkord));
ordwth_array[i].checkordInd = sizeof(ordwth_array[i].checkord);
}
//for (int i = 0; i < ROW_ARRAY_SIZE; i++)
//{
// test_array[i].id = rec_num++;
// strcpy((char*)test_array[i].date, "20150120");
// strcpy((char*)test_array[i].abbr, "liuxing");
// test_array[i].idInd = 0;
// test_array[i].dateInd = SQL_NTS;
// test_array[i].abbrInd = SQL_NTS;
//}
retcode = SQLBulkOperations(hstmt, SQL_ADD);
if (retcode == SQL_ERROR)
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);
printf("db Insert fail, sqlstate=%s, errormsg=%s\n", state, msg);
SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
system("pause");
return;
}
else if (retcode == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);
printf("warning msg=%s\n", msg);
}
retcode = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
}
time_t end;
time(&end);
printf("Insert %d records in %lld seconds, average insert speed: %lld\n", COUNT, end - begin, COUNT / (end - begin));
//printf("Insert %d records in %lld seconds\n", COUNT, end - begin);
system("pause");
return;
}
使用这种方法,每秒钟只能查6000条记录。
2. 参数批量插入
#include <windows.h>
#include <sqlext.h>
#include <stdio.h>
#include <time.h>
SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHSTMT hstmt = NULL;
SQLRETURN retcode;
#define COUNT (100000)
#define ROW_ARRAY_SIZE 1000
typedef struct{
SQLINTEGER rec_num;
SQLINTEGER rec_numInd;
SQLCHAR date[9];
SQLINTEGER dateInd;
SQLCHAR time[9];
SQLINTEGER timeInd;
SQLCHAR reff[11];
SQLINTEGER reffInd;
SQLCHAR acc[11];
SQLINTEGER accInd;
SQLCHAR stock[7];
SQLINTEGER stockInd;
SQLCHAR bs[2];
SQLINTEGER bsInd;
SQLCHAR price[9];
SQLINTEGER priceInd;
SQLCHAR qty[9];
SQLINTEGER qtyInd;
SQLCHAR status[2];
SQLINTEGER statusInd;
SQLCHAR owflag[4];
SQLINTEGER owflagInd;
SQLCHAR ordrec[9];
SQLINTEGER ordrecInd;
SQLCHAR firmid[6];
SQLINTEGER firmidInd;
SQLCHAR branchid[6];
SQLINTEGER branchidInd;
SQLSCHAR checkord[16];
SQLINTEGER checkordInd;
} ORDWTH;
typedef struct{
SQLINTEGER id;
SQLCHAR date[20];
SQLCHAR abbr[10];
//
SQLINTEGER idInd;
SQLINTEGER dateInd;
SQLLEN abbrInd;
} Test;
Test test_array[ROW_ARRAY_SIZE];
ORDWTH ordwth_array[ROW_ARRAY_SIZE];
int rec_num = 1;
void main()
{
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
retcode = SQLConnect(hdbc, (SQLCHAR*) "ctp2_lx", SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123456", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
}
else
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, state, &error_id, msg, 128, &text);
printf("db connect fail, sqlstate=%s, errormsg=%s\n", state, msg);
system("pause");
return;
}
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
printf("Inserting...\n");
time_t begin;
time(&begin);
//设定SQL_ATTR_ROW_ARRAY_SIZE属性,bulk的长度
//SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), 0);
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), SQL_IS_INTEGER);
//设定每次参数的数量
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)(long)ROW_ARRAY_SIZE, SQL_IS_INTEGER);
SQLUSMALLINT ParamStatusArray[ROW_ARRAY_SIZE] = { 0 };
//设定状态数组
retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ParamStatusArray, 0);
SQLINTEGER nBindOffset = 0;
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, (SQLPOINTER)&nBindOffset, 0);
//retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0);
//retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
SQLCHAR *sql = (SQLCHAR*)"Insert into ashare_ordwth(rec_num, date, time, reff, acc, stock, bs, price, qty, status, owflag, ordrec, firmid, branchid, checkord) Values(?, ?, ?, ?, ?,?,?, ?, ?, ?, ?, ?, ?, ?, ?)";
//SQLCHAR *sql = (SQLCHAR*)"Insert into ashare_ordwth(rec_num) Values(?)";
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, sizeof(ordwth_array[0].rec_num), 0, &ordwth_array[0].rec_num, sizeof(ordwth_array[0].rec_num), &ordwth_array[0].rec_numInd);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].date) - 1, 0, &ordwth_array[0].date, sizeof(ordwth_array[0].date), &ordwth_array[0].dateInd);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].time) - 1, 0, &ordwth_array[0].time, sizeof(ordwth_array[0].time), &ordwth_array[0].timeInd);
SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].reff) - 1, 0, &ordwth_array[0].reff, sizeof(ordwth_array[0].reff), &ordwth_array[0].reffInd);
SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].acc) - 1, 0, &ordwth_array[0].acc, sizeof(ordwth_array[0].acc), &ordwth_array[0].accInd);
SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].stock) - 1, 0, &ordwth_array[0].stock, sizeof(ordwth_array[0].stock), &ordwth_array[0].stockInd);
SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].bs) - 1, 0, &ordwth_array[0].bs, sizeof(ordwth_array[0].bs), &ordwth_array[0].bsInd);
SQLBindParameter(hstmt, 8, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].price) - 1, 0, &ordwth_array[0].price, sizeof(ordwth_array[0].price), &ordwth_array[0].priceInd);
SQLBindParameter(hstmt, 9, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].qty) - 1, 0, &ordwth_array[0].qty, sizeof(ordwth_array[0].qty), &ordwth_array[0].qtyInd);
SQLBindParameter(hstmt, 10, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].status) - 1, 0, &ordwth_array[0].status, sizeof(ordwth_array[0].status), &ordwth_array[0].statusInd);
SQLBindParameter(hstmt, 11, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].owflag) - 1, 0, &ordwth_array[0].owflag, sizeof(ordwth_array[0].owflag), &ordwth_array[0].owflagInd);
SQLBindParameter(hstmt, 12, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].ordrec) - 1, 0, &ordwth_array[0].ordrec, sizeof(ordwth_array[0].ordrec), &ordwth_array[0].ordrecInd);
SQLBindParameter(hstmt, 13, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].firmid) - 1, 0, &ordwth_array[0].firmid, sizeof(ordwth_array[0].firmid), &ordwth_array[0].firmidInd);
SQLBindParameter(hstmt, 14, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].branchid) - 1, 0, &ordwth_array[0].branchid, sizeof(ordwth_array[0].branchid), &ordwth_array[0].branchidInd);
SQLBindParameter(hstmt, 15, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY, sizeof(ordwth_array[0].checkord), 0, &ordwth_array[0].checkord, sizeof(ordwth_array[0].checkord), &ordwth_array[0].checkordInd);
SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
retcode = SQLPrepare(hstmt, sql, SQL_NTS);
for (int j = 0; j < COUNT / ROW_ARRAY_SIZE; j++)
{
for (int i = 0; i < ROW_ARRAY_SIZE; i++)
{
ordwth_array[i].rec_num = rec_num++;
ordwth_array[i].ordrecInd = 0;
strcpy((char*)ordwth_array[i].date, "20150120");
ordwth_array[i].dateInd = SQL_NTS;
strcpy((char*)ordwth_array[i].time, "13:20:10");
ordwth_array[i].timeInd = SQL_NTS;
strcpy((char*)ordwth_array[i].reff, "1234567890");
ordwth_array[i].reffInd = SQL_NTS;
strcpy((char*)ordwth_array[i].acc, "0000011111");
ordwth_array[i].accInd = SQL_NTS;
strcpy((char*)ordwth_array[i].stock, "123456");
ordwth_array[i].stockInd = SQL_NTS;
strcpy((char*)ordwth_array[i].bs, "B");
ordwth_array[i].bsInd = SQL_NTS;
strcpy((char*)ordwth_array[i].price, "1.000");
ordwth_array[i].priceInd = SQL_NTS;
strcpy((char*)ordwth_array[i].qty, "1000");
ordwth_array[i].qtyInd = SQL_NTS;
strcpy((char*)ordwth_array[i].status, "R");
ordwth_array[i].statusInd = SQL_NTS;
strcpy((char*)ordwth_array[i].owflag, "ORD");
ordwth_array[i].owflagInd = SQL_NTS;
strcpy((char*)ordwth_array[i].ordrec, "1");
ordwth_array[i].ordrecInd = SQL_NTS;
strcpy((char*)ordwth_array[i].firmid, "123");
ordwth_array[i].firmidInd = SQL_NTS;
strcpy((char*)ordwth_array[i].branchid, "20201");
ordwth_array[i].branchidInd = SQL_NTS;
::memset(ordwth_array[i].checkord, 0, sizeof(ordwth_array[i].checkord));
ordwth_array[i].checkordInd = sizeof(ordwth_array[i].checkord);
}
//执行语句
retcode = SQLExecute(hstmt);
if (retcode == SQL_ERROR)
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);
printf("db Insert fail, sqlstate=%s, errormsg=%s\n", state, msg);
SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
system("pause");
return;
}
else if (retcode == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR msg[128];
SQLCHAR state[128];
SQLINTEGER error_id;
SQLSMALLINT text;
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);
printf("warning msg=%s\n", msg);
}
SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
}
time_t end;
time(&end);
printf("Insert %d records in %lld seconds, average insert speed: %lld\n", COUNT, end - begin, COUNT / (end - begin));
//printf("Insert %d records in %lld seconds\n", COUNT, end - begin);
system("pause");
return;
}
使用这种方法,每秒能插16000条记录。
3. 使用OLEDB插入数据
sqlserver OLEDB介绍地址:
https://msdn.microsoft.com/en-us/library/ms131687(v=sql.105).aspx
该例子包含两个文件:bulkcopy.h bulkcopy.cpp
bulkcopy.h
#define DBINITCONSTANTS
#define OLEDBVER 0x0250 // to include correct interfaces
#include <oledb.h>
#include <oledberr.h>
#include <stdio.h>
#include <stddef.h> // for offsetof
//#include <sqlncli.h>
#include "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Include\sqlncli.h"
// @type UWORD | 2 byte unsigned integer.
typedef unsigned short UWORD;
// @type SDWORD | 4 byte signed integer.
typedef signed long SDWORD;
//委托表数据结构体
struct Data
{
SDWORD rec_num_len; // Length of data (not space allocated).
DWORD rec_num_status; // Status of column.
int rec_num;
SDWORD date_len;
DWORD date_status;
char date[9];
SDWORD time_len;
DWORD time_status;
char time[9];
SDWORD reff_len;
DWORD reff_status;
char reff[11];
SDWORD acc_len;
DWORD acc_status;
char acc[11];
SDWORD stock_len;
DWORD stock_status;
char stock[7];
SDWORD bs_len;
DWORD bs_status;
char bs[2];
SDWORD price_len;
DWORD price_status;
char price[9];
SDWORD qty_len;
DWORD qty_status;
char qty[9];
SDWORD status_len;
DWORD status_status;
char status[2];
SDWORD owflag_len;
DWORD owflag_status;
char owflag[4];
SDWORD ordrec_len;
DWORD ordrec_status;
char ordrec[9];
SDWORD firmid_len;
DWORD firmid_status;
char firmid[6];
SDWORD branchid_len;
DWORD branchid_status;
char branchid[6];
SDWORD checkord_len;
DWORD checkord_status;
BYTE checkord[16];
};
//委托表每列的数据类型
DBTYPEENUM col_type[15] = {
DBTYPE_I4,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_STR,
DBTYPE_BYTES
};
//委托表没列的数据长度
DBBYTEOFFSET col_len[15] = {
sizeof(int),
9,
9,
11,
11,
7,
2,
9,
9,
2,
4,
9,
6,
6,
16
};
// How to lay out each column in memory.
struct COLUMNDATA {
SDWORD idLen; // Length of data (not space allocated).
DWORD idStatus; // Status of column.
int id; // Store data here as a variant.
SDWORD dateLen;
DWORD dateStatus;
char date[21];
};
//函数申明
void set_bindings();
//一个绑定
void set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type);
// Given an ICommand pointer, properties, and query, a rowsetpointer is returned.
HRESULT CreateSessionCommand(DBPROPSET* rgPropertySets, ULONG ulcPropCount, CLSID clsidProv);
// Use to set properties and execute a given query.
HRESULT ExecuteQuery(IDBCreateCommand* pIDBCreateCommand,
WCHAR* pwszQuery,
DBPROPSET* rgPropertySets,
ULONG ulcPropCount,
LONG* pcRowsAffected,
IRowset** ppIRowset,
BOOL fSuccessOnly = TRUE);
void DumpErrorInfo(IUnknown* pObjectWithError, REFIID IID_InterfaceWithError);
// Use to set up options for call to IDBInitialize::Initialize.
void SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp);
// Sets fastload property on/off for session.
HRESULT SetFastLoadProperty(BOOL fSet);
HRESULT FastInsertData();
bulkcopy.cpp
#include "bulkcopy.h"
#include <time.h>
#define COUNT 1000000
#define ROW_SIZE 1000
#define COLUMN_ALIGNVAL 8
#define ROUND_UP(Size, Amount)(((DWORD)(Size) + ((Amount)-1)) & ~((Amount)-1))
WCHAR g_wszTable[] = L"ashare_ordwth";
//WCHAR g_wszTable[] = L"test";
WCHAR g_strTestLOC[100] = L"172.19.124.72";
WCHAR g_strTestDBName[] = L"ctp2_lx";
const UWORD g_cOPTION = 5;
const UWORD MAXPROPERTIES = 5;
const ULONG DEFAULT_CBMAXLENGTH = 20;
IMalloc* g_pIMalloc = NULL;
IDBInitialize* g_pIDBInitialize = NULL;
int rec_num = 1;
//binding数组
DBBINDING g_bindings[15];
int main() {
HRESULT hr = NOERROR;
HRESULT hr2 = NOERROR;
// OLE initialized?
BOOL fInitialized = FALSE;
// One property set for initializing.
DBPROPSET rgPropertySets[1];
// Properties within above property set.
DBPROP rgDBProperties[g_cOPTION];
IDBCreateCommand* pIDBCreateCommand = NULL;
IRowset* pIRowset = NULL;
DBPROPSET* rgProperties = NULL;
IAccessor* pIAccessor = NULL;
// Basic initialization.
if (FAILED(CoInitialize(NULL)))
goto cleanup;
else
fInitialized = TRUE;
hr = CoGetMalloc(MEMCTX_TASK, &g_pIMalloc);
if ((!g_pIMalloc) || FAILED(hr))
goto cleanup;
// Set up property set for call to IDBInitialize in CreateSessionCommand.
rgPropertySets[0].rgProperties = rgDBProperties;
rgPropertySets[0].cProperties = g_cOPTION;
rgPropertySets[0].guidPropertySet = DBPROPSET_DBINIT;
SetupOption(DBPROP_INIT_CATALOG, L"ctp2_lx", &rgDBProperties[0]);
//SetupOption(DBPROP_INIT_DATASOURCE, L"ctp2_lx", &rgDBProperties[0]);
//SetupOption(DBPROP_AUTH_INTEGRATED, L"SSPI", &rgDBProperties[1]);
SetupOption(DBPROP_INIT_DATASOURCE, L"172.19.124.72", &rgDBProperties[1]);
//SetupOption(DBPROP_INIT_LOCATION, L"172.19.124.72", &rgDBProperties[2]);
//密码
SetupOption(DBPROP_AUTH_PASSWORD, L"123456", &rgDBProperties[3]);
//用户名
SetupOption(DBPROP_AUTH_USERID, L"sa", &rgDBProperties[4]);
if (!SUCCEEDED(hr = CreateSessionCommand(rgPropertySets, 1, SQLNCLI_CLSID)))
goto cleanup;
time_t begin;
time(&begin);
// Get IRowsetFastLoad and insert data into IRFLTable.
if (FAILED(hr = FastInsertData()))
goto cleanup;
time_t end;
time(&end);
printf("Elapse Time= [%lld]\n", end - begin);
cleanup:
// Release memory.
if (rgProperties && rgProperties->rgProperties)
delete[](rgProperties->rgProperties);
if (rgProperties)
delete[]rgProperties;
if (pIDBCreateCommand)
pIDBCreateCommand->Release();
if (pIAccessor)
pIAccessor->Release();
if (pIRowset)
pIRowset->Release();
if (g_pIMalloc)
g_pIMalloc->Release();
if (g_pIDBInitialize) {
hr2 = g_pIDBInitialize->Uninitialize();
if (FAILED(hr2))
printf("Uninitialize failed\n");
}
if (fInitialized)
CoUninitialize();
if (SUCCEEDED(hr))
printf("Test completed successfully.\n\n");
else
printf("Test failed.\n\n");
system("pause");
}
void set_bindings()
{
set_bind(g_bindings[0], 1, offsetof(Data, rec_num_len), offsetof(Data, rec_num_status), offsetof(Data, rec_num), col_len[0], col_type[0]);
set_bind(g_bindings[1], 2, offsetof(Data, date_len), offsetof(Data, date_status), offsetof(Data, date), col_len[1], col_type[1]);
set_bind(g_bindings[2], 3, offsetof(Data, time_len), offsetof(Data, time_status), offsetof(Data, time), col_len[2], col_type[2]);
set_bind(g_bindings[3], 4, offsetof(Data, reff_len), offsetof(Data, reff_status), offsetof(Data, reff), col_len[3], col_type[3]);
set_bind(g_bindings[4], 5, offsetof(Data, acc_len), offsetof(Data, acc_status), offsetof(Data, acc), col_len[4], col_type[4]);
set_bind(g_bindings[5], 6, offsetof(Data, stock_len), offsetof(Data, stock_status), offsetof(Data, stock), col_len[5], col_type[5]);
set_bind(g_bindings[6], 7, offsetof(Data, bs_len), offsetof(Data, bs_status), offsetof(Data, bs), col_len[6], col_type[6]);
set_bind(g_bindings[7], 8, offsetof(Data, price_len), offsetof(Data, price_status), offsetof(Data, price), col_len[7], col_type[7]);
set_bind(g_bindings[8], 9, offsetof(Data, qty_len), offsetof(Data, qty_status), offsetof(Data, qty), col_len[8], col_type[8]);
set_bind(g_bindings[9], 10, offsetof(Data, status_len), offsetof(Data, status_status), offsetof(Data, status), col_len[9], col_type[9]);
set_bind(g_bindings[10], 11, offsetof(Data, owflag_len), offsetof(Data, owflag_status), offsetof(Data, owflag), col_len[10], col_type[10]);
set_bind(g_bindings[11], 12, offsetof(Data, ordrec_len), offsetof(Data, ordrec_status), offsetof(Data, ordrec), col_len[11], col_type[11]);
set_bind(g_bindings[12], 13, offsetof(Data, firmid_len), offsetof(Data, firmid_status), offsetof(Data, firmid), col_len[12], col_type[12]);
set_bind(g_bindings[13], 14, offsetof(Data, branchid_len), offsetof(Data, branchid_status), offsetof(Data, branchid), col_len[13], col_type[13]);
set_bind(g_bindings[14], 15, offsetof(Data, checkord_len), offsetof(Data, checkord_status), offsetof(Data, checkord), col_len[14], col_type[14]);
}
void set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type)
{
binding.dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
binding.iOrdinal = col;
binding.pTypeInfo = NULL;
binding.obValue = value_offset;
binding.obLength = len_offset;
binding.obStatus = status_offset;
binding.cbMaxLen = len; // Size of varchar column.
binding.pTypeInfo = NULL;
binding.pObject = NULL;
binding.pBindExt = NULL;
binding.dwFlags = 0;
binding.eParamIO = DBPARAMIO_NOTPARAM;
binding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
binding.bPrecision = 0;
binding.bScale = 0;
binding.wType = type;
}
//插入委托表
HRESULT FastInsertData() {
HRESULT hr = E_FAIL;
HRESULT hr2 = E_FAIL;
DBID TableID;
IDBCreateSession* pIDBCreateSession = NULL;
IOpenRowset* pIOpenRowsetFL = NULL;
IRowsetFastLoad* pIFastLoad = NULL;
IAccessor* pIAccessor = NULL;
HACCESSOR hAccessor = 0;
DBBINDSTATUS bindingStatus[15] = { 0 };
TableID.uName.pwszName = NULL;
LONG i = 0;
void* pData = NULL;
TableID.eKind = DBKIND_NAME;
// if ( !(TableID.uName.pwszName = new WCHAR[wcslen(g_wszTable) + 2]) )
LPOLESTR x = TableID.uName.pwszName = new WCHAR[wcslen(g_wszTable) + 2];
if (!x)
return E_FAIL;
wcsncpy_s(TableID.uName.pwszName, wcslen(g_wszTable) + 2, g_wszTable, wcslen(g_wszTable) + 1);
TableID.uName.pwszName[wcslen(g_wszTable) + 1] = (WCHAR)NULL;
// Get the fastload pointer.
if (FAILED(hr = SetFastLoadProperty(TRUE)))
goto cleanup;
if (FAILED(hr =
g_pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&pIDBCreateSession)))
goto cleanup;
if (FAILED(hr =
pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown **)&pIOpenRowsetFL)))
goto cleanup;
// Get IRowsetFastLoad initialized to use the test table.
if (FAILED(hr =
pIOpenRowsetFL->OpenRowset(NULL,
&TableID,
NULL,
IID_IRowsetFastLoad,
0,
NULL,
(LPUNKNOWN *)&pIFastLoad)))
goto cleanup;
set_bindings();
if (FAILED(hr =
pIFastLoad->QueryInterface(IID_IAccessor, (void **)&pIAccessor)))
return hr;
if (FAILED(hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
15,
g_bindings,
ROUND_UP(sizeof(Data), COLUMN_ALIGNVAL),
&hAccessor,
bindingStatus)))
return hr;
Data *datas = new Data[COUNT];
for (int i = 0; i < COUNT; i++)
{
datas[i].rec_num_len = col_len[0];
datas[i].date_len = col_len[1];
datas[i].time_len = col_len[2];
datas[i].reff_len = col_len[3];
datas[i].acc_len = col_len[4];
datas[i].stock_len = col_len[5];
datas[i].bs_len = col_len[6];
datas[i].price_len = col_len[7];
datas[i].qty_len = col_len[8];
datas[i].status_len = col_len[9];
datas[i].owflag_len = col_len[10];
datas[i].ordrec_len = col_len[11];
datas[i].firmid_len = col_len[12];
datas[i].branchid_len = col_len[13];
datas[i].checkord_len = col_len[14];
datas[i].rec_num_status = 0;
datas[i].date_status = 0;
datas[i].time_status = 0;
datas[i].reff_status = 0;
datas[i].acc_status = 0;
datas[i].stock_status = 0;
datas[i].bs_status = 0;
datas[i].price_status = 0;
datas[i].qty_status = 0;
datas[i].status_status = 0;
datas[i].owflag_status = 0;
datas[i].ordrec_status = 0;
datas[i].firmid_status = 0;
datas[i].branchid_status = 0;
datas[i].checkord_status = 0;
}
for (int i = 0; i < COUNT; i++)
{
datas[i].rec_num = rec_num++;
strncpy(datas[i].date, "20150120", col_len[1]);
strncpy(datas[i].time, "13:20:10", col_len[2]);
strncpy(datas[i].reff, "1234567890", col_len[3]);
strncpy(datas[i].acc, "0000011111", col_len[4]);
strncpy(datas[i].stock, "123456", col_len[5]);
strncpy(datas[i].bs, "B", col_len[6]);
strncpy(datas[i].price, "1.000", col_len[7]);
strncpy(datas[i].qty, "1000", col_len[8]);
strncpy(datas[i].status, "R", col_len[9]);
strncpy(datas[i].owflag, "ORD", col_len[10]);
strncpy(datas[i].ordrec, "1", col_len[11]);
strncpy(datas[i].firmid, "123", col_len[12]);
strncpy(datas[i].branchid, "20201", col_len[13]);
memset(datas[i].checkord, 0, col_len[14]);
if (FAILED(hr = pIFastLoad->InsertRow(hAccessor, &datas[i])))
{
DumpErrorInfo(pIFastLoad, IID_ISQLServerErrorInfo);
goto cleanup;
}
if (FAILED(hr = pIFastLoad->Commit(TRUE)))
{
DumpErrorInfo(pIFastLoad, IID_ISQLServerErrorInfo);
printf("Error on IRFL::Commit\n");
}
}
cleanup:
if (FAILED(hr2 = SetFastLoadProperty(FALSE)))
printf("SetFastLoadProperty(FALSE) failed with %x", hr2);
if (pIAccessor && hAccessor)
if (FAILED(pIAccessor->ReleaseAccessor(hAccessor, NULL)))
hr = E_FAIL;
if (pIAccessor)
pIAccessor->Release();
if (pIFastLoad)
pIFastLoad->Release();
if (pIOpenRowsetFL)
pIOpenRowsetFL->Release();
if (pIDBCreateSession)
pIDBCreateSession->Release();
if (TableID.uName.pwszName)
delete[]TableID.uName.pwszName;
delete[] datas;
return hr;
}
HRESULT SetFastLoadProperty(BOOL fSet) {
HRESULT hr = S_OK;
IDBProperties* pIDBProps = NULL;
DBPROP rgProps[1];
DBPROPSET PropSet;
VariantInit(&rgProps[0].vValue);
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].dwPropertyID = SSPROP_ENABLEFASTLOAD;
if (fSet == TRUE)
rgProps[0].vValue.boolVal = VARIANT_TRUE;
else
rgProps[0].vValue.boolVal = VARIANT_FALSE;
PropSet.rgProperties = rgProps;
PropSet.cProperties = 1;
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDATASOURCE;
if (SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties, (LPVOID *)&pIDBProps)))
hr = pIDBProps->SetProperties(1, &PropSet);
VariantClear(&rgProps[0].vValue);
if (pIDBProps)
pIDBProps->Release();
return hr;
}
HRESULT CreateSessionCommand(DBPROPSET* rgPropertySets,// @parm [in] property sets
ULONG ulcPropCount, // @parm [in] count of prop sets.
CLSID clsidProv) { // @parm [in] Provider CLSID.
HRESULT hr = NOERROR;
IDBCreateSession* pIDBCreateSession = NULL;
IDBProperties* pIDBProperties = NULL;
UWORD i = 0, j = 0; // indexes.
if (ulcPropCount && !rgPropertySets) {
hr = E_INVALIDARG;
return hr;
}
if (!SUCCEEDED(hr = CoCreateInstance(clsidProv,
NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void **)&g_pIDBInitialize)))
goto CLEANUP;
if (!SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties,
(void **)&pIDBProperties)))
goto CLEANUP;
if (!SUCCEEDED(hr = pIDBProperties->SetProperties(ulcPropCount, rgPropertySets)))
goto CLEANUP;
if (!SUCCEEDED(hr = g_pIDBInitialize->Initialize())) {
printf("Call to initialize failed.\n");
goto CLEANUP;
}
CLEANUP:
if (pIDBProperties)
pIDBProperties->Release();
if (pIDBCreateSession)
pIDBCreateSession->Release();
for (i = 0; i < ulcPropCount; i++)
for (j = 0; j < rgPropertySets[i].cProperties; j++)
VariantClear(&(rgPropertySets[i].rgProperties[j]).vValue);
return hr;
}
void SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp) {
pDBProp->dwPropertyID = PropID;
pDBProp->dwOptions = DBPROPOPTIONS_REQUIRED;
pDBProp->colid = DB_NULLID;
pDBProp->vValue.vt = VT_BSTR;
pDBProp->vValue.bstrVal = SysAllocStringLen(wszVal, wcslen(wszVal));
}
// DumpErrorInfo queries SQLOLEDB error interfaces, retrieving available
// status or error information.
void DumpErrorInfo
(
IUnknown* pObjectWithError,
REFIID IID_InterfaceWithError
)
{
// Interfaces used in the example.
IErrorInfo* pIErrorInfoAll = NULL;
IErrorInfo* pIErrorInfoRecord = NULL;
IErrorRecords* pIErrorRecords = NULL;
ISupportErrorInfo* pISupportErrorInfo = NULL;
ISQLErrorInfo* pISQLErrorInfo = NULL;
ISQLServerErrorInfo* pISQLServerErrorInfo = NULL;
// Number of error records.
ULONG nRecs;
ULONG nRec;
// Basic error information from GetBasicErrorInfo.
ERRORINFO errorinfo;
// IErrorInfo values.
BSTR bstrDescription;
BSTR bstrSource;
// ISQLErrorInfo parameters.
BSTR bstrSQLSTATE;
LONG lNativeError;
// ISQLServerErrorInfo parameter pointers.
SSERRORINFO* pSSErrorInfo = NULL;
OLECHAR* pSSErrorStrings = NULL;
// Hard-code an American English locale for the example.
DWORD MYLOCALEID = 0x0409;
// Only ask for error information if the interface supports
// it.
if (FAILED(pObjectWithError->QueryInterface(IID_ISupportErrorInfo,
(void**)&pISupportErrorInfo)))
{
wprintf_s(L"SupportErrorErrorInfo interface not supported");
return;
}
if (FAILED(pISupportErrorInfo->
InterfaceSupportsErrorInfo(IID_InterfaceWithError)))
{
wprintf_s(L"InterfaceWithError interface not supported");
return;
}
// Do not test the return of GetErrorInfo. It can succeed and return
// a NULL pointer in pIErrorInfoAll. Simply test the pointer.
GetErrorInfo(0, &pIErrorInfoAll);
if (pIErrorInfoAll != NULL)
{
// Test to see if it's a valid OLE DB IErrorInfo interface
// exposing a list of records.
if (SUCCEEDED(pIErrorInfoAll->QueryInterface(IID_IErrorRecords,
(void**)&pIErrorRecords)))
{
pIErrorRecords->GetRecordCount(&nRecs);
// Within each record, retrieve information from each
// of the defined interfaces.
for (nRec = 0; nRec < nRecs; nRec++)
{
// From IErrorRecords, get the HRESULT and a reference
// to the ISQLErrorInfo interface.
pIErrorRecords->GetBasicErrorInfo(nRec, &errorinfo);
pIErrorRecords->GetCustomErrorObject(nRec,
IID_ISQLErrorInfo, (IUnknown**)&pISQLErrorInfo);
// Display the HRESULT, then use the ISQLErrorInfo.
wprintf_s(L"HRESULT:\t%#X\n", errorinfo.hrError);
if (pISQLErrorInfo != NULL)
{
pISQLErrorInfo->GetSQLInfo(&bstrSQLSTATE,
&lNativeError);
// Display the SQLSTATE and native error values.
wprintf_s(L"SQLSTATE:\t%s\nNative Error:\t%ld\n",
bstrSQLSTATE, lNativeError);
// SysFree BSTR references.
SysFreeString(bstrSQLSTATE);
// Get the ISQLServerErrorInfo interface from
// ISQLErrorInfo before releasing the reference.
pISQLErrorInfo->QueryInterface(
IID_ISQLServerErrorInfo,
(void**)&pISQLServerErrorInfo);
pISQLErrorInfo->Release();
}
// Test to ensure the reference is valid, then
// get error information from ISQLServerErrorInfo.
if (pISQLServerErrorInfo != NULL)
{
pISQLServerErrorInfo->GetErrorInfo(&pSSErrorInfo,
&pSSErrorStrings);
// ISQLServerErrorInfo::GetErrorInfo succeeds
// even when it has nothing to return. Test the
// pointers before using.
if (pSSErrorInfo)
{
// Display the state and severity from the
// returned information. The error message comes
// from IErrorInfo::GetDescription.
wprintf_s(L"Error state:\t%d\nSeverity:\t%d\n",
pSSErrorInfo->bState,
pSSErrorInfo->bClass);
// IMalloc::Free needed to release references
// on returned values. For the example, assume
// the g_pIMalloc pointer is valid.
g_pIMalloc->Free(pSSErrorStrings);
g_pIMalloc->Free(pSSErrorInfo);
}
pISQLServerErrorInfo->Release();
}
if (SUCCEEDED(pIErrorRecords->GetErrorInfo(nRec,
MYLOCALEID, &pIErrorInfoRecord)))
{
// Get the source and description (error message)
// from the record's IErrorInfo.
pIErrorInfoRecord->GetSource(&bstrSource);
pIErrorInfoRecord->GetDescription(&bstrDescription);
if (bstrSource != NULL)
{
wprintf_s(L"Source:\t\t%s\n", bstrSource);
SysFreeString(bstrSource);
}
if (bstrDescription != NULL)
{
wprintf_s(L"Error message:\t%s\n",
bstrDescription);
SysFreeString(bstrDescription);
}
pIErrorInfoRecord->Release();
}
}
pIErrorRecords->Release();
}
else
{
// IErrorInfo is valid; get the source and
// description to see what it is.
pIErrorInfoAll->GetSource(&bstrSource);
pIErrorInfoAll->GetDescription(&bstrDescription);
if (bstrSource != NULL)
{
wprintf_s(L"Source:\t\t%s\n", bstrSource);
SysFreeString(bstrSource);
}
if (bstrDescription != NULL)
{
wprintf_s(L"Error message:\t%s\n", bstrDescription);
SysFreeString(bstrDescription);
}
}
pIErrorInfoAll->Release();
}
else
{
wprintf_s(L"GetErrorInfo failed.");
}
pISupportErrorInfo->Release();
return;
}
使用上面方法,每秒钟可以插入3万多条记录。
OLEDB的接口确实有点繁琐~有时候读了好多遍MSDN上的接口说明,也执行不正确。