tdbquery.cpp

Oracle OCI的C++类的一个封装

	
	fErrorNo = OCIStmtExecute(db->hDBSvc, hStmt, hErr, (ub4)iters, (ub4)0, 0, 0, OCI_DEFAULT);
	
	OCIAttrGet((dvoid*)hStmt, OCI_HTYPE_STMT, (dvoid *)&fTotalRowsFetched, (ub4 *)0, OCI_ATTR_ROW_COUNT, hErr);
	nTransTimes ++;
	
	if (fErrorNo == OCI_SUCCESS)
		exeResult = true;
	else if ( fErrorNo == OCI_ERROR ) //以下允许返回空参数(1405)
	{
		OCIErrorGet (hErr, (ub4) 1, (text *) NULL, &errcode,
			errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
		if (errcode == 1405) 
			exeResult = true;
		else 
			CheckError();
	}
	else 
	{
			CheckError();
	}
	
	return exeResult;
}


void TDBQuery::GetParamsDef() 
{
	char *params[MAX_PARAMS_COUNT];
	int i, in_literal, n, nParamLen,nFlag = 0;
	char *cp,*ph;
	char *sql;
	
	int nLen = strlen(this->fSqlStmt);
	sql = new char[nLen+1];
	strcpy(sql, this->fSqlStmt);
	sql[nLen] = '\0';

	if (fParamCount>0)
		delete[] paramList;

	// Find and bind input variables for placeholders. 
	for (i = 0, in_literal = false, cp = sql; *cp != 0; cp++)
	{

      if (*cp == '\'')
			in_literal = ~in_literal;
      if (*cp == ':' && *(cp+1) != '=' && !in_literal)
      {

			for ( ph = ++cp, n = 0;  *cp && (isalnum(*cp) || *cp == '_'); cp++, n++);
			if(*cp == 0) 
				nFlag = 1;
			else 
				*cp = 0;

			if ( i > MAX_PARAMS_COUNT)
				throw TDBException(fSqlStmt, ERR_CAPABILITY_NOT_YET_SUPPORT, " param count execedes max numbers, please refer to OCIQuery.h");
			nParamLen = strlen((char *)ph);

			params[i] = new char[nParamLen+1];
			strcpy(params[i],(char *)ph);
			params[i][nParamLen] = '\0';
			i++;
			if(nFlag == 1) break;
      }   
	}
	delete[] sql;

	fParamCount = i;
	if (fParamCount>0)
	{
		paramList = new TDBParam[fParamCount];
		
		for (i=0; i<fParamCount; i++)
		{
			nParamLen = strlen(params[i]);
			paramList[i].name = new char[nParamLen+1];
			strncpy(paramList[i].name, params[i], nParamLen);
			paramList[i].name[nParamLen] = '\0';
			delete[] params[i];
		}
	}
}
void TDBQuery::Open(int prefetch_Row) 
{
	fPrefetchRows = prefetch_Row;
	if (fOpened)
	{
		fCurrRow = 0;
		fFetched = 0;
		fCurrRow = 0;
		fTotalRowsFetched = 0;
	}
	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Open(): sql statement is empty.");

	if ( this->fStmtType !=OCI_STMT_SELECT)
		throw TDBException( fSqlStmt, ERR_GENERAL, "Can't open none-select statement");

	GetFieldsDef();
	fBof = true;
	fOpened = true;
}

int TDBQuery::FieldCount()
{
	return fFieldCount;
}

int TDBQuery::ParamCount()
{
	return fParamCount;
}

TDBField& TDBQuery::Field(int i) 
{
	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Field(i): sql statement is not presented");

	if ( (i>=0) && (i<fFieldCount) ) 
		return fieldList[i];
	else throw TDBException(fSqlStmt , ERR_INDEX_OUT_OF_BOUND, "field index out of bound when call Field(i)");
}

TDBField& TDBQuery::Field(const char *fieldName) 
{
	int i;
	bool found = false;

	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Field(*fieldName): sql statement is not presented");

	if (! fOpened)
		throw TDBException(fSqlStmt, ERR_GENERAL, "can not access field before open");

	for(i=0; i<fFieldCount; i++)
	{
		found = CompareStrNoCase(Field(i).name,fieldName);
		if ( found )
			break;
	}
	if ( found ) 
		return fieldList[i];
	else 
		throw TDBException(fSqlStmt, ERR_FIELD_NOT_EXISTS, fieldName);
}

