oradblib.cpp

访问oracle 数据库的oci (VC)封装

// OraDblib.cpp: implementation of the COraDblib class.
// 用来与Oracel连接
// zhangzheng for openfund 2001.8.15
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "OraDblib.h"

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

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

int COraDblib::m_opennum = 0;
FILE* COraDblib::m_fp = NULL;
int COraDblib::m_refNameSize = 0;
tagRefName COraDblib::m_refName[700] = {NULL};
int COraDblib::m_usenum = 0;
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

COraDblib::COraDblib()
{
	Init();                                 //初始化成员变量
}

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

bool COraDblib::Open(const char *databaseName, const char *loginID, const char *passWord, const char *appname, const char *hostname )
{
	sword status;

	if( m_loginOK == true )
		return true;

	OCIInit();                             //初始化OCI的使用环境

    //connect server
	status = OCIServerAttach(m_srvhp, m_errhp, (text*)databaseName, 
		(ub4)strlen((const char*)databaseName), OCI_DEFAULT ); 
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}
	
	//set service attribute
	status = OCIAttrSet( (dvoid*)m_svchp, OCI_HTYPE_SVCCTX, (dvoid*)m_srvhp, 0, OCI_ATTR_SERVER,
		(OCIError*)m_errhp);
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	status = OCIAttrSet( m_authp, OCI_HTYPE_SESSION, (void*)loginID, (ub4)strlen(loginID),
		OCI_ATTR_USERNAME, m_errhp );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	status = OCIAttrSet( m_authp, OCI_HTYPE_SESSION, (void*)passWord, (ub4)strlen(passWord), 
		OCI_ATTR_PASSWORD, m_errhp );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	//begin one session
	status = OCISessionBegin( m_svchp, m_errhp, m_authp, OCI_CRED_RDBMS, OCI_DEFAULT );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	//set session environment
	status = OCIAttrSet( m_svchp, OCI_HTYPE_SVCCTX, m_authp, (ub4)0, 
		OCI_ATTR_SESSION, m_errhp ); 
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

    strcpy(m_loginID, loginID);
    strcpy(m_passWord, passWord);
	if( appname != NULL )
		strcpy(m_appName, appname);
	if( hostname != NULL )
		strcpy(m_hostName, hostname);
    strcpy(m_databaseName, databaseName);

    m_errorCode = 0;
    strcpy( m_errorMsg, "连接Oracle成功" );
    m_loginOK = true;

	if( m_opennum == 0 )
		m_fp = fopen( ".\\execsql.ora", "w" );
	m_opennum++;
	return true;
}

bool COraDblib::Init()
{
	m_Reconnect = false;
	m_usecursor = false;
    m_loginOK = false;
    memset(m_serverName, 0, sizeof(m_serverName));
    memset(m_loginID, 0, sizeof(m_loginID));
    memset(m_passWord, 0, sizeof(m_passWord));
    memset(m_appName, 0, sizeof(m_appName));
    memset(m_hostName, 0, sizeof(m_hostName));
    memset(m_databaseName, 0, sizeof(m_databaseName));
	m_iters = 0;
    m_rowCount = 0;
    m_colCount = 0;
    m_errorCode = 0;
    memset(m_errorMsg, 0, sizeof(m_errorMsg));
    memset(m_colInfo, 0, sizeof(m_colInfo));

	m_bnd1p = (OCIBind* )0;
	m_bnd2p = (OCIBind* )0;
	m_bnd3p = (OCIBind* )0;
    return true;
}

int COraDblib::GetErrorCode()
{
	return m_errorCode;
}

const char* COraDblib::GetErrorMsg()
{
	return m_errorMsg;
}

