程式師世界 >> 編程語言 >> C語言 >> C++ >> C++入門知識 >> C++ 連接Oracle

C++ 連接Oracle

編輯：C++入門知識

剛剛學習了C++、感覺學東西還是動手比較學得快一點！

下面是一個ADO方式連接Oracle的小程序部分代碼......

首先是Oracle的配置、在Oracle的安裝路徑下找到：Oracle\network\ADMIN\tnsnames.ora文件、配置一下連接配置

[plain]
BOSS =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = boss)
    )
)

BOSS =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = boss)
    )
)新建一個頭文件、名為CDBOperation.h：

[cpp]
#pragma once
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
class CDBOperation
{
public:
    //初始化數據庫操作需要的對象
    CDBOperation(void);
    ~CDBOperation(void);

    //連接至數據庫
    bool ConnToDB(char *ConnectionString, char *UserID, char *Password);

    //數據庫操作函數
    //查詢操作刪除以及添加
    _RecordsetPtr ExecuteWithResSQL(const char *);

private:
    void PrintErrorInfo(_com_error &);

private:
    //初始化數據庫連接、命令、記錄集
    _ConnectionPtr CreateConnPtr();
    _CommandPtr CreateCommPtr();
    _RecordsetPtr CreateRecsetPtr();

private:
    //數據庫連接需要的連接、命令操作對象
    _ConnectionPtr m_pConnection;
    _CommandPtr m_pCommand;
};

#pragma once
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
class CDBOperation
{
public:
//初始化數據庫操作需要的對象
CDBOperation(void);
~CDBOperation(void);

//連接至數據庫
bool ConnToDB(char *ConnectionString, char *UserID, char *Password);

//數據庫操作函數
//查詢操作刪除以及添加
_RecordsetPtr ExecuteWithResSQL(const char *);

private:
void PrintErrorInfo(_com_error &);

private:
//初始化數據庫連接、命令、記錄集
_ConnectionPtr CreateConnPtr();
_CommandPtr CreateCommPtr();
_RecordsetPtr CreateRecsetPtr();

private:
//數據庫連接需要的連接、命令操作對象
_ConnectionPtr m_pConnection;
_CommandPtr m_pCommand;
};新建一個c++源文件、名為CDBOperation.cpp：
[cpp]
#include "stdafx.h"
#include "DBOperation.h"
CDBOperation::CDBOperation(void)
{
    CoInitialize(NULL);
    m_pConnection = CreateConnPtr();
    m_pCommand = CreateCommPtr();
}
CDBOperation::~CDBOperation(void)
{
    m_pConnection->Close();
}
bool CDBOperation::ConnToDB(char *ConnectionString, char *UserID, char *Password)
{
    if (NULL == m_pConnection)
    {
        printf("Failed to create connection\n");
        return false;
    }
    try
    {
        HRESULT hr = m_pConnection->Open(ConnectionString, UserID, Password, NULL);
        if (TRUE == FAILED(hr))
        {
            return false;
        }
        m_pCommand->ActiveConnection = m_pConnection;
        return true;
    }
    catch(_com_error &e)
    {
        PrintErrorInfo(e);
        return false;
    }
}
_RecordsetPtr CDBOperation::ExecuteWithResSQL(const char *sql)
{
    try
    {
        m_pCommand->CommandText = _bstr_t(sql);
        _RecordsetPtr pRst = m_pCommand->Execute(NULL, NULL, adCmdText);
        return pRst;
    }
    catch(_com_error &e)
    {
        PrintErrorInfo(e);
        return NULL;
    }
}
void CDBOperation::PrintErrorInfo(_com_error &e)
{
    printf("Error infomation are as follows\n");
    printf("ErrorNo: %d\nError Message:%s\nError Source:%s\nError Description:%s\n", e.Error(), e.ErrorMessage(), (LPCTSTR)e.Source(), (LPCTSTR)e.Description());
}

_ConnectionPtr CDBOperation::CreateConnPtr()
{
    HRESULT hr;
    _ConnectionPtr connPtr;
    hr = connPtr.CreateInstance(__uuidof(Connection));
    if (FAILED(hr) == TRUE)
    {
        return NULL;
    }
    return connPtr;
}

_CommandPtr CDBOperation::CreateCommPtr()
{
    HRESULT hr;
    _CommandPtr commPtr;
    hr = commPtr.CreateInstance(__uuidof(Command));
    if (FAILED(hr) == TRUE)
    {
        return NULL;
    }
    return commPtr;
}

_RecordsetPtr CDBOperation::CreateRecsetPtr()
{
    HRESULT hr;
    _RecordsetPtr recsetPtr;
    hr = recsetPtr.CreateInstance(__uuidof(Command));
    if (FAILED(hr) ==TRUE)
    {
        return NULL;
    }
    return recsetPtr;
}

#include "stdafx.h"
#include "DBOperation.h"
CDBOperation::CDBOperation(void)
{
CoInitialize(NULL);
m_pConnection = CreateConnPtr();
m_pCommand = CreateCommPtr();
}
CDBOperation::~CDBOperation(void)
{
m_pConnection->Close();
}
bool CDBOperation::ConnToDB(char *ConnectionString, char *UserID, char *Password)
{
if (NULL == m_pConnection)
{
  printf("Failed to create connection\n");
  return false;
}
try
{
  HRESULT hr = m_pConnection->Open(ConnectionString, UserID, Password, NULL);
  if (TRUE == FAILED(hr))
  {
   return false;
  }
  m_pCommand->ActiveConnection = m_pConnection;
  return true;
}
catch(_com_error &e)
{
  PrintErrorInfo(e);
  return false;
}
}
_RecordsetPtr CDBOperation::ExecuteWithResSQL(const char *sql)
{
try
{
  m_pCommand->CommandText = _bstr_t(sql);
  _RecordsetPtr pRst = m_pCommand->Execute(NULL, NULL, adCmdText);
  return pRst;
}
catch(_com_error &e)
{
  PrintErrorInfo(e);
  return NULL;
}
}
void CDBOperation::PrintErrorInfo(_com_error &e)
{
printf("Error infomation are as follows\n");
printf("ErrorNo: %d\nError Message:%s\nError Source:%s\nError Description:%s\n", e.Error(), e.ErrorMessage(), (LPCTSTR)e.Source(), (LPCTSTR)e.Description());
}

