C++使用CLI连接DB2

DB2调用层接口(DB2 Call Level Interface简称CLI)是DB2数据库访问的基础接口。

下面例子依次执行了

• 连接DB2数据库
• 创建操作句柄
• 查询操作
• 新增操作
• 断开连接
• 释放资源

示例代码：
cliTest.cpp

点击(此处)折叠或打开

1. #include <stdio.h>
   
2. #include <stdlib.h>
   
3. #include <sqlcli.h>
   
4. #include <sqlcli1.h>
   
5. #include <sqlenv.h>
   
6. #include <sqlda.h>
   
7. #include <sqlca.h>
   
8. #include <string.h>
   
9. using namespace std;
   
10.  
    
11. struct result
    
12. {
    
13.     char ename[50];
    
14.     char cname[100];
    
15.     int ename_len;
    
16.     int cname_len;
    
17.     
    
18.     result()
    
19.     {
    
20.         memset(ename, '\0', sizeof(ename));
    
21.         memset(cname, '\0', sizeof(cname));
    
22.     }
    
23. };
    
24.  
    
25. int main()
    
26. {
    
27.     SQLRETURN cliRC = SQL_SUCCESS;
    
28.     SQLHANDLE henv = SQL_NULL_HENV;
    
29.     SQLHANDLE hdbc = SQL_NULL_HDBC;
    
30.     SQLHANDLE hstmt = SQL_NULL_HSTMT;
    
31.  
    
32.     //获取环境句柄
    
33.     cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    
34.     //获取连接句柄
    
35.     cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    
36.     cliRC = SQLSetConnectAttr(hdbc,
    
37.                             SQL_ATTR_AUTOCOMMIT,
    
38.                             (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
    
39.                             SQL_IS_INTEGER);
    
40.     cliRC = SQLConnect(hdbc,
    
41.                             (SQLCHAR *)"mydb",
    
42.                             SQL_NTS,
    
43.                             (SQLCHAR *)"user",
    
44.                             SQL_NTS,
    
45.                             (SQLCHAR *)"passwd",
    
46.                             SQL_NTS);
    
47.     if (cliRC != SQL_SUCCESS)
    
48.     {
    
49.         cout<<"connect fail"<<endl;
    
50.     }
    
51.  
    
52.     //设置连接
    
53.     cliRC = SQLSetConnection(hdbc);
    
54.  
    
55.     //获取操作句柄
    
56.     SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    
57.  
    
58.     cliRC = SQLSetStmtAttr(hstmt,
    
59.                             SQL_ATTR_DEFERRED_PREPARE,
    
60.                             (SQLPOINTER)SQL_DEFERRED_PREPARE_ON,
    
61.                             SQL_IS_INTEGER);
    
62.  
    
63.     cliRC = SQLSetStmtAttr(hstmt, SQL_ATTR_BLOCK_FOR_NROWS, (SQLPOINTER)100, SQL_IS_INTEGER);
    
64.     if (cliRC == SQL_SUCCESS)
    
65.     {
    
66.         cout<<"setStmtAttr"<<endl;
    
67.     }
    
68.  
    
69.     //查询语句
    
70.     const char * strSQL = "select col1, col2 from table1 ";
    
71.  
    
72.     cliRC = SQLExecDirect(hstmt, (SQLCHAR*)strSQL, SQL_NTS);
    
73.     if (cliRC == SQL_SUCCESS)
    
74.     {
    
75.         cout<<"SQL execute successfully"<<endl;
    
76.     }
    
77.     SQLSMALLINT ulColumns = 0;
    
78.  
    
79.     //获取列数
    
80.     cliRC = SQLNumResultCols(hstmt, &ulColumns);
    
81.     if (cliRC == SQL_SUCCESS)
    
82.     {
    
83.         cout<<"SQLNumResultCols num="<< ulColumns<<endl;
    
84.     }
    
85.  
    
86.     // SQLCHAR szName[50] = "";
    
87.     // SQLSMALLINT cbNameLen = 0;
    
88.     // SQLSMALLINT iType = 0;
    
89.     // SQLUINTEGER ulColumnSize = 0;
    
90.     // SQLSMALLINT iScale = 0;
    
91.  
    
92.     // cliRC = SQLDescribeCol(hstmt, (SQLSMALLINT)(0 + 1), szName, 32, &cbNameLen, &iType, &ulColumnSize, &iScale, NULL);
    
93.  
    
94.     // if (cliRC == SQL_SUCCESS)
    
95.     // {
    
96.         // printf("SQLDescribeCol ulColumnSize=[%d]\n", ulColumnSize);
    
97.     // }
    
98.  
    
99.     //获取查询结果
    
100.     SQLPOINTER rgbValue;
     
101.     SQLINTEGER aaa = 0;
     
102.     SQLINTEGER *pcbValue = &aaa;
     
103.     char temp[100] = "";
     
104.     rgbValue = temp;
     
105.  
     
106.     struct result rst;
     
107.  
     
108.     cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, rst.ename, sizeof(rst.ename), &rst.ename_len);
     
109.     cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, rst.cname, sizeof(rst.cname), &rst.cname_len);
     
110.  
     
111.     while(SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
     
112.     {
     
113.         cout<<"ename="<<rst.ename<<", cname="<< rst.cname<<endl;
     
114.     }
     
115.  
     
116.      //************************************INSERT 操作 ***************************************
     
117.     const char *insert = " insert into table1 (col1, col2) values('testcli', 'cli')";
     
118.     cliRC = SQLExecDirect(hstmt, (SQLCHAR*)insert, SQL_NTS);
     
119.     if (cliRC != SQL_SUCCESS)
     
120.     {
     
121.         printf("exec fail \n");
     
122.     }
     
123.  
     
124.     //成功操作行数
     
125.     SQLLEN ulRowEffected = 0;
     
126.     cliRC = SQLRowCount(hstmt, &ulRowEffected);
     
127.     cout<<"ulRowEffected = "<<ulRowEffected<<endl;
     
128.  
     
129.     //结束会话
     
130.     cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
     
131.     
     
132.     //释放资源
     
133.     cliRC = SQLFreeStmt(hstmt, SQL_UNBIND);
     
134.     cliRC = SQLFreeStmt(hstmt, SQL_CLOSE);
     
135.  
     
136.     return 0;    
     
137. }

在AIX5.0环境下编译：
xlc -q64 -I${DB2_HOME}/include  -L${DB2_HOME}/lib -ldb2 -lpthread -o cliTest cliTest.cpp