odb.cpp

一个不错的Oracle数据库封装类（.cpp）

// Odb.cpp
//
// Release 1, Copyright (C) 1999 Ben Bryant
// This is sample source code, nothing more is implied. Use it only as such.
// This software is provided 'as-is', without warranty. In no event will the
// author be held liable for any damages arising from the use of this software.
// Permission is granted to anyone to use this software for any purpose.
// The origin of this software must not be misrepresented; you must not claim
// that you wrote the original software. Altered source versions must be plainly
// marked as such, and must not be misrepresented as being the original software.
// Ben Bryant [e-mail]

#include "stdafx.h"
#include "Odb.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif


#define MAXROWS 10


//////////////////////////////////////////////////////////////////
// Oracle Call Interface (OCI) Wrapper
//
#ifdef __STDC__
#include <oci.h>
#else
#define __STDC__ 1
#include <oci.h>
#undef __STDC__
#endif

//////////////////////////////////////////////////////////////////////
// COdb::OdbField
//////////////////////////////////////////////////////////////////////

struct COdb::OdbField
{
	//
	// This structure maintains an OCI field/column/variable
	//
	CString csName;
	dvoid* pBuffer;
	ub2 wType;
	ub2 wSize;
	ub2 wLen;
	sb2 nInd;
	BOOL bQuotedOnUpdate;

	// Methods
	OdbField();
	~OdbField();
	void SetSize( ub2 w );
	void Set( CString cs );
	CString Get() const;
	CString GetMultiRow( int Row ) const;
	void Free();
};

COdb::OdbField::OdbField()
{
	pBuffer = NULL;
	wType = SQLT_STR;
	wSize = 0;
	wLen = 0;
	nInd = 0;
	bQuotedOnUpdate = TRUE;
}

COdb::OdbField::~OdbField()
{
	Free();
}

void COdb::OdbField::SetSize( ub2 w )
{
	//
	// Make sure buffer is at least size w
	//
	if ( w > wSize || ! pBuffer )
	{
		Free();
		pBuffer = (dvoid*) new char[w*MAXROWS];
		wSize = w;
	}
}

void COdb::OdbField::Set( CString cs )
{
	//
	// Set buffer to hold CString
	//
	wLen = cs.GetLength() + 1;
	SetSize( wLen );
	strcpy( (char*)pBuffer, cs );
}

CString COdb::OdbField::Get() const
{
	//
	// All fields are converted to strings by the OCI
	// because SQLT_STR and SQLT_LNG are specified when binding fields
	// Copy from buffer into a string
	//
	CString csResult;
	csResult.Format("%.*s", wLen, (const char*)pBuffer);
	csResult.TrimRight();
	return csResult;
}

CString COdb::OdbField::GetMultiRow( int Row ) const
{
	//
	// All fields are converted to strings by the OCI
	// because SQLT_STR and SQLT_LNG are specified when binding fields
	// Copy from buffer into a string
	//

	/* should test !! */
	CString csResult;
	csResult.Format("%.*s", wLen, (const char*)pBuffer+Row*wSize);
	csResult.TrimRight();
	return csResult;
}

void COdb::OdbField::Free()
{
	//
	// Free field's allocated buffer
	//
	if ( pBuffer )
	{
		delete pBuffer;
		pBuffer = NULL;
	}
}

//////////////////////////////////////////////////////////////////////
// COdb::OdbRecordSet
//////////////////////////////////////////////////////////////////////

struct COdb::OdbRecordSet
{
	//
	// This structure maintains a recordset
	// A recordset holds a row of select results
	//
	CPtrArray paFields;
	int m_nRows;
	CString m_csStatement;

	// Methods
	OdbRecordSet();
	~OdbRecordSet();
	int Find( const CString& csName );
	void RemoveAll();
};

COdb::OdbRecordSet::OdbRecordSet()
{
}

COdb::OdbRecordSet::~OdbRecordSet()
{
	RemoveAll();
}

void COdb::OdbRecordSet::RemoveAll()
{
	//
	// Remove fields
	//
	for ( int iField=0; iField<paFields.GetSize(); ++iField )
		delete (OdbField*)paFields[iField];
	paFields.RemoveAll();
}

