db2lib.cpp

访问db2数据库的VC封装

////////////////////////////////////////////////////////////////////////
// Db2lib.cpp: implementation of the CDb2lib class.
//
// class CDb2lib for access IBM DB2 DATABASE by DB2 CLI
// Written by Yank
// Modified 2001/05/18
////////////////////////////////////////////////////////////////////////

//#ifdef __BORLANDC__
//#pragma warn +wucp
//#endif


#include "stdafx.h"
#include "errorlog.h"
#include <sqlcli.h>
#include <sqlcli1.h>
#include <sqlsystm.h>
#include "Db2lib.h"
#include "time.h"
#include <stdlib.h>
#include <stdio.h>
#include <io.h>
#include <fcntl.h>
#include <sys\types.h>
#include <sys\stat.h>

#ifdef __BORLANDC__
#include <forms.hpp>
#endif

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


CDb2lib::CDb2lib()
{
    Init();
}

CDb2lib::~CDb2lib()
{
	Close();
}


bool CDb2lib::Close()
{
    SQLRETURN lrc ;
	
	/*Disconnect and free Connection Handle before free environment handle*/	
    if (m_hdbc != NULL)
	{
		lrc = SQLDisconnect( m_hdbc ) ;
		if (lrc != SQL_SUCCESS)
			handle_error( SQL_HANDLE_DBC, m_hdbc, lrc) ;
		
		lrc = SQLFreeHandle( SQL_HANDLE_DBC, m_hdbc ) ;
		if (lrc != SQL_SUCCESS)
			handle_error( SQL_HANDLE_DBC, m_hdbc, lrc) ;
	}
	
    /* Free environment handle */
	if ( ( lrc = SQLFreeHandle( SQL_HANDLE_ENV, m_henv ) ) != SQL_SUCCESS )
		handle_error( SQL_HANDLE_ENV, m_henv, lrc) ;
    return true;
}

bool CDb2lib::Init()
{
	
    m_loginOK = false;
    memset(m_serverName, 0, sizeof(m_serverName));
    memset(m_loginID, 0, sizeof(m_loginID));
    memset(m_passWord, 0, sizeof(m_passWord));
    m_rowCount = 0;
    m_colCount = 0;
    m_errorCode = 0;
    memset(m_errorMsg, 0, sizeof(m_errorMsg));
    memset(m_colInfo, 0, sizeof(m_colInfo));
	
	m_henv = NULL;
	m_hdbc = NULL;
	m_hstmt = NULL;
	m_rc = 0;
	memset(m_stmt, 0, sizeof(m_stmt));
    return true;
}

bool CDb2lib::Login(const char *servername, const char *loginID, const char *passWord)
{
	m_rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv ) ;   //alloc Environment Handle 
	if ( m_rc != SQL_SUCCESS ) 
	{
		terminate( m_henv, m_rc );
		return false ;
	}
	/* Connect to DB2 database*/	
	m_rc = DBconnect( m_henv, &m_hdbc, (SQLCHAR *)servername, (SQLCHAR *)loginID, (SQLCHAR *)passWord) ;
	if ( m_rc != SQL_SUCCESS ) 
	{
		terminate( m_henv, m_rc );
		return false ;
	}
	/*Alloc Statement Handle*/
	m_rc = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt ) ;
	if ( m_rc != SQL_SUCCESS )
	{	
		terminate( m_henv, m_rc );
		return false;
	}
	return true;
	
}

SQLRETURN CDb2lib::terminate( SQLHANDLE henv, SQLRETURN rc)
{
	
    SQLRETURN lrc ;
	
    handle_error( SQL_HANDLE_ENV, henv, rc) ;
	
	/*Disconnect and free Connection Handle before free environment handle*/	
    if (m_hdbc != NULL)
	{
		lrc = SQLDisconnect( m_hdbc ) ;
		if (lrc != SQL_SUCCESS)
			handle_error( SQL_HANDLE_DBC, m_hdbc, lrc) ;
		
		lrc = SQLFreeHandle( SQL_HANDLE_DBC, m_hdbc ) ;
		if (lrc != SQL_SUCCESS)
			handle_error( SQL_HANDLE_DBC, m_hdbc, lrc) ;
	}
	
    /* Free environment handle */
	if ( ( lrc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ) != SQL_SUCCESS )
		handle_error( SQL_HANDLE_ENV, henv, lrc) ;
	return( rc ) ;
	
}

