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;
}

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