tociquery.cpp

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

/********************************************************************
	Copyright by 97Dept. of Wholewise, 2001-07-01
	File Name: TOCIQuery.cpp
	Created Date: 2001/12/19:13:42
	Author:	CHEN GONG JIAN		Create Version: 0.0.01
	Create Version Date: 2001/12/19

	purpose:	
	----------------------------------------------------------------------
		Author:		HU GUO BIAO		Modified Version:		
		Modified Version Date:	2001/12/19:13:43
		Function Changed:
			cxx: Warning: TOCIQuery.cpp, line 579: statement is unreachable
									break;
			------------------------^
			cxx: Warning: TOCIQuery.cpp, line 971: use of "=" where "==" may have been inted
					else if (db->errorNo == OCI_SUCCESS)
			-----------------^
		
		Author:		Chengj		
		Modified Version Date: 2002/01/19
		purpose:	
			1. Date Support
			2. Blob Support
			3. Prefetch data to improve performance
			4. Memory leak eliminate(use Rational Purify testing tool)
		----------------------------------------
		Author:		Yuzj		
		Modified Version Date: 2002/03/27
		purpose:	
			Modify function name:TOCIQuery::GetParamsDef()

			error:
				该函数的算法存在问题,在提取SQL语句的参数时
			判断for循环结束的条件有可能存在内存越界的问题,
			在一定条件下会造成内存越界.

			任务单号:DX20020327003
		4)  Modified By Name of Programmer	Yeyh
		Date YYYY-MM-DD			2002-06-27
		Current Version: 0.0.0.5	Task nbr: IBSS20020518002
		Function Changed:
			SetParameter(char *paramName, long paramValue, bool isOutput)
			修改long类型参数对应的oracle数据类型
		Other changes:
		
		5)  Modified By Name of Programmer	黄锦添 
		Date YYYY-MM-DD			2002-07-09
		Current Version: 0.0.0.6	Task nbr: IBSS20020709015
		Function Changed: 
			在TOCIQuery类中增加成员bExecuteFlag
			TOCIQuery::TOCIQuery()		将bExecuteFlag置为false
			TOCIQuery::Execute()		将bExecuteFlag置为true
			TOCIQuery::Commit() 		将bExecuteFlag置为false
			TOCIQuery::Rollback()		将bExecuteFlag置为false
			TOCIQuery::~TOCIQuery()		判断bExecuteFlag,若为true则向log中记录可能锁表 
		Other changes:
			包含文件userlog.h 
		.............
		6）Modified By Name of Programmer	YUZJ		Date YYYY-MM-DD			2002-07-09
		Current Version: 0.0.0.7	Task nbr: IBSS20020718003
		Function Changed: 
			GetFieldsDef函数中有一处size赋值有误
		Other changes:
			
*********************************************************************/
#include <TOCIQuery.h>
#include <stdio.h>
#include <oci.h>
#include <string.h>
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ocidem.h>
#include <ociapr.h>

//Added begin at ver : 0.0.0.6
#define __DEBUG__

#ifdef __DEBUG__
#include <userlog.h>
#endif
//Added end at ver : 0.0.0.6

TOCIException::TOCIException(sword errNumb, OCIError *hErr, char *cat, char *sql)
{
	int nLen;
	sb4 errcode;

	nLen = strlen(cat);
	nLen = (nLen >= MAX_ERR_CAT_LENGTH)? MAX_ERR_CAT_LENGTH : nLen;
	strncpy(errCategory,cat,nLen);
	errCategory[nLen] = '\0';
	
	nLen = strlen(sql);
	nLen = nLen >= MAX_SQLSTMT_LENGTH ? MAX_SQLSTMT_LENGTH : nLen;
	strncpy(errSQL,sql,nLen);
	errSQL[nLen] = '\0';
	
	errNo = errNumb;

	(void)OCIErrorGet ((dvoid *) hErr, (ub4) 1, (text *) NULL, &errcode,
			errMessage, (ub4)sizeof(errMessage)-1, (ub4) OCI_HTYPE_ERROR);
}

TOCIException::TOCIException(const char *sql, const char* errFormat, ...)
{
	int nLen;
	nLen = strlen(sql);
	nLen = (nLen >= MAX_SQLSTMT_LENGTH) ? MAX_SQLSTMT_LENGTH :nLen;
	strncpy(errSQL,sql,nLen);
	errSQL[nLen] = '\0';

	va_list ap;
	va_start(ap, errFormat);
	vsprintf((char *)errMessage, errFormat, ap);	
	va_end(ap);
}

TOCIException::~TOCIException()
{
}

char *TOCIException::GetErrSrc() const
{
	return( (char *)errSQL );
}

char *TOCIException::GetErrMsg() const
{	
	return( (char *)errMessage);
}

