connection.cpp

在动态库中实现异步导出大数据量的oracle数据

#include "stdafx.h"

#define __OCICPP_INTERNAL_USE_
#include "Connection.h"

/*! \class OCICPP::Connection
  \brief Encapsulate a connection to a database instance

  By creating a connection to a database, a programm can interact with a
  database instance by the access right associated to the connection.

  A connection can be initialized by db::connect()
 */

/*!
  Create a connection to a database instance describe by \a tnsname . For 
  authertification \a user and \a password are being used. As optional
  parameter, an Oracle-Environment \a env may be set for this connection.

  \sa db::connect()
 */
OCICPP::Connection::Connection(OCIEnv *env,const string &tnsname,const string &user,const string &password) {
	init(env,tnsname,user,password);
}

/*!
  Default constructor. The connection is not connected to any database
  instance.

  \sa db::connect()
 */
OCICPP::Connection::Connection():envhp(0),srvhp(0),errhp(0),svchp(0),authp(0) 
{ }

/*!
  Initialized a connection to the database specified by \a tnsnames for user
  \a user identified by password \a password . An optional Oracle environment
  \a env may be passed to the oracle server.
 */
void OCICPP::Connection::init(OCIEnv *env,const string &tnsname,const string &user,const string &password) {
	envhp=env; srvhp=0; errhp=0; svchp=0; authp=0;
/*    (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0);
    (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL,(size_t) 0, (dvoid **) 0);
		(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0);
		(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0);

    OCIConnectionPoolCreate(envhp,errhp,poolhp,&poolName,&poolNameLen,(const unsigned char*)tnsname.c_str(),tnsname.length(),
		1,200,1,(const unsigned char*)user.c_str(),user.length(),
		(const unsigned char*)password.c_str(),password.length(),OCI_DEFAULT);
		
		OCIHandleAlloc((dvoid *) envhp,(dvoid **)&authp, (ub4) OCI_HTYPE_AUTHINFO,(size_t) 0, (dvoid **) 0);
		OCIAttrSet((dvoid *) authp,(ub4) OCI_HTYPE_AUTHINFO,(dvoid *) user.c_str(), user.length(),(ub4) OCI_ATTR_USERNAME, errhp);
		OCIAttrSet((dvoid *) authp,(ub4) OCI_HTYPE_AUTHINFO,(dvoid *) password.c_str(), password.length(),(ub4) OCI_ATTR_PASSWORD, errhp);
		OCISessionGet(envhp, errhp, &svchp, authp,(OraText *) poolName,poolNameLen, NULL, 0, NULL, NULL, NULL,OCI_SESSGET_CPOOL);
*/
	
	(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0);
	(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0);
	(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0);
	CHECKERR(errhp,OCIServerAttach( srvhp, errhp, (text *)tnsname.c_str(), strlen(tnsname.c_str()), 0));
	(void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);
	(void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
	(void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) user.c_str(), (ub4) strlen(user.c_str()), (ub4) OCI_ATTR_USERNAME, errhp);
	(void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) password.c_str(), (ub4) strlen(password.c_str()), (ub4) OCI_ATTR_PASSWORD, errhp);
	CHECKERR(errhp,OCISessionBegin ( svchp, errhp, authp, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT));
	(void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp);
	(void) OCIHandleAlloc((dvoid *)envhp, (dvoid **)&trans, OCI_HTYPE_TRANS, 0, 0);
	(void) OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)trans, 0, OCI_ATTR_TRANS, errhp);
	
	Initialized=1;
}

/*!
  Destructor. If a connection has been estabilshed previously, this connection
  is being erased.
 */
OCICPP::Connection::~Connection() {
	drop();
}

/*!
  Drop the connection to the database, if a connection had been estabilshed
  previously.
 */
