更新时间：2024-02-29 GMT+08:00
示例：常用功能和批量绑定
- 在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
常用功能示例代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
// 此示例演示如何通过ODBC方式获取GaussDB中的数据。 // DBtest.c (compile with: libodbc.so) #include <stdlib.h> #include <stdio.h> #include <sqlext.h> #ifdef WIN32 #include <windows.h> #endif 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, 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); } |
批量绑定示例代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 |
/********************************************************************** * 请在数据源中打开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; int batchCount = 1000; // 批量绑定的数据量 SQLLEN rowsCount = 0; int ignoreCount = 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; int i; 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 (int 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 (int 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; } |
父主题： 基于ODBC开发
