示例：常用功能和批量绑定
常用功能示例代码
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. long 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. }
我的书签
添加书签
移除书签
