示例
常用功能示例代码
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. #include "util.c"
15. void Exec(SQLHDBC hdbc, SQLCHAR* sql)
16. {
17. SQLRETURN retcode; // Return status
18. SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
19. SQLCHAR loginfo[2048];
21. // Allocate Statement Handle
22. retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
23. CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
24. hstmt, SQL_HANDLE_STMT);
26. // Prepare Statement
27. retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
28. sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
29. CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
31. // Execute Statement
32. retcode = SQLExecute(hstmt);
33. sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
34. CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
36. // Free Handle
37. retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
38. sprintf((char*)loginfo, "SQLFreeHandle stmt log: %s", (char*)sql);
39. CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
40. }
42. int main ()
43. {
44. SQLHENV henv = SQL_NULL_HENV;
45. SQLHDBC hdbc = SQL_NULL_HDBC;
46. int batchCount = 1000;
47. SQLLEN rowsCount = 0;
48. int ignoreCount = 0;
50. SQLRETURN retcode;
51. SQLCHAR dsn[1024] = {'\0'};
52. SQLCHAR loginfo[2048];
54. // 交互获取数据源名称
55. getStr("Please input your DSN", (char*)dsn, sizeof(dsn), 'N');
56. // 交互获取批量绑定的数据量
57. getInt("batchCount", &batchCount, 'N', 1);
58. do
59. {
60. // 交互获取批量绑定的数据中，不要入库的数据量
61. getInt("ignoreCount", &ignoreCount, 'N', 1);
62. if (ignoreCount > batchCount)
63. {
64. printf("ignoreCount(%d) should be less than batchCount(%d)\n", ignoreCount, batchCount);
65. }
66. }while(ignoreCount > batchCount);
68. retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
69. CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
70. henv, SQL_HANDLE_ENV);
72. // Set ODBC Verion
73. retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
74. (SQLPOINTER*)SQL_OV_ODBC3, 0);
75. CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
76. henv, SQL_HANDLE_ENV);
78. // Allocate Connection
79. retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
80. CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
81. henv, SQL_HANDLE_DBC);
83. // Set Login Timeout
84. retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
85. CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
86. hdbc, SQL_HANDLE_DBC);
88. // Set Auto Commit
89. retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
90. (SQLPOINTER)(1), 0);
91. CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)",
92. hdbc, SQL_HANDLE_DBC);
94. // Connect to DSN
95. sprintf(loginfo, "SQLConnect(DSN:%s)", dsn);
96. retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
97. (SQLCHAR*) NULL, 0, NULL, 0);
98. CHECK_ERROR(retcode, loginfo, hdbc, SQL_HANDLE_DBC);
100. // init table info.
101. Exec(hdbc, "drop table if exists test_odbc_batch_insert");
102. Exec(hdbc, "create table test_odbc_batch_insert(id int primary key, col varchar2(50))");
104. // 下面的代码根据用户输入的数据量，构造出将要入库的数据:
105. {
106. SQLRETURN retcode;
107. SQLHSTMT hstmtinesrt = SQL_NULL_HSTMT;
108. int i;
109. SQLCHAR *sql = NULL;
110. SQLINTEGER *ids = NULL;
111. SQLCHAR *cols = NULL;
112. SQLLEN *bufLenIds = NULL;
113. SQLLEN *bufLenCols = NULL;
114. SQLUSMALLINT *operptr = NULL;
115. SQLUSMALLINT *statusptr = NULL;
116. SQLULEN process = 0;
118. // 这里是按列构造，每个字段的内存连续存放在一起。
119. ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount);
120. cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50);
121. // 这里是每个字段中，每一行数据的内存长度。
122. bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount);
123. bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount);
124. // 该行是否需要被处理，SQL_PARAM_IGNORE 或 SQL_PARAM_PROCEED
125. operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount);
126. memset(operptr, 0, sizeof(operptr[0]) * batchCount);
127. // 该行的处理结果。
128. // 注：由于数据库中处理方式是同一语句隶属同一事务中，所以如果出错，那么待处理数据都将是出错的，并不会部分入库。
129. statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount);
130. memset(statusptr, 88, sizeof(statusptr[0]) * batchCount);
132. if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds)
133. {
134. fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
135. goto exit;
136. }
138. for (int i = 0; i < batchCount; i++)
139. {
140. ids[i] = i;
141. sprintf(cols + 50 * i, "column test value %d", i);
142. bufLenIds[i] = sizeof(ids[i]);
143. bufLenCols[i] = strlen(cols + 50 * i);
144. operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED;
145. }
147. // Allocate Statement Handle
148. retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt);
149. CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
150. hstmtinesrt, SQL_HANDLE_STMT);
152. // Prepare Statement
153. sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)";
154. retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS);
155. sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
156. CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
158. retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount));
159. CHECK_ERROR(retcode, "SQLSetStmtAttr", hstmtinesrt, SQL_HANDLE_STMT);
161. retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds);
162. CHECK_ERROR(retcode, "SQLBindParameter for id", hstmtinesrt, SQL_HANDLE_STMT);
164. retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
165. CHECK_ERROR(retcode, "SQLBindParameter for cols", hstmtinesrt, SQL_HANDLE_STMT);
167. retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
168. CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAMS_PROCESSED_PTR", hstmtinesrt, SQL_HANDLE_STMT);
170. retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount);
171. CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAM_STATUS_PTR", hstmtinesrt, SQL_HANDLE_STMT);
173. retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount);
174. CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAM_OPERATION_PTR", hstmtinesrt, SQL_HANDLE_STMT);
176. retcode = SQLExecute(hstmtinesrt);
177. sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
178. CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
180. retcode = SQLRowCount(hstmtinesrt, &rowsCount);
181. CHECK_ERROR(retcode, "SQLRowCount execution", hstmtinesrt, SQL_HANDLE_STMT);
183. if (rowsCount != (batchCount - ignoreCount))
184. {
185. sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
186. CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
187. }
188. else
189. {
190. sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
191. CHECK_ERROR(SQL_SUCCESS, loginfo, NULL, SQL_HANDLE_STMT);
192. }
194. // check row number returned
195. if (rowsCount != process)
196. {
197. sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
198. CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
199. }
200. else
201. {
202. sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
203. CHECK_ERROR(SQL_SUCCESS, loginfo, NULL, SQL_HANDLE_STMT);
204. }
206. for (int i = 0; i < batchCount; i++)
207. {
208. if (i < ignoreCount)
209. {
210. if (statusptr[i] != SQL_PARAM_UNUSED)
211. {
212. sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]);
213. CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
214. }
215. }
216. else if (statusptr[i] != SQL_PARAM_SUCCESS)
217. {
218. sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]);
219. CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
220. }
221. }
223. retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt);
224. sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt");
225. CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
226. }
229. exit:
230. printf ("\nComplete.\n");
232. // Connection
233. if (hdbc != SQL_NULL_HDBC) {
234. SQLDisconnect(hdbc);
235. SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
236. }
238. // Environment
239. if (henv != SQL_NULL_HENV)
240. SQLFreeHandle(SQL_HANDLE_ENV, henv);
242. return 0;
243. }
我的书签
添加书签
移除书签
