// DatabaseAccess.cpp: implementation of the CDatabaseAccess class.
//
//////////////////////////////////////////////////////////////////////

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

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

CDatabaseAccess::CDatabaseAccess()
{
	CoInitialize(NULL);
	m_bIsOracle=true;		//默認是使用ORACLE數據庫

	try
	{
		m_pCon.CreateInstance(_uuidof(Connection));
	}
	catch(_com_error &e)
	{
		TRACE("--CDatabaseAccess::CDatabaseAccess()-- 創建COM實例失敗%s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
		return;
	}
	catch (...) {
		TRACE("--CDatabaseAccess::CDatabaseAccess()-- 未知錯誤，創建COM實例失敗\n");
		return;
	}
}

CDatabaseAccess::~CDatabaseAccess()
{
	CloseConnect();
}

// 關閉與數據庫的連接
void CDatabaseAccess::CloseConnect()
{	
	try
	{
		if(m_pCon ->State)
			m_pCon ->Close();
	}
	catch(_com_error &e)
	{
		TRACE("--CDatabaseAccess::CloseConnect()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
		return;
	}
	catch (...) {
		TRACE("--CDatabaseAccess::CloseConnect()-- 不明錯誤\n");
		return;
	}
}

// 設置數據庫類型
void CDatabaseAccess::SetDatabaseType(bool bIsOracle/*=true*/)
{
	m_bIsOracle=bIsOracle;
}

//連接到數據庫服務器,采用系統DSN方式 1成功 0失敗
BOOL CDatabaseAccess::ConnectDB(char *pcDSNName,char *pcUserName,char *pcUserPassword)
{  
	//連接Sql Server數據庫
	if (!m_bIsOracle)
	{
	try
	{
		CString		strConnection;	//連接字符串
		strConnection.Format("DSN=%s;UID=%s;PWD=%s" ,pcDSNName ,
			                  pcUserName ,pcUserPassword);
		HRESULT		hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
		strConnection.ReleaseBuffer();

		if(SUCCEEDED(hResult))
			return 1;				//連接成功！
		else
			return 0;				//連接失敗！

	}
	catch(_com_error &e)
	{
		TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
		return 0;				//連接失敗
	}
	catch (...) {
		TRACE("--CDatabaseAccess::ConnectDB()-- 不明錯誤\n");
		return 0;
	}
	}
	return 0;
}

// 連接到數據庫服務器,需要IP,端口和Oracle全局數據庫名或SID的連接方式。1成功 0失敗.
BOOL CDatabaseAccess::ConnectDB(const char *pcUserName ,const char *pcUserPassword ,const char *pcServerIP ,const char *pcServerPort ,const char *pcGlobalDatabaseName/* = "MFD"*/)
{   
	if(!m_bIsOracle)
	{
		try
		{
			CString		strConnection;	//連接字符串
			strConnection.Format("Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s" ,
				 pcUserPassword,pcUserName,pcGlobalDatabaseName,pcServerIP);
			TRACE("%s\n" , strConnection.GetBuffer(0));
			HRESULT		hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
			strConnection.ReleaseBuffer();
			if(SUCCEEDED(hResult))
				return 1;				//連接成功！
			else
				return 0;				//連接失敗！
		}
		catch(_com_error &e)
		{
			TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
			return 0;				//連接失敗
		}
		catch (...) {
			TRACE("--CDatabaseAccess::ConnectDB()-- 不明錯誤\n");
			return 0;
		}
		
	}
    else
	{
	try
	{
		CString		strConnection;	//連接字符串
		strConnection.Format("Provider=MSDAORA.1;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = %s)(PORT = %s)))(CONNECT_DATA =(SERVICE_NAME = %s)));User ID=%s;Password=%s" ,
			pcServerIP ,pcServerPort ,pcGlobalDatabaseName ,pcUserName , pcUserPassword);
		TRACE("%s\n" , strConnection.GetBuffer(0));
		HRESULT		hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
		strConnection.ReleaseBuffer();
		if(SUCCEEDED(hResult))
			return 1;				//連接成功！
		else
			return 0;				//連接失敗！
	}
	catch(_com_error &e)
	{
		TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
		return 0;				//連接失敗
	}
	catch (...) {
		TRACE("--CDatabaseAccess::ConnectDB()-- 不明錯誤\n");
		return 0;
	}
}
}

// 執行存儲過程(普通存儲過程，即非包中的),非零-正確執行；0-失敗(參數個數為負數)。
BOOL CDatabaseAccess::ExecuteProcedure(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bCount)
{
	_CommandPtr			pCmd;
	HRESULT hRet = pCmd.CreateInstance(_uuidof(Command));
	if(FAILED(hRet))
	{
		TRACE("--CDatabaseAccess::ExecuteProcedure()-- 創建COMMAND對象失敗！");
		return 0;
	}

	_ParameterPtr *aParameterPtr = NULL;
	if(bCount > 0)
	{
		// 動態創建參數對象
		aParameterPtr = new _ParameterPtr[bCount];
		memset(aParameterPtr , 0 ,sizeof(_ParameterPtr) * bCount);
	}
	
	try
	{
		_bstr_t    bstrSPName(pcProcedureName);		// 存儲過程名
		if(bCount<=0)								// 無參數
		{
			if(bCount<0)
			{
				return 0;							// 出錯
			}
			
			pCmd->ActiveConnection = m_pCon;		// 設置COMMAND對象的屬性
			pCmd->CommandText = bstrSPName;
			pCmd->CommandType = adCmdStoredProc;
			// 動態創建記錄集對象。
			_RecordsetPtr  *ppRs = new _RecordsetPtr();		
			(*ppRs) = pCmd->Execute(NULL ,NULL ,NULL);		// 執行存儲過程.
			
			return (BOOL)ppRs;
		}
		
        pCmd->ActiveConnection = m_pCon;			// 設置COMMAND對象的屬性
        pCmd->CommandText = bstrSPName;
        pCmd->CommandType = adCmdStoredProc;


        //創建參數實例并添加之
        for(int i=0; i < bCount; i++)
		{
			aParameterPtr[i] = pCmd->CreateParameter(aParameterInfo[i].Name , aParameterInfo[i].Type , aParameterInfo[i].Direction , aParameterInfo[i].Size);
            pCmd->Parameters->Append(aParameterPtr[i]);
		}
		if(!m_bIsOracle)									// SQLServer中需要！
			pCmd->Parameters->Refresh();					// 資源集中的操作
		
		
		// 對輸入參數的處理
        for(i=0; i < bCount; i++)
		{			
			if(aParameterInfo[i].Direction==adParamInput || aParameterInfo[i].Direction == adParamInputOutput)	
			{
				if(m_bIsOracle)								//oracle數據庫
				{
					pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value = aParameterInfo[i].Value; 
				}
				else										//SQLserver數據庫
				{
					CString str;
					str.Format("@%s",((char *)aParameterInfo[i].Name));
					pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value = aParameterInfo[i].Value; 
				}
			}
		}
		
		pCmd->Execute(NULL ,NULL ,NULL);					//通過COMMAND對象來執行存儲過程。
			
		// 對輸出參數的處理
		for(i=0; i < bCount; i++)
		{		
			if(aParameterInfo[i].Direction==adParamOutput || aParameterInfo[i].Direction == adParamInputOutput)	
			{
				if(m_bIsOracle)								//oracle數據庫
				{
					aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value;
				}
				else										//SQLserver數據庫
				{
					CString str;
					str.Format("@%s",((char *)aParameterInfo[i].Name));
					aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value;
				}
			}  
		}
		if(bCount > 0)
			delete []aParameterPtr;
		return 1;												
	}
	catch(_com_error &e)
	{
		TRACE("--CDatabaseAccess::ExecuteProcedure()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
		if(bCount > 0)
			delete []aParameterPtr;
		return 0;
	}
	catch (...) {
		TRACE("--CDatabaseAccess::ExecuteProcedure()-- 不明錯誤\n");
		if(bCount > 0)
			delete []aParameterPtr;
		return 0;