// Odbc1.cpp: implementation of the CMyODBC class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "MyODBC.h"


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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
#pragma comment(lib, "ODBC32")

CMyODBC::CMyODBC()
{
	this->m_hdbc = NULL;
	this->m_henv = NULL;
	this->m_hstmt = NULL;
	this->m_retcode = 0;
	m_pSet = NULL;

}

CMyODBC::~CMyODBC()
{
	DisConnect();
	
}

BOOL CMyODBC::ConnectDB(const char *cpServerName,const char *cpUserName,const char *cpPassword)
{
	//分配環(huán)境句柄
	m_retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		AfxMessageBox("分配環(huán)境句柄失敗!");
		return FALSE;
	}
		/* Set the ODBC version environment attribute */
	m_retcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV, "設(shè)置odbc版本號(hào)時(shí)失敗!");
		return FALSE;
	}	
	/*分配連接句柄 */
	m_retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV,"分配連接句柄失敗!");
		return FALSE;
	}

	/* 連接數(shù)據(jù)庫(kù) */
	m_retcode = SQLConnect(m_hdbc, (SQLCHAR*) cpServerName, SQL_NTS, (SQLCHAR*) cpUserName, SQL_NTS, (SQLCHAR*) cpPassword, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"連接數(shù)據(jù)庫(kù)失敗!");
		return FALSE;
	}
	return TRUE;
}

			

BOOL CMyODBC::ExeSqlDirect(const char *cpSqlStmt)
{
	SQLHSTMT hStmt;
	if(this->m_hdbc == NULL)
	{
		AfxMessageBox("沒有連接數(shù)據(jù)庫(kù)，請(qǐng)先進(jìn)行聯(lián)接!");
		return FALSE;
	}

	m_retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hStmt); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"分配語(yǔ)句句柄失敗,不能執(zhí)行");
		return FALSE;
	}

	m_retcode = SQLExecDirect(hStmt, (unsigned char *)cpSqlStmt, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO) && (m_retcode != SQL_NO_DATA))
	{
		ReportError(hStmt, SQL_HANDLE_STMT, "執(zhí)行sql語(yǔ)句失敗,不能執(zhí)行");
		SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
		hStmt = NULL;
		return FALSE;
	}

	SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
	hStmt = NULL;
	return TRUE;
}

BOOL CMyODBC::DisConnect()
{
	if(m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}

	if(this->m_hdbc != NULL)
	{
		SQLDisconnect(m_hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, m_hdbc);
		m_hdbc = NULL;
	}
	
	if(this->m_henv != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
		m_henv = NULL;
	}

	if(this->m_pSet != NULL)
	{
		m_pSet = NULL;
	}
	return TRUE;

}

