ocidb.cpp

这里对oracle的开发接口oci进行了简单包装

// OciDB.cpp: implementation of the COciDB class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "OciDB.h"
#include "ICUtil.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

#define BUF_SIZE 2048*8

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

COciConnection::COciConnection()
{
	m_pOciHandle = NULL;
	m_bAlwaysLogon = false;
}

COciConnection::~COciConnection()
{
	if(m_pOciHandle!=NULL)
	{
		detach();
	}
}

void COciConnection::detach()
{
	ASSERT(m_pOciHandle);
	//m_pOciHandle->m_nCount--;
	/*
	if(m_pOciHandle->m_bInit)
	{
		for(int i=0;i<MAX_FIELD;i++)
		{
			OCIDescriptorFree((dvoid *) m_pLobLocator[i], (ub4) OCI_DTYPE_LOB);
		}
		OCIHandleFree((dvoid *) m_pStmtHpp, (ub4) OCI_HTYPE_STMT);
	}*/
	//if(m_pOciHandle->m_nCount==0)
	//{
		if(m_pOciHandle->m_bLog)
		{
			m_pOciHandle->m_bLog=FALSE;
			logout();
		}
		if(m_pOciHandle->m_bInit)
		{
			m_pOciHandle->m_bInit=FALSE;
			uninitOciDB();
		}
		delete m_pOciHandle;
		m_pOciHandle=NULL;
	//}

}
bool COciConnection::initOciDB()
{
	if(m_pOciHandle!=NULL)
	{
		m_csError = "已经初始化过Oci连接句柄!";
		return false;
	}
	m_pOciHandle = new OciDBHandle();
	m_pOciHandle->m_nCount=1;
	m_pOciHandle->m_bInit=FALSE;
	m_pOciHandle->m_bLog=FALSE;

	if (OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
		(dvoid * (*)(dvoid *, size_t)) 0,
		(dvoid * (*)(dvoid *, dvoid *, size_t))0,
		(void (*)(dvoid *, dvoid *)) 0 ))
	{
		GetOraError();
		return false;
	}
	
	if (OCIEnvInit((OCIEnv **) &m_pOciHandle->m_pEnvHpp, (ub4) OCI_DEFAULT,
		(size_t) 0, (dvoid **) 0 ))
	{
		GetOraError();
		return false;
	}
	
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pOciHandle->m_pSrvHpp,
		(ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}
	
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pOciHandle->m_pSvcHpp,
		(ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}
	
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pOciHandle->m_pErrHpp,
		(ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}
	
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pOciHandle->m_pSesHpp,
		(ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}
/*	
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pStmtHpp,
		(ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}

	for(int i=0;i<MAX_FIELD;i++)
	{
		if (OCIDescriptorAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) m_pLobLocator+i,
			(ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0))
		{
			GetOraError();
			return false;
		}
	}
*/
	m_pOciHandle->m_bInit=TRUE;
	SetSuccess();
	return true;
}

bool COciConnection::logon(LPCTSTR szOra,LPCTSTR szUser,LPCTSTR szPass)
{
	string	strOra, strUser, strPass;
#ifdef UNICODE
	strOra= CICUtil::WSToMB(szOra);
	strUser= CICUtil::WSToMB(szUser);
	strPass= CICUtil::WSToMB(szPass);
#else
	strOra = szOra;
	strUser = szUser;
	strPass=szPass;
#endif
	

	ASSERT(m_pOciHandle);
	if (OCIServerAttach(m_pOciHandle->m_pSrvHpp, m_pOciHandle->m_pErrHpp, (text *) strOra.c_str(),
		(sb4) _tcslen(szOra), (ub4) OCI_DEFAULT))
	{
		GetOraError();
		return false;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSesHpp, (ub4) OCI_HTYPE_SESSION,
		(dvoid *) strUser.c_str(), (ub4) strlen(strUser.c_str()),
		(ub4) OCI_ATTR_USERNAME, m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return false;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSesHpp, (ub4) OCI_HTYPE_SESSION,
		(dvoid *) strPass.c_str(), (ub4) strlen(strPass.c_str()),
		(ub4) OCI_ATTR_PASSWORD, m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return false;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSvcHpp, (ub4) OCI_HTYPE_SVCCTX,
		(dvoid *) m_pOciHandle->m_pSrvHpp, (ub4) 0, (ub4) OCI_ATTR_SERVER, m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return false;
	}
	
	if (OCISessionBegin(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pOciHandle->m_pSesHpp, (ub4) OCI_CRED_RDBMS,
		(ub4) OCI_DEFAULT))
	{
		GetOraError();
		return false;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSvcHpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) m_pOciHandle->m_pSesHpp,
		(ub4) 0, (ub4) OCI_ATTR_SESSION, m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return false;
	}

	SetSuccess();
	m_pOciHandle->m_bLog=FALSE;
	return true;
}

