hycursor.cpp

《Oracle Spatial与OCI高级编程》相关配套源码。介绍了Oracle数据库中的两项关键开发技术Oracle Spatial以及OCI(Oracle Call Interface)。

/*
* Copyright (c) OCI高级编程
* @File name: hycursor.cpp
* @Author   : He Xiong
* @Content  : HYCursor类的实现
* @Date		: 2003-10
****/


#include "hyafx.h"
#include <string>
#include <iterator>
#include <assert.h>
using namespace std;

char* strClean(char *name)
{
    int j = 0;
	
	assert(name!=NULL);

	for (j = strlen(name)-1; j>=0; j--)
		if ( !((name[j]==' ') || (name[j]=='\t') || (name[j]== '\0')) ) break;
	
		name[j+1] = '\0';

	return name;
}

HYCursor::HYCursor(HYConnection* connect)
{
	fieldValue = " ";
	conn = connect;
	stmthp = NULL;
	colhd = NULL;
	isOpen_ = false;
	lobp = NULL;	
}

bool HYCursor::open()
{
	sword status;
	// 初始化语句句柄
	status = OCIHandleAlloc((dvoid*)conn->envhp, (dvoid**)&stmthp, 
		(ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid**)0);

	if (status != OCI_SUCCESS)
	{
		isOpen_ = false;
		return false;
	}
	else 
	{
		isOpen_ = true ;
		return true;
	}
}

void HYCursor::close()
{
	if(isOpen_)
	{
		OCIHandleFree((dvoid *)stmthp, (ub4)OCI_HTYPE_STMT);
		freeResult();
		isOpen_ = false;
	}
}

void HYCursor::freeResult()
{
	if (lobp)
	{
		OCIDescriptorFree((dvoid *) lobp, (ub4) OCI_DTYPE_LOB);
		lobp = NULL;
	}	

	
	vector<void *>::iterator itb = buffers.begin();
	for (itb; itb!=buffers.end(); itb++)
		delete(*(itb));
	
	buffers.clear();
	ind.clear();
	col_type.clear();
	col_size.clear();
	col_scale.clear();
}

void HYCursor::prepare(const char* stmt)
{
	if (!isOpen_)
		open();
	//如果SQL语句不对，就抛出异常
	if (!HYException::checkErr(conn->errhp, OCIStmtPrepare(stmthp, conn->errhp, (text*)stmt, 
		(ub4)strlen(stmt), (ub4)OCI_NTV_SYNTAX, 
		(ub4)OCI_DEFAULT)))
		throw HYException();
}

