ii_oracle.cpp

ORACLE访问接口

//====================================================================
// 文件名: ii_oracle.cpp
//
// 文件描述:
// ------------------------------------------------------------------
// 跨平台通用数据库接口的oracle数据库插件实现
// ------------------------------------------------------------------
//
// 时间: 2002.9
// 编程: 喻宜
// ------------------------------------------------------------------
// 修改说明(请按格式说明)...
//====================================================================

#include <stdlib.h>
#include <oci.h>
#include "../../../../include/ies_templ.h"
#include "../../../../include/dbi_odbc.h"

#include "ii_oracle.h"

//////////////////////////////////////////////////////////
// ORACLE DATABASE
//////////////////////////////////////////////////////////
class CIIOracleDBI
{
public:
	CIIOracleDBI();
	~CIIOracleDBI();

public:
	//连接和断开数据库
	bool Open(const char *i_pszSrv, const char *i_pszUser, const char *i_pszPwd);
	void Close();

	bool ExecuteSQL(const char *i_pszSqlStr);			//执行SQL语句

	bool IsTableExist(const char *i_szTableName);		//判断表是否存在

	//获取错误号和错误字符串
	int GetErrorNo();
	const char* GetErrorStr();

	///////////////////////////////////////////////////////
	//ODBC操作
	bool PrepareSQL(const char *i_pStringSQL);	//准备SQL语句
	void CloseSQL(bool i_bCommit=true);					//关闭本次SQL处理

	bool BindCol(int i_nColType,
			int i_nColSize, void *i_pColAddr=NULL);		//绑定列
	bool BindPara(int i_nParaType,
			int i_nParaSize, void *i_pParaAddr);		//绑定参数

	int Exec();		//执行SQL语句
	bool Fetch();	//获取查询结果

	bool IsEnd()	//是否到了记录尾
		{return m_bIsEnd;}

	// 事务操作
	bool BeginTrans();
	bool Commit();
	bool Rollback();

protected:
	bool PassCol();									//传递列信息
	bool PassPara();								//传递参数信息
	void ClearParaMem();							//清空参数分配的内存

private:
	OCIEnv     	*m_pEnvHandle;						//环境句柄
	OCIError   	*m_pErrHandle;						//错误句柄
	OCIServer  	*m_pSrvHandle;						//服务器句柄
	OCISvcCtx  	*m_pSvcHandle;						//服务句柄
	OCISession 	*m_pAutHandle;						//会话句柄

	OCIStmt    	*m_pStmtExec;
	OCIStmt    	*m_pStmtAll;

	OCIDefine  	*SelectDefineP[MAX_COLOM_NUMBER];
	OCIBind 	*ParamBindP[MAX_COLOM_NUMBER];

	bool		m_bIsOpenDB;
	bool		m_bIsEnd;
	bool		m_bIsFirst;
	bool		m_bIsPrepareSQL;
	bool		m_bIsAllocParaMem;
	int			m_nAllocParaCount;
	CIILog		m_iiLog;
	CIIString	m_pszSql;
	CIIString	m_pszError;
	int			m_nRowCount;
	int			m_nSqlType;

	CIIArray<SBind>	m_arPara;
	CIIArray<SBind> m_arCol;
};

CIIOracleDBI::CIIOracleDBI():
	m_pEnvHandle(NULL),
	m_pErrHandle(NULL),
	m_pSrvHandle(NULL),
	m_pSvcHandle(NULL),
	m_pAutHandle(NULL),
	m_pStmtExec(NULL),
	m_pStmtAll(NULL),
	m_bIsOpenDB(false),
	m_bIsEnd(true),
	m_bIsFirst(true),
	m_bIsPrepareSQL(false),
	m_bIsAllocParaMem(false),
	m_nAllocParaCount(0),
	m_nRowCount(0),
	m_nSqlType(0)
{
	m_iiLog.SetProcName("oracle_db");
	m_arPara.SetSize(10);
	m_arCol.SetSize(20);
}

CIIOracleDBI::~CIIOracleDBI()
{
	if (m_bIsOpenDB)
		Close();
}