SQLRETURN CDb2lib::handle_error( SQLSMALLINT htype, /* A handle type identifier */
								SQLHANDLE   hndl,  /* A handle */
								SQLRETURN   frc   /* Return code to be included with error msg  */
								)
{
	
	SQLSMALLINT    i = 1 ;
    FILE *fp;
    time_t t;
    struct tm * tmlocal;
	
	/* Catch error informations where execute CLI functions*/
    while ( SQLGetDiagRec( htype,
		hndl,
		i,
		m_dbErrorInfo.SqlState,
		&(m_dbErrorInfo.SqlCode),
		m_dbErrorInfo.ErrorMsg,
		SQL_MAX_MESSAGE_LENGTH + 1,
		&(m_dbErrorInfo.Length)
		) == SQL_SUCCESS ) 
	{
		/* Write error log when some errors catched*/
		i++ ;
        t = ::time(&t);
        tmlocal = ::localtime(&t);
		fp = fopen(GetErrorLogFileName(), "a");
		if(fp != NULL)
		{
			fprintf(fp, "%04d-%02d-%02d [%02d:%02d:%02d] ", tmlocal->tm_year + 1900, tmlocal->tm_mon + 1, tmlocal->tm_mday, tmlocal->tm_hour, tmlocal->tm_min, tmlocal->tm_sec);
			fprintf(fp, "SQLSTATE: %s\n", m_dbErrorInfo.SqlState);
			fprintf(fp,"Native Error Code: %ld\n", m_dbErrorInfo.SqlCode);
			fprintf(fp,"Native Error Message: %s\n", m_dbErrorInfo.ErrorMsg);
			fclose(fp);
		}
    }
	t = ::time(&t);
	tmlocal = ::localtime(&t);
	
	
    return( SQL_ERROR ) ;
	
}

