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

常用功能示例代码

  1. // 此示例演示如何通过ODBC方式获取openGauss中的数据。
  2. // DBtest.c (compile with: libodbc.so)   
  3. #include <stdlib.h> 
  4. #include <stdio.h> 
  5. #include <sqlext.h>
  6. #ifdef WIN32
  7. #include <windows.h>
  8. #endif 
  9. SQLHENV       V_OD_Env;        // Handle ODBC environment 
  10. SQLHSTMT      V_OD_hstmt;      // Handle statement 
  11. SQLHDBC       V_OD_hdbc;       // Handle connection     
  12. char          typename[100];
  13. SQLINTEGER    value = 100;
  14. SQLINTEGER    V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id;
  15. int main(int argc,char *argv[]) 
  16. {         
  17.       // 1. 申请环境句柄       
  18.       V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);     
  19.       if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))        
  20.       {           
  21.            printf("Error AllocHandle\n");           
  22.            exit(0);        
  23.       } 
  24.       // 2. 设置环境属性（版本信息）         
  25.       SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);      
  26.       // 3. 申请连接句柄        
  27.       V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);     
  28.       if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))      
  29.       {                     
  30.            SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);          
  31.            exit(0);       
  32.       }
  33.       // 4. 设置连接属性
  34.       SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, 0);          
  35.       // 5. 连接数据源，这里的“userName”与“password”分别表示连接数据库的用户名和用户密码，请根据实际情况修改。
  36.       // 如果odbc.ini文件中已经配置了用户名密码，那么这里可以留空（""）；但是不建议这么做，因为一旦odbc.ini权限管理不善，将导致数据库用户密码泄露。    
  37.       V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "gaussdb", SQL_NTS,  
  38.                            (SQLCHAR*) "userName", SQL_NTS,  (SQLCHAR*) "password", SQL_NTS);        
  39.       if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))      
  40.       {           
  41.           printf("Error SQLConnect %d\n",V_OD_erg);            
  42.           SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);       
  43.           exit(0);        
  44.       }     
  45.       printf("Connected !\n"); 
  46.       // 6. 设置语句属性
  47.       SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0);
  48.       // 7. 申请语句句柄
  49.       SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);       
  50.       // 8. 直接执行SQL语句。
  51.       SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS);
  52.       SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS);
  53.       SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,li)",SQL_NTS);
  54.       // 9. 准备执行
  55.       SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS); 
  56.       // 10. 绑定参数
  57.       SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,
  58.                        &value,0,NULL);
  59.       // 11. 执行准备好的语句
  60.       SQLExecute(V_OD_hstmt);
  61.       SQLExecDirect(V_OD_hstmt,"select id from testtable",SQL_NTS);
  62.       // 12. 获取结果集某一列的属性
  63.       SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL);                 
  64.       printf("SQLColAtrribute %s\n",typename);
  65.       // 13. 绑定结果集
  66.       SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150,
  67.                 (SQLLEN *)&V_OD_err);
  68.       // 14. 通过SQLFetch取结果集中数据
  69.       V_OD_erg=SQLFetch(V_OD_hstmt);
  70.       // 15. 通过SQLGetData获取并返回数据。
  71.       while(V_OD_erg != SQL_NO_DATA)
  72.       {
  73.           SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL);
  74.           printf("SQLGetData ----ID = %d\n",V_OD_id);
  75.           V_OD_erg=SQLFetch(V_OD_hstmt);
  76.       };
  77.       printf("Done !\n");
  78.       // 16. 断开数据源连接并释放句柄资源
  79.       SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);    
  80.       SQLDisconnect(V_OD_hdbc);         
  81.       SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);       
  82.       SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);  
  83.       return(0);
  84.  }

