C++连接Oracle/DB2/SQL Server

调用OCI连接Oracle

Oracle Call Interface简称OCI，是Oracle数据库访问的基础接口。

下面的例子依次执行了：

初始化OCI环境

连接服务器

连接数据库

创建会话

执行查询

执行新增

断开会话

断开服务器

释放资源

示例代码：

#include <oci.h>#include <iostream>#include <string>#include <string.h>#include <stdlib.h>using namespace std;//存放查询数据的结构体struct result{    char ename[20];    char cname[20];    result()    {        memset(ename, '\0', sizeof(ename));        memset(cname, '\0', sizeof(cname));    }}; int main(){    // 初始化 OCI 环境句柄指针    OCIEnv *envhpp = NULL;    // 初始化服务器句柄    OCIServer *servhpp = NULL;    // 用于捕获 OCI 错误信息    OCIError *errhpp = NULL;    // 初始化会话句柄    OCISession *usrhpp = NULL;    // 初始化服务上下文句柄    OCISvcCtx *svchpp = NULL;    // 初始化表达式句柄    OCIStmt *stmthpp = NULL;     string server="mydb";     // 创建 OCI 环境 , 并设置环境句柄。    sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);    if (swResult != OCI_SUCCESS && swResult != OCI_SUCCESS_WITH_INFO)    {        cout << "Oracle environment initialization error!" << endl;        exit(1);    }    cout << "Oracle environment initialization success!" << endl;     // 创建错误句柄    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);     // 创建服务句柄    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);     // 连接服务器，如果失败则获取错误码    if (OCIServerAttach(servhpp, errhpp, (text *)server.c_str(), strlen(server.c_str()), 0) != OCI_SUCCESS)    {        int errcno;        char errbuf[512] = "";        sb4 errcode;         // 获取错误指针和 OCI 错误代码        OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);        errcno = errcode;         cout << "Oracle server attach error:" << errbuf << endl;        OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV);        OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER);        OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);        exit(1);    }    cout << "Oracle server attach success!"<< endl;     /***************** 连接数据库 ****************/    string user = "user";    string pas = "passwd";    errhpp = NULL;     // 创建错误句柄    (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);    // 创建服务上下文句柄    (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0);    // 设置属性    (void) OCIAttrSet((dvoid *)svchpp, OCI_HTYPE_SVCCTX, (dvoid *)servhpp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhpp);    // 创建用户连接句柄    (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0);    // 设置用户名、密码    (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp);    (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pas.c_str(), (ub4)strlen(pas.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp);     // 创建会话连接    if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS)    {        int errcno;        char errbuf[512]={'\0'};        sb4 errcode;                // 获取错误指针和 OCI 错误代码        OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);        errcno = errcode;        cout << "User session error:" << errbuf << endl;        OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);        OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION);        OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX);        exit(1);     }    cout << "user session success!" << endl;        (void) OCIAttrSet((dvoid *)svchpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)usrhpp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhpp);     /*************** 执行 查询SQL 语句 ******************/    errhpp = NULL;     // 创建一个表达式句柄    if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)    {        cout << "Create STMT error !" << endl;        exit(1);    }    cout << "Create stmt success !" << endl;        // 创建错误句柄    OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);        // Select语句    char sql[255] = "select col1, col2 from table1 ";        if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)    {         cout << "Create prepare error!" << sql << endl;         exit(1);    }    cout << "Create prepare success!" << endl;     /********* 绑定参数 ***********/    // 申请绑定字段的句柄    OCIDefine *bhp1 = NULL;    OCIDefine *bhp2 = NULL;        // 存放数据的结构    struct result rst;        // 指定提取数据长度    ub2 datalen = 0;    // 定义指示器变量 , 用于取可能存在空值的字段    char isnul[6] = "";    // 定义输出变量 ,    OCIDefineByPos(stmthpp, &bhp1, errhpp, 1, (dvoid *)&rst.ename, sizeof(rst.ename), SQLT_CHR, NULL, &datalen, NULL, OCI_DEFAULT);    OCIDefineByPos(stmthpp, &bhp2, errhpp, 2, (dvoid *)&rst.cname, sizeof(rst.cname), SQLT_STR, NULL, &datalen, NULL, OCI_DEFAULT);     // 获取 SQL 语句类型    ub2 stmt_type;    OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, errhpp);        // 执行 SQL 语句    OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)0, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);     // 获取查询信息    int rows_fetched;    do    {        cerr << rst.ename<< " ";        cerr << rst.cname<< " \n";    }    while(OCIStmtFetch2(stmthpp, errhpp, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT) != OCI_NO_DATA);     // 获得记录条数    OCIAttrGet((CONST void *)stmthpp, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched), OCI_ATTR_ROW_COUNT, errhpp);    cout << " rows :" << rows_fetched << endl;     /*************** 执行 新增SQL 语句 ******************/    if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)    {        cout << "Create STMT error !" << endl;        exit(1);    }    cout << "Create stmt success !" << endl;     OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);     // Insert语句    char sql2[255] = "insert into table1 (col1, col2) values('testoci', 'testoci')";        // 准备Sql语句    if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql2, (ub4)strlen(sql2), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)    {         cout << "Create prepare error!" << sql2 << endl;         exit(1);    }    cout << "Create prepare success!" << endl;        // 执行SQL 语句    OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);    // 断开用户会话    OCILogoff(svchpp, errhpp);        // 断开服务器连接    OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);        // 释放资源    OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);    OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX);    OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER);    OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR);     return 0;}

