oracall32.cpp

对Oracle OCI的封装

/*
 * oracall32.cpp	1.01 03/15/01
 *
 * Copyright (c) 2001 Sergey Yakovlev. All Rights Reserved.
 *
 *    E-mail:  [e-mail]
 * Voice/Fax:  [phone] x1385
 *       ICQ:  86060618
 *
 *
 * Author grants you ("Licensee") a non-exclusive, royalty free, license to use,
 * modify and redistribute this software in source and binary code form,
 * provided that i) this copyright notice and license appear on all copies of
 * the software; and ii) Licensee does not utilize the software in a manner
 * which is disparaging to Author.
 *
 * This software is provided "AS IS," without a warranty of any kind. ALL
 * EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY
 * IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
 * NON-INFRINGEMENT, ARE HEREBY EXCLUDED. AUTHOR AND ITS LICENSORS SHALL NOT BE
 * LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING
 * OR DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL AUTHOR OR ITS
 * LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT,
 * INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER
 * CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF
 * OR INABILITY TO USE SOFTWARE, EVEN IF AUTHOR HAS BEEN ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGES.
 *
 * This software is not designed or intended for use in on-line control of
 * aircraft, air traffic, aircraft navigation or aircraft communications; or in
 * the design, construction, operation or maintenance of any nuclear
 * facility. Licensee represents and warrants that it will not use or
 * redistribute the Software for such purposes.
 */


// oracall32.cpp : Defines the entry point for the DLL application.
//

#include <windows.h>

extern "C"
{
#include <string.h>
#include <stdio.h>
#include <oci.h>
}

#include "oracall32.h"

/* Error handling function.
   @param errhp - An error handle. 
   @param status - An error code.
   */