_ConnectionPtr CDBOperation::CreateConnPtr()
{
HRESULT hr;
_ConnectionPtr connPtr;
hr = connPtr.CreateInstance(__uuidof(Connection));
if (FAILED(hr) == TRUE)
{
return NULL;
}
return connPtr;
}

_CommandPtr CDBOperation::CreateCommPtr()
{
HRESULT hr;
_CommandPtr commPtr;
hr = commPtr.CreateInstance(__uuidof(Command));
if (FAILED(hr) == TRUE)
{
return NULL;
}
return commPtr;
}

_RecordsetPtr CDBOperation::CreateRecsetPtr()
{
HRESULT hr;
_RecordsetPtr recsetPtr;
hr = recsetPtr.CreateInstance(__uuidof(Command));
if (FAILED(hr) ==TRUE)
{
return NULL;
}
return recsetPtr;
}我的代碼是放在MFC一個按鈕Click事件裡面的：

記住在處理事件的cpp文件中導入頭文件：#include "DBOperation.h"

[cpp]
CDBOperation dbOper;
    bool bConn = dbOper.ConnToDB("Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=boss", "用戶名", "密碼");
    if (false == bConn)
    {
        MessageBox((LPCTSTR)"連接數據庫出現錯誤\0",0,0);
        return;
    }

    //查詢
    _RecordsetPtr pRst;
    char sql[255] = {0};
    strcpy(sql, " select * from boss_test_table2 where rownum = 1 ");
    pRst = dbOper.ExecuteWithResSQL(sql);
    if (NULL == pRst)
    {
        MessageBox(_T("查詢數據出現錯誤！\0"),0,0);
        return;
    }
    if (pRst->adoEOF)
    {
        pRst->Close();
        MessageBox((LPCTSTR)"There is no records in this table\0",0,0);
        return;
    }
    _variant_t vSno, vName;
    while (!pRst->adoEOF)
    {
        //pRst->MoveFirst(); //記錄集指針移動到查詢結果集的前面
        vSno = pRst->GetCollect(_variant_t("U_NUMBER"));
        vName = pRst->GetCollect(_variant_t("USERS_NAME"));
        MessageBox((LPCTSTR)(_bstr_t)vSno,0,0);
        pRst->MoveNext();
    }

    strcpy(sql, "insert into boss_test_table2 (u_number, users_name, users_phone, status, customno_id) values ('0001', 'C+TTT+', '13999000000', 2, 'BPPPPPPPPPP')");
    pRst = dbOper.ExecuteWithResSQL(sql);
    if (NULL != pRst)
    {
        AfxMessageBox(_T("插入數據成功\n"));
    }
    //執行刪除語句
    sprintf(sql, "delete boss_test_table2 where u_number = '%s'", "009");
    pRst = dbOper.ExecuteWithResSQL(sql);
    if (NULL != pRst)
    {
        MessageBox(_T("刪除數據成功\0"),0,0);
    }
    //執行更新語句
    sprintf(sql, "update boss_test_table2 set users_name = '%s' ", "C++反人類、MFC反社會");
    pRst = dbOper.ExecuteWithResSQL(sql);
    if (NULL != pRst)
    {
        MessageBox(_T("更新數據成功\0"),0,0);
    }

CDBOperation dbOper;
bool bConn = dbOper.ConnToDB("Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=boss", "用戶名", "密碼");
if (false == bConn)
{
MessageBox((LPCTSTR)"連接數據庫出現錯誤\0",0,0);
return;
}

//查詢
_RecordsetPtr pRst;
char sql[255] = {0};
strcpy(sql, " select * from boss_test_table2 where rownum = 1 ");
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL == pRst)
{
  MessageBox(_T("查詢數據出現錯誤！\0"),0,0);
  return;
}
if (pRst->adoEOF)
{
  pRst->Close();
  MessageBox((LPCTSTR)"There is no records in this table\0",0,0);
  return;
}
_variant_t vSno, vName;
while (!pRst->adoEOF)
{
  //pRst->MoveFirst(); //記錄集指針移動到查詢結果集的前面
  vSno = pRst->GetCollect(_variant_t("U_NUMBER"));
  vName = pRst->GetCollect(_variant_t("USERS_NAME"));
  MessageBox((LPCTSTR)(_bstr_t)vSno,0,0);
  pRst->MoveNext();
}

strcpy(sql, "insert into boss_test_table2 (u_number, users_name, users_phone, status, customno_id) values ('0001', 'C+TTT+', '13999000000', 2, 'BPPPPPPPPPP')");
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL != pRst)
{
  AfxMessageBox(_T("插入數據成功\n"));
}
//執行刪除語句
sprintf(sql, "delete boss_test_table2 where u_number = '%s'", "009");
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL != pRst)
{
  MessageBox(_T("刪除數據成功\0"),0,0);
}
//執行更新語句
sprintf(sql, "update boss_test_table2 set users_name = '%s' ", "C++反人類、MFC反社會");
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL != pRst)
{
  MessageBox(_T("更新數據成功\0"),0,0);
}