ocidbclass.cpp

通过oci实现的oracle查询分析器功能和可以 尤其对BLOB字段的度曲

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "OciDbClass.h"


//---------------------------------------------------------------------------
OCIDatabase::OCIDatabase()
{

}
//---------------------------------------------------------------------------
OCIDatabase::~OCIDatabase()
{

}
//---------------------------------------------------------------------------
int OCIDatabase::ConnectDataBase(DATABASEHANDLE & DBHandle)
{
  char ErrMsg[1000];
  int	 i,status;
  while(1)
  {
    memset( ErrMsg, 0, 200 );
    EnterCriticalSection( &(sG_ConnPool.MutexLock) );
    for( i = 0 ; i < sG_ConnPool.ConnNum ; i ++ )
    {
      if( sG_ConnPool.Stat[i] == 0 )
      {
        DBHandle =  sG_ConnPool.DBHandle[i];
        status = TestOraAlive(DBHandle);
        if(status!=OCI_SUCCESS)
        {
          sprintf( DBErrMsg,"ORACLE服务器已断.\n" );

          pDisConnectDataBase(DBHandle);
          status = pConnectDataBase(DBHandle,ErrMsg );
        }
        else//清理上次未成功的事务
          OCITransRollback( DBHandle.svchp, DBHandle.errhp, OCI_DEFAULT );
        sG_ConnPool.DBHandle[i] = DBHandle;
        if( status == OCI_SUCCESS )
          sG_ConnPool.Stat[i] = 1;
        else
        i = sG_ConnPool.ConnNum;

        break;
      }
    }
    LeaveCriticalSection( &(sG_ConnPool.MutexLock) );

    if( i == sG_ConnPool.ConnNum )
      Sleep(1000);
    else
      break;

  }

  return 0;
}
//---------------------------------------------------------------------------
int OCIDatabase::DisConnectDataBase(DATABASEHANDLE & DBHandle)
{
    int	i;
    EnterCriticalSection( &(sG_ConnPool.MutexLock) );
    sG_ConnPool.Stat[DBHandle.id] = 0;
    LeaveCriticalSection( &(sG_ConnPool.MutexLock) );
    return 0;
}
//---------------------------------------------------------------------------
int OCIDatabase::TestOraAlive(DATABASEHANDLE & DBHandle)
{
    int     status,errcode;
    char     bufp[201];
    tmpbuf[0] = 0;
    if( DBHandle.svchp == 0 || DBHandle.errhp == 0 ) return -1;
    status = OCIServerVersion ( DBHandle.svchp,DBHandle.errhp,(unsigned char*)bufp,200,OCI_HTYPE_SVCCTX );
    if( status != OCI_SUCCESS ){
            OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)tmpbuf, 300, OCI_HTYPE_ERROR );
            sprintf( DBErrMsg,"TestOraAlive is error : %s\n", tmpbuf );
    }
    return  status;
}
//---------------------------------------------------------------------------
int OCIDatabase::pDisConnectDataBase(DATABASEHANDLE & DBHandle)
{
    if(  DBHandle.svchp!=0 && DBHandle.errhp!=0 )
            OCILogoff (DBHandle.svchp,DBHandle.errhp);
    if( DBHandle.svchp != NULL )
            OCIHandleFree( DBHandle.svchp, OCI_HTYPE_SVCCTX );
    if( DBHandle.errhp != NULL )
            OCIHandleFree( DBHandle.errhp, OCI_HTYPE_ERROR );
    if( DBHandle.envhp != NULL )
            OCIHandleFree( DBHandle.envhp, OCI_HTYPE_ENV );

    memset( & DBHandle, 0, sizeof(DATABASEHANDLE) );
    return 0;
}
//---------------------------------------------------------------------------
int OCIDatabase::pConnectDataBase(DATABASEHANDLE & DBHandle, char * ErrMsg )
{

  int	status =0,i,errcode=0;


  status = OCIEnvInit( & (DBHandle.envhp),OCI_DEFAULT,0,0 );
  if( status == OCI_ERROR )
  {
          sprintf( DBErrMsg,"初始化OCI环境句柄失败.\n" );
          goto ret;
  }

  status = OCIHandleAlloc( DBHandle.envhp,(dvoid**)& (DBHandle.errhp),
                                  OCI_HTYPE_ERROR,0,0 );
  if( status != OCI_SUCCESS )
  {
          OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
          sprintf(DBErrMsg, "分配错误处理句柄失败->%s", ErrMsg );
          goto ret;
  }

  status = OCIHandleAlloc( DBHandle.envhp,(dvoid**)& (DBHandle.svchp),
                                  OCI_HTYPE_SVCCTX,0,(dvoid**)0 );
  if( status != OCI_SUCCESS )
  {
          OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
          printf( DBErrMsg,"分配服务上下文句柄失败->%s", ErrMsg );
          goto ret;
  }

  status = OCILogon(DBHandle.envhp, DBHandle.errhp, &(DBHandle.svchp), (unsigned char*)UserName, strlen(UserName), (unsigned char*)Password,
                                                  strlen(Password), (unsigned char*)Ora_Service,strlen(Ora_Service));
  if( status != OCI_SUCCESS )
  {
          OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
          sprintf(DBErrMsg, "开始会话失败->%s", ErrMsg );
          goto ret;
  }

  ret:
  if( status != OCI_SUCCESS )
    pDisConnectDataBase(DBHandle);
  return status;
}
//---------------------------------------------------------------------------