SQLRETURN CDb2lib::DBconnect( SQLHANDLE henv, SQLHANDLE * hdbc, SQLCHAR *servername, SQLCHAR *loginID, SQLCHAR *passWord) 
{
	
    SQLRETURN   rc ;

    /* Set environment Attribute to SQL_ATTR_ODBC_VERSION for avoid to HY011 error*/
	if ( SQLSetEnvAttr( henv, SQL_ATTR_ODBC_VERSION , ( SQLPOINTER ) SQL_OV_ODBC3, 0) != SQL_SUCCESS ) 
	{
        m_errorCode = -1;
        strcpy(m_errorMsg, "设置DB2连接属性失败");
        m_loginOK = false;
        return( SQL_ERROR ) ;
    }
	
	/* allocate a connection handle */
    if ( SQLAllocHandle( SQL_HANDLE_DBC, henv, hdbc) != SQL_SUCCESS ) 
	{
        m_errorCode = -1;
        strcpy(m_errorMsg, "连接DB2设置连接句柄失败");
        m_loginOK = false;
        return( SQL_ERROR ) ;
    }
	
    /* Set AUTOCOMMIT OFF */
    if ( SQLSetConnectAttr( * hdbc, SQL_ATTR_AUTOCOMMIT, ( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS) != SQL_SUCCESS ) 
	{
        m_errorCode = -1;
        strcpy(m_errorMsg, "设置DB2连接属性失败");
        m_loginOK = false;
        return( SQL_ERROR ) ;
    }
	/*Connect to database*/
    rc = SQLConnect( * hdbc, servername, SQL_NTS, loginID, SQL_NTS, passWord, SQL_NTS);
	if (rc != SQL_SUCCESS ) 
	{
        m_errorCode = -1;
        strcpy(m_errorMsg, "连接DB2失败");
        m_loginOK = false;
		SQLDisconnect( * hdbc ) ;
        SQLFreeHandle( SQL_HANDLE_DBC, * hdbc ) ;
        return( SQL_ERROR ) ;
    }
	
    return( SQL_SUCCESS ) ;
	
}

bool CDb2lib::Open(const char *servername, const char *loginID, const char *passWord)
{
    if(m_loginOK == true)
        return true;
	
    if(Login(servername, loginID, passWord) == false)
        return false;
	
    strcpy(m_serverName, servername);
    strcpy(m_loginID, loginID);
    strcpy(m_passWord, passWord);
	
    m_errorCode = 0;
    strcpy(m_errorMsg, "连接SQL Server成功");
    m_loginOK = true;
    return true;
}

bool CDb2lib::Run(const char *CommandStr, char *Param[], int ParamNum)
{
	
    char *pdest;
    int  result;
    SQLSMALLINT nresultcols;
	SQLINTEGER autocommit ;
	SQLRETURN lrc;


	/* Confirm the sql command is a stored procedures or not*/	
	pdest = strstr(CommandStr, "CALL");
    result = pdest - CommandStr + 1;
	
	/* Get current connect state to decide to whether login again*/
    lrc = SQLGetConnectAttr( m_hdbc, SQL_AUTOCOMMIT, &autocommit, 0, NULL ) ;
	if (lrc != SQL_SUCCESS && lrc != SQL_SUCCESS_WITH_INFO)
	{  
		handle_error( SQL_HANDLE_DBC, m_hdbc, lrc ) ;
		if ( strcmp((char *)m_dbErrorInfo.SqlState, "08003") == 0 || strcmp((char *)m_dbErrorInfo.SqlState, "HY000") == 0 || strcmp((char *)m_dbErrorInfo.SqlState, "0HY010") == 0) 
		{
			
			/* Login again*/
			m_rc = DBconnect( m_henv, &m_hdbc, (SQLCHAR *)m_serverName, (SQLCHAR *)m_loginID, (SQLCHAR *)m_passWord) ;
			if ( m_rc != SQL_SUCCESS ) 
			{
				terminate( m_henv, m_rc );
				m_errorCode = -1;
				strcpy(m_errorMsg, "DB2连接不成功，执行存储过程失败");
				return false ;
			}
			m_rc = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt ) ;
			if ( m_rc != SQL_SUCCESS )
			{	
				handle_error( SQL_HANDLE_STMT, m_hstmt, m_rc ) ;
				m_errorCode = m_dbErrorInfo.SqlCode;
				strcpy(m_errorMsg, "分配语句句柄不成功，执行存储过程失败");
				return false;
			}
		}
	}
	
	EndTran();
	if( pdest != NULL && (result == 0 || result == 1) )  //当是执行存储过程
	{
		strcpy((char *)m_stmt, (char *)CommandStr);
		m_rc = SQLPrepare(m_hstmt, m_stmt, SQL_NTS);
		/* Bind parameters for execute stored procedure*/
		for ( int iNum = 0; iNum < ParamNum; iNum++)
		{
			m_rc = SQLBindParameter(m_hstmt, iNum+1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
				strlen((const char *)Param[iNum]), 0, Param[iNum], strlen((const char *)Param[iNum]+1), NULL);
			if ( m_rc != SQL_SUCCESS )
			{	
				handle_error( SQL_HANDLE_STMT, m_hstmt, m_rc ) ;
				m_errorCode = m_dbErrorInfo.SqlCode;
				strcpy(m_errorMsg, "输入参数绑定不成功，执行存储过程失败");
				return false;
			}
		}
		
		/*Excute a stored procedure */
		m_rc = SQLExecute(m_hstmt);
		if (m_rc != SQL_SUCCESS && m_rc != SQL_SUCCESS_WITH_INFO)
		{  
			
			handle_error( SQL_HANDLE_STMT, m_hstmt, m_rc ) ;
			m_errorCode = m_dbErrorInfo.SqlCode;
			strcpy(m_errorMsg, "执行存储过程失败");
			return false;
		}
		else
		{
			m_errorCode = 0;
			strcpy(m_errorMsg, "执行存储过程成功");
			/* Bind the result set columns*/
			Bind();
		}
	}
	else                                                 //当是执行SQL语句
	{
		strcpy((char *)m_stmt, (char *)CommandStr);
		/* Excute a Sql*/
		m_rc = SQLExecDirect(m_hstmt, m_stmt, SQL_NTS);
		if ( m_rc != SQL_SUCCESS && m_rc != SQL_NO_DATA_FOUND)
		{
			handle_error( SQL_HANDLE_STMT, m_hstmt, m_rc ) ;
			m_errorCode = m_dbErrorInfo.SqlCode;
			strcpy((char *)m_errorMsg, (char *)(m_dbErrorInfo.ErrorMsg + 1));

			return false;
		}
		/* if have result set, bind the columns to program vars*/
		lrc = SQLNumResultCols(m_hstmt, &nresultcols);     //取是否有结果集返回
		if ( lrc != SQL_SUCCESS) 
			handle_error( SQL_HANDLE_STMT, m_hstmt, lrc ) ;
		
		if (m_rc == SQL_SUCCESS) 
		{
			m_errorCode = 0;
			strcpy(m_errorMsg, "执行SQL语句成功");
			if (nresultcols != 0) 
				Bind();
		}
		else if(m_rc == SQL_NO_DATA_FOUND)
		{
			m_errorCode = 0;
			strcpy(m_errorMsg, "执行SQL语句成功");
		}
		
	}
	return true;    
	
}

bool CDb2lib::Bind()
{