tdbquery.cpp

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

		// column name and column name length
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid**)&columnName,(ub4 *)&columnNameLength, OCI_ATTR_NAME, hErr);
		CheckError();

		// data length 
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid *)&innerDataSize, (ub4 *)0, OCI_ATTR_DATA_SIZE, hErr);
		CheckError();

		// precision 
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid *)&innerPrecision, (ub4 *)0, OCI_ATTR_PRECISION, hErr);
		CheckError();

		// scale 
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid *)&innerScale, (ub4 *)0, OCI_ATTR_SCALE, hErr);
		CheckError();

		// isNULL
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid *)&innerIsNULL, (ub4 *)0, OCI_ATTR_IS_NULL, hErr);
		CheckError();

		// data type:
		fErrorNo = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM, (dvoid *)&innerDataType, (ub4 *)0, OCI_ATTR_DATA_TYPE, hErr);
		CheckError();

		pCurrField = &fieldList[counter-1];

		pCurrField->name = new char[columnNameLength+1];
		if (pCurrField->name == NULL)
			throw TDBException(fSqlStmt, ERR_NOMORE_MEMORY_BE_ALLOCATED, "GetFieldsDef()", __LINE__);
		for (j=0; j<columnNameLength; j++)
			pCurrField->name[j] = columnName[j];
		pCurrField->name[columnNameLength] = '\0';

		pCurrField->nullable = innerIsNULL>0;

		pCurrField->type = innerDataType; //初始化为内部类型，用于错误返回
		pCurrField->fParentQuery = this;
		pCurrField->fDataIndicator = new sb2[fPrefetchRows];

		switch 	(innerDataType)
		{
		case SQLT_NUM://NUMBER_TYPE: 
			if (! innerDataSize)
				pCurrField->size = 255;
			else	pCurrField->size = innerDataSize;
			pCurrField->precision = innerPrecision;
			pCurrField->scale = innerScale;
			//预先定义字符缓冲区，用于接收数值到字符转换的结果
			pCurrField->fDataBuf = new ub1[fPrefetchRows * (pCurrField->size+1)]; 
			if (innerScale == 0) //没有小数点，为整数
					pCurrField->type = INT_TYPE;
			else	pCurrField->type = FLOAT_TYPE;
			
			//绑定输出数据到缓冲区(整数类型绑定到整数缓冲区)
			fErrorNo = OCIDefineByPos(hStmt, &(pCurrField->hDefine), hErr, counter,
				(dvoid *)pCurrField->fDataBuf, pCurrField->size + 1, SQLT_VNU,
				(dvoid *)pCurrField->fDataIndicator, (ub2 *)0 , (ub2 *) 0, OCI_DEFAULT);
			CheckError();
			break;
		case SQLT_DAT://DATE_TYPE:
			pCurrField->type = DATE_TYPE;
			pCurrField->size = 7;
			//绑定输出数据到缓冲区(date类型也是绑定到字符串缓冲区)
			pCurrField->fDataBuf = new ub1[fPrefetchRows *(pCurrField->size)];
			fErrorNo = OCIDefineByPos(hStmt, &(pCurrField->hDefine), hErr, counter,
				pCurrField->fDataBuf, 7, SQLT_DAT,
				(dvoid *)pCurrField->fDataIndicator, (ub2 *)0, (ub2 *) 0, OCI_DEFAULT);
			CheckError();
			break;
		case SQLT_CHR: case SQLT_AFC: //DATA_TYPE_CHAR: case VARCHAR2_TYPE: 
			pCurrField->type = STRING_TYPE;
			/*if (innerDataSize>MAX_STRING_VALUE_LENGTH)
				pCurrField->size = MAX_STRING_VALUE_LENGTH;
			else pCurrField->size = innerDataSize;*/
			pCurrField->size = innerDataSize;  //以系统取得的字段长度作为数据的长度大小
			//绑定输出数据到缓冲区
			pCurrField->fDataBuf = new ub1[fPrefetchRows * (pCurrField->size+1)];
			fErrorNo = OCIDefineByPos(hStmt, &(pCurrField->hDefine), hErr, counter,
				pCurrField->fDataBuf, pCurrField->size+1, SQLT_STR,
				(dvoid *)pCurrField->fDataIndicator, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
			CheckError(); 
			//if string type, provide string length as client needs:
			sprintf(temp, "WIDTH=\"%d\"", pCurrField->size);
			break;
		case SQLT_RDD:
			pCurrField->type = ROWID_TYPE;
			pCurrField->size = 18;
			//绑定输出数据到缓冲区
			pCurrField->fDataBuf = new ub1[fPrefetchRows * (pCurrField->size+1)];
			fErrorNo = OCIDefineByPos(hStmt, &(pCurrField->hDefine), hErr, counter,
				(dvoid *)pCurrField->fDataBuf, 
				pCurrField->size+1, SQLT_STR,
				(dvoid *)pCurrField->fDataIndicator, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);
			CheckError();
			break;
		case SQLT_BLOB:
			pCurrField->size = 4;
			//pCurrField->fDataBuf = new ub1[(pCurrField->size+1];
			pCurrField->type = SQLT_BLOB;
			fErrorNo = OCIDescriptorAlloc((dvoid *)db->hEnv, (dvoid **)&pCurrField->hBlob,
				(ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **) 0);
			CheckError();

			fErrorNo = OCIDefineByPos(hStmt, &(pCurrField->hDefine), hErr, counter,
				(dvoid *)&pCurrField->hBlob, (sb4)-1, SQLT_BLOB,
				(dvoid *)0,  (ub2 *)0, (ub2 *) 0, OCI_DEFAULT);
			CheckError();
			break;
		default:
			throw TDBException(fSqlStmt, ERR_DATA_TYPE_NOT_SUPPORT, pCurrField->name,innerDataType);
			break;
		} //end of data type convertion
	}//end of for loop every column
}