//比较2个字符串是否相同(不考虑大小写)
bool inline CompareStrNoCase(char *ori, char *des)
{
	int j,nLen1,nLen2;
	bool sameChar;
	nLen1 = strlen(ori);
	nLen2 = strlen(des);
	if (nLen1!=nLen2) return false;
	sameChar = true;
	for (j=0; j<nLen1; j++)
		sameChar = sameChar && ( toupper(ori[j]) == toupper(des[j]) );
	return sameChar;
}

/********* TConnection implementation *********/
TOCIDatabase::TOCIDatabase()
{
	sword errorNo;
	errorNo = OCIInitialize((ub4) OCI_DEFAULT,0, 0,0,0 );
	errorNo = errorNo + OCIEnvInit( (OCIEnv **) &hEnv, (ub4) OCI_DEFAULT,(size_t) 0, (dvoid **) 0 );
	//errorNo = errorNo + OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hDBSvc,(ub4) OCI_HTYPE_SVCCTX,(size_t) 0, (dvoid **) 0);
	errorNo = errorNo + OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hDBErr,(ub4) OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0);	
	errorNo = errorNo + OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hSvr,(ub4) OCI_HTYPE_SERVER,(size_t) 0, (dvoid **) 0);
		
	if ( errorNo != 0 )
		throw TOCIException( "TOCIDatabase()", ERR_DB_INIT, __LINE__);
	fConnected = false;
	usr = NULL;
	pwd = NULL;
	tns = NULL;
}

TOCIDatabase::~TOCIDatabase()
{
	delete[] usr;
	delete[] tns;
	delete[] pwd;
	
	if (fConnected) 
		OCIServerDetach(hSvr, hDBErr, OCI_DEFAULT );
	
	OCIHandleFree(hSvr, OCI_HTYPE_SERVER);		
	//OCIHandleFree(hDBSvc, OCI_HTYPE_SVCCTX);
	OCIHandleFree(hDBErr,OCI_HTYPE_ERROR);
	OCIHandleFree(hEnv,OCI_HTYPE_ENV);
}

void TOCIDatabase::SetLogin(char *user, char *password, char *tnsString)
{
	if (fConnected)
		throw TOCIException("SetLogin()", ERR_SET_LOGIN , __LINE__);
	
	int nLen;
	//保存外部传递的参数
	if ( usr != NULL) 
		delete[] usr;
	if (pwd != NULL)
		delete[] pwd;
	if (tns != NULL)
		delete[] tns;

	//保存外部传递的参数
	if (user)
	{
		nLen = strlen(user);
		usr = new char[nLen+1];
		strncpy(usr,user,nLen);
		usr[nLen] = '\0';
	}
	else
	{
		nLen = 0;
		usr = new char[1];
		usr[0] = '\0';
	}

	if (password)
	{
		nLen = strlen(password);
		pwd = new char[nLen+1];
		strncpy(pwd,password,nLen);
		pwd[nLen] = '\0';
	}
	else
	{
		nLen = 0;
		pwd = new char[1];
		pwd[0] = '\0';
	}
	
	if (tnsString)
	{
		nLen = strlen(tnsString);
		tns = new char[nLen+1];
		strncpy(tns,tnsString,nLen);
		tns[nLen] = '\0';
	}
	else	
	{
		nLen = 0;
		tns = new char[1];
		tns[0] = '\0';
	}
}

bool TOCIDatabase::Connect()
{
	sword errorNo;

	if (fConnected)
		return true;
	
	if ( (usr == NULL) || (tns==NULL) )
		throw TOCIException("Connect()", ERR_CONNECT_NO_LOGIN_INFO, __LINE__);
	
	errorNo = OCIServerAttach(hSvr, hDBErr, (text *)tns, strlen(tns), 0);
	if (errorNo != OCI_SUCCESS)
		throw TOCIException(errorNo, hDBErr, "Connect()", "try to connect Server");
	
	return (fConnected = (errorNo == OCI_SUCCESS));
}

bool TOCIDatabase::Connect(char *inUsr, char *inPwd, char *inTns)
{
	SetLogin(inUsr, inPwd, inTns);
	return Connect();
}

void TOCIDatabase::Disconnect()
{
	if (!fConnected) return;
	//OCISessionEnd (hDBSvc, hDBErr, hUser, OCI_DEFAULT);
	dword errorNo = OCIServerDetach(hSvr, hDBErr, OCI_DEFAULT );
	if (errorNo != OCI_SUCCESS)
			throw TOCIException(errorNo, hDBErr,"Disconnect()", "OCIServerDetatch error");
	fConnected = false;
}

/*********** Field Implementation*************/
TOCIField::TOCIField()
{
	//初始化列信息,有部分的初始化信息在Describe中进行
	name = NULL;
	hBlob = NULL;
	hDefine = (OCIDefine *) 0; ;
	fDataBuf = NULL;
	fDataIndicator = NULL;
	fParentQuery = NULL;

	fReturnDataLen = 0;
	size = 0;
	precision = 0;
	scale = 0;
	size = 0;
};

