tociquery.cpp

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

			fParentQuery->CheckError();	
		}
	}

	if ( flushedAmount )
	{
			fParentQuery->fErrorNo = OCILobFlushBuffer(fParentQuery->hSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);
			fParentQuery->CheckError();	
	}
	fclose(fileHandle);
}

void  TOCIField::LoadFromBuffer(unsigned char *buf, unsigned int bufLength)
{
	ub1 innerBuf[MAX_LOB_BUFFER_LENGTH];

	ub4 remainder, nActual, nTry;
	ub4  flushedAmount = 0, offset = 1;

	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "LoadFromBuffer()", name);

	if (type != SQLT_BLOB)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "LoadFromBuffer()");
	
	fParentQuery->fErrorNo = OCILobTrim(fParentQuery->hSvc, fParentQuery->hErr, hBlob, 0);
	fParentQuery->CheckError();
	remainder = bufLength;

	/* enable the BLOB locator for buffering operations */
	fParentQuery->fErrorNo = OCILobEnableBuffering(fParentQuery->hSvc, fParentQuery->hErr, hBlob);
	fParentQuery->CheckError();

	while (remainder > 0)
	{
		nActual = nTry = (remainder > MAX_LOB_BUFFER_LENGTH) ? MAX_LOB_BUFFER_LENGTH : remainder;
		
		memcpy(innerBuf, buf + offset-1, nActual);

		fParentQuery->fErrorNo = OCILobWrite(fParentQuery->hSvc, fParentQuery->hErr, hBlob,
			&nActual, offset, (dvoid *)innerBuf, (ub4)nTry, OCI_ONE_PIECE, (dvoid *)0,
			(sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,	(ub2) 0, (ub1) SQLCS_IMPLICIT);
		fParentQuery->CheckError();
		
		flushedAmount += nTry;
		remainder -= nTry;
		offset += nTry;

		if (flushedAmount >= LOB_FLUSH_BUFFER_SIZE)
		{
			flushedAmount = 0;
			fParentQuery->fErrorNo = OCILobFlushBuffer(fParentQuery->hSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);
			fParentQuery->CheckError();	
		}
	}

	if ( flushedAmount )
	{
			fParentQuery->fErrorNo = OCILobFlushBuffer(fParentQuery->hSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);
			fParentQuery->CheckError();	
	}
}

double TOCIField::asFloat()
{
	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "asFloat()", name);

	if ( isNULL() )
		return 0;
	if ( (type == FLOAT_TYPE) || ( type == INT_TYPE) )
		return atof( (char *)(fDataBuf + (size+1) * fParentQuery->fCurrRow));
	else	throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "asFloat()");
}

char* TOCIField::asDateTimeString()
{
	int year, month, day, hour, minute, second;

	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "asDateTimeString()", name);

	if (type != DATE_TYPE)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "asDateTimeString()");
	else
	{
		this->asDateTimeInternal(year, month, day, hour, minute, second);
		if ( year == 0 )
				sprintf( (char *)fStrBuffer, NULL_STRING);
		else	sprintf( (char *)fStrBuffer,"%d-%d-%d %d:%d:%d", year, month, day, hour, minute,second);
		return (char *)fStrBuffer;
	}
}

void	TOCIField::asDateTime(int &year, int &month, int &day, int &hour, int &minute, int &second)
{
	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "asDateTime()", name);

	if (type != DATE_TYPE)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "asDateTime()");
	else this->asDateTimeInternal(year, month, day, hour, minute, second);
}