int COdb::OdbRecordSet::Find( const CString& csName )
{
	//
	// Return iField or -1 if name not found
	//
	for ( int iField=0; iField<paFields.GetSize(); ++iField )
	{
		OdbField* pField = (OdbField*)paFields.GetAt( iField );
		if ( pField->csName == csName )
			return iField;
	}
	return -1;
}

//////////////////////////////////////////////////////////////////////
// OdbContext
//////////////////////////////////////////////////////////////////////

struct COdb::OdbContext
{
	//
	// This structure maintains all of the OCI variables
	// It is declared here rather than in the header
	// to isolate OCI from COdb clients
	//
	OdbContext();
	void Clear();
	char szError[512];
	HRESULT hr;
	OCIEnv* hpEnv;
	OCIServer* hpServer;
	OCIError* hpErr;
	OCISvcCtx* hpContext;
	OCIStmt *hpSelect;
	OdbRecordSet rsSelect;

	/* add by lf */
	OCIStmt *hpStatement;
};

COdb::OdbContext::OdbContext()
{
	Clear();
}

void COdb::OdbContext::Clear()
{
	hr = OCI_SUCCESS;
	hpEnv = NULL;
	hpErr = NULL;
	hpServer = NULL;
	hpContext = NULL;
	hpSelect = NULL;
	szError[0] = '\0';

	hpStatement = NULL;
}

//////////////////////////////////////////////////////////////////////
// COdb
//////////////////////////////////////////////////////////////////////

COdb::COdb()
{
	m_po = new OdbContext;
	TRACE("\n ODB initialize OK!");
}

COdb::~COdb()
{
	Close();
	delete m_po;
	TRACE("\n ODB exit!");
}

void COdb::CheckErr( short status )
{
	//
	// This sets the OCI error string
	// Later, this might be made to generate an exception
	// The table of codes and strings is kept here in a static array
	//
	static struct CErrorPair
	{
		sword w;
		const char* szText;
	}
		epaErrors[] =
	{
		OCI_SUCCESS,			"SUCCESS",
		OCI_SUCCESS_WITH_INFO,	"SUCCESS_WITH_INFO",
		OCI_NEED_DATA,			"NEED_DATA",
		OCI_NO_DATA,			"NO_DATA",
		OCI_INVALID_HANDLE,		"INVALID_HANDLE",
		OCI_STILL_EXECUTING,	"STILL_EXECUTE",
		OCI_CONTINUE,			"CONTINUE",
	};

	// Check status
	m_po->hr = status;
	if ( m_po->hr == OCI_ERROR )
	{
		// for the special case OCI_ERROR, call OCIErrorGet for the string
		sb4 errcode = 0;
	    OCIErrorGet(m_po->hpErr, 1, NULL, &errcode, 
			(unsigned char*)m_po->szError, sizeof(m_po->szError), OCI_HTYPE_ERROR);
	}
	else
	{
		// Look for description in static array
		DWORD nTotalResultCodes = sizeof(epaErrors) / sizeof(CErrorPair);
		for ( int iPos = 0; iPos < sizeof(epaErrors); ++ iPos )
		{
			if ( epaErrors[iPos].w == m_po->hr )
			{
				strcpy( m_po->szError, epaErrors[iPos].szText );
				break;
			}
		}
	}
}

CString COdb::GetErrorDescription()
{
	return m_po->szError;
}