void OCICPP::Connection::drop() {
	if(Initialized) {	
		if(envhp && errhp && svchp && authp) {
/*
			OCISessionRelease(svchp,errhp,NULL,0,OCI_DEFAULT);
			OCIHandleFree((dvoid *)authp, OCI_HTYPE_AUTHINFO);
			OCIConnectionPoolDestroy(poolhp,errhp,OCI_DEFAULT);
			OCIHandleFree(poolhp,OCI_HTYPE_CPOOL);
			CHECKERR(errhp,OCISessionEnd(svchp,errhp,authp,OCI_DEFAULT));
			CHECKERR(errhp,OCIServerDetach(srvhp,errhp,OCI_DEFAULT));
			OCIHandleFree(svchp,OCI_HTYPE_SVCCTX);
			OCIHandleFree(srvhp,OCI_HTYPE_SERVER);
			OCIHandleFree(errhp,OCI_HTYPE_ERROR);
			OCITerminate(OCI_DEFAULT);
*/
			
			OCIHandleFree(trans,OCI_HTYPE_TRANS);
			CHECKERR(errhp,OCISessionEnd(svchp,errhp,authp,OCI_DEFAULT));
			OCIHandleFree(authp,OCI_HTYPE_SESSION);
			CHECKERR(errhp,OCIServerDetach(srvhp,errhp,OCI_DEFAULT));
			OCIHandleFree(svchp,OCI_HTYPE_SVCCTX);
			OCIHandleFree(srvhp,OCI_HTYPE_SERVER);
			OCIHandleFree(errhp,OCI_HTYPE_ERROR);
			
		}
	}
	Initialized=0;
}


/*!
  Execute a query (an SQL-Statement) \a query on the previously initialized
  database connection. A cursor is returned as a result. The user is
  responsible for destroying this cursor if it is not required any more.

  \sa void execQuery(const string &,Cursor &,int)
 */
OCICPP::Cursor *OCICPP::Connection::execQuery(const std::string &query) {
	DEBUG(DLEV_INFO,"executing %s\n",query.c_str());
	Cursor *cur=(Cursor *) 0;
	try {
		cur=new Cursor(envhp,svchp,errhp,1);
		if(cur->prepare(query)==OCI_STMT_SELECT) {
			cur->exec();
			cur->describe();
			cur->define();
		} else { // It was non-select request does not supported etall yet 
			DEBUG(DLEV_DEBUG,"Execute Non-select stmt\n");
			cur->exec(); 
		}
	} catch(OraError err) {
		delete cur;
		throw; /* tell to application error occured */
	}
	DEBUG(DLEV_DEBUG,"Query Executed successfully\n");
	return cur;
}

/*!
  Execute a query (an SQL-statement) \a query on the database server. All
  relevant information are store within the reference \a cur to a cursor
  object. \a prefetch stores, how many rows are to be fetched when executing
  Cursor::fetch() for the created cursor.

  \sa Cursor *execQuery(const string &)
 */
void OCICPP::Connection::execQuery(const std::string &query,OCICPP::Cursor &cur,int prefetch=1) {
	DEBUG(DLEV_INFO,"executing %s\n",query.c_str());
	cur.init(envhp,svchp,errhp,prefetch);
	if(cur.prepare(query)==OCI_STMT_SELECT) {
		cur.exec();
		cur.describe();
		cur.define();
	} else { // It was non-select request does not supported etall yet 
		cur.exec(); 
	}
	DEBUG(DLEV_DEBUG,"Query Executed successfully\n");
}

/*!
  Execute an update statement \a request on the database server. This method
  can be used to execute \b UPDATE, \b INSERT and \b DELETE SQL statements,
  which are not returning a cursor.
 */
void OCICPP::Connection::execUpdate(const std::string &request) {
	DEBUG(DLEV_INFO,"executed %s\n",request.c_str());
	Cursor *cur=(Cursor *) 0;
	try {
		cur=new Cursor(envhp,svchp,errhp,1);
		cur->prepare(request);
		cur->exec();
		delete cur;
	} catch (OraError err) {
		delete cur;
		throw; /* Tell to application error occured */
	}
}

unsigned int OCICPP::Connection::execQueryEx(const std::string &query,OCICPP::Cursor &cur,int prefetch=1) {
	unsigned int nRet = 0;
	DEBUG(DLEV_INFO,"executing %s\n",query.c_str());
	cur.init(envhp,svchp,errhp,prefetch);
	if(cur.prepare(query)==OCI_STMT_SELECT) {
		nRet = cur.execEx();
		cur.describe();
		cur.define();
	} else { // It was non-select request does not supported etall yet
		nRet = cur.execEx();
	}
	return nRet;
}