void COraDblib::ErrorProc(dvoid *errhp, sword status)
{
	text errbuf[512];
	sb4 errcode;
	char errMsg[1024];

	memset( errbuf, 0, sizeof( errbuf ) );
	switch(status)
	{
		case OCI_SUCCESS:
			m_errorCode = OCI_SUCCESS;
			break;
		case OCI_SUCCESS_WITH_INFO:
			m_errorCode = OCI_SUCCESS_WITH_INFO;
			strcpy( m_errorMsg, "OCI 错误代码：OCI_SUCCESS_WITH_INFO" );
			break;
		case OCI_NEED_DATA:
			m_errorCode = OCI_NEED_DATA;
			strcpy( m_errorMsg, "OCI 错误代码：OCI_NEED_DATA" );
			break;
		case OCI_NO_DATA:
			m_errorCode = OCI_NO_DATA;
			strcpy( m_errorMsg, "OCI 错误代码：OCI_NO_DATA" );
			break;
		case OCI_ERROR:
			m_errorCode = OCI_ERROR;
			OCIErrorGet(( dvoid*)errhp, (ub4)1, NULL, &errcode,
				errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR );
			sprintf( errMsg, "OCI 错误号：%d，错误信息：%s", errcode, errbuf );
			strncpy( m_errorMsg, errMsg, 80 );
			m_errorMsg[80] = 0;
			if( errcode == 3113 || errcode == 3114 )
			{
				m_Reconnect = true;
			}
			break;
		case OCI_INVALID_HANDLE:
			m_errorCode = OCI_INVALID_HANDLE;
			strcpy( m_errorMsg, "OCI 错误代码：OCI_INVALID_HANDLE" );
			break;
		case OCI_STILL_EXECUTING:
			m_errorCode = OCI_STILL_EXECUTING;
			strcpy( m_errorMsg, "OCI 错误代码：OCI_STILL_EXECUTION" );
			break;
	}
}

bool COraDblib::Cancel()
{
	sword status;

	m_usecursor = false;
	if( GetErrorCode() == 0 )
	{
		status = OCITransCommit( m_svchp, m_errhp, 0 );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
	}
	if( m_tempstmthp != NULL )
		OCIHandleFree( m_tempstmthp, OCI_HTYPE_STMT );
	if( m_stmthp != NULL )
		OCIHandleFree( m_stmthp, OCI_HTYPE_STMT );
	m_tempstmthp = NULL;
	m_stmthp = NULL;
	return true;
}

bool COraDblib::Close()
{
	sword status;

	if( m_loginOK )
	{
		status = OCISessionEnd( m_svchp, m_errhp, m_authp, OCI_DEFAULT );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		status = OCIServerDetach( m_srvhp, m_errhp, OCI_DEFAULT ); 
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		Init();
		OCIHandleFree( m_envhp, OCI_HTYPE_ENV );
	}

	m_opennum--;
	if( m_opennum == 0 )
		fclose( m_fp );
	return true;
}

bool COraDblib::MoveNext()
{
	sword status;

	if( !m_usecursor )
		return false;
	memset( m_data, 0, sizeof( m_data ) );
	if( m_errorCode == 0 )
	{
		status = OCIStmtFetch( m_curstmthp, m_errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT );
		if( status == OCI_ERROR || status == OCI_INVALID_HANDLE )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		if( status != OCI_NO_DATA )
		{
			m_rowCount++;
			return true;
		}
	}
	Cancel();
	return false;
}

char* COraDblib::GetColData(const char *colName)
{
    ub4 index;

    for(index = 0; index < m_colCount; index ++)
	{
        if(stricmp(m_colInfo[index].Name, colName) == 0)
            return GetColData(index);
	}

    return (char *)"";
}

char* COraDblib::GetColData(int index)
{
	if(m_errorCode != 0 || m_colCount <= 0 || (ub4)index >= m_colCount)
        return (char *)"";
	return (char*)m_data[index];
}