void	TOCIField::asDateTimeInternal(int &year, int &month, int &day, int &hour, int &minute, int &second)
{
	unsigned char cc,yy,mm,dd,hh,mi,ss;
	ub1	*data;

	if ( isNULL() )
	{
		year = 0;
		month = 0;
		day = 0;
		hour = 0;
		minute = 0;
		second = 0;
		return;
	}
	data = fDataBuf + 7 * (fParentQuery->fCurrRow);
	cc=data[0];	
	yy=data[1]; 
	mm=data[2]; 
	dd=data[3]; 
	hh=data[4]-1; 
	mi=data[5]-1; 
	ss=data[6]-1; 
	cc=(unsigned char)abs(cc-100);
	yy=(unsigned char)abs(yy-100);
	year = (unsigned int)cc*100 + (unsigned int) yy;
	month = mm;
	day = dd;
	hour = hh;
	minute = mi;
	second = ss;
}

int TOCIField::asInteger()
{	
	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "asInteger()", name);

	//if (type != INT_TYPE)
	if ((type != INT_TYPE)&&(type != FLOAT_TYPE))  //by hwy 060610
		throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "asInteger()");
	else return atoi( (char *)(fDataBuf + (size+1) * fParentQuery->fCurrRow));
}

/*********** TOCIQuery Implementation************/
TOCIQuery::TOCIQuery(TOCIDatabase *oradb)
{
	if (! oradb->fConnected)
		throw TOCIException("", ERR_GENERAL, "TOCIQuery(TOCIDatabase &db): Can not declare a TOCIQuery when the database is not connected");

	fFetched = 0;
	fPrefetchRows = 1;
	fCurrRow = 0;
	fTotalRowsFetched = 0;
	fBof = false;
	fEof = false;
//Added begin at ver : 0.0.0.6
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
//Added end at ver : 0.0.0.6
	nTransTimes = 0;
	db = oradb;
	fActivated = false;
	fFieldCount = 0;
	fParamCount = 0;

	fSqlStmt = NULL;
	paramList = NULL;
	fieldList = NULL;

	fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **) &hUser,(ub4) OCI_HTYPE_SESSION,(size_t) 0, (dvoid **) 0);
	CheckError();

	fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **) &hErr,(ub4) OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0);
	CheckError();

	fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **)&hStmt, OCI_HTYPE_STMT, 0, 0);
	CheckError();
	
	//fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **)&hTrans, OCI_HTYPE_TRANS, 0, 0);
	//CheckError();

	fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **)&hSvc, OCI_HTYPE_SVCCTX,0, 0);
	CheckError();

	fErrorNo = OCIAttrSet (hSvc, OCI_HTYPE_SVCCTX, db->hSvr, 0, OCI_ATTR_SERVER, hErr);
	CheckError();

	/* set the username/password in user handle */
	OCIAttrSet(hUser, OCI_HTYPE_SESSION, db->usr, strlen(db->usr),OCI_ATTR_USERNAME, hErr);
	OCIAttrSet(hUser, OCI_HTYPE_SESSION, db->pwd, strlen(db->pwd),OCI_ATTR_PASSWORD, hErr);

	// Set the Authentication handle in the service handle
	fErrorNo = OCIAttrSet(hSvc, OCI_HTYPE_SVCCTX, hUser, 0, OCI_ATTR_SESSION, hErr);

	OCISessionBegin (hSvc, hErr, hUser, OCI_CRED_RDBMS, OCI_DEFAULT);

	//Set Trans:
	//OCIAttrSet(hSvc, OCI_HTYPE_SVCCTX, hTrans, 0, OCI_ATTR_TRANS, hErr);
}

TOCIQuery::~TOCIQuery()
{
	if (fSqlStmt != NULL)
		delete[] fSqlStmt;
	if (fParamCount >0)
		delete[] paramList;
	if (fFieldCount >0)
		delete[] fieldList;
	if (nTransTimes)
	{
		fErrorNo = OCITransRollback(hSvc, hErr, OCI_DEFAULT);
		CheckError();
	}
//Added begin at ver : 0.0.0.6
#ifdef __DEBUG__
//    if(bExecuteFlag)
//        userlog("TOCIQuery执行了Execute()但是没有提交或回滚,可能造成锁表。"); 
#endif
//Added end at ver : 0.0.0.6
	OCISessionEnd (hSvc, hErr, hUser, OCI_DEFAULT);
	OCIHandleFree(hStmt, OCI_HTYPE_STMT);
	//OCIHandleFree(hTrans,OCI_HTYPE_TRANS);
	OCIHandleFree(hSvc, OCI_HTYPE_SVCCTX);
	OCIHandleFree(hUser,OCI_HTYPE_SESSION);
	OCIHandleFree(hErr,OCI_HTYPE_ERROR);
}