void checkerr(OCIError *errhp, sword& status)
{
	text errbuf[512];
	sb4 errcode = 0;
	
	switch (status)
	{
		case OCI_SUCCESS:
			break;
		case OCI_SUCCESS_WITH_INFO:
			MessageBox(NULL, "Error - OCI_SUCCESS_WITH_INFO", NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_NEED_DATA:
			MessageBox(NULL, "Error - OCI_NEED_DATA", NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_NO_DATA:
			MessageBox(NULL, "Error - OCI_NODATA", NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_ERROR:
			(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
								errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
			
			if(errcode == 1405)
				status = 0;
			else
				MessageBox(NULL, (char*)errbuf, NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_INVALID_HANDLE:
			MessageBox(NULL, "Error - OCI_INVALID_HANDLE", NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_STILL_EXECUTING:
			MessageBox(NULL, "Error - OCI_STILL_EXECUTE", NULL, MB_OK | MB_ICONWARNING);
			break;
		case OCI_CONTINUE:
			MessageBox(NULL, "Error - OCI_CONTINUE", NULL, MB_OK | MB_ICONWARNING);
			break;
		case CONERR_ALRCON:
			MessageBox(NULL, "Error - CONERR_ALRCON", NULL, MB_OK | MB_ICONWARNING);
			break;
		case CONERR_NOTCON:
			MessageBox(NULL, "Error - CONERR_NOTCON", NULL, MB_OK | MB_ICONWARNING);
			break;
		default:
			break;
	}
}

/* CConnection constructor */
CConnection::CConnection()
{
	state = not_connected;
	stm = NULL;
	init();
}

/* CConnection destructor */
CConnection::~CConnection()
{
	// disconnect if connection exists
	if(state == connected)
	{
		if(disconnect() == 0)
			deallocate();
	}
}

/* creating and initializing an OCI environment. */
int CConnection::init(void)
{
	sword status;
	
	/* initialize the mode to be the threaded and object environment */
	if(status = OCIEnvCreate(&envhp, OCI_THREADED|OCI_OBJECT, (dvoid *)0, 
							0, 0, 0, (size_t) 0, (dvoid **)0))
		return (status);
	
	/* allocate an error handle */
	checkerr(errhp, status = OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,
							OCI_HTYPE_ERROR, 0, (dvoid **) 0));
	
	return (status);
}

/* freeing an allocated OCI environment. */
int CConnection::deallocate(void)
{
	sword status;
	
	/* freeing an error handle */
	if(errhp)
	{
		if(status = OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR))
			return (status);
	}

	/* Detaches the process from the shared memory subsystem and releases the shared memory */
	status = OCITerminate(OCI_DEFAULT);

	return (status);
}

/* This function is used to create a simple logon session.
   @param username - The username. 
   @param password -  The user's password.
   @param dblink - The name of the database to connect to.
   */
int CConnection::connect(const char *username, const char *password, const char *dblink)
{
	sword status;
	
	if(state == connected)
	{
		// this object is already connected
		return (CONERR_ALRCON);
	}
	
	// create a simple logon session
	if(status = OCILogon(envhp, errhp, &svchp, (text*)username, (ub4)strlen(username), 
			(text*)password, (ub4)strlen(password), (text*)dblink, strlen(dblink)))
	{
		checkerr(errhp, status); 
	}
	else
	{
		// successful login
		state = connected;
	}

	return (status);
}

/* This function is used to terminate a connection and session created 
   with connect(). 
   */
int CConnection::disconnect()
{
	sword status;
	
	// delete the statement object
	if(stm != NULL)
	{
		delete stm;
		stm = NULL;
	}

	if(state == not_connected)
	{
		// this object has not been connected
		return (CONERR_NOTCON);
	}
	
	
	if(status = OCILogoff(svchp, errhp))
	{
		checkerr(errhp, status);
	}
	else
	{
		// successful logout
		state = not_connected;
	}
	
	return (status);
}

/* Commits the transaction associated with a specified service context. */
int CConnection::commit()
{
	sword status;
	
	if(state == not_connected)
	{
		// this object has not been connected
		return (CONERR_NOTCON);
	}
	
	checkerr(errhp, status = OCITransCommit(svchp, errhp, OCI_DEFAULT));

	return (status);
}

/* Rolls back the current transaction. */
int CConnection::rollback()
{
	sword status;
	
	if(state == not_connected)
	{
		// this object has not been connected
		return (CONERR_NOTCON);
	}
	
	checkerr(errhp, status = OCITransRollback(svchp, errhp, OCI_DEFAULT));

	return (status);
}

/* SQL statements with or without parameters are normally 
   executed using CStatement objects.
   */
CStatement* CConnection::createStatement()
{
	if(stm != NULL)
	{
		delete stm;
		stm = NULL;
	}

	if(state == connected)
	{
		// this object has been connected
		stm = new CStatement(this);
	}
	
	return stm;
}

/* Gets the table comments. */
CResultSet* CConnection::getTableComments()
{
	if(createStatement()) 
		return stm->executeQuery("SELECT * FROM user_tab_comments");
	else
		return NULL;
}

/* Gets the column comments.
   @param tab_name - The table name. 
   */
CResultSet* CConnection::getColumnComments(const char* tab_name)
{
	if(createStatement())
	{
		if(tab_name == NULL)
			return stm->executeQuery("SELECT * FROM user_col_comments");
		else if(!stm->prepareStatement("SELECT * FROM user_col_comments WHERE UPPER(table_name) = UPPER(:1)") &&
				!stm->setString(1, tab_name))
			return stm->executeQuery();
		else
			return NULL;
	}
	else
		return NULL;
}

/* write error message to the given file */
//void CConnection::display_error(FILE *file) const
//{
/*	if(lda.rc != 0)
	{
		sword n;
		text msg[512];
		
		n = oerhms((cda_def *)&lda, lda.rc, msg, (sword) sizeof(msg));
		err_report(file, msg, lda.fc);
	}
*/
//}

/* CStatement constructor.
   @param connection - The CConnection handle.
   */
CStatement::CStatement(CConnection* connection)
{
	con = connection;
	rs = NULL;
	stmthp = NULL;
	type = 0;
}

/* CStatement destructor. */
CStatement::~CStatement()
{
	// delete the statement object
	if(rs != NULL)
	{
		delete rs;
		rs = NULL;
	}
}

/* Execute a SQL statement that returns a single resultset.
   @param sql - typically this is a static SQL SELECT statement. 
   */
CResultSet* CStatement::executeQuery(const char* sql)
{
	if(rs != NULL)
	{
		delete rs;
		rs = NULL;
	}
	
	if(prepareStatement(sql) == 0 && isStmSelect())
		rs = executeQuery();

	return rs;
}

/* A prepared SQL query is executed and its resultset is returned. */
CResultSet* CStatement::executeQuery()
{
	sword status;
	
	if(rs != NULL)
	{
		delete rs;
		rs = NULL;
	}
	
	/* execute */
	if(status = OCIStmtExecute(con->svchp, stmthp, con->errhp, (ub4) 0, (ub4) 0,
		(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
	{
		checkerr(con->errhp, status);
	}
	else
	{
		rs = new CResultSet(this);		
	}

	return rs;
}

/* Execute a SQL INSERT, UPDATE or DELETE statement. 
   @param sql - a SQL INSERT, UPDATE or DELETE statement or a SQL statement that returns nothing. 
   */
int CStatement::executeUpdate(const char* sql)
{
	sword status;
	
	if(rs != NULL)
	{
		delete rs;
		rs = NULL;
	}
	
	if(!(status = prepareStatement(sql)) && !isStmSelect())
		status = executeUpdate();

	return (status);
}

/* Execute a SQL INSERT, UPDATE or DELETE statement. */
int CStatement::executeUpdate()
{
	sword status;
	
	if(rs != NULL)
	{
		delete rs;
		rs = NULL;
	}
	
	/* execute and fetch */
	if(status = OCIStmtExecute(con->svchp, stmthp, con->errhp, (ub4) 1, (ub4) 0,
			(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
	{
		checkerr(con->errhp, status);
	}

	return (status);
}

/* A SQL statement with or without IN parameters can be pre-compiled 
   and stored in a CStatement object.
   @param sql - a SQL statement that may contain one or more '?' IN parameter placeholders. 
   */
int CStatement::prepareStatement(const char* sql)
{
	sword status;
	
	if(status = OCIHandleAlloc((dvoid *) con->envhp, (dvoid **) &stmthp,
		OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
	{
		checkerr(con->errhp, status);
		return status;
	}

	// prepares a SQL or PL/SQL statement for execution
	checkerr(con->errhp, status = OCIStmtPrepare(stmthp, con->errhp, (text*)sql,
								(ub4) strlen(sql) + 1,
								(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
	
	// determine what type of SQL statement was prepared
	checkerr(con->errhp, status = OCIAttrGet((dvoid*) stmthp, (ub4) OCI_HTYPE_STMT, 
		(dvoid*) &type, (ub4*) 0, (ub4) OCI_ATTR_STMT_TYPE, con->errhp));

	return (status);
}

/* Set a parameter to a Oracle String value.
   @param index - the first parameter is 1, the second is 2, ... 
   @param val - the parameter value.
   */
int CStatement::setString(const short index, const char* val)
{
	sword status;
	OCIBind*	bndp = (OCIBind*) 0;	/* the bind handle */	
	
	/* Bind the placeholders in the statement. */
	checkerr(con->errhp, status = OCIBindByPos(stmthp, &bndp, con->errhp, (ub4)index, 
			(text*) val, strlen(val) + 1, SQLT_STR, (dvoid *) 0,
			(ub2*) 0, (ub2*) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

	return (status);
}

/* Set a parameter to a Oracle Integer value. 
   @param index - the first parameter is 1, the second is 2, ... 
   @param val - the parameter value.
   */
int CStatement::setInt(const short index, const int* val)
{
	sword status;
	OCIBind*	bndp = (OCIBind*) 0;	/* the bind handle */	
	
	/* Bind the placeholders in the statement. */
	checkerr(con->errhp, status = OCIBindByPos(stmthp, &bndp, con->errhp, (ub4)index, 
			(dvoid*) val, (sb4) sizeof(int), SQLT_INT, (dvoid *) 0,
			(ub2*) 0, (ub2*) 0, (ub4) 0, (ub4*) 0, OCI_DEFAULT));

	return (status);
}