ocidb.cpp

提供OCI连接数据库并进行相关操作的通用的类

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

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

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

#define BUF_SIZE 2048*8

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

COciDB::COciDB()
{
	m_pOciHandle = NULL;
}

COciDB::~COciDB()
{
	if(m_pOciHandle!=NULL)
		Detach();
}

BOOL COciDB::Attach(OciDBHandle* pOciHandle)
{
	ASSERT(pOciHandle);
	m_pOciHandle = pOciHandle;
	m_pOciHandle->m_nCount++;
	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;
		}
	}
	SetSuccess();
	return TRUE;
}

void COciDB::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 COciDB::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 COciDB::LogOn(LPCSTR szOra,LPCSTR szUser,LPCSTR szPass)
{
	ASSERT(m_pOciHandle);
	if (OCIServerAttach(m_pOciHandle->m_pSrvHpp, m_pOciHandle->m_pErrHpp, (text *) szOra,
		(sb4) strlen(szOra), (ub4) OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSesHpp, (ub4) OCI_HTYPE_SESSION,
		(dvoid *) szUser, (ub4) strlen(szUser),
		(ub4) OCI_ATTR_USERNAME, m_pOciHandle->m_pErrHpp))
	{
		GetOraError();
		return FALSE;
	}
	
	if (OCIAttrSet((dvoid *) m_pOciHandle->m_pSesHpp, (ub4) OCI_HTYPE_SESSION,
		(dvoid *) szPass, (ub4) strlen(szPass),
		(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=TRUE;
	return TRUE;
}

void COciDB::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 COciDB::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 COciDB::GetLastError()
{
	return m_csError;
}

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

CString COciDB::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 COciDB::GetCode(int nCode)
{
	CString csCode;
	csCode.Format("%d",nCode);
	return csCode;
}

BOOL COciDB::AllocSQL(LPCSTR szSQL)
{
	ASSERT(m_pOciHandle);
	if (OCIStmtPrepare(m_pStmtHpp, m_pOciHandle->m_pErrHpp,
		(text*)szSQL, (ub4) strlen(szSQL),
		(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	SetSuccess();
	return TRUE;
}

BOOL COciDB::BindVarchar(int nPos,char* pString,int nLen)
{
	ASSERT(m_pOciHandle);
	if (OCIDefineByPos(m_pStmtHpp, m_pDefineHpp+(nPos-1), m_pOciHandle->m_pErrHpp,
		nPos,(dvoid *) pString, nLen, SQLT_VCS,
		(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	SetSuccess();
	return TRUE;
}

BOOL COciDB::BindInt(int nPos,int* pInt)
{
	ASSERT(m_pOciHandle);
	if (OCIDefineByPos(m_pStmtHpp, m_pDefineHpp+(nPos-1), m_pOciHandle->m_pErrHpp,
		nPos,(dvoid *) pInt, sizeof(int), SQLT_INT,
		(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	SetSuccess();
	return TRUE;
}

BOOL COciDB::ExecuteSQL()
{
	ASSERT(m_pOciHandle);
    if (OCIStmtExecute(m_pOciHandle->m_pSvcHpp, m_pStmtHpp, m_pOciHandle->m_pErrHpp,
		(ub4) 1, (ub4) 0,
		(CONST OCISnapshot *) 0, (OCISnapshot *) 0,
		(ub4) OCI_DEFAULT))
    {
		GetOraError();
		if(m_nError!=1405)
			return FALSE;
    }
	SetSuccess();
	return TRUE;
}

BOOL COciDB::MoveNext()
{
	ASSERT(m_pOciHandle);
	long status=OCIStmtFetch(m_pStmtHpp, m_pOciHandle->m_pErrHpp,
		(ub4) 1,(ub4) OCI_FETCH_NEXT,
		(ub4) OCI_DEFAULT);
	GetOraError();
	if(status == OCI_SUCCESS ||
		status == OCI_SUCCESS_WITH_INFO ||
		m_nError==1405)
		return TRUE;
	return FALSE;
}

void COciDB::VarToChar(char* szVar)
{
	int nLen=szVar[1]*256+szVar[0];
	if(nLen==0)
	{
		szVar[0]=0;
	}
	else
	{
		szVar[nLen+2]=0;
		strcpy(szVar,szVar+2);
	}
}

void COciDB::Commit()
{
	ASSERT(m_pOciHandle);
	OCITransCommit(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, (ub4)0);
}

BOOL COciDB::BindLob(int nPos,LPCSTR szSQL)
{
	ASSERT(m_pOciHandle);
	int colc = 1;
	
	if (OCIStmtPrepare(m_pStmtHpp, m_pOciHandle->m_pErrHpp,
		(text*)szSQL, (ub4) strlen(szSQL),
		(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	if (OCIDefineByPos(m_pStmtHpp, &m_pDefineHpp[nPos], m_pOciHandle->m_pErrHpp, (ub4) 1,
		(dvoid *) &m_pLobLocator[nPos], (sb4) -1, (ub2) SQLT_BLOB,
		(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	
	if (OCIStmtExecute(m_pOciHandle->m_pSvcHpp, m_pStmtHpp, m_pOciHandle->m_pErrHpp, 
		(ub4) 1, (ub4) 0,
		(CONST OCISnapshot*) 0, (OCISnapshot*) 0,
		(ub4) OCI_DEFAULT))
	{
		GetOraError();
		return FALSE;
	}
	SetSuccess();
	return TRUE;
}

BOOL COciDB::WriteLob(int nPos,void* pData,int nLen)
{	
	ASSERT(m_pOciHandle);
	if (OCILobWrite(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos], (unsigned int*)&nLen, 1, (dvoid *) pData,
		(ub4) nLen, OCI_ONE_PIECE, (dvoid *)0,
		(sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
		(ub2) 0, (ub1) SQLCS_IMPLICIT))
    {
		GetOraError();
		return FALSE;
    }
	OCILobFlushBuffer(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos], OCI_LOB_BUFFER_NOFREE);
	Commit();
	SetSuccess();
	return TRUE;
}

BOOL COciDB::ReadLob(int nPos,void* pBuf,int nLen)
{
	ASSERT(m_pOciHandle);
	if (OCILobRead(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos], (unsigned int*)&nLen,
		1, (dvoid *) pBuf,
		(ub4) nLen, (dvoid *)0,
		(sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
		(ub2) 0, (ub1) SQLCS_IMPLICIT))
    {
		GetOraError();
		return FALSE;
    }
	Commit();
	SetSuccess();
	return TRUE;
}

BOOL COciDB::WriteLobFromFile(int nPos,LPCSTR szFile)
{
	ASSERT(m_pOciHandle);
	ub4 offset = 1;	
	ub4 amtp;
	char buf[BUF_SIZE];
	ub4 nLen;
	ub4 nSize;
	CFile file;
	CFileException e;
	if(!file.Open(szFile,CFile::modeRead,&e))
	{
		m_csError = "无法打开文件！";
		m_csError+=e.m_cause;
		return FALSE;
	}
	nLen = file.GetLength();
	while(offset<nLen)
	{
		nSize = nLen - offset + 1;
		if(nSize > BUF_SIZE)
		{
			nSize = BUF_SIZE;
		}
		nSize = file.Read(buf,nSize);
		amtp = nSize;
		if (OCILobWrite(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos],
			&amtp, offset, (dvoid *) buf,
			(ub4) nSize, OCI_ONE_PIECE, (dvoid *)0,
			(sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
			(ub2) 0, (ub1) SQLCS_IMPLICIT))
		{
			GetOraError();
			return FALSE;
		}
		offset += nSize;
	}
	file.Close();
	OCILobFlushBuffer(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos], OCI_LOB_BUFFER_NOFREE);
	SetSuccess();
	return TRUE;
}

BOOL COciDB::ReadLobToFile(int nPos,LPCSTR szFile)
{
	ASSERT(m_pOciHandle);
	ub4 offset = 1;	
	ub4 amtp;
	char buf[BUF_SIZE];
	ub4 nLen;
	ub4 nSize;
	CFile file;
	CFileException e;	
	if(!file.Open(szFile,CFile::modeCreate|CFile::modeWrite,&e))
	{
		m_csError = "无法打开文件！";
		m_csError+=e.m_cause;
		return FALSE;
	}
	nLen = GetLobLen(nPos);
	while(offset<nLen)
	{
		nSize = nLen - offset + 1;
		if(nSize > BUF_SIZE)
		{
			nSize = BUF_SIZE;
		}
		amtp = nSize;
		if (OCILobRead(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos],
			&amtp, offset, (dvoid *) buf,
			(ub4) nSize, (dvoid *)0,
            (sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1)) 0,
			(ub2) 0, (ub1) SQLCS_IMPLICIT))
		{
			GetOraError();
			return FALSE;
		}
		if(amtp<0)
		{
			break;
		}
		file.Write(buf,amtp);
		offset += amtp;
	}
	file.Close();
	SetSuccess();
	return TRUE;
}

int  COciDB::GetLobLen(int nPos)
{
	ASSERT(m_pOciHandle);
	unsigned int nLen;
	if(OCILobGetLength(m_pOciHandle->m_pSvcHpp, m_pOciHandle->m_pErrHpp, m_pLobLocator[nPos], &nLen))
		return 0;
	else
		return (int)nLen;
}