bool COraDblib::Bind()                         //绑定游标中的列
{
	sword status;
	text *p;
	ub4 sizep;

	status = OCIAttrGet( m_curstmthp, OCI_HTYPE_STMT, &m_colCount, 0, OCI_ATTR_PARAM_COUNT, m_errhp );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}
	
	if( m_colCount > MAX_COLNUM ) m_colCount = MAX_COLNUM;

	for( ub4 i = 1; i <= m_colCount; i++ )
	{
		m_colInfo[i-1].MaxLength = 0;
		memset( m_colInfo[i-1].Name, 0, sizeof(m_colInfo[i-1].Name) );
		//得到列信息
		status = OCIParamGet( m_curstmthp, OCI_HTYPE_STMT, m_errhp, (void**)&m_colhp, i );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//得到列的最大长度
		status = OCIAttrGet( m_colhp, OCI_DTYPE_PARAM, &m_colInfo[i-1].MaxLength, 0, OCI_ATTR_DATA_SIZE, m_errhp );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//得到列的类型
		status = OCIAttrGet( m_colhp, OCI_DTYPE_PARAM, &m_colInfo[i-1].Type, 0, OCI_ATTR_DATA_TYPE, m_errhp );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//得到列的精确位数
		status = OCIAttrGet( m_colhp, OCI_DTYPE_PARAM, &m_colInfo[i-1].Precision, 0, OCI_ATTR_PRECISION, m_errhp );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//得到列名
		status = OCIAttrGet( m_colhp, OCI_DTYPE_PARAM, (text**)&p, &sizep, OCI_ATTR_NAME, m_errhp );
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		if( sizep > MAXCOLNAMELEN )
			sizep = MAXCOLNAMELEN;
		strncpy( m_colInfo[i-1].Name, (const char*)p, sizep );

		status = OCIDefineByPos( m_curstmthp, &m_defhp[i-1], m_errhp, i, (ub1*)m_data[i-1], m_colInfo[i-1].MaxLength+1,
			SQLT_STR, &m_ind[i-1], 0, (ub2*)0, OCI_DEFAULT );
		if( status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
	}
	return true;
}

bool COraDblib::BindParam( const char* CommandStr )
{
	sword status;
	char cmd[2048];
	char procname[DBMAXCHAR+1];
	char packname[DBMAXCHAR+1];
	char *p;
	int len;
	int offset;

	m_bnd1p = (OCIBind*)0;
	m_bnd2p = (OCIBind*)0;
	m_bnd3p = (OCIBind*)0;
	memset( cmd, 0, sizeof(cmd) );
	memset( procname, 0, sizeof( procname ) );
	memset( packname, 0, sizeof( packname ) );

	API_ltrim( (char*)CommandStr );
	API_rtrim( (char*)CommandStr );
	len = strlen( CommandStr );
	
	if( memicmp( CommandStr, "exec ", 5 ) == 0 )     //判断是否是执行存储过程
	{
		m_iters = 1;
		CommandStr = CommandStr + 5;                //截去 exec
		API_ltrim( (char*)CommandStr );
		strcpy( cmd, "begin " );

		p = (char*)CommandStr;                      //取出存储过程名
		len = strlen( CommandStr );
		for( offset = 0; *p != ' ' && offset < len; offset++ )
			p++;
		strncpy( procname, CommandStr, offset );
		procname[offset] = 0;

		m_usecursor = false;                         //用来判断是否绑定游标变量   
		for(int i = 0; i < m_refNameSize; i++ )         //检查是否是包中存储过程
		{
			if( stricmp( m_refName[i].procName, procname ) == 0 )
			{
				m_usecursor = true;
				strcpy( packname, m_refName[i].packName );
				break;
			}
		}

		if( m_usecursor )
		{
			strcat( cmd, packname );
			strcat( cmd, "." );
		}

		strcat( cmd, procname );

		if( m_usecursor )
		{
			strcat( cmd, "(:errorCode, :errorMsg, :cursor" );
		}
		else
		{
			strcat( cmd, "(:errorCode, :errorMsg" );
		}

		CommandStr = CommandStr + offset;
		if( *CommandStr != '\0' )
		{
			strcat( cmd, "," );
			strcat( cmd, CommandStr );
		}
		strcat( cmd, "); end;" );
		//准备执行语句
		status = OCIStmtPrepare (m_stmthp, m_errhp, (text *)cmd, strlen((const char *)cmd), 
			OCI_NTV_SYNTAX, OCI_DEFAULT);
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//绑定入参m_errorCode
		status = OCIBindByName (m_stmthp, (OCIBind **) &m_bnd1p, m_errhp,
			(text *)":errorCode", (sb4)strlen((char *)":errorCode"),
			(dvoid *)&m_errorCode, (sb4)sizeof(m_errorCode), SQLT_INT, (dvoid *)0,
			(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}
		//绑定入参m_errorMsg
		status = OCIBindByName (m_stmthp, (OCIBind **) &m_bnd2p, m_errhp,
			(text *)":errorMsg", (sb4)strlen((char *)":errorMsg"),
			(dvoid *)&m_errorMsg, (sb4)sizeof(m_errorMsg), SQLT_STR, (dvoid *)0,
			(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);
		if( status != OCI_SUCCESS )
		{
			ErrorProc( m_errhp, status );
			return false;
		}