示例：常用功能和批量绑定

    批量绑定示例代码

    2. * 请在数据源中打开UseBatchProtocol，同时指定数据库中参数support_batch_bind
    3. * 为on
    4. * CHECK_ERROR的作用是检查并打印错误信息。
    5. * 此示例将与用户交互式获取DSN、模拟的数据量，忽略的数据量，并将最终数据入库到test_odbc_batch_insert中
    6. ***********************************************************************/
    7. #include <stdio.h>
    8. #include <stdlib.h>
    9. #include <sql.h>
    10. #include <sqlext.h>
    11. #include <string.h>
    13. void Exec(SQLHDBC hdbc, SQLCHAR* sql)
    14. {
    15.     SQLRETURN retcode;                  // Return status
    16.     SQLHSTMT hstmt = SQL_NULL_HSTMT;    // Statement handle
    17.     SQLCHAR     loginfo[2048];
    19.     // Allocate Statement Handle
    20.     retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    22.     if (!SQL_SUCCEEDED(retcode)) {
    23.         printf("SQLAllocHandle(SQL_HANDLE_STMT) failed");
    24.         return;
    25.     }
    27.     // Prepare Statement
    28.     retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
    29.     sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
    31.     if (!SQL_SUCCEEDED(retcode)) {
    32.         printf("SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS) failed");
    33.         return;
    34.     }
    36.     // Execute Statement
    37.     retcode = SQLExecute(hstmt);
    38.     sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
    40.     if (!SQL_SUCCEEDED(retcode)) {
    41.         printf("SQLExecute(hstmt) failed");
    42.         return;
    43.     }
    44.     // Free Handle
    45.     retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    46.     sprintf((char*)loginfo, "SQLFreeHandle stmt log: %s", (char*)sql);
    48.     if (!SQL_SUCCEEDED(retcode)) {
    49.         printf("SQLFreeHandle(SQL_HANDLE_STMT, hstmt) failed");
    50.         return;
    51.     }
    52. }
    54. int main () 
    55. {
    56.     SQLHENV  henv  = SQL_NULL_HENV;
    57.     SQLHDBC  hdbc  = SQL_NULL_HDBC; 
    58.     int      batchCount = 1000; // 批量绑定的数据量
    59.     SQLLEN   rowsCount = 0;
    60.     int      ignoreCount = 0; // 批量绑定的数据中，不要入库的数据量
    62.     SQLRETURN   retcode;
    63.     SQLCHAR     dsn[1024] = {'\0'};
    64.     SQLCHAR     loginfo[2048];
    66.     do 
    67.     {
    68.         if (ignoreCount > batchCount)
    69.         {
    70.             printf("ignoreCount(%d) should be less than batchCount(%d)\n", ignoreCount, batchCount);
    71.         }
    72.     }while(ignoreCount > batchCount);
    74.     retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    76.     if (!SQL_SUCCEEDED(retcode)) {
    77.         printf("SQLAllocHandle failed");
    78.         goto exit;
    79.     }
    81.     // Set ODBC Verion
    82.     retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
    83.                                         (SQLPOINTER*)SQL_OV_ODBC3, 0);
    85.     if (!SQL_SUCCEEDED(retcode)) {
    86.         printf("SQLSetEnvAttr failed");
    87.         goto exit;
    88.     }
    90.     // Allocate Connection
    91.     retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    93.     if (!SQL_SUCCEEDED(retcode)) {
    94.         printf("SQLAllocHandle failed");
    95.         goto exit;
    96.     }
    99.     // Set Login Timeout
    100.     retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    102.     if (!SQL_SUCCEEDED(retcode)) {
    103.         printf("SQLSetConnectAttr failed");
    104.         goto exit;
    105.     }
    108.     // Set Auto Commit
    109.     retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
    110.                                         (SQLPOINTER)(1), 0);
    111.     if (!SQL_SUCCEEDED(retcode)) {
    113.         goto exit;
    114.     }
    117.     // Connect to DSN
    118.     // gaussdb替换成用户所使用的数据源名称
    119.     sprintf(loginfo, "SQLConnect(DSN:%s)", dsn);
    120.     retcode = SQLConnect(hdbc, (SQLCHAR*) "gaussdb", SQL_NTS,
    121.                                (SQLCHAR*) NULL, 0, NULL, 0);
    123.     if (!SQL_SUCCEEDED(retcode)) {
    124.         printf("SQLConnect failed");
    125.         goto exit;
    126.     }
    128.     // init table info.
    129.     Exec(hdbc, "drop table if exists test_odbc_batch_insert");
    130.     Exec(hdbc, "create table test_odbc_batch_insert(id int primary key, col varchar2(50))");
    132.     // 下面的代码根据用户输入的数据量，构造出将要入库的数据:
    133.     {
    134.         SQLRETURN retcode; 
    135.         SQLHSTMT hstmtinesrt = SQL_NULL_HSTMT;
    136.         int          i;
    137.         SQLCHAR      *sql = NULL;
    138.         SQLINTEGER   *ids  = NULL;
    139.         SQLCHAR      *cols = NULL;
    140.         SQLLEN       *bufLenIds = NULL;
    141.         SQLLEN       *bufLenCols = NULL;
    142.         SQLUSMALLINT *operptr = NULL;
    143.         SQLUSMALLINT *statusptr = NULL;
    144.         SQLULEN      process = 0;
    146.         // 这里是按列构造，每个字段的内存连续存放在一起。
    147.         ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount);
    148.         cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50);
    149.         // 这里是每个字段中，每一行数据的内存长度。
    150.         bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount);
    151.         bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount);
    152.         // 该行是否需要被处理，SQL_PARAM_IGNORE 或 SQL_PARAM_PROCEED
    153.         operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount);
    154.         memset(operptr, 0, sizeof(operptr[0]) * batchCount);
    155.         // 该行的处理结果。
    156.         // 注：由于数据库中处理方式是同一语句隶属同一事务中，所以如果出错，那么待处理数据都将是出错的，并不会部分入库。
    157.         statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount);
    158.         memset(statusptr, 88, sizeof(statusptr[0]) * batchCount);
    160.         if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds)
    161.         {
    162.             fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
    163.             goto exit;
    164.         }
    166.         for (int i = 0; i < batchCount; i++)
    167.         {
    168.             ids[i] = i;
    169.             sprintf(cols + 50 * i, "column test value %d", i);
    170.             bufLenIds[i] = sizeof(ids[i]);
    171.             bufLenCols[i] = strlen(cols + 50 * i);
    172.             operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED;
    173.         }
    175.         // Allocate Statement Handle
    176.         retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt);
    178.         if (!SQL_SUCCEEDED(retcode)) {
    179.             printf("SQLAllocHandle failed");
    180.             goto exit;
    181.         }
    183.         // Prepare Statement
    184.         sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)";
    185.         retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS);
    186.         sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
    188.         if (!SQL_SUCCEEDED(retcode)) {
    189.             printf("SQLPrepare failed");
    190.             goto exit;
    191.         }
    193.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount));
    195.         if (!SQL_SUCCEEDED(retcode)) {
    196.             printf("SQLSetStmtAttr failed");
    197.             goto exit;
    198.         }
    200.         retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds);
    202.         if (!SQL_SUCCEEDED(retcode)) {
    203.             printf("SQLBindParameter failed");
    204.             goto exit;
    205.         }
    207.         retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
    209.         if (!SQL_SUCCEEDED(retcode)) {
    210.             printf("SQLBindParameter failed");
    211.             goto exit;
    212.         }
    214.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
    216.         if (!SQL_SUCCEEDED(retcode)) {
    217.             printf("SQLSetStmtAttr failed");
    218.             goto exit;
    219.         }
    221.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount);
    224.             goto exit;
    225.         }
    227.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount);
    229.         if (!SQL_SUCCEEDED(retcode)) {
    230.             printf("SQLSetStmtAttr failed");
    231.             goto exit;
    232.         }
    234.         retcode = SQLExecute(hstmtinesrt);
    235.         sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
    237.         if (!SQL_SUCCEEDED(retcode)) {
    238.             printf("SQLExecute(hstmtinesrt) failed");
    239.             goto exit;
    241.         retcode = SQLRowCount(hstmtinesrt, &rowsCount);
    243.         if (!SQL_SUCCEEDED(retcode)) {
    244.             printf("SQLRowCount failed");
    245.             goto exit;
    246.         }
    248.         if (rowsCount != (batchCount - ignoreCount))
    249.         {
    250.             sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
    252.             if (!SQL_SUCCEEDED(retcode)) {
    253.                 printf("SQLExecute failed");
    254.                 goto exit;
    255.             }
    256.         }
    257.         else
    258.         {
    259.             sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
    261.             if (!SQL_SUCCEEDED(retcode)) {
    262.                 printf("SQLExecute failed");
    263.                 goto exit;
    264.             }
    265.         }
    267.         // check row number returned
    268.         if (rowsCount != process)
    269.         {
    270.             sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
    272.             if (!SQL_SUCCEEDED(retcode)) {
    273.                 printf("SQLExecute failed");
    274.                 goto exit;
    275.             }
    276.         }
    277.         else
    278.         {
    279.             sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
    281.             if (!SQL_SUCCEEDED(retcode)) {
    282.                 printf("SQLExecute failed");
    283.                 goto exit;
    284.             }
    285.         }
    287.         for (int i = 0; i < batchCount; i++)
    288.         {
    289.             if (i < ignoreCount)
    290.             {
    291.                 if (statusptr[i] != SQL_PARAM_UNUSED)
    292.                 {
    293.                     sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]);
    295.                     if (!SQL_SUCCEEDED(retcode)) {
    296.                         printf("SQLExecute failed");
    297.                         goto exit;
    298.                     }
    299.                 }
    300.             }
    301.             else if (statusptr[i] != SQL_PARAM_SUCCESS)
    302.             {
    303.                 sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]);
    305.                 if (!SQL_SUCCEEDED(retcode)) {
    306.                     printf("SQLExecute failed");
    307.                     goto exit;
    308.                 }
    309.             }
    310.         }
    312.         retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt);
    313.         sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt");
    315.         if (!SQL_SUCCEEDED(retcode)) {
    316.             printf("SQLFreeHandle failed");
    317.             goto exit;
    318.         }
    319.     }
    322. exit:
    323.     (void) printf ("\nComplete.\n");
    325.     // Connection
    326.     if (hdbc != SQL_NULL_HDBC) {
    327.         SQLDisconnect(hdbc);
    328.         SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    329.     }
    331.     // Environment
    332.     if (henv != SQL_NULL_HENV)
    333.         SQLFreeHandle(SQL_HANDLE_ENV, henv);
    335. }