oracle.cpp

通信BOSS计费方面的服务器源码,有很多经典的创意,值得借鉴,在UNIX上运行.

#include "Oracle.h"

COracle::COracle():CDatabase()
{
	OciMode = OCI_DEFAULT;
	InitData();
	ret=0;
}

COracle::COracle(char *pUser,char *pPass,char *pServer,char *pDb,
	char *pApp,char *pHost):
	CDatabase(pUser,pPass,pServer,pDb,pApp,pHost)
{
	OciMode = OCI_DEFAULT;
	InitData();
	ret=0;
}

COracle::~COracle()
{
	if (isConnected) DisConnect();
	//delete[] data;//改为函数DeleteData()来释放
	
	DeleteData();
	
	if (ret!=0) free(ret);
	ret=0;
}

void COracle::DeleteData()
{
	
	CStrResult *pDataNext=NULL,*pTempData=NULL;
	//userlog("DeleteData()1");	
	pTempData = data->GetHeader();
	data = pTempData->next;
	while (data!=NULL)
	{
		pDataNext = data->next;
	//userlog("DeleteData()11:%d: rowid=%d: cols=%d:head=%d: next=%d: buffer=%d",data,data->rowid,data->cols,data->header,data->next,data->rowbuffer);	
		delete data;
	//userlog("DeleteData()12:%d",data);	
		data = pDataNext;
	//userlog("DeleteData()13");	
	}
	data = pTempData;
	
	data->istail = TRUE;
	data->next = NULL;
	delete data;
}

BOOL COracle::Connect()
{
	Status = OCIInitialize(OciMode,0,0,0,0);
	CheckErr("OCIInitialize");
	if (ErrNo < 0) return FALSE;
	Status = OCIEnvInit(&OciEnv,OciMode,0,0);
	CheckErr("OCIEnvInit");
	if (ErrNo < 0) return FALSE;
	Status = OCIHandleAlloc(OciEnv,(void **)&OciError,OCI_HTYPE_ERROR,0,0);
	CheckErr("OCIHandleAlloc:OciError");
	if (ErrNo < 0) return FALSE;
	Status = OCIHandleAlloc(OciEnv,(void **)&OciServer,OCI_HTYPE_SERVER,0,0);
	CheckErr("OCIHandleAlloc:OciServer");
	if (ErrNo < 0) return FALSE;
	Status = OCIServerAttach(OciServer,OciError,(text *)Server,strlen(Server),0);
	CheckErr("OCIServerAttach");
	if (ErrNo < 0) return FALSE;
	Status = OCIHandleAlloc(OciEnv,(void **)&OciSvcCtx,OCI_HTYPE_SVCCTX,0,0);
	CheckErr("OCIHandleAlloc:OciSvcCtx");
	if (ErrNo < 0) return FALSE;
	Status = OCIAttrSet(OciSvcCtx,OCI_HTYPE_SVCCTX,OciServer,0,OCI_ATTR_SERVER,OciError);
	CheckErr("OCIAttrSet:OciSvcCtx->OciServer");
	if (ErrNo < 0) return FALSE;
	Status = OCIHandleAlloc(OciEnv,(void **)&OciSession,OCI_HTYPE_SESSION,0,0);
	CheckErr("OCIHandleAlloc:OciSession");
	if (ErrNo < 0) return FALSE;
	Status = OCIAttrSet(OciSession,OCI_HTYPE_SESSION,UserName,strlen(UserName),OCI_ATTR_USERNAME,OciError);
	CheckErr("OCIAttrSet:OciSession->UserName");
	if (ErrNo < 0) return FALSE;
	Status = OCIAttrSet(OciSession,OCI_HTYPE_SESSION,Password,strlen(Password),OCI_ATTR_PASSWORD,OciError);
	CheckErr("OCIAttrSet:OciSession->Password");
	if (ErrNo < 0) return FALSE;
	Status = OCISessionBegin(OciSvcCtx,OciError,OciSession,OCI_CRED_RDBMS,OCI_DEFAULT);
	CheckErr("OCISessionBegin");
	if (ErrNo < 0) return FALSE;
	Status = OCIAttrSet(OciSvcCtx,OCI_HTYPE_SVCCTX,OciSession,0,OCI_ATTR_SESSION,OciError);
	CheckErr("OCIAttrSet:OciSvcCtx->OciSession");
	if (ErrNo < 0) return FALSE;

	Status = OCIHandleAlloc(OciEnv,(void **)&OciStmt,OCI_HTYPE_STMT,0,0);
	CheckErr("OCIHandleAlloc:OciStmt");
	if (ErrNo < 0) return FALSE;

	isConnected = TRUE;
	return TRUE;
}