void HYCursor::bindByPos(int pos, void* value, int size, void* indicator, int type)
{
	OCIBind *bindp = NULL;

	if (!HYException::checkErr(conn->errhp, 
		OCIBindByPos(stmthp, &bindp, conn->errhp, pos, (dvoid *)value, 
								(sb4)size, type, (dvoid *)indicator, (ub2 *)0, 
								(ub2 *)0, (ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))
		throw HYException();

}

void HYCursor::bindByName(char* name, void* value, int size, void* indicator, int type)
{
	OCIBind *bindp = NULL;

	if (!HYException::checkErr(conn->errhp, 
		OCIBindByName(stmthp, &bindp, conn->errhp, (text*)name, 
			(sb4)-1, (dvoid *)value, (sb4)size, type, 
			(dvoid *)indicator, (ub2 *)0, (ub2 *)0, 
			(ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))
		throw HYException();
}

void HYCursor::defineByPos(int pos, void* value, int size, void* indicator, int type)
{
	OCIDefine *defnp = NULL;

	if (!HYException::checkErr(conn->errhp,
		OCIDefineByPos(stmthp, &defnp, conn->errhp, (ub4)pos, 
			 (dvoid *)value, (sb4)size, type, 
			 (dvoid *)indicator, (ub2 *)0, (ub2 *)0,
			 (ub4)OCI_DEFAULT)))
		throw HYException();
}

bool HYCursor::execute(int times)
{
	sword status;
	status = OCIStmtExecute(conn->svchp, stmthp, conn->errhp, 
								  (ub4)times, (ub4)0, (OCISnapshot *)NULL, 
								  (OCISnapshot *)NULL, (ub4)OCI_DEFAULT);

	if (!HYException::checkErr(conn->errhp, status))
		throw HYException();

	return true;
}

bool HYCursor::getColumnDesc()
{
	//times为0，刚好只描述表结构信息
	return execute(0);
}

bool HYCursor::fetch(int rows)
{
	sword status = 	OCIStmtFetch(stmthp, conn->errhp, (ub4) rows, (ub4) OCI_FETCH_NEXT,
           (ub4) OCI_DEFAULT);
	if (status == OCI_SUCCESS || status == OCI_SUCCESS_WITH_INFO)
		return true;		
	return false;
}

bool HYCursor::query(const char* query)
{
	bool status=true;

	if (!isOpen_)
		open();
		
	prepare(query);
		
	if (queryType() == 1) //是查询类型,获描述类型
	{
		if(execute(0))
		{
			if(!allocateCursor())
				status = false;
		}
		else
			status = false;
	}
	else
	{	
		HYException::checkErr(conn->errhp, 
			OCITransStart (conn->svchp, conn->errhp, 60, OCI_TRANS_NEW));
		if(execute(1))
			HYException::checkErr(conn->errhp, 
				OCITransCommit(conn->svchp, conn->errhp, (ub4) 0));
		else
		{
			HYException::checkErr(conn->errhp, 
				OCITransRollback(conn->svchp, conn->errhp, (ub4) 0));
			status = false;
		}
	}

	return status; 
}

int HYCursor::numCols()
{
		int numcols_ = 0;

		if (!HYException::checkErr(conn->errhp, 
			OCIAttrGet(stmthp, OCI_HTYPE_STMT, &numcols_,
				0, OCI_ATTR_PARAM_COUNT, conn->errhp)))
			throw HYException();
		return (numcols_);
}

int HYCursor::numRows()
{
		int numrows_ = 0;
		
		//获取查询得到的记录行数
		if (!HYException::checkErr(conn->errhp, 
			OCIAttrGet((dvoid*)stmthp, (ub4)OCI_HTYPE_STMT, (dvoid*)&numrows_,
			(ub4*)0, (ub4)OCI_ATTR_ROW_COUNT, (OCIError*)conn->errhp)))
			throw HYException();
		return (numrows_);
}

ub2 HYCursor::colType(int colnumber)
{
	ub2	dtype = 0;

	//获取第i列的参数
	if (!HYException::checkErr(conn->errhp, 
		OCIParamGet((dvoid*)stmthp, (ub4)OCI_HTYPE_STMT, 
		(OCIError *)conn->errhp, (dvoid**)&colhd, (ub4)colnumber)))
		throw HYException();

	//获取实际的数据类型
	if (!HYException::checkErr(conn->errhp, OCIAttrGet((dvoid *)colhd, (ub4)OCI_DTYPE_PARAM,
	(dvoid *)&dtype, (ub4*)0, (ub4)OCI_ATTR_DATA_TYPE, (OCIError *)conn->errhp)))
		throw HYException();
	return (dtype);
}

char* HYCursor::colName(int colnumber)
{
	text *colname_ = NULL;
	ub4	colnamesz_;
	
	//得到第i列的参数
	if (!HYException::checkErr(conn->errhp, 
		OCIParamGet((dvoid*)stmthp, (ub4)OCI_HTYPE_STMT, 
		(OCIError *)conn->errhp, (dvoid**)&colhd, (ub4)colnumber)))
		throw HYException();
	
	
	//获取第i列的具体属性
	if (!HYException::checkErr(conn->errhp, 
		OCIAttrGet((dvoid *)colhd, (ub4)OCI_DTYPE_PARAM,
		(dvoid **)&colname_, (ub4*)&colnamesz_, (ub4)OCI_ATTR_NAME, (OCIError *)conn->errhp )))
		throw HYException();
	
	char temp[100];
	int i;
	for(i=0;i<colnamesz_;i++)
		temp[i] = colname_[i];
	temp[colnamesz_] = '\0';
	
	fieldValue = temp;
				
	return ((char*)fieldValue.c_str()); 
}

ub2	HYCursor::colSize (int colnumber)
{
	ub2  colsize_ = (ub2) 0;
				
	if (!HYException::checkErr(conn->errhp, 
		OCIParamGet((dvoid*)stmthp, (ub4)OCI_PTYPE_COL, 
		(OCIError *)conn->errhp, (dvoid**)&colhd, (ub4)colnumber)))
		
	{}
	
	if (!HYException::checkErr(conn->errhp, OCIAttrGet((dvoid *)colhd, (ub4)OCI_DTYPE_PARAM,
		(dvoid *)&colsize_, (ub4*)0, (ub4)OCI_ATTR_DATA_SIZE, (OCIError *)conn->errhp )))
	
	{}
	
	return (colsize_);

}

sb1	HYCursor::colScale (int colnumber)
{
	sb1  colscale_ = (sb1) 0;
				
	//得到第i列的参数
	if (!HYException::checkErr(conn->errhp, 
		OCIParamGet((dvoid*)stmthp, (ub4)OCI_PTYPE_COL, 
		(OCIError *)conn->errhp, (dvoid**)&colhd, (ub4)colnumber)))
	{}
	
	//获取第i列的具体属性
	if (!HYException::checkErr(conn->errhp, 
		OCIAttrGet((dvoid *)colhd, (ub4)OCI_DTYPE_PARAM,
		(dvoid *)&colscale_, (ub4*)0, (ub4)OCI_ATTR_SCALE, (OCIError *)conn->errhp )))
	{}
	
	return (colscale_);
}

int	HYCursor::queryType(void)
{
	ub2 querytype_;
	
	if (!HYException::checkErr(conn->errhp, OCIAttrGet((dvoid *)stmthp, 
		(ub4)OCI_HTYPE_STMT, (ub2 *)&querytype_,
		(ub4*)NULL, (ub4)OCI_ATTR_STMT_TYPE, (OCIError *) conn->errhp)))
		throw HYException();
	
	return (querytype_);
}

bool HYCursor::allocateCursor(void)
{
	int			coltype=0;
	int			colsize=0;
	int			colscale=0;
		

	int numcols = numCols();
	
	defines.resize(numcols);
	buffers.resize(numcols);
	col_type.resize(numcols);
	col_size.resize(numcols);
	col_scale.resize(numcols);
	ind.resize(numcols);
	
	for(int i=1; i<=numcols;i++)
	{
		coltype = colType(i);
		colsize = colSize(i);
		colscale = colScale(i);
		
		col_type[i-1] = coltype;
		col_size[i-1] = colsize;
		col_scale[i-1] = colscale;
		
		switch (coltype)
		{
		case 3: //INTEGER
			
			buffers[i-1] = new signed int;
			colsize = sizeof(signed int);
			
			if (!HYException::checkErr(conn->errhp, 
				OCIDefineByPos(stmthp, &(defines[i-1]), conn->errhp, (ub4)i, 
				(dvoid *)(signed int*)buffers[i-1], (sb4)colsize, coltype, 
				(dvoid *)&ind[i-1], (ub2 *)0, (ub2 *)0,
				(ub4)OCI_DEFAULT)))
				throw HYException();
			
			break;
			
			
		case 2: //NUMBER
			
			buffers[i-1] = (OCINumber *) new OCINumber; //();								
			if (!HYException::checkErr(conn->errhp, 
				OCIDefineByPos(stmthp, &(defines[i-1]), conn->errhp, (ub4)i, 
				(dvoid *)buffers[i-1], sizeof(OCINumber), SQLT_VNU, 
				(dvoid *)&ind[i-1], (ub2 *)0, (ub2 *)0,
				(ub4)OCI_DEFAULT)))
				throw HYException();
			
			break;
			
			
			
		case 4: //FLOAT DOUBLE
			
			buffers[i-1] = new double;
			colsize = sizeof(double);
			coltype = 4;
			
			if (!HYException::checkErr(conn->errhp, 
				OCIDefineByPos(stmthp, &(defines[i-1]), conn->errhp, (ub4)i, 
				(dvoid *)(double*)buffers[i-1], (sb4)colsize, coltype, 
				(dvoid *)&ind[i-1], (ub2 *)0, (ub2 *)0,
				(ub4)OCI_DEFAULT)))
				throw HYException();
			
			
			break;
			
		case 96: //CHAR
		case 9: //VARCHAR:
		case 1: //VARCHAR2:	
			buffers[i-1] = (char *) new char[colsize+1];
			memset(buffers[i-1],0,(colsize+1)); //preenche vetor com zeros
			
			if (!HYException::checkErr(conn->errhp, 
				OCIDefineByPos(stmthp, &(defines[i-1]), conn->errhp, (ub4)i, 
				(dvoid *)buffers[i-1], (sb4)(colsize+1), SQLT_STR, 
				(dvoid *)&ind[i-1], (ub2 *)0, (ub2 *)0,
				(ub4)OCI_DEFAULT)))
				throw HYException();
			
			break;
			
		case 113 :	//SQLT_BLOB
			
			if (!HYException::checkErr(conn->errhp, 
				OCIDescriptorAlloc((dvoid *) conn->envhp, (dvoid **) &lobp,
				(ub4) OCI_DTYPE_LOB,
				(size_t) 0, (dvoid **) 0)))
				throw HYException();
			
			if (!HYException::checkErr(conn->errhp, 
				OCIDefineByPos (stmthp, &(defines[i-1]), conn->errhp, (ub4)i,
				(dvoid *)&lobp, 0 , SQLT_BLOB,
				(dvoid *)&ind[i-1], (ub2 *)0, (ub2 *)0, OCI_DEFAULT)))
				throw HYException();
			break;
			
		default:
			break;
			
		} //switch
		
	} //for


	return true;
}

char*	HYCursor::getFieldValue(int i)
{
	int				tempInt;
	double			tempDouble;
	char			str[30];
	
	if(i < 1)
		return (char*)0;

	int	numcols = numCols();
	int coltype = col_type[i-1]; //ColType(i);
	int colsize = col_size[i-1]; //ColSize(i);
	int colscale = col_scale[i-1]; //ColScale(i);

	int indica = ind[i-1];

	switch (coltype)
		{
			case 3: //INTEGER

				if(indica == -1)
					return "0";

				tempDouble = *((double*)buffers[i-1]);
				tempInt = (const int)tempDouble;
				sprintf(str,"%d",tempInt);
				fieldValue = str;
				fieldValue = strClean((char*)fieldValue.c_str());
				
				return ((char*)fieldValue.c_str());
				break;

			case 2: //NUMBER
				
				if(indica == -1)
					return "0";
				
				if (!HYException::checkErr(conn->errhp, 
					OCINumberToReal(conn->errhp, ((OCINumber *)buffers[i-1]), 
					(uword)sizeof(double), (dvoid *)&tempDouble)))
					throw HYException();
				
				if(colscale > 0)
					sprintf(str,"%lf",tempDouble);
				else
					sprintf(str,"%d",(int)tempDouble);
			
				fieldValue = str; 
				fieldValue = strClean((char*)fieldValue.c_str());
				
				return ((char*)fieldValue.c_str());
				break;
				

			case 4: //FLOAT DOUBLE
					
				if(indica == -1)
					return "0";
				
				tempDouble = *((const double*)buffers[i-1]);
				sprintf(str,"%lf",tempDouble);
				fieldValue = str; 
				fieldValue = strClean((char*)fieldValue.c_str());
				
				return ((char*)fieldValue.c_str());
				break;

			case 96: //CHAR
			case 9: //VARCHAR:
			case 1: //VARCHAR2:
				
				if(indica == -1)
					return "";

				tempInt = strlen((char*)buffers[i-1]);
				fieldValue = ((char*)buffers[i-1]);
				fieldValue = strClean((char*)fieldValue.c_str());

				return ((char*)fieldValue.c_str());
				
				break;

			case 108: //OBJECT SDO_GEOMETRY
			default:
				break;
								
		} //switch
		
	return (char*)0;
}

bool HYCursor::writeBlob(const unsigned char* buffer, unsigned int bufsize)
{
	int		offset = 1;
	
	if (!HYException::checkErr(conn->errhp, 
		OCILobWrite(conn->svchp, conn->errhp, lobp, &bufsize, offset,
        (dvoid *) buffer, (ub4) bufsize, OCI_ONE_PIECE,
        (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
		(ub2) 0, (ub1) SQLCS_IMPLICIT)))
		throw HYException();
	
	return true;
}

int	HYCursor::sizeBlob()
{
	ub4 lenp=0;
	if (!HYException::checkErr(conn->errhp, 
		OCILobGetLength(conn->svchp, conn->errhp, lobp, &lenp)))
		throw HYException();
	return lenp;
}

bool HYCursor::readBlob(unsigned char* buffer, unsigned int bufsize)
{
	int		offset = 1;
		
	if (!HYException::checkErr(conn->errhp, 
		OCILobRead(conn->svchp, conn->errhp, lobp, &bufsize, offset,
               (dvoid *) buffer, (ub4)bufsize , (dvoid *) 0, 0, (ub2) 0, 
			   (ub1) SQLCS_IMPLICIT)))
		throw HYException();
    
	return true;
}