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上的接口说明，也执行不正确。
原标题：《c++ 访问sqlserver数据库，插入速度优化》
来源：http://blog.csdn.net/frank_liuxing/article/details/43231233
另一参考文章：
《Bulk Copy Data Using IRowsetFastLoad (OLE DB)》
https://docs.microsoft.com/zh-cn/sql/relational-databases/native-client-ole-db-how-to/bulk-copy-data-using-irowsetfastload-ole-db
《使用 IROWSETFASTLOAD 和 ISEQUENTIALSTREAM 将 BLOB 数据发送到 SQL SERVER (OLE DB)》
https://technet.microsoft.com/zh-cn/library/ff878198(de-de,SQL.120).aspx
《利用Native Client OLEDB 11 高效率地对SQL SERVER 进行查询和插入操作》
http://www.cnblogs.com/SamRichard/p/5550050.html
831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
