C++操作oracle数据库
分类： Oracle数据库操作方式：可以采用ADO方式，也可以采用oracle本身提供的Proc*C/C++或者是OCCI方式操作数据库。
连接方式：可以是客户端连接、也可以是服务器端连接。
数据库配置：无论是何种连接都需要进行数据库连接的配置，一般在ORACLE_HOME下面的network/admin/tnsnames.ora文件中进行配置，如果没有此目录或者是此文件，需要自己手工添加。内容格式大致如下：
点击(此处)折叠或打开
[*] BM2D0 =
[*] (DESCRIPTION =
[*] (ADDRESS_LIST =
[*] (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))
[*] )
[*] (CONNECT_DATA =
[*] (SERVICE_NAME = BM2D0)
[*] )
[*] )
其中橄榄色可任意起名，一般在数据库连接是作为服务和用户名、密码一起确定数据库连接的参数。
第一个鲜粉色是远程oracle数据库所在服务器的IP地址，端口号一般为1521。
第二个鲜粉色是远程oracle所在主机的全局数据库名字，不能随意更改。
后两个搭配起来能够确定唯一连接对象。
客户端连接：
方式一：ADO
main.cpp
点击(此处)折叠或打开
[*] #include "DBOperation.h"
[*] #include
[*] using namespace std;
[*]
[*] void main()
[*] {
[*] CDBOperation dbOper;
[*] bool bConn = dbOper.ConnToDB("Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=xxx1", "xxx2", "xxx3");
[*] if (false == bConn)
[*] {
[*] printf("连接数据库出现错误\n");
[*] system("PAUSE");
[*] return;
[*] }
[*]
[*] _RecordsetPtr pRst;
[*]
[*] //执行查询语句
[*] //char *sql = "select * from TSTUDENT";
[*] char sql = {0};
[*] strcpy(sql, "select * from TSTUDENT");
[*] pRst = dbOper.ExecuteWithResSQL(sql);
[*] if (NULL == pRst)
[*] {
[*] printf("查询数据出现错误！\n");
[*] system("PAUSE");
[*] return;
[*] }
[*] if (pRst->adoEOF)
[*] {
[*] pRst->Close();
[*] printf("There is no records in this table\n");
[*] return;
[*] }
[*] _variant_t vSno, vName, v***, vAge, vDno, vDname, vCname;
[*] while (!pRst->adoEOF)
[*] {
[*] //pRst->MoveFirst(); //记录集指针移动到查询结果集的前面
[*] vSno = pRst->GetCollect(_variant_t((long)0));
[*] vName = pRst->GetCollect(_variant_t("name"));
[*] v*** = pRst->GetCollect(_variant_t("***"));
[*] vAge = pRst->GetCollect(_variant_t("age"));
[*] //vDno = pRst->GetCollect("dno");
[*] //vDname = pRst->GetCollect("dname");
[*] //vCname = pRst->GetCollect("cname");
[*]
[*] printf("%s\t%s\t%s\t%d\n", (LPSTR)(LPCSTR)(_bstr_t)vSno, (LPSTR)(LPCSTR)_bstr_t(vName), (LPSTR)(LPCSTR)_bstr_t(v***), vAge.intVal);
[*] pRst->MoveNext();
[*] }
[*]
[*] //执行插入语句
[*] //sprintf(sql, "insert into TSTUDENT(sno, name, ***, age) values('%s', '%s', '%s', %d)", "20080016", "全局", "女", 25);
[*] strcpy(sql, "insert into TSTUDENT(sno, name, ***, age) values('20080001', '全局', '女', 25)");
[*] pRst = dbOper.ExecuteWithResSQL(sql);
[*] if (NULL != pRst)
[*] {
[*] printf("插入数据成功\n");
[*] }
[*] //执行删除语句
[*]
[*] sprintf(sql, "delete from TSTUDENT where sno = '%s'", "20080017");
[*] pRst = dbOper.ExecuteWithResSQL(sql);
[*] if (NULL != pRst)
[*] {
[*] printf("删除数据成功\n");
[*] }
[*] system("PAUSE");
[*] //pRst->Close();
[*] }
其中XXX1：是tnsnames.ora中配置的服务名，XXX2是用户名，XXX3是密码。
DBOperation.h:
点击(此处)折叠或打开
[*] #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 *);
[*] //bool ExecuteNoResSQL(const char *);//delete and add
[*]
[*] private:
[*] void PrintErrorInfo(_com_error &);
[*]
[*] private:
[*] //初始化数据库连接、命令、记录集
[*] _ConnectionPtr CreateConnPtr();
[*] _CommandPtr CreateCommPtr();
[*] _RecordsetPtr CreateRecsetPtr();
[*]
[*] private:
[*] //数据库连接需要的连接、命令操作对象
[*] _ConnectionPtr m_pConnection;
[*] _CommandPtr m_pCommand;
[*] };
DBOperation.cpp
点击(此处)折叠或打开
[*] #include "DBOperation.h"
[*]
[*] CDBOperation::CDBOperation(void)
[*] {
[*] CoInitialize(NULL);
[*] m_pConnection = CreateConnPtr();
[*] m_pCommand = CreateCommPtr();
[*] }
[*]
[*] CDBOperation::~CDBOperation(void)
[*] {
[*] //m_pCommand->Close();
[*] 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)
[*] {
[*] //已经在连接至数据库的时候进行判断了
[*] //if (NULL == m_pCommand || 0 == m_pConnection->State)
[*] //{
[*] // printf("Failed to create command OR the state of connection is zero\n");
[*] // return NULL;
[*] //}
[*]
[*] //char *query = new char;
[*] //strcpy(query, sql);
[*] try
[*] {
[*] m_pCommand->CommandText = _bstr_t(sql);
[*] _RecordsetPtr pRst = m_pCommand->Execute(NULL, NULL, adCmdText);
[*] return pRst;
[*] //_variant_t ra;
[*] //_RecordsetPtr pRst = m_pConnection->Execute((_bstr_t)query, &ra, adCmdText);
[*] }
[*] catch(_com_error &e)
[*] {
[*] PrintErrorInfo(e);
[*] return NULL;
[*] }
[*] }
[*]
[*] //bool CDBOperation::ExecuteNoResSQL(const char *sql)
[*] //{
[*] // //if (NULL == m_pCommand || 0 == m_pConnection->State)
[*] // //{
[*] // // printf();
[*] // //}
[*] // try
[*] // {
[*] // char *query = NULL;
[*] // strcpy(query, sql);
[*] // m_pCommand->CommandText = (_bstr_t)query;
[*] //
[*] // }
[*] //}
[*]
[*] 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;
[*] }
方式二：OCCI
默认oracle安装了occi库，但是只是安装了release版本的资源，因此需要将程序配置为release模式，或者是参看51cto.comarticle-42057-1.html为debug模式获取必备的头文件以及库文件，本文采用的是release模式，使用默认安装的库文件以及头文件。
1.修改配置属性
改为Rlease模式
2.添加库文件目录
$(ORACLE_HOME)\oci\include
3.添加头文件目录
$(ORACLE_HOME)\oci\lib
4.添加库文件:oraocci10.lib
应用程序：
点击(此处)折叠或打开
[*] //代码的目的就是验证makefile中oracle的头文件和lib文件路径是否正确了
[*] #include
[*] #define WIN32COMMON //避免函数重定义错误
[*] #include
[*] using namespace std;
[*] using namespace oracle::occi;
[*] int main()
[*] {
[*] Environment *env=Environment::createEnvironment();
[*] cout
页:
[1]