ocicursor.cpp

在c++中将Oracle的OCI封装成类,使的调用变的非常简单,且不易出错!

#include "StdAfx.h"
#include "jy_oci.h"
#include <algorithm>

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


COciCursor::COciCursor(COciConnection* connect)
{
	m_fieldValue = "";
	m_conn = connect;
	m_stmthp = NULL;
	m_colhd = NULL;
	m_isOpen = false;
	m_lobp = NULL;	
}

COciCursor::~COciCursor(void)
{
	Close();
}

//打开 游标
bool COciCursor::Open()
{
	sword status;
	// 初始化语句句柄
	status = OCIHandleAlloc((dvoid*)m_conn->m_envhp, (dvoid**)&m_stmthp, 
		(ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid**)0);

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

//准备SQL语句 传入参数 C 风格字符串
void COciCursor::Prepare(const char *stmt)
{
	if (!m_isOpen)
		Open();
	//如果SQL语句不对，就抛出异常
	if (!COciException::checkErr(m_conn->m_errhp, OCIStmtPrepare(m_stmthp, m_conn->m_errhp, (text*)stmt, 
		(ub4)strlen(stmt), (ub4)OCI_NTV_SYNTAX,  
		(ub4)OCI_DEFAULT)))
		throw COciException();
}

//准备SQL语句 传入参数 c++风格字符串
void COciCursor::Prepare(std::string stmt)
{
	if (!m_isOpen)
		Open();
	//如果SQL语句不对，就抛出异常
	if (!COciException::checkErr(m_conn->m_errhp, OCIStmtPrepare(m_stmthp, m_conn->m_errhp, (text*)stmt.c_str(), 
		(ub4)stmt.length(), (ub4)OCI_NTV_SYNTAX,  
		(ub4)OCI_DEFAULT)))
		throw COciException();
}
//绑定输入变量 按索引绑定
void COciCursor::BindByPos(int pos, void* value, int size, void* indicator, int type)
{
	OCIBind *bindp = NULL;

	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIBindByPos(m_stmthp, &bindp, m_conn->m_errhp, pos, (dvoid *)value, 
		(sb4)size, type, (dvoid *)indicator, (ub2 *)0, 
		(ub2 *)0, (ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))
		throw COciException();
}

//绑定输入变量 按字段名绑定
void COciCursor::BindByName(char *name, void *value, int size, void *indicator, int type)
{
	OCIBind *bindp = NULL;

	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIBindByName(m_stmthp, &bindp, m_conn->m_errhp, (text*)name, 
		(sb4)-1, (dvoid *)value, (sb4)size, type, 
		(dvoid *)indicator, (ub2 *)0, (ub2 *)0, 
		(ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))
		throw COciException();
}

//执行SQL、获取结果集 缺省为执行一次
bool COciCursor::Execute(int times )
{
	sword status;
	status = OCIStmtExecute(m_conn->m_svchp, m_stmthp, m_conn->m_errhp, 
		(ub4)times, (ub4)0, (OCISnapshot *)NULL, 
		(OCISnapshot *)NULL, (ub4)OCI_DEFAULT);

	if (!COciException::checkErr(m_conn->m_errhp, status))
		throw COciException();

	return true;
}

//获取列数
bool COciCursor::GetColumnDesc()
{
	//times为0，刚好只描述表结构信息
	return Execute(0);
}

//获取结果
bool COciCursor::Fetch(int rows/* =1 */)
{
	sword status = 	OCIStmtFetch(m_stmthp, m_conn->m_errhp, (ub4) rows, (ub4) OCI_FETCH_NEXT,
		(ub4) OCI_DEFAULT);
	if (status == OCI_SUCCESS || status == OCI_SUCCESS_WITH_INFO)
		return true;		
	return false;
}

//查询SQL  传入参数 C 风格字符串
bool COciCursor::Query(const char *query)
{
	bool status=true;

	if (!m_isOpen)
		Open();

	Prepare(query);

	if (QueryType() == 1) //是查询类型,获描述类型
	{
		if(Execute(0))
		{
			if(!AllocateCursor())
				status = false;
		}
		else
		{
			status = false;
		}
	}
	else
	{	
		COciException::checkErr(m_conn->m_errhp, 
			OCITransStart (m_conn->m_svchp, m_conn->m_errhp, 60, OCI_TRANS_NEW));
		if(Execute())
		{
			COciException::checkErr(m_conn->m_errhp, 
				OCITransCommit(m_conn->m_svchp, m_conn->m_errhp, (ub4) 0));
		}
		else
		{
			COciException::checkErr(m_conn->m_errhp, 
				OCITransRollback(m_conn->m_svchp, m_conn->m_errhp, (ub4) 0));
			status = false;
		}
	}

	return status; 
}

//查询SQL  传入参数 C++ 风格字符串
bool COciCursor::Query(std::string query)
{
	bool status=true;

	if (!m_isOpen)
		Open();

	Prepare(query);

	if (QueryType() == 1) //是查询类型,获描述类型
	{
		if(Execute(0))
		{
			if(!AllocateCursor())
				status = false;
		}
		else
		{
			status = false;
		}
	}
	else
	{	
		COciException::checkErr(m_conn->m_errhp, 
			OCITransStart (m_conn->m_svchp, m_conn->m_errhp, 60, OCI_TRANS_NEW));
		if(Execute())
		{
			COciException::checkErr(m_conn->m_errhp, 
				OCITransCommit(m_conn->m_svchp, m_conn->m_errhp, (ub4) 0));
		}
		else
		{
			COciException::checkErr(m_conn->m_errhp, 
				OCITransRollback(m_conn->m_svchp, m_conn->m_errhp, (ub4) 0));
			status = false;
		}
	}

	return status; 
}

//得到SQL操作类型(SELECT、UPDATE、INSERT)
int	COciCursor::QueryType(void)
{
	ub2 querytype;

	if (!COciException::checkErr(m_conn->m_errhp, OCIAttrGet((dvoid *)m_stmthp, 
		(ub4)OCI_HTYPE_STMT, (ub2 *)&querytype,
		(ub4*)NULL, (ub4)OCI_ATTR_STMT_TYPE, (OCIError *) m_conn->m_errhp)))
		throw COciException();

	return (querytype);
}

//获取列数
int COciCursor::NumCols()
{
	int tmpNumcols = 0;

	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIAttrGet(m_stmthp, OCI_HTYPE_STMT, &tmpNumcols,
		0, OCI_ATTR_PARAM_COUNT, m_conn->m_errhp)))
		throw COciException();
	return (tmpNumcols);

}
//获取行数
int COciCursor::NumRows()
{
	int tmpNumrows = 0;

	//获取查询得到的记录行数
	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIAttrGet((dvoid*)m_stmthp, (ub4)OCI_HTYPE_STMT, (dvoid*)&tmpNumrows,
		(ub4*)0, (ub4)OCI_ATTR_ROW_COUNT, (OCIError*)m_conn->m_errhp)))
		throw COciException();
	return (tmpNumrows);
}
//获取列类型
ub2 COciCursor::ColType(int colnumber)
{
	ub2	dtype = 0;

	//获取第i列的参数
	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIParamGet((dvoid*)m_stmthp, (ub4)OCI_HTYPE_STMT, 
		(OCIError *)m_conn->m_errhp, (dvoid**)&m_colhd, (ub4)colnumber)))
		throw COciException();

	//获取实际的数据类型
	if (!COciException::checkErr(m_conn->m_errhp, OCIAttrGet((dvoid *)m_colhd, (ub4)OCI_DTYPE_PARAM,
		(dvoid *)&dtype, (ub4*)0, (ub4)OCI_ATTR_DATA_TYPE, (OCIError *)m_conn->m_errhp)))
		throw COciException();
	return (dtype);
}
//获取列名
char * COciCursor::ColName(int colnumber)
{
	text *tmpColname = NULL;
	ub4	tmpColnamesz;

	//得到第i列的参数
	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIParamGet((dvoid*)m_stmthp, (ub4)OCI_HTYPE_STMT, 
		(OCIError *)m_conn->m_errhp, (dvoid**)&m_colhd, (ub4)colnumber)))
	{
	}


	//获取第i列的具体属性
	if (!COciException::checkErr(m_conn->m_errhp, 
		OCIAttrGet((dvoid *)m_colhd, (ub4)OCI_DTYPE_PARAM,
		(dvoid **)&tmpColname, (ub4*)&tmpColnamesz, (ub4)OCI_ATTR_NAME, (OCIError *)m_conn->m_errhp )))
	{
	}

	char temp[100];
	unsigned int i;
	for(i=0;i<tmpColnamesz;i++)
		temp[i] = tmpColname[i];
	temp[tmpColnamesz] = '\0';

	m_fieldValue = temp;

	return ((char*)m_fieldValue.c_str()); 
}
//根据列明获取列索引
int COciCursor::GetColIdWithColName(std::string colName)
{
	int size=NumCols();
	std::transform(colName.begin(),colName.end(),colName.begin(),toupper);
	int col_id=-1;
	for (int i=0; i<size; i++)
	{
		if (ColName(i+1)==colName)
		{
			col_id=i;
		}
	}
	return col_id;
}
//获取列值的长度
ub2 COciCursor::ColSize (int colnumber)
{
	ub2  tmpColsize = (ub2) 0;

	if (!COciException::checkErr(m_conn->m_errhp,