在AIX 5.0环境下编译指令：

xlC -q64 -I/ora10g/app/oracle/product/10.2.0/rdbms/public -L/ora10g/app/oracle/product/10.2.0/lib -lclntsh  -o ociTest ociTest.cpp

执行：

./ociTest

输出：
Oracle environment initialization success!
Oracle server attach success!
user session success!
Create stmt success !
Create prepare success!
    
col1  col2  
 rows :1
Create stmt success !

Create prepare success!

调用CLI连接DB2

DB2 Call Level Interface简称CLI ，是DB2数据库访问的基础接口。

下面例子依次执行了：

连接DB2数据库

创建操作句柄

查询操作

新增操作

断开连接

释放资源

示例代码：

#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

使用OleDB连接SQL Server

下面例子依次调用了：

初始化环境

连接SQL Server

执行查询

关闭连接

示例代码：

#include "stdafx.h"#include <windows.h>#include <sys\timeb.h>#include <time.h>#include <stdio.h>#include <iostream>#include <map>#include <atldbcli.h>#include <atlconv.h>using namespace std; typedef struct result{    char ename[19];    char cname[100];    DBSTATUS dwEnameStatus;    DBSTATUS dwCnameStatus;} RESULT; int _tmain(int argc, _TCHAR* argv[]){    HRESULT hr;    CDataSource m_ds;    CSession m_session;     //连接串指定数据库，用户名，密码    char szConnectionString[512] = "Provider=SQLNCLI10.1;Data Source=10.2.0.10;Initial Catalog=MYDB;User ID=user;Password=passwd;Application Name=SQLSERVER;MARS Connection=True";     ::CoInitialize(NULL);     //连接数据库    hr = m_ds.OpenFromInitializationString(CA2W(szConnectionString));        //打开会话    hr = m_session.Open(m_ds);    DBORDINAL ulColumns = 0;    CDBPropSet dbPropSet(DBPROPSET_ROWSET);    CCommand<CManualAccessor, CRowset, CNoMultipleResults> m_command;    hr = m_command.CreateCommand(m_session);     CComPtr<ICommandText> spCommandText;    hr = m_command.m_spCommand->QueryInterface(&spCommandText);    USES_CONVERSION;        //查询语句    const TCHAR *cmd = _T(" select col1, col2 from table1 ");     hr = spCommandText->SetCommandText(DBGUID_SQL, T2COLE(cmd));     dbPropSet.AddProperty(DBPROP_ISequentialStream, true);     hr = m_command.Open(&dbPropSet, NULL, false);     DBCOLUMNINFO *m_pColumnInfo;    LPOLESTR m_pStrings;     //获取列信息    hr = m_command.GetColumnInfo(&ulColumns        , &m_pColumnInfo        , &m_pStrings);     BYTE * m_pRowInfoBuff;        //初始化列绑定字段    DBCOUNTITEM cbRowSize = (m_pColumnInfo[0].ulColumnSize + 1) * sizeof(CHAR);    DBCOUNTITEM cbRowSize2 = (m_pColumnInfo[1].ulColumnSize + 1) * sizeof(CHAR);     RESULT rst;     int bufferSize = sizeof(rst.ename) + sizeof(rst.cname) +1;    m_pRowInfoBuff = new BYTE[bufferSize];     hr = m_command.CreateAccessor(ulColumns, m_pRowInfoBuff, (DBLENGTH)bufferSize);     //DBORDINAL* pLength = (DBORDINAL*)(m_pRowInfoBuff + cbRowSize);    //DBSTATUS* pStatus = (DBSTATUS*)(m_pRowInfoBuff + cbRowSize);    DBTYPE dbType=DBTYPE_STR;    DBLENGTH dbLength = (m_pColumnInfo[0].ulColumnSize + 1) * sizeof(TCHAR);    DBLENGTH dbLength2 = (m_pColumnInfo[1].ulColumnSize + 1) * sizeof(TCHAR);     //m_command.AddBindEntry(1, dbType, dbLength, pValue, pLength, pStatus);    m_command.AddBindEntry(1, dbType, dbLength, rst.ename, NULL, &rst.dwEnameStatus);    m_command.AddBindEntry(2, dbType, dbLength2, rst.cname, NULL, &rst.dwCnameStatus);     hr = m_command.Bind();     //获取查询信息    while(S_OK == m_command.MoveNext())    {        printf(" ename = [%s] ,cname = [%s]\n", rst.ename, rst.cname);    }     //关闭连接    m_command.Close();    m_session.Close();    m_ds.Close();     ::CoUninitialize();     ::getchar(); }