C++使用CLI连接DB2

pengjunling

 
<!--推荐博文--> 
DB2调用层接口(DB2 Call Level Interface简称CLI)是DB2数据库访问的基础接口。

下面例子依次执行了

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

示例代码：
cliTest.cpp
　　点击(此处)折叠或打开

• #include <stdio.h>
  
• #include <stdlib.h>
  
• #include <sqlcli.h>
  
• #include <sqlcli1.h>
  
• #include <sqlenv.h>
  
• #include <sqlda.h>
  
• #include <sqlca.h>
  
• #include <string.h>
  
• using namespace std;
  
• 
  
• struct result
  
• {
  
• char ename[50];
  
• char cname[100];
  
• int ename_len;
  
• int cname_len;
  
• 
  
• result()
  
• {
  
• memset(ename, '\0', sizeof(ename));
  
• memset(cname, '\0', sizeof(cname));
  
• }
  
• };
  
• 
  
• int main()
  
• {
  
• SQLRETURN cliRC = SQL_SUCCESS;
  
• SQLHANDLE henv = SQL_NULL_HENV;
  
• SQLHANDLE hdbc = SQL_NULL_HDBC;
  
• SQLHANDLE hstmt = SQL_NULL_HSTMT;
  
• 
  
• //获取环境句柄
  
• cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  
• //获取连接句柄
  
• cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  
• cliRC = SQLSetConnectAttr(hdbc,
  
• SQL_ATTR_AUTOCOMMIT,
  
• (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
  
• SQL_IS_INTEGER);
  
• cliRC = SQLConnect(hdbc,
  
• (SQLCHAR *)"mydb",
  
• SQL_NTS,
  
• (SQLCHAR *)"user",
  
• SQL_NTS,
  
• (SQLCHAR *)"passwd",
  
• SQL_NTS);
  
• if (cliRC != SQL_SUCCESS)
  
• {
  
• cout<<"connect fail"<<endl;
  
• }
  
• 
  
• //设置连接
  
• cliRC = SQLSetConnection(hdbc);
  
• 
  
• //获取操作句柄
  
• SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  
• 
  
• cliRC = SQLSetStmtAttr(hstmt,
  
• SQL_ATTR_DEFERRED_PREPARE,
  
• (SQLPOINTER)SQL_DEFERRED_PREPARE_ON,
  
• SQL_IS_INTEGER);
  
• 
  
• cliRC = SQLSetStmtAttr(hstmt, SQL_ATTR_BLOCK_FOR_NROWS, (SQLPOINTER)100, SQL_IS_INTEGER);
  
• if (cliRC == SQL_SUCCESS)
  
• {
  
• cout<<"setStmtAttr"<<endl;
  
• }
  
• 
  
• //查询语句
  
• const char * strSQL = "select col1, col2 from table1 ";
  
• 
  
• cliRC = SQLExecDirect(hstmt, (SQLCHAR*)strSQL, SQL_NTS);
  
• if (cliRC == SQL_SUCCESS)
  
• {
  
• cout<<"SQL execute successfully"<<endl;
  
• }
  
• SQLSMALLINT ulColumns = 0;
  
• 
  
• //获取列数
  
• cliRC = SQLNumResultCols(hstmt, &ulColumns);
  
• if (cliRC == SQL_SUCCESS)
  
• {
  
• cout<<"SQLNumResultCols num="<< ulColumns<<endl;
  
• }
  
• 
  
• // SQLCHAR szName[50] = "";
  
• // SQLSMALLINT cbNameLen = 0;
  
• // SQLSMALLINT iType = 0;
  
• // SQLUINTEGER ulColumnSize = 0;
  
• // SQLSMALLINT iScale = 0;
  
• 
  
• // cliRC = SQLDescribeCol(hstmt, (SQLSMALLINT)(0 + 1), szName, 32, &cbNameLen, &iType, &ulColumnSize, &iScale, NULL);
  
• 
  
• // if (cliRC == SQL_SUCCESS)
  
• // {
  
• // printf("SQLDescribeCol ulColumnSize=[%d]\n", ulColumnSize);
  
• // }
  
• 
  
• //获取查询结果
  
• SQLPOINTER rgbValue;
  
• SQLINTEGER aaa = 0;
  
• SQLINTEGER *pcbValue = &aaa;
  
• char temp[100] = "";
  
• rgbValue = temp;
  
• 
  
• struct result rst;
  
• 
  
• cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, rst.ename, sizeof(rst.ename), &rst.ename_len);
  
• cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, rst.cname, sizeof(rst.cname), &rst.cname_len);
  
• 
  
• while(SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
  
• {
  
• cout<<"ename="<<rst.ename<<", cname="<< rst.cname<<endl;
  
• }
  
• 
  
• //************************************INSERT 操作 ***************************************
  
• const char *insert = " insert into table1 (col1, col2) values('testcli', 'cli')";
  
• cliRC = SQLExecDirect(hstmt, (SQLCHAR*)insert, SQL_NTS);
  
• if (cliRC != SQL_SUCCESS)
  
• {
  
• printf("exec fail \n");
  
• }
  
• 
  
• //成功操作行数
  
• SQLLEN ulRowEffected = 0;
  
• cliRC = SQLRowCount(hstmt, &ulRowEffected);
  
• cout<<"ulRowEffected = "<<ulRowEffected<<endl;
  
• 
  
• //结束会话
  
• cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
  
• 
  
• //释放资源
  
• cliRC = SQLFreeStmt(hstmt, SQL_UNBIND);
  
• cliRC = SQLFreeStmt(hstmt, SQL_CLOSE);
  
• 
  
• return 0;
  
• }
  

　　源自：http://blog.iyunv.com/uid-7454512-id-3269938.html