sqlserver_helper.cpp

C++ODBC连接数据库的标准封装类

/***************************************************************
* Author：		liuxinyi
* Company：		Intervision							  			
* Last Update：	2005-11-10		 								
* Copyright(c), Intervision Software Co., Ltd. Beijing. 
* 				All Rights Reserved	
*------------------------------------------------------------ 	
* FileName：		SQLServer_Helper.h/cpp
* Function Note：	系统连接ODBC，进行加载等操作.
* Update note：		Created at 2006-04-19
*****************************************************************/

#include <stdio.h>
#include <assert.h>
#include <STAR/CORBA.h>
#include <SQLServer_Helper.h>
#include "des.h"

using namespace Global_Defines_T;

const long MAXBUFLEN=100;

SQLServer_Helper_T::SQLServer_Helper_T()
{
	//获得sql server 用户密码
	if(!getDBUserPW())
	{
		STAR_COUT<<"获取数据库用户错误"<<STAR_ENDL;
		g_CLog.Userlog(1, "%s::%s", "数据库操作","读取数据库用户错误");		
		exit(0);
	}
}


BOOL SQLServer_Helper_T::Connect(LPCSTR pszSourceName,LPCSTR pszUserId,LPCSTR pszPassword,BOOL blnAutoTranfer) 
{ 
	BOOL bReturn=TRUE; 
	SWORD wLengthUID=0; 
	SWORD wLengthPSW=0; 
	SWORD wLengthSN=0;
	SQLRETURN sr; 
	
	//SQLAllocHandle 创建ODBC句柄
	sr=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_henv); 
	if(sr!=SQL_SUCCESS) 
	{ 
		g_CLog.Userlog(1, "%s::%s", "数据库操作","建立ODBC环境句柄错误");		
		exit(0);		
	} 
	
	//将ODBC设置成为版本3    SQL_ATTR_ODBC_VERSION SQLPOINTER SQL_IS_INTEGER
	sr=SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION , (void *) SQL_OV_ODBC3, 0); 	
	
	if(sr!=SQL_SUCCESS) 
	{ 
		g_CLog.Userlog(1, "%s::%s", "数据库操作","系统不支持ODBC3.x");		
		exit(0);		
	} 
	//创建连接句柄 
	sr=SQLAllocHandle(SQL_HANDLE_DBC,m_henv,&m_hdbc); 
	if(sr!=SQL_SUCCESS) 
	{ 
		SQLFreeHandle(SQL_HANDLE_DBC, m_henv); 					
		g_CLog.Userlog(1, "%s::%s", "数据库操作","SQLAllocHandle error");		
		exit(0);				
	}
	if(blnAutoTranfer)
	{
		sr = SQLSetConnectAttr (m_hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, SQL_IS_POINTER);
	}else
	{
		sr= SQLSetConnectAttr(m_hdbc,SQL_LOGIN_TIMEOUT,(void*) 5,0); 		
	}
	if(sr!=SQL_SUCCESS) 
	{ 
		g_CLog.Userlog(1, "%s::%s", "数据库操作","SQLSetConnectAttr error");		
		exit(0);						
	}
	
	//连接源
	m_pszSourceName=new char[strlen(pszSourceName)+1]; 
	strcpy(m_pszSourceName,pszSourceName); 
	wLengthSN=strlen(pszSourceName)+1;
	
	if(pszUserId!=NULL) 
	{ 
		m_pszUserId=new char[strlen(pszUserId)+1]; 
		strcpy(m_pszUserId,pszUserId); 
		wLengthUID=strlen(pszUserId)+1; 
	} 
	
	if(pszPassword!=NULL) 
	{ 
		m_pszPassword=new char[strlen(pszPassword)+1]; 
		strcpy(m_pszPassword,pszPassword); 
		wLengthPSW=strlen(pszPassword)+1; 
	} 
	
	sr=SQLConnect(m_hdbc,
		(PUCHAR) pszSourceName,wLengthSN, 
		(PUCHAR) pszUserId,wLengthUID, 
		(PUCHAR) pszPassword,wLengthPSW); 
	
	//操作系统找不到已输入的环境选项。
	DWORD aaa=GetLastError();
	
	if(sr!=SQL_SUCCESS) 
	{ 
		bReturn=true; 
	} 
	m_bConnected = bReturn;
	return bReturn; 
} 

