oradblib.cpp

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

		//绑定游标变量
		if( m_usecursor )
		{
			status = OCIBindByName (m_stmthp, (OCIBind **) &m_bnd3p, m_errhp,
				(text *)":cursor", (sb4)strlen((char *)":cursor"),
				(dvoid *)&m_curstmthp, (sb4) 0,  SQLT_RSET, (dvoid *)0,
				(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);
			if( status != OCI_SUCCESS )
			{
				ErrorProc( m_errhp, status );
				return false;
			}
		}
		//保存游标变量
		m_tempstmthp = m_curstmthp;
	}
	else                                          //非执行存储过程的sql语句
	{
		if( memicmp( CommandStr, "select ", 7 ) == 0 )
		{
			m_iters = 0;
			m_usecursor = true;
		}
		else
			m_iters = 1;
		strcpy( cmd, CommandStr );
		//准备执行语句
		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_tempstmthp = m_curstmthp;
		m_curstmthp = m_stmthp;               //方便以后的提取列值，统一为m_curstmthp变量
	}
	fputs( cmd, m_fp );
	fputs( "\n", m_fp );
	fflush( m_fp );
	return true;
}

bool COraDblib::__Run__(const char *CommandStr)
{
	sword status;

	m_tempstmthp = NULL;
	m_curstmthp = NULL;
	m_stmthp = NULL;
	
	//申请语句句柄，分配内存
	status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_stmthp, OCI_HTYPE_STMT, (size_t)0, 0 );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	//申请用于游标变量的语句句柄，分配内存
	status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_curstmthp, OCI_HTYPE_STMT, (size_t)0, 0 );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	if( !BindParam( CommandStr ) )
		return false;

	m_rowCount = 0;
	memset( m_errorMsg, 0, sizeof( m_errorMsg ) );
	//执行语句
	status = OCIStmtExecute( m_svchp, m_stmthp, m_errhp, (ub4)m_iters, 0, 0, 0, OCI_DEFAULT );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		if( m_Reconnect )                            //是否断开，需重连
		{
			if( !Reconnect() )
			{
				return false;
			}
			
			m_tempstmthp = NULL;
			m_curstmthp = NULL;
			m_stmthp = NULL;
			
			status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_stmthp, OCI_HTYPE_STMT, (size_t)0, 0 );
			if( status != OCI_SUCCESS )
			{
				ErrorProc( m_errhp, status );
				return false;
			}

			status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_curstmthp, OCI_HTYPE_STMT, (size_t)0, 0 );
			if( status != OCI_SUCCESS )
			{
				ErrorProc( m_errhp, status );
				return false;
			}

			if( !BindParam( CommandStr ) )
				return false;

			m_rowCount = 0;
			status = OCIStmtExecute( m_svchp, m_stmthp, m_errhp, (ub4)m_iters, 0, 0, 0, OCI_DEFAULT );
			if( status != OCI_SUCCESS )
			{
				ErrorProc( m_errhp, status );
				return false;
			}
		}
		else
			return false;
	}

	if( m_usecursor )
	{
		if( !Bind() )
			return false;
	}
	return true;
}

bool COraDblib::__Run(const char *CommandStr)
{
	if( !__Run__( CommandStr ) )
	{
		Cancel();
		return false;
	}
	return true;
}

bool COraDblib::Use(const char* CommandStr )
{
	if( m_usenum == 0 )
	{
		if( !readPackName( CommandStr ) )         //将数据库中的存储过程与包的对应关系读入内存
			return false;
		m_usenum++;
	}
	return true;
}

void COraDblib::API_ltrim(char *s)
{
	int i=0;
	int len;
	char *tmp;
	len=strlen(s);
	if(API_Spaces(s)){
		*s='\0';
		return;
	}
	tmp=s;
	for(i=0; *tmp && *tmp==' '; tmp++,i++);
	memmove(s,tmp,len);
}

void COraDblib::API_rtrim(char *s)
{
	int len;
	len=strlen(s);
	char *p=s+len-1;

	if (API_Spaces(s)){
		*s='\0';
		return;
	 }
	while(*p==' ') p--;
	*(++p)='\0';
}

int COraDblib::API_Spaces(char *p)
{
	char *s=p;
	while (*s) {
		if(*s==' ') s++;
		else return 0;
	}
	return 1;
}

bool COraDblib::Run(const char *CommandStr,...)
{
    char msg[2048];
    va_list arg_ptr;

    va_start(arg_ptr, CommandStr);
    vsprintf(msg, CommandStr, arg_ptr);
    va_end(arg_ptr);

    return __Run(msg);
}

const SDBCOL * COraDblib::GetColInfo(const char * colName)
{
	unsigned int index;

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

	return (const SDBCOL *)NULL;
}

const SDBCOL * COraDblib::GetColInfo(int index)
{
	if(m_errorCode != 0 || m_colCount <= 0 || (unsigned int)index >= m_colCount)
		return (const SDBCOL *)NULL;
	return &m_colInfo[index];
}

bool COraDblib::__Run10(const char *CommandStr)
{
	return __Run( CommandStr );
}

bool COraDblib::__Run20(const char *CommandStr)
{
    return __Run( CommandStr );
}

bool COraDblib::Run10(const char *CommandStr, ... )
{
    char msg[1024];
    va_list arg_ptr;

    memset(msg, '@', sizeof(msg));
    va_start(arg_ptr, CommandStr);
    vsprintf(msg, CommandStr, arg_ptr);
    va_end(arg_ptr);

    return __Run10(msg);
}