批量绑定示例代码

  1. /**********************************************************************
  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);
  112.     if (!SQL_SUCCEEDED(retcode)) {
  113.         printf("SQLSetConnectAttr failed");
  114.         goto exit;
  115.     }
  118.     // Connect to DSN
  119.     // gaussdb替换成用户所使用的数据源名称
  120.     sprintf(loginfo, "SQLConnect(DSN:%s)", dsn);
  121.     retcode = SQLConnect(hdbc, (SQLCHAR*) "gaussdb", SQL_NTS,
  122.                                (SQLCHAR*) NULL, 0, NULL, 0);
  124.     if (!SQL_SUCCEEDED(retcode)) {
  125.         printf("SQLConnect failed");
  126.         goto exit;
  127.     }
  129.     // init table info.
  130.     Exec(hdbc, "drop table if exists test_odbc_batch_insert");
  131.     Exec(hdbc, "create table test_odbc_batch_insert(id int primary key, col varchar2(50))");
  133.     // 下面的代码根据用户输入的数据量，构造出将要入库的数据:
  134.     {
  135.         SQLRETURN retcode; 
  136.         SQLHSTMT hstmtinesrt = SQL_NULL_HSTMT;
  137.         int          i;
  138.         SQLCHAR      *sql = NULL;
  139.         SQLINTEGER   *ids  = NULL;
  140.         SQLCHAR      *cols = NULL;
  141.         SQLLEN       *bufLenIds = NULL;
  142.         SQLLEN       *bufLenCols = NULL;
  143.         SQLUSMALLINT *operptr = NULL;
  144.         SQLUSMALLINT *statusptr = NULL;
  145.         SQLULEN      process = 0;
  147.         // 这里是按列构造，每个字段的内存连续存放在一起。
  148.         ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount);
  149.         cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50);
  150.         // 这里是每个字段中，每一行数据的内存长度。
  151.         bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount);
  152.         bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount);
  153.         // 该行是否需要被处理，SQL_PARAM_IGNORE 或 SQL_PARAM_PROCEED
  154.         operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount);
  155.         memset(operptr, 0, sizeof(operptr[0]) * batchCount);
  156.         // 该行的处理结果。
  157.         // 注：由于数据库中处理方式是同一语句隶属同一事务中，所以如果出错，那么待处理数据都将是出错的，并不会部分入库。
  158.         statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount);
  159.         memset(statusptr, 88, sizeof(statusptr[0]) * batchCount);
  161.         if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds)
  162.         {
  163.             fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
  164.             goto exit;
  165.         }
  167.         for (int i = 0; i < batchCount; i++)
  168.         {
  169.             ids[i] = i;
  170.             sprintf(cols + 50 * i, "column test value %d", i);
  171.             bufLenIds[i] = sizeof(ids[i]);
  172.             bufLenCols[i] = strlen(cols + 50 * i);
  173.             operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED;
  174.         }
  176.         // Allocate Statement Handle
  177.         retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt);
  179.         if (!SQL_SUCCEEDED(retcode)) {
  180.             printf("SQLAllocHandle failed");
  181.             goto exit;
  182.         }
  184.         // Prepare Statement
  185.         sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)";
  186.         retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS);
  187.         sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
  189.         if (!SQL_SUCCEEDED(retcode)) {
  190.             printf("SQLPrepare failed");
  191.             goto exit;
  192.         }
  194.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount));
  196.         if (!SQL_SUCCEEDED(retcode)) {
  197.             printf("SQLSetStmtAttr failed");
  198.             goto exit;
  199.         }
  201.         retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds);
  203.         if (!SQL_SUCCEEDED(retcode)) {
  204.             printf("SQLBindParameter failed");
  205.             goto exit;
  206.         }
  208.         retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
  210.         if (!SQL_SUCCEEDED(retcode)) {
  211.             printf("SQLBindParameter failed");
  212.             goto exit;
  213.         }
  215.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
  217.         if (!SQL_SUCCEEDED(retcode)) {
  218.             printf("SQLSetStmtAttr failed");
  219.             goto exit;
  220.         }
  222.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount);
  224.         if (!SQL_SUCCEEDED(retcode)) {
  225.             printf("SQLSetStmtAttr failed");
  226.             goto exit;
  227.         }
  229.         retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount);
  231.         if (!SQL_SUCCEEDED(retcode)) {
  232.             printf("SQLSetStmtAttr failed");
  233.             goto exit;
  234.         }
  236.         retcode = SQLExecute(hstmtinesrt);
  237.         sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
  239.         if (!SQL_SUCCEEDED(retcode)) {
  240.             printf("SQLExecute(hstmtinesrt) failed");
  241.             goto exit;
  243.         retcode = SQLRowCount(hstmtinesrt, &rowsCount);
  245.         if (!SQL_SUCCEEDED(retcode)) {
  246.             printf("SQLRowCount failed");
  247.             goto exit;
  248.         }
  250.         if (rowsCount != (batchCount - ignoreCount))
  251.         {
  252.             sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
  254.             if (!SQL_SUCCEEDED(retcode)) {
  255.                 printf("SQLExecute failed");
  256.                 goto exit;
  257.             }
  258.         }
  259.         else
  260.         {
  261.             sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
  263.             if (!SQL_SUCCEEDED(retcode)) {
  264.                 printf("SQLExecute failed");
  265.                 goto exit;
  266.             }
  267.         }
  269.         // check row number returned
  270.         if (rowsCount != process)
  271.         {
  272.             sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
  274.             if (!SQL_SUCCEEDED(retcode)) {
  275.                 printf("SQLExecute failed");
  276.                 goto exit;
  277.             }
  278.         }
  279.         else
  280.         {
  281.             sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
  283.             if (!SQL_SUCCEEDED(retcode)) {
  284.                 printf("SQLExecute failed");
  285.                 goto exit;
  286.             }
  287.         }
  289.         for (int i = 0; i < batchCount; i++)
  290.         {
  291.             if (i < ignoreCount)
  292.             {
  293.                 if (statusptr[i] != SQL_PARAM_UNUSED)
  294.                 {
  295.                     sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]);
  297.                     if (!SQL_SUCCEEDED(retcode)) {
  298.                         printf("SQLExecute failed");
  299.                         goto exit;
  300.                     }
  301.                 }
  302.             }
  303.             else if (statusptr[i] != SQL_PARAM_SUCCESS)
  304.             {
  305.                 sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]);
  307.                 if (!SQL_SUCCEEDED(retcode)) {
  308.                     printf("SQLExecute failed");
  309.                     goto exit;
  310.                 }
  311.             }
  312.         }
  314.         retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt);
  315.         sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt");
  317.         if (!SQL_SUCCEEDED(retcode)) {
  318.             printf("SQLFreeHandle failed");
  319.             goto exit;
  320.         }
  321.     }
  324. exit:
  325.     (void) printf ("\nComplete.\n");
  327.     // Connection
  328.     if (hdbc != SQL_NULL_HDBC) {
  329.         SQLDisconnect(hdbc);
  330.         SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  331.     }
  333.     // Environment
  334.     if (henv != SQL_NULL_HENV)
  335.         SQLFreeHandle(SQL_HANDLE_ENV, henv);
  337.     return 0;
  338. }