tociquery.cpp

oci是操作oracle数据库的接口,已对相应的接口用C++进行的封装成类,以方便使用

		delete[] fSqlStmt;
	int nLen = strlen(inSqlstmt);
	fSqlStmt = new char[nLen + 1];
	if (fSqlStmt == NULL)
		throw TOCIException(inSqlstmt, ERR_NOMORE_MEMORY_BE_ALLOCATED, "SetSQL()", __LINE__);
	strcpy(fSqlStmt,inSqlstmt);
	fSqlStmt[nLen] = '\0';
	
	fErrorNo = OCIStmtPrepare(hStmt, hErr, (unsigned char *)fSqlStmt, strlen(fSqlStmt), OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
	fActivated = (fErrorNo == OCI_SUCCESS);
	CheckError();

	fErrorNo = OCIAttrGet(hStmt, OCI_HTYPE_STMT, &(this->fStmtType),  0, OCI_ATTR_STMT_TYPE, hErr);
	CheckError();
	GetParamsDef();
}


TOCIParam *TOCIQuery::ParamByName(char *paramName)
{
	TOCIParam *para = NULL;
	bool found = false;
	int i;
	
	if (fSqlStmt == NULL)
		throw TOCIException(paramName, ERR_GENERAL, "ParamByName(): sql statement is empty.");
	
	for(i=0; i<fParamCount; i++)
	{
		found = CompareStrNoCase(Param(i).name,paramName);
		if ( found )
			break;
	}
	if ( found ) 
		para = &paramList[i];
	else 
		throw TOCIException(fSqlStmt, ERR_PARAM_NOT_EXISTS, paramName);
	return para;
}

void TOCIQuery::SetParameterNULL(char *paramName)
{
	TOCIParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->dataType = SQLT_LNG;
	fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name), //you don't have to pass any value/value length if the parameter value is null, or may raise oci success with info
		-1, (ub1 *)0,(sword)0, para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	
	CheckError();
}

void TOCIQuery::SetParameter(char *paramName, double paramValue, bool isOutput )
{
	TOCIParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_FLT;
	para->dblValue = paramValue;

	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)para->name,
		-1,  (ub1 *)&(para->dblValue),(sb4)sizeof(para->dblValue), 
		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), (dvoid *)&(para->dblValue),(sb4)sizeof(para->dblValue), 
		para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

	CheckError();
}

void TOCIQuery::SetParameter(char *paramName, long paramValue, bool isOutput)
{
	TOCIParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_INT;
	para->longValue = paramValue;
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1, (ub1 *)&(para->longValue),(sword)sizeof(para->longValue), 
		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->longValue),(sword)sizeof(para->longValue), 
		para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	
	CheckError();
}

void TOCIQuery::SetParameter(char *paramName, int paramValue, bool isOutput )
{
	TOCIParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_INT;
	para->intValue = paramValue;
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1, (ub1 *)&(para->intValue),(sword)sizeof(para->intValue), 
		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->intValue),(sword)sizeof(para->intValue), 
		para->dataType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	
	CheckError();
}

void TOCIQuery::SetParameter(char *paramName, char* paramValue, bool isOutput )
{
	TOCIParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_STR;
	if (para->stringValue)
		delete[] para->stringValue;

	int nLen;

//Modify begin version:0.0.0.8
	if (isOutput)
	{
		nLen = MAX_STRING_VALUE_LENGTH-1; 
		para->stringValue = new char[nLen+1];
		para->stringValue[nLen] = '\0';
	}
	else 
	{
		if(paramValue != NULL)
		{
			nLen = strlen(paramValue);
			para->stringValue = new char[nLen+1];
			strncpy((char *)para->stringValue,paramValue,nLen);
			para->stringValue[nLen] = '\0';
		}
		else
			SetParameterNULL(paramName);
	}
//Modify end version:0.0.0.8
	
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1,  (dvoid *)(para->stringValue),(sb4)(nLen+1), 
		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->stringValue),(sb4)(nLen+1), 
		para->dataType, (dvoid *)&para->indicator, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	CheckError();
}

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

bool TOCIQuery::Execute()
{
	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(hSvc, hStmt, hErr, (ub4)1, (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 *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);
}