void COciConnection::logout()
{
	ASSERT(m_pOciHandle);
	OCISessionEnd(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pOciHandle->m_pSesHpp, (ub4) 0);
	OCIServerDetach(m_pOciHandle->m_pSrvHpp, m_pOciHandle->m_pErrHpp, (ub4) OCI_DEFAULT);
}

void COciConnection::uninitOciDB()
{
	ASSERT(m_pOciHandle);
	OCIHandleFree((dvoid *) m_pOciHandle->m_pSrvHpp, (ub4) OCI_HTYPE_SERVER);
	OCIHandleFree((dvoid *) m_pOciHandle->m_pSvcHpp, (ub4) OCI_HTYPE_SVCCTX);
	OCIHandleFree((dvoid *) m_pOciHandle->m_pErrHpp, (ub4) OCI_HTYPE_ERROR);
	OCIHandleFree((dvoid *) m_pOciHandle->m_pSesHpp, (ub4) OCI_HTYPE_SESSION);
}

CString COciConnection::getLastError()
{
	return m_csError;
}

CString COciConnection::SetSuccess()
{
	m_csError = "成功";
	return m_csError;
}

CString COciConnection::GetOraError()
{
	ASSERT(m_pOciHandle);
	text  msgbuf[512];
	m_nError = 0;
	
	(void) OCIErrorGet((dvoid *) m_pOciHandle->m_pErrHpp, (ub4) 1, (text *) NULL, &m_nError,
		msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
	m_csError = msgbuf;
	return m_csError;
}
/*
CString COciConnection::getCode(int nCode)
{
	CString csCode;
	csCode.Format(_T("%d"),nCode);
	return csCode;
}
*/

bool COciConnection::transCommit()
{
	ASSERT(m_pOciHandle);
	
	m_status = OCITransCommit(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, OCI_DEFAULT);
	if (m_status != OCI_SUCCESS)
	{
		GetOraError();
		return false;
	}
	return true;
}


bool COciConnection::transRollback() 
{
	ASSERT(m_pOciHandle);
	if(m_status = OCITransRollback(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, OCI_DEFAULT))
	if (m_status != OCI_SUCCESS)
	{
		GetOraError();
		return false;
	}
	return true;
}


//////////////////////////////// define recordset for oracle //////////////////////////////////////////

COciRecordset::COciRecordset()
{
	m_pStmtHpp = NULL;
	m_bIsEOF = true;

}
COciRecordset::~COciRecordset()
{
	close();
}

bool COciRecordset::setConnection(COciConnection * pOciConnect)
{
	ASSERT(pOciConnect);
	m_pOciHandle = pOciConnect->m_pOciHandle;
	if (OCIHandleAlloc((dvoid *) m_pOciHandle->m_pEnvHpp, (dvoid **) &m_pStmtHpp,	(ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
	{
		GetOraError();
		return false;
	}

	return true;
}

void COciRecordset::detachConnect()
{
	if(m_pStmtHpp != NULL)
	{
		OCIHandleFree((dvoid *) m_pStmtHpp, (ub4) OCI_HTYPE_STMT);
		m_pStmtHpp = NULL;
	}
}

CString COciRecordset::GetOraError()
{
	ASSERT(m_pOciHandle);
	text  msgbuf[512];
	m_nError = 0;

	OCIErrorGet((dvoid *) m_pOciHandle->m_pErrHpp, (ub4) 1, (text *) NULL, &m_nError,
								msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
	m_csError = msgbuf;
	return m_csError;
}

bool COciRecordset::open(LPCTSTR szSQL)
{

	string	strSql;
#ifdef UNICODE
	strSql= CICUtil::WSToMB(szSQL);
#else
	strSql = szSQL;
#endif

	ASSERT(m_pStmtHpp != NULL);
	ASSERT(m_pOciHandle);
	int nStatus;
	m_bIsEOF = true;
	if ((nStatus =OCIStmtPrepare(m_pStmtHpp, m_pOciHandle->m_pErrHpp, (text*)strSql.c_str(), (ub4) strlen(strSql.c_str()),
								(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)))
	{
		GetOraError();
		return false;
	}

	memset(m_pszBuf, 0, sizeof(m_pszBuf));

	OCIDefine *m_pdefnp;
	m_pdefnp = (OCIDefine *)0;

	if((nStatus = OCIDefineByPos(m_pStmtHpp, &(m_pdefnp), m_pOciHandle->m_pErrHpp, (ub4)1,
		(dvoid *)m_pszBuf, (sb4)(1024),
		SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT)))
	{
		GetOraError();
		return false;
	}

	if ((nStatus = OCIStmtExecute(m_pOciHandle->m_pSvcHpp, m_pStmtHpp, m_pOciHandle->m_pErrHpp, (ub4) 0, (ub4) 0,
							(CONST OCISnapshot *) 0, (OCISnapshot *) 0,	(ub4) OCI_DEFAULT)))
	{
		GetOraError();
		if(m_nError!=1405)
			return false;
	}

	if(nStatus == OCI_NO_DATA)
	{
		m_bIsEOF = true;
		return true;
	}

	m_nColNum = 0;
	if(OCIAttrGet(m_pStmtHpp, OCI_HTYPE_STMT,&m_nColNum,0,	OCI_ATTR_PARAM_COUNT,m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return false;
	}

	//text     *namep; //字段名称
	//ub4       sizep; //字段名称的字符串长度
	//text tempText[100];
	ub2 coltype[30];//列类型
	//CString ColName[50]; //存储表的字段名称
	//get properties of field
	//获取表的字段的属性信息
	for(int i=0;i<(int)m_nColNum;i++)
	{
		//为选择项分配参数描述符
		OCIParamGet(m_pStmtHpp,	OCI_HTYPE_STMT,m_pOciHandle->m_pErrHpp,(void **)&m_colhp,ub4(i+1));

		//读取选择项的数据长度
		OCIAttrGet(m_colhp, OCI_DTYPE_PARAM,&m_collen[i],0,	OCI_ATTR_DATA_SIZE,m_pOciHandle->m_pErrHpp);

		//读取选择项的数据类型
		OCIAttrGet(m_colhp,	OCI_DTYPE_PARAM,&coltype[i],0,OCI_ATTR_DATA_TYPE,m_pOciHandle->m_pErrHpp);

		//若这个字段为日期型，则把其字符宽度置为30
		if(coltype[i]==SQLT_DAT) m_collen[i]=30;

		//分配缓冲区
		m_colbuf[i]=(text*)new text[(int)m_collen[i]+1];

		/*
		OCIAttrGet(colhp,OCI_DTYPE_PARAM, (dvoid*)&namep,	(ub4*)&sizep,OCI_ATTR_NAME, m_pOciHandle->m_pErrHpp);
		strncpy((char *)tempText,	(char *)namep, (size_t) sizep);
		tempText[sizep] = '\0';
		//把字段的名称赋予字段名称数组
		ColName[i].Format("%s",tempText);*/
	}

	//定义变量
	for(int i=0;i<(int)m_nColNum;i++)
	{
		if(nStatus=OCIDefineByPos(m_pStmtHpp ,&m_defhp[i], m_pOciHandle->m_pErrHpp, i+1,(ub1*)m_colbuf[i], m_collen[i]+1,
												SQLT_STR,&m_ind[i],0,0, OCI_DEFAULT))
		{ 
			GetOraError();
			return false; 
		}
	}

	return moveNext();
	
}

bool COciRecordset::moveNext()
{
	int nStatus;
	nStatus = OCIStmtFetch(m_pStmtHpp,m_pOciHandle->m_pErrHpp ,1,OCI_FETCH_NEXT,OCI_DEFAULT);
	if(nStatus != OCI_SUCCESS)
	{
		m_bIsEOF =true;
		if(nStatus ==OCI_NO_DATA)
			return true;

		GetOraError();
		return false;
	}

	m_bIsEOF =false;

	return true;
}

void COciRecordset::getValueString(CString &strVal, int nColumnIndex)
{
	ASSERT(nColumnIndex >= 0 && nColumnIndex < MAX_COLUMN);
	strVal = m_colbuf[nColumnIndex];
}

bool COciRecordset::close()
{
	
	//删除所分配的缓冲区
	for(int i=0;i<(int)m_nColNum;i++)
		delete m_colbuf[i];

	m_nColNum = 0;

	detachConnect();

	return true;
}