BOOL SQLServer_Helper_T::ExecDirectSQL(SQLHSTMT &m_hstmt,unsigned char* sqltext)
{
	SQLRETURN sr; 
	sr=SQLAllocHandle(SQL_HANDLE_STMT,m_hdbc,&m_hstmt); 
	
	
	if(sr!=SQL_SUCCESS) 
	{ 
	    STAR_COUT<<"连接数据库失败,请检查数据库配置"<<STAR_ENDL;
		g_CLog.Userlog(1, "%s::%s", "数据库操作","申请SQL语句句柄失败");		
		exit(0);								
	} 
	
	sr=SQLExecDirect(m_hstmt,sqltext,SQL_NTS); 
	
	if ((sr != SQL_SUCCESS) && (sr != SQL_SUCCESS_WITH_INFO)&&
		(sr != SQL_NO_DATA)) 
	{
		std::string temp="执行SQL语句失败";
		STAR_COUT<<"执行SQL语句失败"<<STAR_ENDL;

		g_CLog.Userlog(1, "%s::%s:%d", "数据库操作",temp.c_str(),sr);		
		//exit(0);			
		return FALSE;
	}
	return TRUE; 
}

//断开连接函数 
void SQLServer_Helper_T::Disconnect() 
{ 
	SQLRETURN sr; 
	if(m_pszSourceName) 
	{ 
		delete m_pszSourceName; 
		m_pszSourceName=NULL; 
	} 
	
	if(m_pszUserId) 
	{ 
		delete m_pszUserId; m_pszUserId=NULL; 
	} 
	
	if(m_pszPassword) 
	{ 
		delete m_pszPassword; m_pszPassword=NULL; 
	} 
	
	if(m_hdbc) 
	{ 
		if(m_bConnected) 
		{ 
			sr=SQLDisconnect(m_hdbc); 
			m_bConnected=FALSE; 
		} 
		sr=SQLFreeHandle(SQL_HANDLE_DBC,m_hdbc); 
		m_hdbc=SQL_NULL_HENV; 
	} 
	
	if(m_henv) 
	{ 
		sr=SQLFreeHandle(SQL_HANDLE_ENV,m_henv); 
		m_henv=SQL_NULL_HENV; 
	} 
} 

	
SQLServer_Helper_T::~SQLServer_Helper_T()
{
	Disconnect();
}
	

/************************************************************************/
/*解密des，密钥直接写在函数中，输入密文secret，返回解密数值source       */
/************************************************************************/
void SQLServer_Helper_T::decryptDes(const char *secret,char &source)
{
	unsigned char key[]="suspectkyry";
	DES *des=new DES;
	
	int datalen = (strlen(secret)/16+1)*8;				//给des提供的数据的长度，也是从des返回的数据的长度(需要解密的字符串的hex长度，如果存在一半，则舍去)
	unsigned char *data = new unsigned char[datalen];	//进行调用des类的传入传出字符串
	memset(data,0,datalen);								//设置data的初始值，因为下面赋值语句不一定能够占满data的长度

	//将需要解密的字符串转换成hex模式放置到data中
	char tmpChar[3];									//临时的三位的char
	
	for (unsigned int i=0;i<(strlen(secret)/2);i++)
	{
		tmpChar[0] = secret[i*2];
		tmpChar[1] = secret[i*2+1];
		tmpChar[2] = '\0';

		data[i] = (unsigned char)strtoul(tmpChar,NULL,16);
	}
	
	//调用decrypt进行解密
	des->decrypt(key, data, datalen/8);

	memcpy(&source,data,datalen);

	delete[] data;
	delete des;
}