TDBParam& TDBQuery::Param(int index) 
{
	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Param(index): sql statement is not presented");

#ifdef debug
	printf("param i constructor\n");
#endif

	if ( (index>=0) && (index<fParamCount) ) 
		return paramList[index];
	else
		throw TDBException(fSqlStmt , ERR_INDEX_OUT_OF_BOUND, "param index out of bound when call Param(i)");
}

TDBParam& TDBQuery::Param(const char *inName) 
{
	int i;
	bool found = false;

	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Param(paramName): sql statement is not presented");
	
	for(i=0; i<fParamCount; i++)
	{
		found = CompareStrNoCase(paramList[i].name,inName);
		if (found)
			break;
	}
	if ( found ) 
		return paramList[i];
	else
		throw TDBException(fSqlStmt, ERR_PARAM_NOT_EXISTS, (const char*)inName);
}

bool TDBQuery::Next() 
{
	int fCanFetch = 1;					//当前记录指针的位置是否可以存取数据
	int tmpFetchedAllRows;

	sb4 errcode;
	text errbuf[MAX_ERRMSG_LENGTH];
	bool exeResult = true;

	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Next(): sql statement is not presented");
	
	if (!fOpened)
		throw TDBException(fSqlStmt, ERR_GENERAL, "Next(): can not access data before open it");
		
	fCurrRow ++ ;
	if( (fCurrRow == fFetched) && (fFetched < fPrefetchRows)) 
		fCanFetch=0;
	else if(fCurrRow==fFetched || ! fFetched)
	{
		fErrorNo = OCIStmtFetch(hStmt, hErr, (ub4)fPrefetchRows, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);	
		tmpFetchedAllRows = fTotalRowsFetched;
		fErrorNo = OCIAttrGet((dvoid*)hStmt, OCI_HTYPE_STMT, (dvoid *)&fTotalRowsFetched, (ub4 *)0, OCI_ATTR_ROW_COUNT, hErr);
		fFetched = fTotalRowsFetched - tmpFetchedAllRows;
		if(fFetched) 
		{
			fCanFetch=1;
			fCurrRow=0;
		}
		else fCanFetch=0; 

		if (fErrorNo == OCI_SUCCESS)
			exeResult = true;
		else if (fErrorNo == OCI_NO_DATA)
			exeResult = false;
		else if ( fErrorNo == OCI_ERROR ) //以下允许返回空列(1405),修正：不可以返回被截断的列(1406)
		{
			OCIErrorGet (hErr, (ub4) 1, (text *) NULL, &errcode,
				errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
			if (errcode == 1405)
				exeResult = true;
			else CheckError();
		}
		else	CheckError();
	}

	fBof = false;
	fEof = (fFetched && !fCanFetch);
	return (exeResult && fCanFetch);
}


/****************** parameter implementation **************************/
TDBParam::TDBParam()
{
	fIsOutput = false;
	stringValue = NULL;
	indicator = 0;
	hBind = (OCIBind *) 0; 
}

TDBParam::~TDBParam()
{
	delete[] name;
	delete[] stringValue;
}

int TDBParam::AsInteger() 
{
	if ( isNULL() )
		intValue = 0;

	if (dataType == SQLT_INT)
		return intValue;
	else	throw TDBException("TDBParam", ERR_READ_PARAM_DATA, name, "AsInteger()");	
}

long TDBParam::AsLong() 
{
	if ( isNULL() )
		longValue = 0;
	
	if (dataType == SQLT_LNG)
		return longValue;
	else	throw TDBException("TDBParam", ERR_READ_PARAM_DATA, name, "AsLong()");	
}

double TDBParam::AsFloat() 
{
	if ( isNULL() )
		dblValue = 0;

	if (dataType == SQLT_FLT)
		return dblValue;
	else	throw TDBException("TDBParam", ERR_READ_PARAM_DATA, name, "AsFloat()");
}


char* TDBParam::AsString() 
{
	if ( isNULL() )
		stringValue[0] = '\0';

	if (dataType == SQLT_STR)
		return stringValue;
	else	throw TDBException("TDBParam", ERR_READ_PARAM_DATA, name, "AsString()");
}

bool TDBParam::isNULL() 
{
	if (! fIsOutput)
		throw TDBException("TDBParam, not an output parameter", ERR_READ_PARAM_DATA, name, "isNULL()");
	return (indicator == -1);
}

/*********************************************************************************
*TDBSession	implementation
*********************************************************************************/
TDBSession::TDBSession(TDBDatabase *pDB)
{
	if(!pDB->fConnected)
		throw TDBException("", ERR_GENERAL, "TDBSession(pDB): Can not create a TDBSession when the database is not connected");
	
	m_hSession = NULL;
	m_hSrvCtx = NULL;
	m_bActive = FALSE;
	
	OCIHandleAlloc((dvoid *)pDB->hEnv,(dvoid **)&m_hSession,(ub4)OCI_HTYPE_SESSION,(size_t)0,(dvoid **) 0);
	
	OCIHandleAlloc((dvoid *)pDB->hEnv,(dvoid **)&m_hSrvCtx,(ub4)OCI_HTYPE_SVCCTX,(size_t)0,(dvoid **) 0);

	OCIHandleAlloc((dvoid *)pDB->hEnv,(dvoid **)&m_hError,(ub4)OCI_HTYPE_ERROR,(size_t)0,(dvoid **)0);

	m_iErrorNo = OCIAttrSet(m_hSrvCtx,OCI_HTYPE_SVCCTX,pDB->hSvr,0,OCI_ATTR_SERVER,m_hError);
	CheckError();
	
	//set the username/password in session handle
	m_iErrorNo = OCIAttrSet(m_hSession,OCI_HTYPE_SESSION,pDB->usr,strlen(pDB->usr),OCI_ATTR_USERNAME,m_hError);
	m_iErrorNo = OCIAttrSet(m_hSession,OCI_HTYPE_SESSION,pDB->pwd,strlen(pDB->pwd),OCI_ATTR_PASSWORD,m_hError);
	
	//set the Authentication handle in the server context handle
	m_iErrorNo = OCIAttrSet(m_hSrvCtx,OCI_HTYPE_SVCCTX,m_hSession,0,OCI_ATTR_SESSION,m_hError);
	CheckError();

};

TDBSession::~TDBSession()
{
	if(m_bActive)
		sessionEnd();
	
	OCIHandleFree((dvoid *)m_hSession,(ub4)OCI_HTYPE_SESSION);
	OCIHandleFree((dvoid *)m_hSrvCtx,(ub4)OCI_HTYPE_SVCCTX);
	
}

void TDBSession::sessionBegin()
{
	if(m_bActive)
		return;
		
	//begin a session
	m_iErrorNo = OCISessionBegin(m_hSrvCtx,m_hError,m_hSession,OCI_CRED_RDBMS,(ub4)OCI_DEFAULT);
	CheckError();

	m_bActive = TRUE;                            
}

void TDBSession::sessionEnd()
{
	if(!m_bActive)
		return;
	//end a session
	m_iErrorNo = OCISessionEnd(m_hSrvCtx,m_hError,m_hSession,OCI_DEFAULT);
	CheckError();	
}
static void errprint(dvoid *errhp, ub4 htype, sb4 *errcodep)
{
  text errbuf[512];

  if (errhp)
  {
    sb4  errcode;

    if (errcodep == (sb4 *)0)
      errcodep = &errcode;

    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, errcodep,
                       errbuf, (ub4) sizeof(errbuf), htype);
    (void) printf("Error - %.*s\n", 512, errbuf);
  }
}

