C++使用CLI连接DB2

 

<!--推荐博文-->

 

 

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.chinaunix.net/uid-7454512-id-3269938.html