tociquery.cpp

调用OCI的C++类

	if(fStmtType==OCI_STMT_SELECT)
			fErrorNo = OCIStmtExecute(db->hDBSvc, hStmt, hErr, (ub4)0, (ub4)0, 0, 0, OCI_DEFAULT);
	else	fErrorNo = OCIStmtExecute(db->hDBSvc, hStmt, hErr, (ub4)1, (ub4)0, 0, 0, OCI_DEFAULT);
	checkError(); 
	//在Execute后，可以获得列的个数和行的个数?
	//如果没有为select语句的返回值定义输出缓冲区，则hErr后的参数iters应当设置为0而不是>0;如果是非SELECT语句，此值>0;
	fErrorNo = OCIAttrGet((dvoid *)hStmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&nColumnCount, (ub4 *) 0, (ub4)OCI_ATTR_PARAM_COUNT, hErr);
	checkError();

	if (fFieldCount >0 )
	{
		delete[] fieldList;
		paramList = NULL;
	}
	fieldList = new TOCIField[nColumnCount];
	fFieldCount = nColumnCount;
	
	for(counter=1; counter<=nColumnCount ; counter ++)
	{
		fErrorNo = OCIParamGet(hStmt, OCI_HTYPE_STMT, hErr, (dvoid **)&param, counter);
		checkError();

		// 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 TOCIException(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: 
			//Modify begin version：0.0.0.7
			if (! innerDataSize)
				pCurrField->size = 255;
			else	pCurrField->size = innerDataSize;
			//Modify end version:0.0.0.7
			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;
			fPrefetchRows=1;	//	BLOB 目前只能以一条一条的方式fetch
			//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 TOCIException(fSqlStmt, ERR_DATA_TYPE_NOT_SUPPORT, pCurrField->name,innerDataType);
			break;
		} //end of data type convertion
	}//end of for loop every column
}

void TOCIQuery::setSQL(const char *inSqlstmt)
{
	if (! db->fConnected)
		throw TOCIException(inSqlstmt, ERR_GENERAL, "SetSQL(): can't set sqlstmt on disconnected Database");
	
	if (fActivated)
		throw TOCIException(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 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(const 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(const 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(const 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(const 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(const 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::setLongRawParameter(char *paramName, char* paramValue, bool isOutput )
{

	TOCIParam *para = paramByName(paramName); //在paramByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_LBI;
	if (para->stringValue)
		delete[] para->stringValue;

	int nLen;

	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);
	}
	
	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::setParameter(char *paramName, void* paramValue, int iLen,bool isOutput )
{
	 TOCIParam *para = paramByName(paramName); //在ParamByName中已经有判断参数不存在抛出异常
	
	para->fIsOutput = isOutput;
	para->dataType = SQLT_LBI;
	if (para->stringValue)
		delete[] para->stringValue;

	int nLen;

	if (isOutput)
	{
		nLen = MAX_STRING_VALUE_LENGTH; 
		para->stringValue = new char[nLen];
		para->stringValue[nLen] = '\0';
	}
	else 
	{
		if(paramValue != NULL)
		{
			nLen = iLen;
			para->stringValue = new char[nLen];
			memcpy((void *)para->stringValue,paramValue,nLen);
		}
		else
			setParameterNULL(paramName);
	}
	
	if (isOutput)
		fErrorNo = OCIBindByName(hStmt, &para->hBind, hErr, (text *)(para->name),
		-1,  (dvoid *)(para->stringValue),(sb4)(nLen), 
		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), 
		para->dataType, (dvoid *)&para->indicator, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
	checkError();
}

void TOCIQuery::setParameter(const char *paramName, const 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::setParamArray(char const *paramName, char ** paramValue,int iStructSize,int iStrSize,  bool isOutput)
{
	TOCIParam *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 TOCIQuery::setParamArray(char const *paramName, int * paramValue, int iStructSize, bool isOutput )
{
	TOCIParam *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 TOCIQuery::setParamArray(char const *paramName, double * paramValue, int iStructSize,bool isOutput) 
{
	TOCIParam *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 TOCIQuery::setParamArray(char const *paramName, long * paramValue, int iStructSize, bool isOutput )
{
	TOCIParam *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),