void TDBQuery::SetSQL(const char *inSqlstmt) 
{
	if (! db->fConnected)
		throw TDBException(inSqlstmt, ERR_GENERAL, "SetSQL(): can't set sqlstmt on disconnected Database");
	
	if (fActivated)
		throw TDBException(inSqlstmt, ERR_GENERAL, "SetSQL(): can't set sqlstmt on opened state");
	
	//如果有已经分配空间给sqlstatement,则在Close()中已经释放；因为只有在Close()后才可以赋予SQLstatement值
	fActivated  = false;
	fTotalRowsFetched = 0;
	fEof = false;
	fOpened = false;
	
	//保存sql语句
	if (fSqlStmt != NULL)
		delete[] fSqlStmt;
	int nLen = strlen(inSqlstmt);
	fSqlStmt = new char[nLen + 1];
	if (fSqlStmt == NULL)
		throw TDBException(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();
}


TDBParam *TDBQuery::ParamByName(const char *paramName) 
{
	TDBParam *para = NULL;
	bool found = false;
	int i;
	
	if (fSqlStmt == NULL)
		throw TDBException(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 TDBException(fSqlStmt, ERR_PARAM_NOT_EXISTS, paramName);
	return para;
}

void TDBQuery::SetParameterNULL(const char *paramName) 
{
	TDBParam *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 TDBQuery::SetParameter(const char *paramName, double paramValue) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->dataType = SQLT_FLT;
	para->dblValue = paramValue;

		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 TDBQuery::SetParameter(const char *paramName, long paramValue) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->dataType = SQLT_INT;
	para->longValue = paramValue;

		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 TDBQuery::SetParameter(const char *paramName, long long paramValue)
{
    TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
/*
    para->dataType = SQLT_FLT;
    para->dblValue = (double)paramValue;

        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);
*/
	para->dataType = SQLT_INT;
	para->longValue = (long )paramValue;

		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 TDBQuery::SetParameter(const char *paramName, int paramValue)
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->dataType = SQLT_INT;
	para->intValue = paramValue;
	
		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();
}


//add by fu.wenjun@20041125
void TDBQuery::SetParameter(const char *paramName, const char paramValue)
{
    char strparamvalue[2];
    strparamvalue[0] = paramValue ;
    strparamvalue[1] = '\0';
    SetParameter(paramName,strparamvalue);
}
void TDBQuery::SetParameter(const char *paramName, const char* paramValue) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->dataType = SQLT_STR;
	if (para->stringValue)
		delete[] para->stringValue;

	int nLen;

		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);

		//printf("%s\n",this->fSqlStmt);
		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);
		//printf("%s,%s\n",paramName,paramValue);
	//printf("%s\n",this->fSqlStmt);
		CheckError();
}


void TDBQuery::SetParamArray(const char *paramName, char ** paramValue,int iStructSize,int iStrSize,int iArraySize,bool isOutput) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_STR;
	para->stringArray = 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 TDBQuery::SetParamArray(const char *paramName, int * paramValue, int iStructSize,int iArraySize, bool isOutput ) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_INT;
	para->intArray = paramValue;
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1, (ub1 *)(para->intArray),(sword)sizeof(para->intArray[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->intArray),(sword)sizeof(para->intArray[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 TDBQuery::SetParamArray(const char *paramName, double * paramValue,int iStructSize,int iArraySize, bool isOutput)  
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_FLT;
	para->dblArray = paramValue;

	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)para->name,
		-1,  (ub1 *)(para->dblArray),(sb4)sizeof(para->dblArray[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), (dvoid *)(para->dblArray),(sb4)sizeof(para->dblArray[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 TDBQuery::SetParamArray(const char *paramName, long * paramValue, int iStructSize,int iArraySize, bool isOutput ) 
{
	TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_INT;
	para->longArray = paramValue;
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-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 TDBQuery::SetParamArray(const char *paramName, long long * paramValue, int iStructSize,int iArraySize, bool isOutput ) 
{
    TDBParam *para = ParamByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
   
	para->fIsOutput = isOutput;
	para->dataType = SQLT_INT;
	para->longArray = (long *)paramValue;

	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-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 TDBQuery::CheckError() 
{
	if (fErrorNo != OCI_SUCCESS) 
		throw TDBException(fErrorNo, hErr, "Oracle OCI Call", fSqlStmt);
}

bool TDBQuery::Execute(int iters) 
{
	sb4 errcode;
	text errbuf[MAX_ERRMSG_LENGTH-1];
	bool exeResult = false;
#ifdef __DEBUG__
    bExecuteFlag = true;
#endif
	
	if (fSqlStmt == NULL)
		throw TDBException("", ERR_GENERAL, "Execute(): sql statement is not presented");
	
	if	(this->fStmtType == OCI_STMT_SELECT)
		throw TDBException( fSqlStmt, ERR_GENERAL, "Execute(): Can't Execute a select statement.");