GaussDB批量绑定
GaussDB批量绑定
前提条件：数据源已配置成功。Linux系统请参考Linux下配置数据源；Windows系统请参考Windows下配置数据源。
/**********************************************************************
* 请在数据源中打开UseBatchProtocol，同时指定数据库中参数support_batch_bind为on
* CHECK_ERROR的作用是检查并打印错误信息。
* 此示例将与用户交互式获取DSN、模拟的数据量，忽略的数据量，并将最终数据入库到test_odbc_batch_insert中。
***********************************************************************/
#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
void Exec(SQLHDBC hdbc, SQLCHAR* sql)
{
SQLRETURN retcode; // Return status
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLCHAR loginfo[2048];
// 分配语句句柄。
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLAllocHandle(SQL_HANDLE_STMT) failed");
return;
}
// 准备语句。
retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS) failed");
return;
}
// 执行语句。
retcode = SQLExecute(hstmt);
sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute(hstmt) failed");
return;
}
// 释放句柄。
retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
sprintf((char*)loginfo, "SQLFreeHandle stmt log: %s", (char*)sql);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLFreeHandle(SQL_HANDLE_STMT, hstmt) failed");
return;
}
}
int main ()
{
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
long int batchCount = 1000; // 批量绑定的数据量
SQLLEN rowsCount = 0;
int ignoreCount = 0; // 批量绑定的数据中，不要入库的数据量
int i = 0;
SQLRETURN retcode;
SQLCHAR dsn[1024] = {'\0'};
SQLCHAR loginfo[2048];
do
{
if (ignoreCount > batchCount)
{
printf("ignoreCount(%d) should be less than batchCount(%d)\n", ignoreCount, batchCount);
}
}while(ignoreCount > batchCount);
// 分配环境句柄。
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLAllocHandle failed");
goto exit;
}
// 设置ODBC版本。
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetEnvAttr failed");
goto exit;
}
// 分配连接。
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLAllocHandle failed");
goto exit;
}
// 设置登录超时。
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetConnectAttr failed");
goto exit;
}
// 设置自动提交。
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)(1), 0);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetConnectAttr failed");
goto exit;
}
// 连接到数据库。
sprintf(loginfo, "SQLConnect(DSN:%s)", dsn);
retcode = SQLConnect(hdbc, (SQLCHAR*) "gaussdb", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLConnect failed");
goto exit;
}
// 初始化表信息。
Exec(hdbc, "drop table if exists test_odbc_batch_insert");
Exec(hdbc, "create table test_odbc_batch_insert(id int primary key, col varchar2(50))");
// 下面的代码根据用户输入的数据量，构造出将要入库的数据:
{
SQLRETURN retcode;
SQLHSTMT hstmtinesrt = SQL_NULL_HSTMT;
SQLCHAR *sql = NULL;
SQLINTEGER *ids = NULL;
SQLCHAR *cols = NULL;
SQLLEN *bufLenIds = NULL;
SQLLEN *bufLenCols = NULL;
SQLUSMALLINT *operptr = NULL;
SQLUSMALLINT *statusptr = NULL;
SQLULEN process = 0;
// 这里是按列构造，每个字段的内存连续存放在一起。
ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount);
cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50);
// 这里是每个字段中，每一行数据的内存长度。
bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount);
bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount);
// 该行是否需要被处理，SQL_PARAM_IGNORE 或 SQL_PARAM_PROCEED。
operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount);
memset(operptr, 0, sizeof(operptr[0]) * batchCount);
// 该行的处理结果。
// 注：由于数据库中处理方式是同一语句隶属同一事务中，所以如果出错，那么待处理数据都将是出错的，并不会部分入库。
statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount);
memset(statusptr, 88, sizeof(statusptr[0]) * batchCount);
if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds)
{
fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
goto exit;
}
for (i = 0; i < batchCount; i++)
{
ids[i] = i;
sprintf(cols + 50 * i, "column test value %d", i);
bufLenIds[i] = sizeof(ids[i]);
bufLenCols[i] = strlen(cols + 50 * i);
operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED;
}
// 分配语句句柄。
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLAllocHandle failed");
goto exit;
}
// 准备语句。
sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)";
retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS);
sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLPrepare failed");
goto exit;
}
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount));
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetStmtAttr failed");
goto exit;
}
retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLBindParameter failed");
goto exit;
}
retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLBindParameter failed");
goto exit;
}
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetStmtAttr failed");
goto exit;
}
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetStmtAttr failed");
goto exit;
}
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLSetStmtAttr failed");
goto exit;
}
retcode = SQLExecute(hstmtinesrt);
sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute(hstmtinesrt) failed");
goto exit;
retcode = SQLRowCount(hstmtinesrt, &rowsCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLRowCount failed");
goto exit;
}
if (rowsCount != (batchCount - ignoreCount))
{
sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
else
{
sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
// 检查返回的行数。
if (rowsCount != process)
{
sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
else
{
sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
for (i = 0; i < batchCount; i++)
{
if (i < ignoreCount)
{
if (statusptr[i] != SQL_PARAM_UNUSED)
{
sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
}
else if (statusptr[i] != SQL_PARAM_SUCCESS)
{
sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]);
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLExecute failed");
goto exit;
}
}
}
retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt);
sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt");
if (!SQL_SUCCEEDED(retcode)) {
printf("SQLFreeHandle failed");
goto exit;
}
}
}
exit:
(void) printf ("\nComplete.\n");
// 断开连接。
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// 释放环境句柄。
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
浙公网安备 33010602011771号