tociquery.cpp

调用OCI的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:

		7)  Modified By Name of Programmer	Unknow
		Current Version: 0.0.0.8	

		8)  Modified By Name of Programmer	RuanYJ
		Date YYYY-MM-DD			2004-03-18
		Current Version: 0.0.0.9	
		Function Changed: 
			新增 SetLongRawParameter (char *, char *, bool);
		9)  Modified By Name of Programmer 	Linjx
		Date YYYY-MM-DD			2005-06-07
		Current Version: 0.0.0.10	
		Function Changed: 
			新增	SetParameter(char *, void*, int iLen, bool);　对blob 数据单条入库
			新增	SetParamArray	(char *, void ** ,int ,int ,  bool);　对blob 数据多条入库	
			修改	asBlobBuffer	释放申请的空间	
			修改	在select blob 型数据时　fPrefetchRows=1;　BLOB 目前只能以一条一条的方式fetch　
					以后应该可以批量fetch
*********************************************************************/
#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>
#include "TOCIQuery.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 const *cat, 
char const *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);

	m_iErrCode = errcode;
}

int TOCIException::getErrCode() const
{
	return m_iErrCode;
}

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

	m_iErrCode = 0;
}

TOCIException::~TOCIException()
{
}

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

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

//比较2个字符串是否相同(不考虑大小写)
bool inline compareStrNoCase(const char *ori, const 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|OCI_OBJECT,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';
	}
}

void TOCIDatabase::checkError()
{
	if (fErrorNo != OCI_SUCCESS) 
		throw TOCIException(fErrorNo, hDBErr, "Oracle OCI Call", "OCIDatabase");
	
}


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");
	
	//modified: 2003.1
	fErrorNo = OCIHandleAlloc(hEnv, (dvoid **) &hUser,(ub4) OCI_HTYPE_SESSION,(size_t) 0, (dvoid **) 0);
	checkError();

	fErrorNo = OCIHandleAlloc(hEnv, (dvoid **)&hDBSvc, OCI_HTYPE_SVCCTX,0, 0);
	checkError();

	fErrorNo = OCIAttrSet (hDBSvc, OCI_HTYPE_SVCCTX, hSvr, 0, OCI_ATTR_SERVER, hDBErr);
	checkError();

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

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

	OCISessionBegin (hDBSvc, hDBErr, hUser, OCI_CRED_RDBMS, OCI_DEFAULT);

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

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

void TOCIDatabase::commit()
{
    OCITransCommit(hDBSvc, hDBErr, OCI_DEFAULT);
}

void TOCIDatabase::rollback()
{
	OCITransRollback(hDBSvc, hDBErr, OCI_DEFAULT);
}

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;
	iBlobBufCnt=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;
	static char intStr[100];
	int status;

	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,"%04d%02d%02d%02d%02d%02d", year, month, day, hour, minute, second);
		return (char *)fStrBuffer;
	case INT_TYPE:
		long intValue;
		if ( (status=OCINumberToInt(fParentQuery->hErr, (OCINumber *)(fDataBuf + (size+1) * fParentQuery->fCurrRow ),sizeof(intValue), OCI_NUMBER_SIGNED,&intValue))!= OCI_SUCCESS)
		{
			fParentQuery->checkError();
		}
		
		sprintf(intStr, "%ld", intValue);
		return intStr;
	case FLOAT_TYPE:
		//int status;
		double floatValue;
		if ( (status=OCINumberToReal(fParentQuery->hErr, (OCINumber *)(fDataBuf + (size+1) * fParentQuery->fCurrRow ),sizeof(floatValue), &floatValue))!= OCI_SUCCESS)
		{
			fParentQuery->checkError();
		}

		sprintf(intStr, "%f", floatValue);
		return intStr;		
		//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->db->hDBSvc, fParentQuery->hErr, hBlob, &lobLength);

	while (nActual)
	{
		fParentQuery->fErrorNo = OCILobRead(fParentQuery->db->hDBSvc, 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)