BOOL COracle::DisConnect()
{
	if (OciServer) OCIServerDetach(OciServer,OciError,OCI_DEFAULT);
	CheckErr("OCIServerDetach");
	if (ErrNo < 0) return FALSE;
	if (OciSession) OCISessionEnd(OciSvcCtx,OciError,OciSession,OCI_DEFAULT);
	CheckErr("OCISessionEnd");
	if (ErrNo < 0) return FALSE;
	if (OciStmt) OCIHandleFree(OciStmt,OCI_HTYPE_STMT);
	OciStmt = 0;
	CheckErr("OCIHandleFree:OciStmt");
	if (ErrNo < 0) return FALSE;
	if (OciSvcCtx) OCIHandleFree(OciSvcCtx,OCI_HTYPE_SVCCTX);
	OciSvcCtx = 0;
	CheckErr("OCIHandleFree:OciSvcCtx");
	if (ErrNo < 0) return FALSE;
	if (OciSession) OCIHandleFree(OciSession,OCI_HTYPE_SESSION);
	OciSession = 0;
	CheckErr("OCIHandleFree:OciSession");
	if (ErrNo < 0) return FALSE;
	if (OciError) OCIHandleFree(OciError,OCI_HTYPE_ERROR);
	CheckErr("OCIHandleFree:OciError");
	if (ErrNo < 0) return FALSE;
	if (OciEnv) OCIHandleFree(OciEnv, OCI_HTYPE_ENV);
	CheckErr("OCIHandleFree:OciEnv");
	if (ErrNo < 0) return FALSE;
	return TRUE;
}
 
BOOL COracle::Execute(char *pSql,int aProcType)
{
	BOOL Ret;
	//userlog("execote_1=%s",pSql);
	if (!SendSql(pSql)) return FALSE;
	Ret = HandleReturn(aProcType);
	return Ret;
}