BOOL CMyODBC::PrepareSql(const char *cpSql, CODBCSet &rset)
{
	CString strWarn,  strError;;
	if(this->m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}

	if(this->m_hdbc == NULL)
	{
		AfxMessageBox("沒有連接數(shù)據(jù)庫(kù)，請(qǐng)先進(jìn)行聯(lián)接!");
		return FALSE;
	}

	m_retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"分配語(yǔ)句句柄失敗,不能執(zhí)行");
		return FALSE;
	}

	this->m_pSet = &rset;
	if(!m_pSet->IsEmpty())
	{
		m_pSet->Empty();
	}

	m_retcode = SQLExecDirect(m_hstmt, (unsigned char *)cpSql, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hstmt,SQL_HANDLE_STMT, "執(zhí)行sql語(yǔ)句失敗");
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
		return FALSE;
	}
	
	SQLSMALLINT iNumCols = 0;
	m_retcode = SQLNumResultCols(m_hstmt, &iNumCols); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hstmt,SQL_HANDLE_STMT,  "取列數(shù)失敗,不能執(zhí)行");
		return FALSE;
	}
	m_pSet->m_cols = iNumCols;
	m_pSet->m_coldata = new COL_DATA_ODBC[iNumCols];
	if (m_pSet->m_coldata == NULL) 
	{
		AfxMessageBox("分配每個(gè)列內(nèi)存空間失敗\n");
		return FALSE;
	}

	m_pSet->m_coldatafmt  = new COL_DATAFMT_ODBC[iNumCols];
	if(m_pSet->m_coldatafmt == NULL)
	{
		AfxMessageBox("分配每個(gè)列信息的內(nèi)存空間失敗\n");
		delete []m_pSet->m_coldata;
		m_pSet->m_coldata = 0;
		return FALSE;
	}

	SQLINTEGER    dataLen;
	SQLSMALLINT  SqlType, cType;   
	char   SqlColName[100];
	SQLSMALLINT StringLeng;
	SqlType = SQL_INTEGER ;

	for(int iCount = 0; iCount <iNumCols; iCount++)
	{
		m_retcode = SQLColAttribute(m_hstmt, ((SQLUSMALLINT)iCount) + 1, SQL_DESC_NAME, (SQLPOINTER)SqlColName, 100, &StringLeng, 0);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT,"取列名時(shí)失敗");
			return FALSE;
		}
		strncpy(m_pSet->m_coldatafmt[iCount].name, SqlColName, StringLeng);
		m_pSet->m_coldatafmt[iCount].name[StringLeng] = 0;

		m_retcode = SQLColAttribute(m_hstmt, ((SQLUSMALLINT)iCount) + 1, SQL_DESC_TYPE, NULL, 0, NULL, (SQLPOINTER)&SqlType);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT,"取類型代碼時(shí)失敗");
			return FALSE;
		}
		cType = GetDefaultCType(SqlType);
		m_pSet->m_coldatafmt[iCount].datatype = cType;
		dataLen = GetColLength(m_hstmt, SqlType,iCount+1)  ;
		m_pSet->m_coldatafmt[iCount].maxlength = dataLen + 1;
		
		m_pSet->m_coldata[iCount].valuelen = new long;
		m_pSet->m_coldata[iCount].value = new char[dataLen+1];

		if( m_pSet->m_coldata[iCount].value == NULL ||  m_pSet->m_coldata[iCount].valuelen == NULL) 
		{
			AfxMessageBox("fail: new char[]");
			delete m_pSet->m_coldata[iCount].value;
			delete m_pSet->m_coldata[iCount].valuelen;
			delete m_pSet->m_coldata;
			delete m_pSet->m_coldatafmt;
			m_pSet->m_coldata = 0;
			m_pSet->m_coldatafmt = 0;
			return FALSE;
		}
		memset(m_pSet->m_coldata[iCount].value, 0, dataLen+1);
	}
		//bind
	// 
	for(int i = 0; i < iNumCols; i++)
	{
		m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i)+1,(SQLSMALLINT)m_pSet->m_coldatafmt[i].datatype ,m_pSet->m_coldata[i].value, m_pSet->m_coldatafmt[i].maxlength, (long *)m_pSet->m_coldata[i].valuelen);

		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strError.Format("第 %d 列綁定失敗,你指定的類型是%d",i + 1, m_pSet->m_coldatafmt[i].datatype);
			ReportError(m_hstmt,SQL_HANDLE_STMT, strError);
			return FALSE;
		}
	}

	return TRUE;
}





BOOL CMyODBC::FetchData()
{
	if((m_retcode = SQLFetch(m_hstmt)) != SQL_NO_DATA)
	{
		ConvertDataToChar();
		return TRUE;
		
	}
	else
	{
		if(m_hstmt != NULL)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
			m_hstmt = NULL;
		}
		return FALSE;
	}

}

//iColumnIndex用來(lái)表示列的序號(hào)，從1開始計(jì)數(shù)
SQLSMALLINT   CMyODBC::GetDefaultCType(long iODBCType)
{
	CString strWarn;
	SQLSMALLINT      iResult;
	switch(iODBCType)
	{
	case SQL_VARCHAR:
	case SQL_CHAR:
	case SQL_DECIMAL:
	case SQL_NUMERIC:
	case SQL_DOUBLE:
	case SQL_SMALLINT:
	case SQL_INTEGER:
	case SQL_FLOAT:
	case SQL_REAL:     
		iResult = SQL_C_CHAR; 
		break;
	case SQL_DATETIME:
		iResult = SQL_C_TYPE_TIMESTAMP;
		break;
	default:
		strWarn.Format("不支持這種轉(zhuǎn)換--%d", iODBCType);
		AfxMessageBox(strWarn);
		iResult = -1;
		break;
	}
	return iResult;

}

long CMyODBC::GetColLength(SQLHSTMT &hstmt,int iDataType, int iCol)
{
	long lTemp = 0, lResult = -1;
	switch(iDataType)
	{

	case SQL_CHAR:
	case SQL_VARCHAR:
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_OCTET_LENGTH, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("取列長(zhǎng)度時(shí)失敗");
			return FALSE;
		}
		break;
	case SQL_NUMERIC:
	case SQL_DECIMAL:
	case SQL_DOUBLE:
	case SQL_SMALLINT:
	case SQL_INTEGER:
	case SQL_FLOAT:
	case SQL_REAL:    
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_PRECISION, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("取列整數(shù)部分的長(zhǎng)度時(shí)失敗");
			return FALSE;
		}

		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_SCALE, NULL, 0, NULL, (SQLPOINTER)&lTemp);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("取列小數(shù)長(zhǎng)度時(shí)失敗");
			return FALSE;
		}
		lResult += lTemp ;
		break;

	case SQL_DATETIME:
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_PRECISION, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("取列整數(shù)部分的長(zhǎng)度時(shí)失敗");
			return FALSE;
		}

		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_SCALE, NULL, 0, NULL, (SQLPOINTER)&lTemp);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			AfxMessageBox("取列小數(shù)長(zhǎng)度時(shí)失敗");
			return FALSE;
		}
		lResult += lTemp + 1;
		break;

		default:
			AfxMessageBox("不支持這種類型");
			break;
	}

	return lResult ;

}

