tdbquery.cpp

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

	ub4  flushedAmount = 0;
	FILE *fileHandle ;

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

	if (type != SQLT_BLOB)
		throw TDBException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "LoadFromFile()");

	if( (fileHandle = fopen(fileName, (const char *) "rb")) == NULL )
		throw TDBException(fParentQuery->fSqlStmt, ERR_FILE_IO, "LoadFromFile()", fileName);
	
	fseek(fileHandle,0,SEEK_END);
	remainder = ftell(fileHandle);
	fseek(fileHandle, 0, 0);

	fParentQuery->fErrorNo = OCILobGetLength(fParentQuery->db->hDBSvc, fParentQuery->hErr, hBlob, &LobLength);
	fParentQuery->CheckError();

	fParentQuery->fErrorNo = OCILobTrim(fParentQuery->db->hDBSvc, fParentQuery->hErr, hBlob, 0);
	fParentQuery->CheckError();

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

	while ( (remainder > 0) && !feof(fileHandle))
	{
		nActual = nTry = (remainder > MAX_LOB_BUFFER_LENGTH) ? MAX_LOB_BUFFER_LENGTH : remainder;
		
		if (fread((void *)buf, (size_t)nTry, (size_t)1, fileHandle) != (size_t)1)
			throw TDBException(fParentQuery->fSqlStmt, ERR_MEM_BUFFER_IO, name, fileName, __LINE__);
		
		fParentQuery->fErrorNo = OCILobWrite(fParentQuery->db->hDBSvc, fParentQuery->hErr, 
			hBlob, &nActual, offset, (dvoid *) buf, (ub4) nTry, OCI_ONE_PIECE, (dvoid *)0,
			(sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,	(ub2) 0, (ub1) SQLCS_IMPLICIT);
		if ( fParentQuery->fErrorNo != OCI_SUCCESS) 
		{
			fclose(fileHandle);
			fParentQuery->CheckError();
		}

		flushedAmount += nTry;
		remainder -= nTry;
		offset += nTry;
		//incase the internal buffer is not big enough for the lob , flush the buffer content to db after some interval:
		if (flushedAmount >= LOB_FLUSH_BUFFER_SIZE)
		{
			flushedAmount = 0;
			fParentQuery->fErrorNo = OCILobFlushBuffer(fParentQuery->db->hDBSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);
			fParentQuery->CheckError();	
		}
	}

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

void  TDBField::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 TDBException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "LoadFromBuffer()", name);

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

	/* enable the BLOB locator for buffering operations */
	fParentQuery->fErrorNo = OCILobEnableBuffering(fParentQuery->db->hDBSvc, 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->db->hDBSvc, 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->db->hDBSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);
			fParentQuery->CheckError();	
		}
	}

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

double TDBField::AsFloat() 
{
	double iRet; 
	int status;
	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TDBException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "AsFloat()", name);

	if ( isNULL() )
		return 0;
	if ( (type == FLOAT_TYPE) || ( type == INT_TYPE) ){
		if ( (status=OCINumberToReal(fParentQuery->hErr, (OCINumber *)(fDataBuf + (size+1) * fParentQuery->fCurrRow ),sizeof(iRet), &iRet))!= OCI_SUCCESS)
		{
			fParentQuery->CheckError();
		}
		return iRet;
	}
	
	else	throw TDBException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "AsFloat()");
}

char* TDBField::AsDateTimeString() 
{
	int year, month, day, hour, minute, second;

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

	if (type != DATE_TYPE)
		throw TDBException(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,"%04d%02d%02d%02d%02d%02d", year, month, day, hour, minute,second);
		return (char *)fStrBuffer;
	}
}

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

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

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

//Modify by zhangyu 20050314
llong TDBField::AsInteger() 
{	
	llong iRet=0;
	int status;
	if (fParentQuery->fBof || fParentQuery->fEof)
		throw TDBException(fParentQuery->fSqlStmt, ERR_NO_DATASET, "AsInteger()", name);

	if (type != INT_TYPE && type != FLOAT_TYPE)
		throw TDBException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "AsInteger()");
	else {
		if ( (status=OCINumberToInt(fParentQuery->hErr, (OCINumber *)(fDataBuf + (size+1) * fParentQuery->fCurrRow ),sizeof(iRet), OCI_NUMBER_SIGNED,&iRet))!= OCI_SUCCESS)
		{
			fParentQuery->CheckError();
		}
		return iRet;
	}
}


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

	fFetched = 0;
	fPrefetchRows = 1;
	fCurrRow = 0;
	fTotalRowsFetched = 0;
	fBof = false;
	fEof = false;
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
	nTransTimes = 0;
	db = oradb;
	fActivated = false;
	fFieldCount = 0;
	fParamCount = 0;

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


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

}

TDBQuery::TDBQuery(TDBDatabase *oradb,TDBSession *session) 
{
	if (! session->m_bActive)
		throw TDBException("", ERR_GENERAL, "TDBQuery(TDBDatabase &db): Can not declare a TDBQuery when the database is not connected");

	fFetched = 0;
	fPrefetchRows = 1;
	fCurrRow = 0;
	fTotalRowsFetched = 0;
	fBof = false;
	fEof = false;
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
	nTransTimes = 0;
	db = oradb;
	fActivated = false;
	fFieldCount = 0;
	fParamCount = 0;

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

/*	hUser = session->m_hSession;
*/
	hErr = session->m_hError;
	/*hSvc = session->m_hSrvCtx;
	*/
	fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **)&hStmt, OCI_HTYPE_STMT, 0, 0);
	CheckError();
	
	//fErrorNo = OCIHandleAlloc(db->hEnv, (dvoid **)&hTrans, OCI_HTYPE_TRANS, 0, 0);
	//CheckError();	
}


TDBQuery::~TDBQuery()
{
	if (fSqlStmt != NULL)
		delete[] fSqlStmt;
	if (fParamCount >0)
		delete[] paramList;
	if (fFieldCount >0)
		delete[] fieldList;
	if (nTransTimes)
	{
		//fErrorNo = OCITransRollback(db->hDBSvc, hErr, OCI_DEFAULT);
		//CheckError();
	}
#ifdef __DEBUG__
    if(bExecuteFlag)
        userlog("TDBQuery执行了Execute()但是没有提交或回滚,可能造成锁表。"); 
#endif
/*
	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 TDBQuery::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 TDBQuery::Commit()
{
	bool exeSuccess = false;
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
	fErrorNo = OCITransCommit(db->hDBSvc, hErr, OCI_DEFAULT);
	CheckError();
	if (fErrorNo == OCI_SUCCESS)
		nTransTimes = 0;
	return (fErrorNo == OCI_SUCCESS);	
}

bool TDBQuery::Rollback()
{
	bool exeSuccess = false;
#ifdef __DEBUG__
    bExecuteFlag = false;
#endif
	fErrorNo = OCITransRollback(db->hDBSvc, hErr, OCI_DEFAULT);
	if (fErrorNo == OCI_SUCCESS)
	{
		nTransTimes = 0;
		exeSuccess = true;
	}
	else
		CheckError();
	return exeSuccess;
}

void TDBQuery::GetFieldsDef() 
{
	char temp[20];
	
	TDBField *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(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 TDBField[nColumnCount];
	fFieldCount = nColumnCount;
	
	for(counter=1; counter<=nColumnCount ; counter ++)
	{
		fErrorNo = OCIParamGet(hStmt, OCI_HTYPE_STMT, hErr, (dvoid **)&param, counter);
		CheckError();