bool CIIOracleDBI::Open(const char *i_pszSrv, const char *i_pszUser, const char *i_pszPwd)
{
	// 初始化进程 ...
	if( OCIInitialize((ub4)OCI_THREADED, (dvoid*)0, (dvoid *(*)(dvoid *, size_t))0,
				(dvoid *(*)(dvoid*, dvoid*, size_t))0, (void (*)(dvoid *, dvoid *))0)
	// 初始化环境 ...
	|| OCIEnvInit((OCIEnv**)&m_pEnvHandle, OCI_DEFAULT, (size_t)0, (dvoid **)0)
	// 分配句柄 ...
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pErrHandle,
						OCI_HTYPE_ERROR, (size_t)0, (dvoid**)0)
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pSrvHandle,
						OCI_HTYPE_SERVER, (size_t)0, (dvoid**)0)
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pSvcHandle,
						OCI_HTYPE_SVCCTX, (size_t)0, (dvoid**)0)
	// 连接服务 ...
	|| OCIServerAttach(m_pSrvHandle, m_pErrHandle, (text*)i_pszSrv, strlen((char*)i_pszSrv), 0)
	// Set attribute server context in the service context ...
	|| OCIAttrSet((dvoid*)m_pSvcHandle, OCI_HTYPE_SVCCTX, (dvoid*)m_pSrvHandle,
							(ub4)0, OCI_ATTR_SERVER, (OCIError*)m_pErrHandle)
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pAutHandle,
							OCI_HTYPE_SESSION, (size_t)0, (dvoid**)0)
	|| OCIAttrSet((dvoid*)m_pAutHandle, OCI_HTYPE_SESSION, (dvoid*)i_pszUser,
							(ub4)strlen(i_pszUser), OCI_ATTR_USERNAME, m_pErrHandle)
	|| OCIAttrSet((dvoid*)m_pAutHandle, OCI_HTYPE_SESSION, (dvoid*)i_pszPwd,
							(ub4)strlen(i_pszPwd), OCI_ATTR_PASSWORD, m_pErrHandle)
	// 开始会话 ...
	|| OCISessionBegin(m_pSvcHandle, m_pErrHandle, m_pAutHandle,
						OCI_CRED_RDBMS,(ub4)OCI_DEFAULT)
	|| OCIAttrSet((dvoid*)m_pSvcHandle, (ub4)OCI_HTYPE_SVCCTX,
						(dvoid *)m_pAutHandle, (ub4)0,(ub4)OCI_ATTR_SESSION, m_pErrHandle)
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pStmtExec,
						OCI_HTYPE_STMT,(size_t)0, (dvoid **)0)
	|| OCIHandleAlloc((dvoid*)m_pEnvHandle, (dvoid**)&m_pStmtAll,
						OCI_HTYPE_STMT, (size_t)0, (dvoid**)0))
	{
		return false;
	}

	m_bIsOpenDB = true;

	return true;
}

void CIIOracleDBI::Close()
{
	CloseSQL();

	// 结束会话 ...
	OCISessionEnd(m_pSvcHandle, m_pErrHandle, m_pAutHandle, (ub4)0);
	OCIServerDetach( m_pSrvHandle, m_pErrHandle, (ub4)OCI_DEFAULT);

	if (m_pStmtExec)
		OCIHandleFree((dvoid *)m_pStmtExec, OCI_HTYPE_STMT);
	if (m_pStmtAll)
		OCIHandleFree((dvoid *)m_pStmtAll, OCI_HTYPE_STMT);

	// 释放句柄 ...
	if (m_pSrvHandle)
		OCIHandleFree((dvoid*)m_pSrvHandle, (ub4)OCI_HTYPE_SERVER);
	if (m_pSvcHandle)
		OCIHandleFree((dvoid*)m_pSvcHandle, (ub4)OCI_HTYPE_SVCCTX);
	if (m_pErrHandle)
		OCIHandleFree((dvoid*)m_pErrHandle, (ub4)OCI_HTYPE_ERROR);
	if (m_pAutHandle)
		OCIHandleFree((dvoid*)m_pAutHandle, (ub4)OCI_HTYPE_SESSION);
	if (m_pEnvHandle)
		OCIHandleFree((dvoid*)m_pEnvHandle, (ub4)OCI_HTYPE_ENV);

	m_bIsOpenDB = false;
}