void TDBSession::CheckError()
{

 	switch (m_iErrorNo)
 	 {
 	 case OCI_SUCCESS:
 	   break;
 	 case OCI_SUCCESS_WITH_INFO:
 	   (void) printf( "Error - OCI_SUCCESS_WITH_INFO\n");
 	   errprint(m_hError, OCI_HTYPE_ERROR, &m_iErrorNo);
 	   break;
 	 case OCI_NEED_DATA:
 	   (void) printf( "Error - OCI_NEED_DATA\n");
 	   break;
 	 case OCI_NO_DATA:
 	   (void) printf( "Error - OCI_NODATA\n");
 	   break;
 	 case OCI_ERROR:
 		errprint(m_hError, OCI_HTYPE_ERROR, &m_iErrorNo);
 	   break;
 	 case OCI_INVALID_HANDLE:
 	   (void) printf( "Error - OCI_INVALID_HANDLE\n");
 	   break;
 	 case OCI_STILL_EXECUTING:
 	   (void) printf( "Error - OCI_STILL_EXECUTE\n");
 	   break;
 	 case OCI_CONTINUE:
 	   (void) printf( "Error - OCI_CONTINUE\n");
 	   break;
 	 default:
 	   break;
 	 }	
}

//判断SQL是否完成
int SqlDone()
{
	return 0;
}