myodbc.cpp

VC后台服务的模板框架

{
	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("不支持这种转换--%d", iODBCType);
		DEBUGINFO(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))
		{
			DEBUGINFO("取列长度时失败");
			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))
		{
			DEBUGINFO("取列整数部分的长度时失败");
			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))
		{
			DEBUGINFO("取列小数长度时失败");
			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))
		{
			DEBUGINFO("取列整数部分的长度时失败");
			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))
		{
			DEBUGINFO("取列小数长度时失败");
			return FALSE;
		}
		lResult += lTemp + 1;
		break;

		default:
			DEBUGINFO("不支持这种类型");
			break;
	}

	return lResult ;

}

void CMyODBC::ReportError(SQLHSTMT &hstmt, int iHandleType ,CString strAlert)
{
	
	unsigned char *SQLState = new unsigned char[6];
	if(SQLState == NULL)
	{
		DEBUGINFO("报告发生错误的原因时，分配sqlstat内存失败");
		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);
	DEBUGINFO(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:
			DEBUGINFO("还没有支持这种数据类型的转换");
			return -1;
		}
	}
	return 0;
		

}

int CMyODBC::ExecTrans(CStringList &strSqlList)
{
	POSITION pos;
	CString strSql;
	BOOL bStatus;
	int iReturn = 0;
	if(m_hdbc == NULL)
	{
		DEBUGINFO("连接句柄为空，不能执行");
		return -1;
	}
	//设置提交方式为手动
	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,"设置手动提交方式失败");
		return -1;
	}
	
	//执行sql
	for(pos = strSqlList.GetHeadPosition(); pos != NULL;)
	{
		strSql = strSqlList.GetNext(pos);
		bStatus = this->ExeSqlDirect(strSql); 
		if(bStatus == FALSE)//执行失败，回滚
		{
			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,"执行事务失败，并且回滚失败");
			}
			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,"执行事务完成后，提交失败");
		iReturn = -1;
	}
	//再把提交方式设为自动
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,"设置自动提交方式失败");
		iReturn = -1;
	}

	return iReturn;

}

//下面的这个不能检查到数据库是否已经关了。

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

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

}


BOOL CMyODBC::MySqlBind(int i,int tInt_BindType,unsigned char *uChr_Value,int *tInt_len)
{
		CString   strError;
	
		m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i),tInt_BindType ,uChr_Value, 60, (long *)tInt_len);

		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strError.Format("第 %d 列绑定失败,你指定的类型是%d",i + 1, tInt_BindType);
			ReportError(m_hstmt,SQL_HANDLE_STMT, strError);
			return FALSE;
		}
		
		return TRUE;

}



void CMyODBC::MyODBCSQLFreeHandle()
{
	if(m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}
}
BOOL CMyODBC::ExeSqlDirect(const char *cpSqlStmt)
{
	BOOL tbl_conn=true,tbl_Exec=false;
	int tInt_times=0;

    while(!tbl_Exec){

		if(tbl_conn)
			tbl_Exec=ExeSqlDirectEx(cpSqlStmt);
		
		if(tbl_Exec||(tInt_times>2))break;
		else{ 
			if(tbl_conn)
				DEBUGINFO("执行数据库语句[%s]失败，并尝试重新连接\n",cpSqlStmt);

			DisConnect();
			tbl_conn=ConnectDB(gStr_dsname,gStr_username,gStr_passwd);
			if(tbl_conn){
				tInt_times++;
				::OutputDebugString("\n=======Database ReConnect  ok!!!=====\n");
			}else {
				
				::OutputDebugString("\n=======Database ReConnect  fail!!!=====\n");
				Sleep(2);
				}
			
		}
	}

	return TRUE;

}
BOOL CMyODBC::ExeSqlDirectEx(const char *cpSqlStmt)
{
	SQLHSTMT hStmt;
	if(this->m_hdbc == NULL)
	{
		DEBUGINFO("没有连接数据库，请先进行联接!");
		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,"分配语句句柄失败,不能执行");
		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, "执行sql语句失败,不能执行");
		SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
		hStmt = NULL;
		return FALSE;
	}

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


BOOL CMyODBC::PrepareSql(const char *cpSql, CODBCSet &rset)
{
	BOOL tbl_conn=true,tbl_Exec=false;
	int tInt_times=0;


    while(!tbl_Exec){

		if(tbl_conn)
			tbl_Exec=PrepareSqlEx(cpSql, rset);
		
		if(tbl_Exec||(tInt_times>2))break;
		else{ 

			if(tbl_conn)
				DEBUGINFO("执行数据库语句[%s]失败，并尝试重新连接\n",cpSql);

			DisConnect();
			tbl_conn=ConnectDB(gStr_dsname,gStr_username,gStr_passwd);
			if(tbl_conn){
				tInt_times++;
				::OutputDebugString("\n=======Database ReConnect  ok!!!=====\n");
			}else {
				
				::OutputDebugString("\n=======Database ReConnect  fail!!!=====\n");
				Sleep(2);
				}	
		}
	}

	return TRUE;
}

BOOL CMyODBC::PrepareSql(const char *cpSql, CODBCSet &rset, int BindNum)
{
	BOOL tbl_conn=true,tbl_Exec=false;
	int tInt_times=0;

    while(!tbl_Exec){

		if(tbl_conn)
			tbl_Exec=PrepareSqlEx(cpSql, rset,BindNum);
		
		if(tbl_Exec||(tInt_times>2))break;
		else{ 

			if(tbl_conn)
				DEBUGINFO("执行数据库语句[%s]失败，并尝试重新连接\n",cpSql);

			DisConnect();
			tbl_conn=ConnectDB(gStr_dsname,gStr_username,gStr_passwd);
			if(tbl_conn){
				tInt_times++;
				::OutputDebugString("\n=======Database ReConnect  ok!!!=====\n");
			}else {
				
				::OutputDebugString("\n=======Database ReConnect  fail!!!=====\n");
				Sleep(2);
				}	
		}
	}

	return TRUE;
}