BOOL SQLServer_Helper_T::getDBUserPW()
{
	BOOL state = FALSE;
	SQLCHAR mysqlTemp[]="select user_name,password from sys_user";
	SQLINTEGER Len[2] ;
	SQLCHAR strUser[30]={'\0'};
	SQLCHAR strPW[40]={'\0'};
	
				
	BOOL blnCnn=Connect(m_pszSourceName,m_pszUserId,m_pszPassword,FALSE);
	if(!blnCnn)
	{
		g_CLog.Userlog(1, "%s::%s", "数据库操作","getDBUserPW连接出错");		
		exit(0);			
	}else
	{
		m_blnQuery=TRUE;
	}
	SQLRETURN sr; 
	SQLHSTMT m_hstmt;
	try{	
		state=ExecDirectSQL(m_hstmt,mysqlTemp);
		if(!state)
		{
			g_CLog.Userlog(1, "%s::%s", "数据库操作","getDBUserPW 不能正常读取用户和密码");	
			if(m_hstmt)
			{
				sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt);
				m_hstmt=SQL_NULL_HENV;  
			}		
			exit(0);	
		}
		
		SQLBindCol(m_hstmt,1,SQL_C_CHAR,&strUser,40,&Len[1]); 
		SQLBindCol(m_hstmt,2,SQL_C_CHAR,&strPW,50,&Len[2]); 
		while ((SQLFetch(m_hstmt)) != SQL_NO_DATA) 
		{ 
			// printf("strUser=%s\nstrPW=%s\n ",strUser,strPW); 
			m_pszDBUser=CORBA::string_dup((char*)strUser);
			m_pszDBPW=CORBA::string_dup((char*)strPW);

			//使用DES解密数据库密码
			decryptDes(m_pszDBPW,*m_pszDBPW);
		}
	}catch (...)
	{
		g_CLog.Userlog(1, "%s::%s", "数据库操作","getDBUserPW绑定读取用户和密码出错");
		if(m_hstmt)
		{
			sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt);
			m_hstmt=SQL_NULL_HENV;  
		}		
	}
	// printf("m_pszDBUser=%s\n m_pszDBPW=%s\n ",m_pszDBUser,m_pszDBPW); 	
	if(m_hstmt)
	{
		sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt);
		m_hstmt=SQL_NULL_HENV;  
	}		
	
	return TRUE;
}


BOOL SQLServer_Helper_T::OperaterDB(std::string strSQL)
{
	SQLHSTMT     m_hstmt1;
	BOOL state = FALSE;
	SQLRETURN sr; 
	
	//std::string strSyncDataSQL  = "Delete FROM  SUS_INFO_CHANGE ";
	if(!m_blnQuery)
	{
		BOOL blnCnn=Connect(m_pszSourceName,m_pszUserId,m_pszPassword,FALSE);
		if(!blnCnn)
		{
			m_blnQuery=FALSE;
			g_CLog.Userlog(1, "%s::%s", "数据库操作","getDBUserPW连接出错");		
			exit(0);			
		}else
		{
			m_blnQuery=TRUE;
		}
	}
	if(strSQL.length()==0)
	{
		return FALSE;
	}
	
	try{
		state=ExecDirectSQL(m_hstmt1,(unsigned char*)strSQL.c_str());
		if(!state)
		{
			SQLEndTran(SQL_HANDLE_DBC,m_hstmt1,SQL_ROLLBACK);
			g_CLog.Userlog(1, "%s::%s", "数据库操作","SyncData更新数据库数据失败");
			if(m_hstmt1)
			{
				sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt1);
				m_hstmt1=SQL_NULL_HENV;  
			}
			//exit(0);										
		}else
			SQLEndTran(SQL_HANDLE_DBC,m_hstmt1,SQL_COMMIT);		
	}
	catch (...) 
	{
		g_CLog.Userlog(1, "%s::%s", "数据库操作","更新数据库数据失败");
		if(m_hstmt1)
		{
			sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt1);
			m_hstmt1=SQL_NULL_HENV;  
		}
	}	
	if(m_hstmt1)
	{
		sr=SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt1);
		m_hstmt1=SQL_NULL_HENV;  
	}
	state=TRUE;
	return state;		
}