bool COraDblib::Run20(const char *CommandStr, ... )
{
    char msg[1024];
    va_list arg_ptr;

    memset(msg, '@', sizeof(msg));
    va_start(arg_ptr, CommandStr);
    vsprintf(msg, CommandStr, arg_ptr);
    va_end(arg_ptr);

    return __Run20(msg);
}

bool COraDblib::Run20Error(const char *CommandStr, ... )
{
#ifdef __BORLANDC__
    char msg[1024];
    va_list arg_ptr;

    memset(msg, '@', sizeof(msg));
    va_start(arg_ptr, CommandStr);
    vsprintf(msg, CommandStr, arg_ptr);
    va_end(arg_ptr);

    __Run20(msg);

    if(GetErrorCode() == 0)
    {
        return true;
    }
    else
    {
        MessageBox(Screen->ActiveForm->Handle, GetErrorMsg(), "系统提示信息", MB_OK | MB_ICONSTOP);
        return false;
    }
#else
    return false;
#endif

}

bool COraDblib::Run20Message(const char *CommandStr, ... )
{
#ifdef __BORLANDC__
    char msg[1024];
    va_list arg_ptr;

    memset(msg, '@', sizeof(msg));
    va_start(arg_ptr, CommandStr);
    vsprintf(msg, CommandStr, arg_ptr);
    va_end(arg_ptr);

    __Run20(msg);

    if(GetErrorCode() == 0)
    {
        MessageBox(Screen->ActiveForm->Handle, GetErrorMsg(), "系统提示信息", MB_OK | MB_ICONINFORMATION);
        return true;
    }
    else
    {
        MessageBox(Screen->ActiveForm->Handle, GetErrorMsg(), "系统提示信息", MB_OK | MB_ICONSTOP);
        return false;
    }
#else
    return false;
#endif
}

bool COraDblib::OCIInit()
{
	sword status;

	//apply environment handle
	OCIEnvCreate( &m_envhp, OCI_DEFAULT, (dvoid*)0, 0, 0,
		0, 0, (dvoid**)0 );

	//apply error handle
	OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_errhp, OCI_HTYPE_ERROR, (size_t)0, 
		(dvoid**)0 );

	//apply server handle
	status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_srvhp, OCI_HTYPE_SERVER, (size_t)0,
		(dvoid**)0 );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	//apply service handle
	status = OCIHandleAlloc( (dvoid*)m_envhp, (dvoid**)&m_svchp, OCI_HTYPE_SVCCTX, (size_t)0,
		(dvoid**)0 );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	//apply session handle
	status = OCIHandleAlloc( m_envhp, (void**)&m_authp, OCI_HTYPE_SESSION, 0, 0 );
	if( status != OCI_SUCCESS )
	{
		ErrorProc( m_errhp, status );
		return false;
	}

	return true;
}

bool COraDblib::Reconnect()
{

	sword status;

	if( m_tempstmthp != NULL )
		OCIHandleFree( m_tempstmthp, OCI_HTYPE_STMT );

	OCIHandleFree( m_stmthp, OCI_HTYPE_STMT );

	m_Reconnect = false; 

	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;
	}

	OCIHandleFree( m_envhp, OCI_HTYPE_ENV );

	m_loginOK = false;

	if( !Open( m_databaseName, m_loginID, m_passWord, m_appName, m_hostName ) )
	{
		return false;
	}
	return true;

}

bool COraDblib::sqldebug()                      //用于sql语句的调试
{
	char cmd[150];

	strcpy( cmd, "ALTER SESSION SET PLSQL_DEBUG = TRUE;" );
	__Run( cmd );
	if( GetErrorCode() != 0 )
	{
		Cancel();
		return false;
	}
	Cancel();

 	strcpy( cmd, "declare v_DebugID varchar2(30); begin v_DebugID := \
            DBMS_DEBUG.INITIALIZE( 'MyDebug' ); DBMS_DEBUG.DEBUG_ON; end;" );
	__Run( cmd );
	if( GetErrorCode() != 0 )
	{
		Cancel();
		return false;
	}
	Cancel();
	return true;
}

bool COraDblib::enddebug()                    //结束调试
{
	char cmd[50];

 	strcpy( cmd, "BEGIN DBMS_DEBUG.DEBUG_OFF; ALTER SESSION SET PLSQL_DEBUG = FALSE; END;" );

	__Run( cmd );
	if( GetErrorCode() != 0 )
	{
		Cancel();
		return false;
	}
	Cancel();
	return true;
}

bool COraDblib::readPackName( const char* CommandStr )
{
	int i = 0;

	memset( m_refName, 0, sizeof( m_refName ) );
	strcpy( m_refName[i].procName, "up_get_xt_func_pkg" );
	strcpy( m_refName[i].packName, "pkg_xt_func" );
	m_refNameSize = 1;
    i = 1;

	__Run( CommandStr );
	if( GetErrorCode() != 0 )
	{
		Cancel();
		return false;
	}
	while( MoveNext() )
	{
		//memset( m_refName[i].packName, 0, sizeof( m_refName[i].packName ) );
		//memset( m_refName[i].procName, 0, sizeof( m_refName[i].procName ) );
		strcpy( m_refName[i].packName, GetColData( 5 ) );
		strcpy( m_refName[i].procName, GetColData( 4 ) );
		i++;
	}
	m_refNameSize = i;
	return true;
}