void CMyODBC::ReportError(SQLHSTMT &hstmt, int iHandleType ,CString strAlert)
{
	
	unsigned char *SQLState = new unsigned char[6];
	if(SQLState == NULL)
	{
		AfxMessageBox("報(bào)告發(fā)生錯(cuò)誤的原因時(shí)，分配sqlstat內(nèi)存失敗");
		return;
	}
	char Message[500] = "\0";
	short iMesLen;
	CString strError;
	SQLGetDiagRec(iHandleType, hstmt, 1, SQLState, NULL, (unsigned char  *)Message, 500, &iMesLen);
	strError.Format("%s,%s" , strAlert, Message);
	AfxMessageBox(strError);
	delete SQLState; SQLState= NULL;
}



int CMyODBC::ConvertDataToChar()
{
	int i;
	TIMESTAMP_STRUCT timeStamp;

	for(i = 0; i < m_pSet->GetCols(); i++)
	{
		if(*(m_pSet->m_coldata[i].valuelen) == -1)//處理的值為空
		{
			memset(m_pSet->m_coldata[i].value, 0, m_pSet->m_coldatafmt[i].maxlength);
			*(m_pSet->m_coldata[i].valuelen) = m_pSet->m_coldatafmt[i].maxlength - 1;
			continue;
		}
		
		switch (m_pSet->m_coldatafmt[i].datatype)
		{
		case SQL_C_CHAR:
			break;

		case SQL_C_TYPE_TIMESTAMP:
			timeStamp.year = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->year;
			timeStamp.month = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->month;
			timeStamp.day = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->day;
			timeStamp.hour = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->hour;
			timeStamp.minute = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->minute;
			timeStamp.second = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->second;
			timeStamp.fraction = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->fraction;
			sprintf(m_pSet->m_coldata[i].value, "%4u-%2u-%2u %2u:%2u:%2u", timeStamp.year, timeStamp.month, timeStamp.day, timeStamp.hour, timeStamp.minute, timeStamp.second);
			*(m_pSet->m_coldata[i].valuelen) = 19;
			break;
		default:
			AfxMessageBox("還沒有支持這種數(shù)據(jù)類型的轉(zhuǎn)換");
			return -1;
		}
	}
	return 0;
		

}

int CMyODBC::ExecTrans(CStringList &strSqlList)
{
	POSITION pos;
	CString strSql;
	BOOL bStatus;
	int iReturn = 0;
	if(m_hdbc == NULL)
	{
		AfxMessageBox("連接句柄為空，不能執(zhí)行");
		return -1;
	}
	//設(shè)置提交方式為手動(dòng)
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT,	SQL_AUTOCOMMIT_OFF);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"設(shè)置手動(dòng)提交方式失敗");
		return -1;
	}
	
	//執(zhí)行sql
	for(pos = strSqlList.GetHeadPosition(); pos != NULL;)
	{
		strSql = strSqlList.GetNext(pos);
		bStatus = this->ExeSqlDirect(strSql); 
		if(bStatus == FALSE)//執(zhí)行失敗，回滾
		{
			m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK);
			if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
			{
				ReportError(m_hdbc, SQL_HANDLE_DBC,"執(zhí)行事務(wù)失敗，并且回滾失敗");
			}
			iReturn = -1;
			goto RESET_AUTO_COMMIT;
		}
	}
	//提交
	m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_COMMIT);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"執(zhí)行事務(wù)完成后，提交失敗");
		iReturn = -1;
	}
	//再把提交方式設(shè)為自動(dòng)
RESET_AUTO_COMMIT:
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"設(shè)置自動(dòng)提交方式失敗");
		iReturn = -1;
	}

	return iReturn;

}

//下面的這個(gè)不能檢查到數(shù)據(jù)庫(kù)是否已經(jīng)關(guān)了。

BOOL CMyODBC::IsOpen()
{
	if(this->m_henv == NULL)
	{
		return FALSE;
	}

	if(this->m_hdbc == NULL)
	{
		return FALSE;
	}
	return TRUE;

}