unsigned int OCICPP::Connection::execUpdateEx(const std::string &request)
{
	unsigned int nRet = 0;
	DEBUG(DLEV_INFO,"executed %s\n",request.c_str());
	Cursor *cur=(Cursor *) 0;
	try {
		cur=new Cursor(envhp,svchp,errhp,1);
		cur->prepare(request);
		nRet = cur->execEx();
		delete cur;
	} catch (OraError err) {
		delete cur;
		throw; /* Tell to application error occured */
	}
	return nRet;
}



/*!
  Start a new transaction
 */
void OCICPP::Connection::transStart(int flags=SERIALIZABLE) {
	CHECKERR(errhp,OCITransStart(svchp,errhp,(uword) 0,flags)); /* Detaching is not supperted yet so timeout is ignored */
}

/*!
  Commit (accept) a previously started transaction. The transaction ends with
  either being committed or rolled back.
  \sa transStart(), transCommit(), transRollback()
 */
void OCICPP::Connection::transCommit() {
	CHECKERR(errhp,OCITransCommit(svchp,errhp,OCI_DEFAULT));
}
/* Hm... we should pass a dvoid * to Rollback and OCISvcCtx to Commit isn't it an architecture bug ? */
/* It was a bug in documentation $ORACLE_HOME/rdbms/demo/ociap.h contains this prototype of OCITransRollback() :
   sword   OCITransRollback  (OCISvcCtx *svchp, OCIError *errhp, ub4 flags);
and here's a documentation prototype:
   sword   OCITransRollback  (dvoid *svchp, OCIError *errhp, ub4 flags);
*/

/*!
  Roll back a previously started transaction. The transaction ends with
  either being committed or rolled back.
  \sa transStart(), transCommit(), transRollback()
 */
void OCICPP::Connection::transRollback() {
	CHECKERR(errhp,OCITransRollback(svchp,errhp,OCI_DEFAULT));
}

/*!
  Prepare and SQL-Statement \a sql. The cursor \a cur is accordingly
  initialized. For fetch-operations, an amount or rows to be prefetched
  \a prefetch may be specified.
 */
void OCICPP::Connection::prepare(const std::string &sql,OCICPP::Cursor &cur,int prefetch=1) {
	cur.init(envhp,svchp,errhp,prefetch);
	cur.prepare(sql);
}

/*!
  Initialize the queue-instance \a queue to represent the advanced database
  queue named \a queue_name.
 */
void OCICPP::Connection::getQueue(std::string& queue_name, OCICPP::AQQueue& queue) {
	queue.init(queue_name, envhp, svchp, errhp);
}

/*! 
  Create new BFile \a bfile , placed in directory \a dir with name \a fname.
  The directory should be created first on server, and file should exist at his
  place.
*/
void OCICPP::Connection::createBFile(const string &dir,const string &fname,
									BFile &bfile) {
	bfile.init(envhp,svchp,errhp,dir,fname);
}

#if ( OCILIBVERMAJOR>=8 && OCILIBVERMINOR>=1 ) || DOXYGEN
/*! 
  Create a temporary large object (see class TLob) of type \a lobtype with
  caching mode \a cachemode. The temporary LOB structure \a lob is initialized
  during this call
  \param lobtype \c OCICPP::CLOB and \c OCICPP::BLOB are supported
  \param cachmode \c OCICPP::CACHE_ON or \c OCICPP::CACHE_OFF is supported
  \ora8i_fn
*/
void OCICPP::Connection::createTLob(OCICPP::LobType lobtype,OCICPP::CacheMode cachemode,
									TLob &tlob) {
	tlob.init(envhp,svchp,errhp,lobtype,cachemode);
}
#endif 
		
/*! 
  Returns the identification string of the Oracle server associated to this
  connection.
*/
string OCICPP::Connection::serverVersion() {
	char buf[1024];
	CHECKERR(errhp,OCIServerVersion((dvoid *)srvhp,errhp,(OraText *)buf,1023,OCI_HTYPE_SERVER));
	return string(buf);
}