GaussDB-示例：常用功能和批量绑定
-
在Windows环境下编译ODBC应用代码的命令示例：
gcc odbctest.c -o odbctest -lodbc32执行命令为：
./odbctest.exe -
在Linux环境下编译ODBC应用代码的命令示例：
gcc odbctest.c -o odbctest -lodbc执行命令为：
./odbctest -
如果编译找不到sql.h或者API接口，尝试手动连接unixodbc的头文件和动态库，即：
gcc -I /home/omm/unixodbc/include -L /home/omm/unixodbc/lib odbctest.c -o odbctest -lodbc
常用功能示例代码
| ``` // 此示例演示如何通过ODBC方式获取GaussDB中的数据。 // DBtest.c (compile with: libodbc.so) #ifdef WIN32 #include <windows.h> #endif #include <stdio.h> #include <stdlib.h> #include <sql.h> #include <sqlext.h> SQLHENV V_OD_Env; // Handle ODBC environment SQLHSTMT V_OD_hstmt; // Handle statement SQLHDBC V_OD_hdbc; // Handle connection char typename[100]; SQLINTEGER value = 100; SQLINTEGER V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id; int main(int argc,char argv[]) { // 1. 申请环境句柄 V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHandle\n"); exit(0); } // 2. 设置环境属性（版本信息） SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void)SQL_OV_ODBC3, 0); // 3. 申请连接句柄 V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } // 本示例以用户名和密码保存在环境变量中为例，运行本示例前请先在本地环境中设置环境变量(环境变量名称请根据自身情况进行设置)EXAMPLE_USERNAME_ENV和EXAMPLE_PASSWORD_ENV。 char userName; userName = getenv("EXAMPLE_USERNAME_ENV"); char password; password = getenv("EXAMPLE_PASSWORD_ENV"); // 4. 设置连接属性 SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER )SQL_AUTOCOMMIT_ON, 0); // 5. 连接数据源，这里的userName与password分别表示连接数据库的用户名和用户密码。 // 如果odbc.ini文件中已经配置了用户名密码，那么这里可以留空（""）；但是不建议这么做，因为一旦odbc.ini权限管理不善，将导致数据库用户密码泄露。 V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR) "gaussdb", SQL_NTS, (SQLCHAR) userName, SQL_NTS, (SQLCHAR) password, SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SQLConnect %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Connected !\n"); // 6. 设置语句属性 SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0); // 7. 申请语句句柄 SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt); // 8. 直接执行SQL语句。 SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS); SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS); SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,'li')",SQL_NTS); // 9. 准备执行 SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS); // 10. 绑定参数 SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0, &value,0,NULL); // 11. 执行准备好的语句 SQLExecute(V_OD_hstmt); SQLExecDirect(V_OD_hstmt,"select c_customer_sk from customer_t1",SQL_NTS); // 12. 获取结果集某一列的属性 SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL); printf("SQLColAtrribute %s\n",typename); // 13. 绑定结果集 SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150, (SQLLEN *)&V_OD_err); // 14. 通过SQLFetch取结果集中数据 V_OD_erg=SQLFetch(V_OD_hstmt); // 15. 通过SQLGetData获取并返回数据。 while(V_OD_erg != SQL_NO_DATA) { SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL); printf("SQLGetData ----ID = %d\n",V_OD_id); V_OD_erg=SQLFetch(V_OD_hstmt); }; printf("Done !\n"); // 16. 断开数据源连接并释放句柄资源 SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); return(0); }
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
#### 批量绑定示例代码
| ```
/********************************************************************** * 请在数据源中打开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]; // Allocate Statement Handle retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle(SQL_HANDLE_STMT) failed"); return; } // Prepare Statement 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; } // Execute Statement retcode = SQLExecute(hstmt); sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql); if (!SQL_SUCCEEDED(retcode)) { printf("SQLExecute(hstmt) failed"); return; } // Free Handle 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; } // Set ODBC Verion retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetEnvAttr failed"); goto exit; } // Allocate Connection retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle failed"); goto exit; } // Set Login Timeout retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetConnectAttr failed"); goto exit; } // Set Auto Commit retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)(1), 0); if (!SQL_SUCCEEDED(retcode)) { printf("SQLSetConnectAttr failed"); goto exit; } // Connect to DSN // GaussDB替换成用户所使用的数据源名称 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; } // init table info. 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; } // Allocate Statement Handle retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt); if (!SQL_SUCCEEDED(retcode)) { printf("SQLAllocHandle failed"); goto exit; } // Prepare Statement 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; } } // check row number returned 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"); // Connection if (hdbc != SQL_NULL_HDBC) { SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); } // Environment if (henv != SQL_NULL_HENV) SQLFreeHandle(SQL_HANDLE_ENV, henv); return 0; }
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>