TOCIField::~TOCIField()
{
	if (fDataIndicator != NULL)
		delete[] fDataIndicator;
	if (name != NULL)
		delete[] name; 
	if (fDataBuf != NULL)
		delete[] fDataBuf;
	if (type == SQLT_BLOB)
		OCIDescriptorFree((dvoid *)hBlob, (ub4) OCI_DTYPE_LOB);
}

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

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

	if ( isNULL() )
	{
		sprintf((char *)fStrBuffer, NULL_STRING);
		return (char *)fStrBuffer;
	}

	switch ( this->type )
	{
	case DATE_TYPE:
		this->asDateTimeInternal(year, month, day, hour, minute, second);
		sprintf((char *)fStrBuffer,"%d-%d-%d", year, month, day);
		return (char *)fStrBuffer;
	case INT_TYPE:
	case FLOAT_TYPE:
		return((char *)(fDataBuf + (size+1) * fParentQuery->fCurrRow));
	case STRING_TYPE:
	case ROWID_TYPE:
		return((char *)(fDataBuf + (size+1) * fParentQuery->fCurrRow));
	case SQLT_BLOB:
		sprintf((char *)fStrBuffer, "BLOB...");
		return (char *)fStrBuffer;
	default:
		throw TOCIException(fParentQuery->fSqlStmt, ERR_DATA_TYPE_CONVERT, name, type, "asString()");
	}
}

bool TOCIField::isNULL()
{
	return (fDataIndicator[fParentQuery->fCurrRow]==-1);
}

void  TOCIField::asBlobFile(const char *fileName)
{
	ub4 offset = 1;
	ub1 buf[MAX_LOB_BUFFER_LENGTH];
	ub4 nActual = 0;	//实际读取数
	ub4 nTry = 0;		//试图读取数
	ub4 totalSize = 0;
	FILE *fileHandle;
	ub4 lobLength;

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

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

	fileHandle = fopen( fileName, (const char *) "wb");
	fseek(fileHandle, 0, 0);
	/* set amount to be read per iteration */
	nTry = nActual = MAX_LOB_BUFFER_LENGTH;
	OCILobGetLength(fParentQuery->hSvc, fParentQuery->hErr, hBlob, &lobLength);

	while (nActual)
	{
		fParentQuery->fErrorNo = OCILobRead(fParentQuery->hSvc, fParentQuery->hErr, 
			hBlob, &nActual, (ub4)offset, (dvoid *) buf, (ub4) nTry, (dvoid *)0, 
			(sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT);
		fParentQuery->CheckError();	  
		
		if (nActual<=0) break;
		
		totalSize += nActual;
		fwrite((void *)buf, (size_t)nActual, (size_t)1, fileHandle);
		offset += nActual;
	}
	fclose(fileHandle);
}

void  TOCIField::asBlobBuffer(unsigned char* &buf, unsigned int *lobLength)
{
	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, "asBlobBuffer()", name);

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

	OCILobGetLength(fParentQuery->hSvc, fParentQuery->hErr, hBlob, &remainder);
	*lobLength = nActual = nTry = remainder;

	try
	{
		buf = new unsigned char[sizeof(ub1) * remainder];
	}
	catch (...)
	{
		throw TOCIException(fParentQuery->fSqlStmt, ERR_NOMORE_MEMORY_BE_ALLOCATED, "asBlobBuffer()", __LINE__);
	}

	nTry = nActual = MAX_LOB_BUFFER_LENGTH;
	while (remainder)
	{		
		fParentQuery->fErrorNo = OCILobRead(fParentQuery->hSvc, fParentQuery->hErr, 
			hBlob, &nActual, (ub4)offset, (dvoid *)innerBuf, (ub4) nTry, (dvoid *)0, 
			(sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT);
		fParentQuery->CheckError();	  
		memcpy( (buf) + offset -1, innerBuf, nActual);
		if (nActual<=0) break;
		
		offset += nActual;
		remainder -= nActual;
	}
}

void  TOCIField::LoadFromFile(const char *fileName)
{
	ub4 remainder, nActual, nTry, offset = 1;//从文件中读取的剩余数据量
	ub1 buf[MAX_LOB_BUFFER_LENGTH];
	ub4 LobLength;
	ub4  flushedAmount = 0;
	FILE *fileHandle ;

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

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

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

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

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

	/* enable the BLOB locator for buffering operations */
	fParentQuery->fErrorNo = OCILobEnableBuffering(fParentQuery->hSvc, 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 TOCIException(fParentQuery->fSqlStmt, ERR_MEM_BUFFER_IO, name, fileName, __LINE__);
		
		fParentQuery->fErrorNo = OCILobWrite(fParentQuery->hSvc, 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->hSvc, fParentQuery->hErr, hBlob, OCI_LOB_BUFFER_NOFREE);