void TOCIQuery::Close()
{
	if (! fActivated)
		return;
	
	if (fSqlStmt != NULL)
	{
		delete[] fSqlStmt;
		fSqlStmt = NULL;
	}
	if (fParamCount > 0)
	{
		delete[] paramList;
		paramList = NULL;
	}
	if (fFieldCount > 0)
	{
		delete[] fieldList;
		fieldList = NULL;
	}
	
	fFieldCount = 0;
	fParamCount = 0;
	fActivated = false;

	fFetched = 0;
	fPrefetchRows = PREFETCH_ROWS;
	fCurrRow = 0;
	fTotalRowsFetched = 0;
}

bool TOCIQuery::Commit()
{
	bool exeSuccess = false;
//Added begin at ver : 0.0.0.6
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
//Added end at ver : 0.0.0.6
	fErrorNo = OCITransCommit(hSvc, hErr, OCI_DEFAULT);
	CheckError();
	if (fErrorNo == OCI_SUCCESS)
		nTransTimes = 0;
	return (fErrorNo == OCI_SUCCESS);	
}

bool TOCIQuery::Rollback()
{
	bool exeSuccess = false;
//Added begin at ver : 0.0.0.6
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
//Added end at ver : 0.0.0.6
	fErrorNo = OCITransRollback(hSvc, hErr, OCI_DEFAULT);
	if (fErrorNo == OCI_SUCCESS)
	{
		nTransTimes = 0;
		exeSuccess = true;
	}
	else
		CheckError();
	return exeSuccess;
}

void TOCIQuery::GetFieldsDef()
{
	char temp[20];
	
	TOCIField *pCurrField;

	OCIParam	*param = (OCIParam *)0;
	ub4			counter;			//计数器，在循环语句中实用
	ub4			nColumnCount;	//在分析sqlstmt后，获得的列数目
	//以下参数的长度参见"OCI Programmer's" Guide P208 , 6-10
	text   *columnName;				//字段名称
	ub4	 columnNameLength,j;		//字段名称长度 unsigned int
	ub2	 innerDataSize;			//数据长度 unsigned short
	ub2	 innerDataType;			//Oracle 内部数据类型 signed short
	ub2	 innerPrecision;			//包括小数点的总位数, ub1 is a bug in documentation?
	sb1    innerScale;				//小数点个数
	ub1    innerIsNULL;				//是否允许为空值
	
	if(fStmtType==OCI_STMT_SELECT)
			fErrorNo = OCIStmtExecute(hSvc, hStmt, hErr, (ub4)0, (ub4)0, 0, 0, OCI_DEFAULT);
	else	fErrorNo = OCIStmtExecute(hSvc, 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();
//		userlog("GetFieldsDef name=%s,type=%ld,size=%ld,innerScale=%ld",columnName, innerDataType,innerDataSize,innerScale);

		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];
//userlog("DataType1====SQLT_NUM%ld,SQLT_DAT%ld,SQLT_CHR%ld,SQLT_RDD%ld,SQLT_BLOB%ld",SQLT_NUM,SQLT_DAT,SQLT_CHR,SQLT_RDD,SQLT_BLOB);
//userlog("DataType1====INT_TYPE%ld,FLOAT_TYPE%ld,DATE_TYPE%ld,STRING_TYPE%ld,ROWID_TYPE%ld,SQLT_BLOB%ld",INT_TYPE,FLOAT_TYPE,DATE_TYPE,STRING_TYPE,ROWID_TYPE,SQLT_BLOB);
		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_STR,
				(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 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(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)