int OCIDatabase::InitG_ConnPool(char Service[300],char User[300],char Pass[300],int count)
{

	int	status =0,i;

	char	ErrMsg[300];
	int		errcode;


        StrCopy(Ora_Service,Service);
        StrCopy(UserName,User);
        StrCopy(Password,Pass);
        Connectcount=count;

	if( Connectcount <= 0 && Connectcount >= 301 ){
           sprintf(DBErrMsg,"connectpool 数错误 :%d .\n" , Connectcount) ;
		throw Exception(DBErrMsg);
	      	//return HIM_GETCFG_ERR;
	}

	status = OCIInitialize( OCI_THREADED,(dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0  );
	if( status == OCI_ERROR )
	{       sprintf(DBErrMsg,"初始化OCI进程环境失败.\n") ;
		throw Exception( DBErrMsg );
	       //	return -1;
	}
	memset( & sG_ConnPool, 0 , sizeof(G_ConnPool) );
	InitializeCriticalSection( &(sG_ConnPool.MutexLock) );

	sG_ConnPool.Stat = new char[Connectcount];
	sG_ConnPool.DBHandle = new DATABASEHANDLE[Connectcount];
	memset(sG_ConnPool.Stat,0,sizeof(char)*Connectcount);
	memset(sG_ConnPool.DBHandle,0,sizeof(DATABASEHANDLE)*Connectcount);
	


	sG_ConnPool.ConnNum = Connectcount;
	for( i = 0 ; i < Connectcount ; i ++ ){

		sG_ConnPool.DBHandle[i].id = i;

		status = OCIEnvInit( & (sG_ConnPool.DBHandle[i].envhp),OCI_DEFAULT,(size_t) 0,(dvoid **) 0 );
		if( status == OCI_ERROR )
		{       sprintf(DBErrMsg,"初始化OCI进程环境失败.\n") ;
			throw Exception( "初始化OCI环境句柄失败.\n" );
		       //	goto ret;
		}

		status = OCIHandleAlloc( sG_ConnPool.DBHandle[i].envhp,(dvoid**)& (sG_ConnPool.DBHandle[i].errhp),
					OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0 );
		if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"分配错误处理句柄失败->%s", ErrMsg) ;
		       	throw Exception(DBErrMsg);
			//goto ret;
		}
                status = OCIHandleAlloc( sG_ConnPool.DBHandle[i].envhp,(dvoid**)& (sG_ConnPool.DBHandle[i].srvhp),
                 OCI_HTYPE_SERVER,(size_t) 0, (dvoid **) 0);
                if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"分配服务上下文句柄失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
		status = OCIHandleAlloc( sG_ConnPool.DBHandle[i].envhp,(dvoid**)& (sG_ConnPool.DBHandle[i].svchp),
					OCI_HTYPE_SVCCTX,(size_t) 0,(dvoid**)0 );
		if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"分配服务上下文句柄失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status = OCIServerAttach( sG_ConnPool.DBHandle[i].srvhp, sG_ConnPool.DBHandle[i].errhp, (text *)Ora_Service, (sb4) strlen(Ora_Service), 0);
               	if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status = OCIAttrSet( (dvoid *)(sG_ConnPool.DBHandle[i].svchp), OCI_HTYPE_SVCCTX, (dvoid *)(sG_ConnPool.DBHandle[i].srvhp),
                     (ub4) 0, OCI_ATTR_SERVER, sG_ConnPool.DBHandle[i].errhp);
                if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status =OCIHandleAlloc((dvoid *)sG_ConnPool.DBHandle[i].envhp, (dvoid **)&(sG_ConnPool.DBHandle[i].authp),
                        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
                 if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status =OCIAttrSet((dvoid *)sG_ConnPool.DBHandle[i].authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) UserName, (ub4) strlen((char *)UserName),
                 (ub4) OCI_ATTR_USERNAME, sG_ConnPool.DBHandle[i].errhp);
                  if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status =OCIAttrSet((dvoid *)sG_ConnPool.DBHandle[i].authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) Password, (ub4) strlen((char *)Password),
                 (ub4) OCI_ATTR_PASSWORD, sG_ConnPool.DBHandle[i].errhp);
                  if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status =OCISessionBegin ( sG_ConnPool.DBHandle[i].svchp,  sG_ConnPool.DBHandle[i].errhp, sG_ConnPool.DBHandle[i].authp, OCI_CRED_RDBMS,
                          (ub4) OCI_DEFAULT);
                    if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
                status =OCIAttrSet((dvoid *) sG_ConnPool.DBHandle[i].svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) sG_ConnPool.DBHandle[i].authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, sG_ConnPool.DBHandle[i].errhp);
                if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}
	    /*	   status = OCILogon(sG_ConnPool.DBHandle[i].envhp, sG_ConnPool.DBHandle[i].errhp, &(sG_ConnPool.DBHandle[i].svchp), (unsigned char*)UserName, strlen(UserName), (unsigned char*)Password,

							strlen(Password), (unsigned char*)Ora_Service,strlen(Ora_Service));

		if( status == OCI_ERROR )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg  ) ;
                        throw Exception(DBErrMsg);
		       //	goto ret;
		} */

	}

