tociquery.cpp

调用OCI的C++类

		-1, (ub1 *)(para->longArray),(sword)sizeof(para->longArray[0]), 
		para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	else 
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		strlen(para->name),(ub1 *)(para->longArray),(sword)sizeof(para->longArray[0]), 
		para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	
	checkError();
	fErrorNo = OCIBindArrayOfStruct ( para->hBind, hErr,(ub4) iStructSize,0,0,0);
	checkError();
}

void TOCIQuery::setParamArray(char const *paramName, void ** paramValue,int iStructSize,int iStrSize,  bool isOutput)
{
	TOCIParam *para = paramByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_LBI;
	para->stringArray = (char **)paramValue;
	para->stringSize = iStrSize;
	
	
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1,  (dvoid *)(para->stringArray),(sb4)(para->stringSize), 
		para->dataType, (dvoid *) &para->indicator, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *)0, OCI_DEFAULT);
	else
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		(sb4)strlen(para->name), (dvoid *)(para->stringArray),(sb4)(para->stringSize), 
		para->dataType, (dvoid *)&para->indicator, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	checkError();

	fErrorNo = OCIBindArrayOfStruct ( para->hBind, hErr,(ub4) iStructSize,0,0,0);
	checkError();
}

void TOCIQuery::checkError()
{
	if (fErrorNo != OCI_SUCCESS) 
		throw TOCIException(fErrorNo, hErr, "Oracle OCI Call", fSqlStmt);
}

bool TOCIQuery::execute(int iters)
{
	sb4 errcode;
	text errbuf[MAX_ERRMSG_LENGTH-1];
	bool exeResult = false;
//Added begin at ver : 0.0.0.6
#ifdef __DEBUG__
    bExecuteFlag = true;
#endif
//Added end at ver : 0.0.0.6
	
	if (fSqlStmt == NULL)
		throw TOCIException("", ERR_GENERAL, "Execute(): sql statement is not presented");
	
	if	(this->fStmtType == OCI_STMT_SELECT)
		throw TOCIException( fSqlStmt, ERR_GENERAL, "Execute(): Can't Execute a select statement.");
	
	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 TOCIQuery::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 TOCIException(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 TOCIParam[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 TOCIQuery::open(int prefetch_Row)
{
	fPrefetchRows = prefetch_Row;
	if (fOpened)
	{
		fCurrRow = 0;
		fFetched = 0;
		fCurrRow = 0;
		fTotalRowsFetched = 0;
	}
	if (fSqlStmt == NULL)
		throw TOCIException("", ERR_GENERAL, "open(): sql statement is empty.");

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

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

int TOCIQuery::fieldCount()
{
	return fFieldCount;
}

int TOCIQuery::paramCount()
{
	return fParamCount;
}

TOCIField& TOCIQuery::field(int i)
{
	if (fSqlStmt == NULL)
		throw TOCIException("", ERR_GENERAL, "Field(i): sql statement is not presented");

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

TOCIField& TOCIQuery::field(char const *fieldName)
{
	int i;
	bool found = false;

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

	if (! fOpened)
		throw TOCIException(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 TOCIException(fSqlStmt, ERR_FIELD_NOT_EXISTS, fieldName);
}

TOCIParam& TOCIQuery::param(int index)
{
	if (fSqlStmt == NULL)
		throw TOCIException("", 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 TOCIException(fSqlStmt , ERR_INDEX_OUT_OF_BOUND, "param index out of bound when call Param(i)");
}

TOCIParam& TOCIQuery::param(char *inName)
{
	int i;
	bool found = false;

	if (fSqlStmt == NULL)
		throw TOCIException("", 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 TOCIException(fSqlStmt, ERR_PARAM_NOT_EXISTS, (const char*)inName);
}

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

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

	if (fSqlStmt == NULL)
		throw TOCIException("", ERR_GENERAL, "next(): sql statement is not presented");
	
	if (!fOpened)
		throw TOCIException(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 **************************/
TOCIParam::TOCIParam()
{
	fIsOutput = false;
	stringValue = NULL;
	indicator = 0;
	hBind = (OCIBind *) 0; 
}

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

int TOCIParam::asInteger()
{
	if ( isNULL() )
		intValue = 0;

	if (dataType == SQLT_INT)
		return intValue;
	else	throw TOCIException("TOCIParam", ERR_READ_PARAM_DATA, name, "asInteger()");	
}

long TOCIParam::asLong()
{
	if ( isNULL() )
		longValue = 0;
	
	if (dataType == SQLT_LNG)
		return longValue;
	else	throw TOCIException("TOCIParam", ERR_READ_PARAM_DATA, name, "asLong()");	
}

double TOCIParam::asFloat()
{
	if ( isNULL() )
		dblValue = 0;

	if (dataType == SQLT_FLT)
		return dblValue;
	else	throw TOCIException("TOCIParam", ERR_READ_PARAM_DATA, name, "asFloat()");
}


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

	if (dataType == SQLT_STR)
		return stringValue;
	else	throw TOCIException("TOCIParam", ERR_READ_PARAM_DATA, name, "asString()");
}

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

/*********************************************************************************
*TOCISession	implementation
*********************************************************************************/
TOCISession::TOCISession(TOCIDatabase *pDB)
{
	if(!pDB->fConnected)
		throw TOCIException("", ERR_GENERAL, "TOCISession(pDB): Can not create a TOCISession 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();

};

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

void TOCISession::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 TOCISession::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 TOCISession::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;
 	 }	
}