HRESULT COdb::Open( const CString csConnect )
{
	//
	// This opens a connection with the database
	// Connect string format is user/password@host
	//
	CString csUser, csPassword, csServer;
	int iSlash = csConnect.Find("/");
	if ( iSlash > -1 )
	{
		csUser = csConnect.Left(iSlash);
		csPassword = csConnect.Mid(iSlash + 1);
		int iAt = csConnect.Find("@");
		if ( iAt > iSlash )
		{
			csPassword = csConnect.Mid( iSlash + 1, iAt - iSlash - 1 );
			csServer = csConnect.Mid(iAt + 1);
		}
	}
	else
	{
		csUser = csConnect;
	}
	if ( csUser.IsEmpty() && csPassword.IsEmpty() )
	{
		// For default OS logon, specify slash for user name and password
		csUser = "/";
		csPassword = "/";
	}

	// First, we make sure its closed
	Close();

	// Initialize OCI DLL specifying the mode
	CheckErr( OCIInitialize( OCI_OBJECT, NULL, NULL, NULL, NULL ) );
	if ( FAILED(m_po->hr) )
		return m_po->hr;

	// Note an alternative is to use the OCILogon/Logoff functions
	// Here we allocate all of the handles explicitly
	// Initialize the environment handle
	CheckErr( OCIEnvInit( &m_po->hpEnv, OCI_DEFAULT, 0, NULL ) );
	if ( FAILED(m_po->hr) )
		return m_po->hr;

	// Allocate error, server, and service context handles
	OCIHandleAlloc( m_po->hpEnv, (void**)&m_po->hpErr, OCI_HTYPE_ERROR, 0, NULL );
	OCIHandleAlloc( m_po->hpEnv, (void**)&m_po->hpServer, OCI_HTYPE_SERVER, 0, NULL );
	OCIHandleAlloc( m_po->hpEnv, (void**)&m_po->hpContext, OCI_HTYPE_SVCCTX, 0, NULL );

	// Associate TNS with server handle
	CheckErr( OCIServerAttach( m_po->hpServer,
		m_po->hpErr,
		(unsigned char*)(const char*)csServer,
		csServer.GetLength(),
		0 )
		);
	if ( FAILED(m_po->hr) )
		return m_po->hr;

	// Get server version string
	const nVersionLength = 1024;
	CString csVersion;
	CheckErr( OCIServerVersion( m_po->hpServer,
		m_po->hpErr,
		(text*)csVersion.GetBuffer(nVersionLength),
		nVersionLength,
		OCI_HTYPE_SERVER )
		);
	if ( FAILED(m_po->hr) )
		return m_po->hr;
	csVersion.ReleaseBuffer();
	m_csLog += "\n" + csVersion + "\n";

	// Specify server handle to service context
	CheckErr( OCIAttrSet( m_po->hpContext,
		OCI_HTYPE_SVCCTX,
		m_po->hpServer,
		0,
		OCI_ATTR_SERVER,
		m_po->hpErr )
		);
	if ( FAILED(m_po->hr) )
		return m_po->hr;

	// Allocate a session handle
	OCISession *hpSession = NULL;
	OCIHandleAlloc( m_po->hpEnv, (void**)&hpSession, OCI_HTYPE_SESSION, 0, NULL);

	// Associate username with session handle
	OCIAttrSet( hpSession,
		OCI_HTYPE_SESSION,
		(void*)(const char*)csUser,
		csUser.GetLength(),
		OCI_ATTR_USERNAME,
		m_po->hpErr
		);

	// Associate password with session handle
	OCIAttrSet( hpSession,
		OCI_HTYPE_SESSION,
		(void*)(const char*)csPassword,
		csPassword.GetLength(),
		OCI_ATTR_PASSWORD,
		m_po->hpErr
		);

	// Open session using service context and session handle
	CheckErr( OCISessionBegin( m_po->hpContext,
		m_po->hpErr,
		hpSession,
		OCI_CRED_RDBMS,
		OCI_DEFAULT )
		);
	if ( FAILED(m_po->hr) )
		return m_po->hr;

	// Specify session handle to service context
	OCIAttrSet( m_po->hpContext,
		OCI_HTYPE_SVCCTX,
		hpSession,
		0,
		OCI_ATTR_SESSION,
		m_po->hpErr
		);

	// Change date format
	if ( SUCCEEDED(m_po->hr) )
	{
		CString csSQL = "alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'";
		m_po->hr = Exec( csSQL );
	}

	/* Alloc OCIHandle, add by lf */
	Exec_Before();

	return m_po->hr;
}

HRESULT COdb::Close()
{
	//
	// This closes and/or cleans up the connection
	// It should work even if the connection is closed or partly open
	//
	m_po->hr = OCI_SUCCESS;

	// Free select statement handle