ret:
       return status;
       //throw Exception(DBErrMsg);
}
/*
int OCIDatabase::InitG_ConnPool(char Service[300],char User[300],char Pass[300],int count)
{

	int	status =0,i;

	char	ErrMsg[200];
	int		errcode;


        StrCopy(Ora_Service,Service);
        StrCopy(UserName,User);
        StrCopy(Password,Pass);
        Connectcount=count;

	if( Connectcount <= 0 && Connectcount >= 301 ){
           sprintf(DBErrMsg,"connectpool 数错误 :%d .\n" , Connectcount) ;
		throw Exception(DBErrMsg);
	      //	return HIM_GETCFG_ERR;
	}

	status = OCIInitialize( OCI_THREADED,0,0,0,0 );
	if( status == OCI_ERROR )
	{       sprintf(DBErrMsg,"初始化OCI进程环境失败.\n") ;
		throw Exception( DBErrMsg );
	       //	return -1;
	}
	memset( & sG_ConnPool, 0 , sizeof(G_ConnPool) );
	InitializeCriticalSection( &(sG_ConnPool.MutexLock) );

	sG_ConnPool.Stat = new char[Connectcount];
	sG_ConnPool.DBHandle = new DATABASEHANDLE[Connectcount];
	memset(sG_ConnPool.Stat,0,sizeof(char)*Connectcount);
	memset(sG_ConnPool.DBHandle,0,sizeof(DATABASEHANDLE)*Connectcount);
	


	sG_ConnPool.ConnNum = Connectcount;
	for( i = 0 ; i < Connectcount ; i ++ ){

		sG_ConnPool.DBHandle[i].id = i;

		status = OCIEnvInit( & (sG_ConnPool.DBHandle[i].envhp),OCI_DEFAULT,0,0 );
		if( status == OCI_ERROR )
		{       sprintf(DBErrMsg,"初始化OCI进程环境失败.\n") ;
			throw Exception( "初始化OCI环境句柄失败.\n" );
			//goto ret;
		}

		status = OCIHandleAlloc( sG_ConnPool.DBHandle[i].envhp,(dvoid**)& (sG_ConnPool.DBHandle[i].errhp),
					OCI_HTYPE_ERROR,0,0 );
		if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"分配错误处理句柄失败->%s", ErrMsg) ;
		       	throw Exception(DBErrMsg);
			//goto ret;
		}
			
		status = OCIHandleAlloc( sG_ConnPool.DBHandle[i].envhp,(dvoid**)& (sG_ConnPool.DBHandle[i].svchp),
					OCI_HTYPE_SVCCTX,0,(dvoid**)0 );
		if( status != OCI_SUCCESS )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"分配服务上下文句柄失败->%s", ErrMsg ) ;
                        throw Exception(DBErrMsg);
			//goto ret;
		}

	status = OCILogon(sG_ConnPool.DBHandle[i].envhp, sG_ConnPool.DBHandle[i].errhp, &(sG_ConnPool.DBHandle[i].svchp), (unsigned char*)UserName, strlen(UserName), (unsigned char*)Password,

							strlen(Password), (unsigned char*)Ora_Service,strlen(Ora_Service));

		if( status == OCI_ERROR )
		{
			OCIErrorGet( sG_ConnPool.DBHandle[i].errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
                        sprintf(DBErrMsg,"开始会话失败->%s", ErrMsg  ) ;
                        throw Exception(DBErrMsg);
		       //	goto ret;
		}
	}

ret:
	return status;
       // throw Exception(DBErrMsg);
}*/
//---------------------------------------------------------------------------
int OCIDatabase::OCI_ExecuteStmt(DATABASEHANDLE & DBHandle, char * stmtstr, char * ErrMsg )
{
	int  status = 0,errcode;
	OCIStmt * stmthp = 0;

	status = OCIHandleAlloc( DBHandle.envhp, (dvoid**) &stmthp,
	   		   OCI_HTYPE_STMT,0,0 );
	if( status != OCI_SUCCESS )
	{
		OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
		sprintf( DBErrMsg,ErrMsg );
                //throw Exception(DBErrMsg);
		goto ret;
	}

	status = OCIStmtPrepare( stmthp, DBHandle.errhp,
				 (text*)stmtstr,(ub4)strlen( stmtstr ),
				 (ub4)OCI_NTV_SYNTAX,(ub4)OCI_DEFAULT );
	if( status != OCI_SUCCESS )
	{
		OCIErrorGet( DBHandle.errhp, 1, NULL, &errcode, (text*)ErrMsg, 300, OCI_HTYPE_ERROR );
		sprintf( DBErrMsg,ErrMsg );
                // throw Exception(DBErrMsg);
		goto ret;
	}