bool CIIOracleDBI::ExecuteSQL(const char *i_pszSqlStr)
{
	if ((OCIStmtPrepare(m_pStmtExec, m_pErrHandle, (text*)i_pszSqlStr,
			(ub4)strlen(i_pszSqlStr), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT))
		|| ( OCIStmtExecute( m_pSvcHandle, m_pStmtExec, m_pErrHandle, (ub4)1, (ub4)0,
			(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS)))
	{
		return false;
	}

	return true;
}

bool CIIOracleDBI::IsTableExist(const char *i_szTableName)
{
	int nCount = 0;
	int bRet;
	CIIString tSQLString = "SELECT count(*) FROM ";

	if (i_szTableName == NULL)
		return false;

	tSQLString += i_szTableName;
	tSQLString += " WHERE 1=0";

	if (OCIStmtPrepare(m_pStmtExec, m_pErrHandle, (text*)tSQLString.GetBuf(),
			(ub4)tSQLString.GetLength(), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT ))
	{
		return false;
	}

	if (OCIDefineByPos(m_pStmtExec, &SelectDefineP[MAX_COLOM_NUMBER-1], m_pErrHandle,
				1, (dvoid*)&nCount, (sb4)4,
				(ub2)SQLT_INT, (dvoid*)0, (ub2*)0, (ub2*)0, (ub4)OCI_DEFAULT))
	{
		return false;
	}

	bRet = OCIStmtExecute(m_pSvcHandle, m_pStmtExec, m_pErrHandle, (ub4)1,
			(ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);

	if (bRet == OCI_ERROR)
		return false;

	return true;
}

int CIIOracleDBI::GetErrorNo()
{
	char  	errbuf[512];
	int 	ret;

	OCIErrorGet(m_pErrHandle, 1, NULL, &ret, (text*)errbuf,
								sizeof(errbuf), OCI_HTYPE_ERROR);
	return ret;
}

const char* CIIOracleDBI::GetErrorStr()
{
	char  	errbuf[512];
	int 	ret;

	OCIErrorGet(m_pErrHandle, 1, NULL, &ret, (text*)errbuf,
								sizeof(errbuf), OCI_HTYPE_ERROR);
	m_pszError = errbuf;
	return m_pszError.GetBuf();
}

bool CIIOracleDBI::PrepareSQL(const char *i_pStringSQL)
{
	if (!m_bIsOpenDB)
		return false;

	//如果未关闭SQL，则先关闭SQL
	CloseSQL(false);

	m_pszSql = i_pStringSQL;
	m_pszSql.TrimAll();
	m_pszSql.ReplaceFind("?", ":p");

	CIIString tStr = m_pszSql.Left(6);
	tStr.ToUpper();

	if (tStr == "SELECT")
		m_nSqlType = 1;
	else if (tStr == "UPDATE")
		m_nSqlType = 2;
	else
		m_nSqlType = 0;

	m_bIsPrepareSQL = true;

	return true;
}

void CIIOracleDBI::CloseSQL(bool i_bCommit)
{
	if (m_bIsPrepareSQL)
	{
		if (i_bCommit)
			OCITransCommit(m_pSvcHandle, m_pErrHandle, 0);

		//清空参数分配内存
		if (m_bIsAllocParaMem)
		{
			ClearParaMem();
			m_bIsAllocParaMem = false;
		}
		m_arPara.Empty();
		m_arCol.Empty();

		m_bIsEnd = true;
		m_bIsFirst = true;
		m_bIsPrepareSQL = false;
	}
}

bool CIIOracleDBI::BindCol(int i_nColType,
							int i_nColSize, void *i_pColAddr)
{
	SBind tBind;

	if (i_pColAddr == NULL)
		return false;

	tBind.nType = i_nColType;
	tBind.nSize = i_nColSize;
	tBind.pAddr = i_pColAddr;

	m_arCol.AddLast(tBind);

	return true;
}

bool CIIOracleDBI::BindPara(int i_nParaType,
							int i_nParaSize, void *i_pParaAddr)
{
	if (i_pParaAddr == NULL)
		return false;

	SBind tmp;
	bool bRet;

	tmp.nType = i_nParaType;
	tmp.pAddr = i_pParaAddr;
	tmp.nSize = i_nParaSize;

	bRet = m_arPara.AddLast(tmp);

	return bRet;
}

bool CIIOracleDBI::PassCol()
{
	int t_nColSize = 0;
	int t_nColType = 0;

	if (!m_bIsOpenDB)
		return false;

	for (int i = 0; i < m_arCol.GetNum(); i++)
	{
		switch(m_arCol[i].nType)
		{
		case IES_SQL_BOOL:
		case IES_SQL_UTINYINT:
			t_nColType = SQLT_UIN;
			t_nColSize = 1;
			break;
		case IES_SQL_SMALLINT:
			t_nColType = SQLT_INT;
			t_nColSize = 2;
			break;
		case IES_SQL_USMALLINT:
			t_nColType = SQLT_UIN;
			t_nColSize = 2;
			break;
		case IES_SQL_INT:
			t_nColType = SQLT_INT;
			t_nColSize = 4;