oraclehandler.cpp

基础数据管理工具 主要针对政务系统的基础数据管理、分析、应用等功能！ 项目代码

// OracleHandler.cpp: implementation of the COracleHandler class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "BaseFunc.h"
#include "OracleHandler.h"
#include "CommonUtil.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

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

COracleHandler::COracleHandler()
{
}

COracleHandler::~COracleHandler()
{

}

bool COracleHandler::InitInstance(const EBOraConnectionInfo &ebInfo)
{
	ebConInfo = ebInfo;
	CString sConnectString;
	sConnectString.Format("Provider=OraOLEDB.Oracle;Data Source=%s;User ID=%s;Password=%s;",ebConInfo.sServerName,ebConInfo.sUserName,ebConInfo.sPassword);
	return CADOHandler::InitInstance(sConnectString);
}

bool COracleHandler::TestDBLinkConnective(LPCTSTR pzsLinkName)
{
	CString sSQL;
	sSQL.Format("Select * From Dual@%s",pzsLinkName);
	return ExecuteSQL(sSQL);
}

bool COracleHandler::DropDataBaseLink(LPCTSTR pzsLinkName)
{
	CString sSQL;
	sSQL.Format("Drop DataBase Link %s",pzsLinkName);
	return ExecuteSQL(sSQL);
}

bool COracleHandler::ResetDataBaseLink(LPCTSTR pzsLinkName,LPCTSTR pzsTNSstring,LPCTSTR pzsUserName,LPCTSTR pzsPassWord)
{
	DropDataBaseLink(pzsLinkName);
	CString sSQL;
	sSQL.Format("Create Database Link %s connect to %s identified by \"%s\" using \'%s\'",pzsLinkName,pzsUserName,pzsPassWord,pzsTNSstring);
	if(!ExecuteSQL(sSQL))
		return false;
	//测试连接
	return TestDBLinkConnective(pzsLinkName);
}

bool COracleHandler::RunSQlScript(LPCTSTR pzsFilePath)
{
	//在当前路径下创建BAT文件
	CStdioFile batFile;
	CString sbatPath;
	sbatPath.Format("%s%sSQLRun.bat",CCommonUtil::GetSysPath(),COleDateTime::GetCurrentTime().Format("%Y年%m月%d日-%H时%M分%S秒"));
	if(batFile.Open(sbatPath,CFile::modeCreate|CFile::modeReadWrite)==0)
		return false;
	//写BAT文件
	CString sRunString;
	sRunString.Format("SqlPlus %s/%s@%s @%s",ebConInfo.sUserName,ebConInfo.sPassword,ebConInfo.sServerName,pzsFilePath);
	batFile.WriteString(sRunString);
	batFile.Close();
	//执行BAT文件
	CCommonUtil::RunBatFile(sbatPath);
	CFile::Remove(sbatPath);
	return true;
}

bool COracleHandler::ExportTables(CString sFilePath,CString sTables)
{
	CString sExpString;
	if(sTables.IsEmpty())
		sExpString.Format("EXP %s/%s@%s file=%s log=%s",ebConInfo.sUserName,ebConInfo.sPassword,ebConInfo.sServerName,sFilePath,sFilePath.Left(sFilePath.GetLength()-3)+"log");
	else
		sExpString.Format("EXP %s/%s@%s tables=%s file=%s log=%s",ebConInfo.sUserName,ebConInfo.sPassword,ebConInfo.sServerName,sTables,sFilePath,sFilePath.Left(sFilePath.GetLength()-3)+"log");
	CCommonUtil::RunCommand(sExpString,CREATE_NEW_CONSOLE);
	return true;
}

bool COracleHandler::GetUserBack(LPCTSTR pzsSysUserID,LPCTSTR pzsSysUserPassWord,LPCTSTR pzsBackFilePath)
{
	CString sFilePath=pzsBackFilePath;
	ClearUserAllObjects();
	//导入备份数据
	CString sImpString;
	sImpString.Format("IMP \"\'%s/%s@%s As sysdba\'\" fromuser=%s touser=%s file=%s log=%s",pzsSysUserID,pzsSysUserPassWord,ebConInfo.sServerName,ebConInfo.sUserName,ebConInfo.sUserName,sFilePath,sFilePath.Left(sFilePath.GetLength()-3)+"log");
	CCommonUtil::RunCommand(sImpString,CREATE_NEW_CONSOLE);
	return true;
}

void COracleHandler::ClearUserAllObjects()
{
	//删除用户当前的所有对像
	CString sSQL = "Select \'DROP \'||OBJECT_TYPE||\' \'||OBJECT_NAME||\'' From USER_OBJECTS";
	_RecordsetPtr pRec = GetRecordset(sSQL);
	while(!pRec->GetadoEOF())
	{
		CString sRunstring = CCommonUtil::VarToStr(pRec->GetCollect((long)0));
		ExecuteSQL(sRunstring);
		pRec->MoveNext();
	}
	//递归，确保所有对像被删除
	pRec = GetRecordset(sSQL);
	if(!pRec->GetadoEOF())
		ClearUserAllObjects();
}

bool COracleHandler::CreateSequence(LPCTSTR pzsSequenceName,long lngMinValue,long lngMaxValue,long lngStartValue,long lngIncrementValue)
{
	CString sSQL;
	sSQL.Format("Create Sequence %s Minvalue %d Maxvalue %d Start With %d Increment By %d",pzsSequenceName,lngMinValue,lngMaxValue,lngStartValue,lngIncrementValue);
	if(!ExecuteSQL(sSQL))
		return false;
	return true;
}

long COracleHandler::GetSequenceID(LPCTSTR pzsSequenceName)
{
	long lReturn;
	try
	{
		CString sSQL;
		CString sSeqName = pzsSequenceName;
		sSeqName.MakeUpper();
		//检查该序列对象是否存在
		sSQL.Format("Select * From USER_SEQUENCES Where SEQUENCE_NAME = '%s'",sSeqName);
		_RecordsetPtr pRec = GetRecordset(sSQL);
		if(pRec->GetadoEOF())
		{	//如果不存在则创建该对象
			if(!CreateSequence(sSeqName))
				return 0;
		}
		sSQL.Format("Select %s.Nextval From Dual",pzsSequenceName);
		pRec = GetRecordset(sSQL);
		if(!pRec->GetadoEOF())
			lReturn = pRec->GetCollect((long)0);
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return 0;
	}
	return lReturn;
}