char *COracle::ExecProc(char *pSql)
{
	
	ub2 StmtType;
	ub4 Addr;
	dvoid    *tmp;
	char   returncode[50];
	char   ls_sql[40];
	char    ls_temp[50];
        OCIBind  *bnd1p = NULL;                      /* the first bind handle */
        OCIBind  *bnd2p = NULL;                     /* the second bind handle */
        OCIBind  *bnd3p = NULL;  
        OCIBind  *bnd4p = NULL;                    
        OCIBind  *bnd5p = NULL;
        

        memset(returncode,0,50);
        memset(ls_sql,0,40);
        //userlog(pSql);
	Status = OCIStmtPrepare(OciStmt,OciError,(unsigned char *)pSql,strlen(pSql),OCI_NTV_SYNTAX,OCI_DEFAULT);       
        CheckErr("OCIStmtPrepare");
        if (ErrNo < 0) return ErrText;
        //userlog(ErrText);
        
/*        Status=OCIBindByName(OciStmt, &bnd1p, OciError, (text *) ":areacode",-1, (ub1 *) &areacode,
            (sword) sizeof(areacode), SQLT_CHR, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
        CheckErr("OCIStmtBind1");
        if (ErrNo < 0) return FALSE;
        userlog(ErrText);
	
        Status=OCIBindByName(OciStmt, &bnd2p, OciError, (text *) ":calling_nbr",-1, (ub1 *) &calling_nbr,
             (sword) sizeof(calling_nbr), SQLT_CHR, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
        CheckErr("OCIStmtBind2");
        if (ErrNo < 0) return FALSE;
        userlog(ErrText);

        Status=OCIBindByName(OciStmt, &bnd3p, OciError, (text *) ":billing_cycle_id",-1, (ub1 *) &billing_cycle_id,
             (sword) sizeof(billing_cycle_id), SQLT_INT, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
        CheckErr("OCIStmtBind3");
        if (ErrNo < 0) return FALSE;
        userlog(ErrText);
        
        Status=OCIBindByName(OciStmt, &bnd4p, OciError, (text *) ":edit_time",-1, (ub1 *) &edit_time,
             (sword) sizeof(edit_time), SQLT_CHR, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
        CheckErr("OCIStmtBind4");
        if (ErrNo < 0) return FALSE;
        userlog(ErrText);
 */ 
        Status=OCIBindByName(OciStmt, &bnd5p, OciError, (text *) ":returncode",-1, (ub1 *) &returncode,
             (sword) sizeof(returncode), SQLT_CHR, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
                     
       CheckErr("OCIStmtBind5");
       //userlog(ErrText);
       if (ErrNo < 0) return ErrText;
       
       
       Status=OCIStmtExecute(OciSvcCtx, OciStmt, OciError, (ub4) 1, (ub4) 0, 
       (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
        CheckErr("OCIStmtExecute");
        
        if(Status==OCI_SUCCESS)
        {
	        sprintf(ls_temp,"fqssss%d",Status);
	        //userlog(ls_temp);
	}
	else
	{
		userlog(ErrText);
	}
	
      if (ErrNo < 0) return ErrText;
      //userlog(returncode);
      ret=(char *)malloc(strlen(returncode)+1);
      strcpy(ret,returncode);
      return ret;
     
}
BOOL COracle::SendSql(char *pSql)
{
	//userlog("send_1=%s",pSql);
	
	ub2 StmtType;
	Status = OCIStmtPrepare(OciStmt,OciError,(unsigned char *)pSql,strlen(pSql),OCI_NTV_SYNTAX,OCI_DEFAULT);
//	//userlog(pSql);
	CheckErr("OCIStmtPrepare");
	if (ErrNo < 0) return FALSE;
	Status = OCIAttrGet(OciStmt,OCI_HTYPE_STMT,&StmtType,0,OCI_ATTR_STMT_TYPE,OciError);
	CheckErr("OCIAttrGet:OciStmt->OCI_ATTR_STMT_TYPE");
	if (ErrNo < 0) return FALSE;
	if (StmtType == OCI_STMT_SELECT)
	{
		Status = OCIStmtExecute(OciSvcCtx,OciStmt,OciError,0,0,0,0,OCI_DEFAULT);
		CheckErr("OCIStmtExecute:OCI_STMT_SELECT");
	}else{
		Status = OCIStmtExecute(OciSvcCtx,OciStmt,OciError,1,0,0,0,OCI_DEFAULT);
		CheckErr("OCIStmtExecute:Expect OCI_STMT_SELECT");
	}
	if (ErrNo < 0) return FALSE;
	return TRUE;
}

BOOL COracle::HandleReturn(int aProcType)
{
	BOOL bRet;
	ub4 ColIter;
	ub2 StmtType;
	Cols = 0;
	//userlog("handleretrun1");
	ResetData();
	//userlog("handleretrun2");
	Status = OCIAttrGet(OciStmt,OCI_HTYPE_STMT,&StmtType,0,OCI_ATTR_STMT_TYPE,OciError);
	CheckErr("OCIAttrGet:OciStmt->OCI_ATTR_STMT_TYPE");
	if (ErrNo < 0) return FALSE;
	bRet = TRUE;
	switch (StmtType)
	{
		case OCI_STMT_SELECT:
	//userlog("handleretrun3");
			bRet = BindColumns();
	//userlog("handleretrun4");
			if (!bRet) break;
	//userlog("handleretrun5");
			bRet = FetchRow(aProcType);
	//userlog("handleretrun6");
			break;
		default:
			Status = OCIAttrGet(OciStmt,OCI_HTYPE_STMT,&ColIter,0,OCI_ATTR_ROW_COUNT,OciError);
			CheckErr("OCIAttrGet:OciStmt->OCI_ATTR_ROW_COUNT");
			if (ErrNo < 0)
			{
				Rows = 0;
				bRet = FALSE;
			}else{
				Rows = ColIter;
				bRet = TRUE;
			}
			break;
	}
	return bRet;
}

BOOL COracle::BeginTran()
{
	Status = OCITransStart(OciSvcCtx,OciError,10,OCI_TRANS_NEW);
	CheckErr("OCITransStart");
	if (ErrNo != OCI_SUCCESS) return FALSE;
	return TRUE;
}

BOOL COracle::Commit()
{
	Status = OCITransCommit (OciSvcCtx,OciError,OCI_DEFAULT);
	CheckErr("OCITransCommit");
	if (ErrNo != OCI_SUCCESS) return FALSE;
	return TRUE;
}

BOOL COracle::Rollback()
{
	Status = OCITransRollback (OciSvcCtx,OciError,OCI_DEFAULT);
	CheckErr("OCITransRollback");
	if (ErrNo != OCI_SUCCESS) return FALSE;
	return TRUE;
}

BOOL COracle::BindColumns()
{
	OCIParam *pParam;
	ub4 ColIter;
	Cols = 1;
	Status = OCIParamGet(OciStmt,OCI_HTYPE_STMT,OciError,(void **)&pParam,Cols);
	CheckErr("OCIParamGet");
	if (ErrNo < 0) {Cols = 0;return FALSE;}
	while(Status == OCI_SUCCESS)
	{
		Cols++;
		Status = OCIParamGet(OciStmt,OCI_HTYPE_STMT,OciError,(void **)&pParam,Cols);
		CheckErr("OCIParamGet");
	}
	//if (ErrNo < 0) {Cols = 0;return FALSE;}
	Cols--;
	for (ColIter = 0;ColIter < Cols;ColIter++)
	{
		Status = OCIDefineByPos(OciStmt,&OciDef[ColIter],OciError,ColIter+1,RowBuf[ColIter],MAX_COLSIZE - 1,SQLT_STR,&ColInd[ColIter],0,0,OCI_DEFAULT);
		CheckErr("OCIDefineByPos");
		if (ErrNo < 0) return FALSE;
	}
	return TRUE;
}

BOOL COracle::FetchRow(int aProcType)
{
	ub4 RowCount;
	//userlog("fetchrow");
	register ub4 i;
	RowCount = 0;
	do
	{
		Status = OCIStmtFetch(OciStmt,OciError,1,OCI_FETCH_NEXT,OCI_DEFAULT);
		if (Status < 0 ) break;
		if (Status == OCI_NO_DATA) break;
		for(i = 0;i < Cols;i++)
			if(*(ColInd + i) == -1) *(char *)(RowBuf + i) = 0;
		data->AddTail(RowBuf,Cols);
		data=data->next;
		RowCount++;
	}while(1);
	CheckErr("OCIStmtFetch");
	if ((Status != OCI_NO_DATA) && (ErrNo < 0)) return FALSE;
	Rows = RowCount;
	ErrNo = 0;
	strcpy(ErrText,"");
	return TRUE;
}

void COracle::CheckErr(const char *pPre)
{
	switch (Status)
		{
			case OCI_SUCCESS:
				ErrNo = OCI_SUCCESS;
				sprintf(ErrText,"SUCESS(%s)",pPre);
				break;
			case OCI_SUCCESS_WITH_INFO:
				ErrNo = OCI_SUCCESS_WITH_INFO;
				sprintf(ErrText,"Error%s: - OCI_SUCCESS_WITH_INFO\n",pPre);
				break;
			case OCI_NEED_DATA:
				ErrNo = OCI_NEED_DATA;
				sprintf(ErrText,"Error%s: - OCI_NEED_DATA\n",pPre);
				break;
			case OCI_NO_DATA:
				ErrNo = OCI_NO_DATA;
				sprintf(ErrText,"Error(%s) - OCI_NODATA\n",pPre);
				break;
			case OCI_ERROR:
				sb4 ErrCode;
				text ErrBuff[512];
				OCIErrorGet(OciError,1,NULL,&ErrCode,ErrBuff,sizeof(ErrBuff),OCI_HTYPE_ERROR);
				ErrNo = OCI_ERROR;
				sprintf(ErrText,"Error(%s) - %.*s\n",pPre,1024,ErrBuff);
				break;
			case OCI_INVALID_HANDLE:
				ErrNo = OCI_INVALID_HANDLE;
				sprintf(ErrText,"Error(%s) - OCI_INVALID_HANDLE\n",pPre);
				break;
			case OCI_STILL_EXECUTING:
				ErrNo = OCI_STILL_EXECUTING;
				strcpy(ErrText,"Error - OCI_STILL_EXECUTE\n");
				break;
			case OCI_CONTINUE:
				ErrNo = OCI_CONTINUE;
				strcpy(ErrText,"Error - OCI_CONTINUE\n");
				break;
			default:
				ErrNo = Status;
				sprintf(ErrText,"No relative error message(id:%d)!",ErrNo);
				break;
  }
}

char *COracle::GetData()
{
//	char *localret;
	int i,j;
	
//	//userlog("nxlsRet_address_free:%p",ret);
	
	if (ret!=0) free(ret);
	ret=0;
	if (Rows==0) 
	{
//		return ret;
		return "";
	}
	
	//ret=(char *)calloc(MAX_COLUMN*MAX_COLSIZE,Rows);
	//ret=(char *)calloc((Cols+1)*MAX_COLSIZE,Rows);
	ret=(char *)malloc((Cols+1)*MAX_COLSIZE*Rows);
	
//	//userlog("nxlsRet_address_calloc:%p",ret);

	if (ret==0) return ret;
	
	ret[0]=0;
	for(i=1;i<=Rows;i++)
	{
		for(j=1;j<=Cols;j++)
		{
			if(j==1&&i!=1)
				strcat(ret,"\r\n");
			if(j!=1)
				strcat(ret,"\t");
			strcat(ret,data->GetColData(i,j));
		}
	}
	//ret[strlen(ret)]='\0';
//	//userlog("in");
//	//userlog(ret);
//	localret=ret;
	return ret;
	
}


BOOL COracle::DoSql(char* CommandBuffer)
{
	bool ret;
	//userlog("dosql_1=%s",CommandBuffer);
	ret=Execute(CommandBuffer,-1);
	//userlog("dosql_2=%s",CommandBuffer);
	
	//调试输出
//	//userlog(CommandBuffer);
//	//userlog("\nrow= %d\n",GetRowCount());
	//if(ErrNo<0)
	//{
		////userlog(CommandBuffer);
//		//userlog("sqlcode= %d",ErrNo);
//		//userlog(ErrText);	
	//}
	return ret;
	
}

long COracle::GetSqlCode()
{
	return ErrNo;
}

long COracle::GetRowCount()
{
	return Rows;
}

void COracle :: InitData(void)
{	 	
	data=new CStrResult;
	data->header=data;
	data->rowid=0;
	Rows=0;
	
	data->next = NULL;
}

void COracle :: ResetData(void)
{
	//delete[] data;
	//userlog("ResetData()1");	
	DeleteData();
	//userlog("ResetData()2");	
	
	
	data=new CStrResult;
	//userlog("ResetData()3:%d",data);
	data->header=data;
	data->rowid=0;
	Rows=0;
	
	data->next = 0;
	//userlog("ResetData()3");	
}

char * COracle :: GetSqlErrText(void)
{
	return ErrText;
}


//CStrResult方法描述
CStrResult::CStrResult()
{
	istail=TRUE;
	rowbuffer = 0;
	rowid = 0;
	cols = 0;
	header = NULL;
	next = NULL;
}

CStrResult::~CStrResult()
{
	//userlog("rowidcol=%d",rowid);
	
	if (rowid!=0)
	{
		//delete[] rowbuffer;
		
		if (rowbuffer)
		{
			
			for (int i=0;i<cols;i++)
			{
				//userlog("~CStrResult():%d  cols:%d  value:[%s]",rowbuffer[i],cols,(char *)rowbuffer[i]);
				delete rowbuffer[i];
			}
			delete[] rowbuffer;
		}
	}
	
	//DropResult(header);
}

CStrResult* CStrResult::GetHeader()
{
	return header;
}

CStrResult* CStrResult::GetTail()
{
	CStrResult* search;
	if(istail!=TRUE)
		search=next;
	else
		return this;
	while(search->istail!=TRUE)
		search=search->next;
	return search;
}

char *CStrResult::GetColData(int row,int col)
{
	CStrResult *search;
	if(col>cols)
		return "";
	if(rowid>row)
		search=this->header;
	else
		search=this;
	while(search->rowid!=row)
	{
		if(search->istail==TRUE)
			return "";
		else
			search=search->next;
	}
	return search->rowbuffer[col-1];
}

void CStrResult::AddTail(char data[MAX_COLUMN][MAX_COLSIZE],int cols)
{
	int i;
	CStrResult *deal;
	deal=GetTail();
	deal->istail=FALSE;
	deal->next=new CStrResult;
	//userlog("addtail():%d  cols:%d",deal->next,cols);
	deal->next->header=this->header;
	deal->next->cols=cols;
	deal->next->rowid=deal->rowid+1;
	deal->next->rowbuffer=new char*[cols];
	
	deal->next->next = NULL;
	
	for(i=0;i<cols;i++)
	{
		deal->next->rowbuffer[i]=new char[strlen(data[i])+1];
		deal->next->rowbuffer[i][0]='\0';
		strcpy(deal->next->rowbuffer[i],data[i]);
		deal->next->rowbuffer[i][strlen(data[i])]='\0';
	}
}