odb.cpp

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

	if (m_po->hpSelect)
		OCIHandleFree( m_po->hpSelect, OCI_HTYPE_STMT );

	// Detach server from server handle
	if (m_po->hpErr)
		OCIServerDetach( m_po->hpServer, m_po->hpErr, OCI_DEFAULT );

	// Free server handle
	if (m_po->hpServer)
		CheckErr( OCIHandleFree(m_po->hpServer, OCI_HTYPE_SERVER) );

	// Free service context
	if (m_po->hpContext)
		OCIHandleFree( m_po->hpContext, OCI_HTYPE_SVCCTX);

	// Free error handle
	if (m_po->hpErr)
		OCIHandleFree( m_po->hpErr, OCI_HTYPE_ERROR );

	/* add by lf */
	Exec_After();

	TRACE("\n Oracle Database Closed!");
	m_po->Clear();
	return m_po->hr;
}

HRESULT COdb::Exec_Do( const CString csStatement, BOOL bNeedCommit )
{
	//
	// Execute statement
	//
	m_csLog += csStatement + "\n";

	// Allocate statement handle
	OCIStmt *hpStatement = NULL;
	CheckErr( OCIHandleAlloc( m_po->hpEnv,		// environment handle
		(void**)&hpStatement,					// return handle
		OCI_HTYPE_STMT,							// specifies generation of statement
		0,										// amount of user memory
		NULL )									// Returns pointer to user memory
		);

	// Associate statement string with handle
	CString csCleanStatement = csStatement;
	CleanWhitespace( csCleanStatement );
	CheckErr( OCIStmtPrepare( hpStatement,		// statement handle
		m_po->hpErr,							// error handle
		(text *)csCleanStatement.GetBuffer(0),	// statement
		csCleanStatement.GetLength(),			// length of statement
		OCI_NTV_SYNTAX,							// specify native syntax
		OCI_DEFAULT )							// default mode
		);

	// Execute statement
	CheckErr( OCIStmtExecute( m_po->hpContext,	// context handle
		hpStatement,							// statement handle
		m_po->hpErr,							// error handle
		(ub4) 1,								// number of times statement executed for non-Select statements
		(ub4) 0,								// row offset	// starting index of data
		(CONST OCISnapshot *) NULL,				// snapshot descriptor
		(OCISnapshot *) NULL,					// pointer to snapshot descriptor
		OCI_DEFAULT )
		);

	// Commit the change
	if(bNeedCommit == TRUE)
		CheckErr( OCITransCommit(m_po->hpContext, m_po->hpErr, 0));

	// Free statement handle
	if ( hpStatement )
		OCIHandleFree( hpStatement, OCI_HTYPE_STMT );

	return m_po->hr;
}

HRESULT COdb::Exec_Before( void )
{
	// Allocate statement handle
	CheckErr( OCIHandleAlloc( m_po->hpEnv,		// environment handle
		(void**)&m_po->hpStatement,				// return handle
		OCI_HTYPE_STMT,							// specifies generation of statement
		0,										// amount of user memory
		NULL )									// Returns pointer to user memory
		);

	return m_po->hr;
}

HRESULT COdb::Exec( CString csStatement, BOOL bNeedCommit )
{
	// Associate statement string with handle
	CString csCleanStatement = csStatement;
	CleanWhitespace( csCleanStatement );
	CheckErr( OCIStmtPrepare( m_po->hpStatement,// statement handle
		m_po->hpErr,							// error handle
		(text *)csCleanStatement.GetBuffer(0),	// statement
		csCleanStatement.GetLength(),			// length of statement
		OCI_NTV_SYNTAX,							// specify native syntax
		OCI_DEFAULT )							// default mode
		);

	// Execute statement
	CheckErr( OCIStmtExecute( m_po->hpContext,	// context handle
		m_po->hpStatement,						// statement handle
		m_po->hpErr,							// error handle
		(ub4) 1,								// number of times statement executed for non-Select statements
		(ub4) 0,								// row offset	// starting index of data
		(CONST OCISnapshot *) NULL,				// snapshot descriptor
		(OCISnapshot *) NULL,					// pointer to snapshot descriptor
		OCI_DEFAULT )
		);

	// Commit the change
	if(bNeedCommit == TRUE)
		CheckErr( OCITransCommit(m_po->hpContext, m_po->hpErr, 0));

	return m_po->hr;
}

HRESULT COdb::Exec_After( void )
{
	// Free statement handle
	if ( m_po->hpStatement )
		OCIHandleFree( m_po->hpStatement, OCI_HTYPE_STMT );

	return m_po->hr;
}

HRESULT COdb::Select( const CString csStatement )
{
	//
	// Execute select statement and parse the select list description
	// Use member recordset rsSelect
	// See also Get, FetchNext, IsEOS
	//
	m_csLog += csStatement + "\n";

	// Free previous select handle and remove fields
	m_po->rsSelect.m_nRows = 0;
	m_po->rsSelect.RemoveAll();
	if (m_po->hpSelect)
		OCIHandleFree( m_po->hpSelect, OCI_HTYPE_STMT );

	// Allocate statement handle
	CheckErr( OCIHandleAlloc( m_po->hpEnv,
		(void**)&m_po->hpSelect,	// ref to statement handle pointer
		OCI_HTYPE_STMT,				// type of handle being allocated
		0,
		NULL )
		);

	// Associate statement string with handle
	m_po->rsSelect.m_csStatement = csStatement;
	CleanWhitespace( m_po->rsSelect.m_csStatement );
	CheckErr( OCIStmtPrepare( m_po->hpSelect,
		m_po->hpErr,		// error handle pointer
		(text *)m_po->rsSelect.m_csStatement.GetBuffer(0), // statement string
		m_po->rsSelect.m_csStatement.GetLength(),
		OCI_NTV_SYNTAX,		// specify native syntax
		OCI_DEFAULT )
		);

	// Execute but don't fetch yet
	CheckErr( OCIStmtExecute( m_po->hpContext,	// context handle
		m_po->hpSelect, 
		m_po->hpErr,							// error handle
		(ub4) 0,								// iters
		(ub4) 0,								// max rows to fetch during this call
		(CONST OCISnapshot *) NULL,				// snapshot descriptor
		(OCISnapshot *) NULL,					// pointer to snapshot descriptor
		OCI_DEFAULT )
		);

	// If it returns OCI_NO_DATA then no need to define recordset
	if ( m_po->hr == OCI_NO_DATA || FAILED(m_po->hr) )
	{
		return m_po->hr;
	}

	// Load the types into recordset
	int nColumnCount = 0;
	while ( m_po->hr == OCI_SUCCESS )
	{
		// Get pointer to column
		void* pFieldAttr = NULL;
		HRESULT hrGetNext;
		hrGetNext = OCIParamGet( m_po->hpSelect, OCI_HTYPE_STMT,
			m_po->hpErr,
			&pFieldAttr,
			nColumnCount+1 // position
			);
		if ( hrGetNext != OCI_SUCCESS )
			break;

		// Increment column count and allocate an OdbField structure
		++nColumnCount;
		OdbField* pField = new OdbField;
		m_po->rsSelect.paFields.Add( pField );

		// Get data type
		CheckErr( OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
			&pField->wType,
			0,
			OCI_ATTR_DATA_TYPE,
			m_po->hpErr )
			);

		// Get data size
		CheckErr( OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
			&pField->wSize,
			0,
			OCI_ATTR_DATA_SIZE,
			m_po->hpErr )
			);

		// Type conversions
		if ( pField->wType == SQLT_LNG )
		{
			// LONG size
			pField->wSize = 32760;
		}
		else if ( pField->wType == SQLT_DAT )
		{
			// String is bound to DATE
			pField->wType = SQLT_STR;
			pField->wSize = 50;
		}
		else if ( pField->wType == SQLT_NUM )
		{
			// String is bound to NUMBER
			pField->wType = SQLT_STR;
			pField->wSize += 1; // allow for null-terminator
		}

		// Get column name
		text* pName;
		ub4 nNameLen;
		CheckErr( OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
			(void**)&pName,
			&nNameLen,
			OCI_ATTR_NAME,
			m_po->hpErr )
			);

		// Set size and name
		pField->SetSize( pField->wSize );
		pField->csName.Format( "%.*s", nNameLen, pName );
	}

	// Bind storage for receiving input variables
	OCIDefine *pDefn;	// to hold pointer to field definition
	int iField;
	for ( iField=0; iField<m_po->rsSelect.paFields.GetSize(); ++iField )
	{
		// Get pointer to field structure
		OdbField* pField = (OdbField*)m_po->rsSelect.paFields[iField];

		// Bind
		pDefn = NULL;
		CheckErr( OCIDefineByPos( m_po->hpSelect,
			&pDefn,				// function allocs and gives back a pointer to field definition
			m_po->hpErr,
			iField+1,			// position in select starting at 1
			pField->pBuffer,	// storage area for field result
			pField->wSize,		// sizeof storage area
			pField->wType,		// field type
			&pField->nInd,		// indp, null indicator
			&pField->wLen,		// rlenp
			NULL,
			OCI_DEFAULT )
			);
	}

	// Fetch
	FetchNext();

	return m_po->hr;
}

HRESULT COdb::FetchNext()
{
	//
	// Fetch next row of select statement and parse the select list description
	//
	CheckErr( OCIStmtFetch( m_po->hpSelect,
		m_po->hpErr,
		1,									// number of rows to be fetched
		OCI_FETCH_NEXT, 					// the only value in Oracel 8
		OCI_DEFAULT )
		);

	// Set result to 0 if no data returned
	m_csResults = "";
	m_po->rsSelect.m_nRows = 0;
	if ( SUCCEEDED(m_po->hr) && m_po->hr != OCI_NO_DATA )
	{
		// Set number of rows to 1 so IsEOS() will fail
		m_po->rsSelect.m_nRows = 1;

		/* attention: not get result cstring */
	}

	return m_po->hr;
}

HRESULT COdb::FetchMultiNext( int num )
{
	//
	// Fetch next row of select statement and parse the select list description
	//

	CheckErr( OCIStmtFetch( m_po->hpSelect,
		m_po->hpErr,
		num,								// number of rows to be fetched
		OCI_FETCH_NEXT, 					// the only value in Oracel 8
		OCI_DEFAULT )
		);

	// Set result to 0 if no data returned
	m_po->rsSelect.m_nRows = 0;
	if ( SUCCEEDED(m_po->hr) && m_po->hr != OCI_NO_DATA )
	{
		// Set number of rows to 1 so IsEOS() will fail
		m_po->rsSelect.m_nRows = 1;

		/* attention: not get result cstring */
	}

	return m_po->hr;
}


BOOL COdb::IsEOS()
{
	//
	// Return FALSE if m_nRows is 1, otherwise TRUE
	//
	BOOL bIsEOS = TRUE;
	if ( m_po->rsSelect.m_nRows )
		bIsEOS = FALSE;
	return bIsEOS;
}

CString COdb::GetField( int iField )
{
	//
	// Return string from indexed field in rsSelect
	//
	if ( iField >= 0 && iField < m_po->rsSelect.paFields.GetSize() )
	{
		OdbField* pField= (OdbField*)m_po->rsSelect.paFields.GetAt( iField );
		return pField->Get();
	}
	return "";
}

CString COdb::GetField( CString csName )
{
	//
	// Return string from named field in rsSelect
	//
	for ( int iField = 0; iField < m_po->rsSelect.paFields.GetSize(); ++iField )
	{
		OdbField* pField= (OdbField*)m_po->rsSelect.paFields.GetAt( iField );
		if ( pField->csName.CompareNoCase(csName) == 0 )
			return pField->Get();
	}
	return "";
}

CString COdb::GetMultiRowField( CString csName, int Row )
{
	//
	// Return string from named field in rsSelect
	//
	for ( int iField = 0; iField < m_po->rsSelect.paFields.GetSize(); ++iField )
	{
		OdbField* pField= (OdbField*)m_po->rsSelect.paFields.GetAt( iField );
		if ( pField->csName.CompareNoCase(csName) == 0 )
			return pField->GetMultiRow( Row );
	}
	return "";
}

BOOL COdb::GetField( int iField, CString& csName, CString& csValue, BOOL bQuotesIfValueRequires )
{
	//
	// Return TRUE if iField in range, otherwise FALSE if iField is invalid
	// Get name and value, surround value with quotes if flag set
	//
	if ( iField >= 0 && iField < m_po->rsSelect.paFields.GetSize() )
	{
		OdbField* pField= (OdbField*)m_po->rsSelect.paFields.GetAt( iField );
		if ( bQuotesIfValueRequires && pField->bQuotedOnUpdate )
			csValue = "'" + ProcessQuotes(pField->Get()) + "'";
		else
			csValue = pField->Get();
		csName = pField->csName;
		return TRUE;
	}
	return FALSE;
}

void COdb::CleanWhitespace( CString& csStatement )
{
	//
	// Sometimes crlfs can make a statement execute improperly
	// Replace whitespace with spaces because Exec and Select balk at crlfs etc
	//
	char* pStatement = csStatement.GetBuffer(0);
	while ( *pStatement )
	{
		if ( strchr( "\r\n\t", *pStatement ) )
			*pStatement = ' ';
		++pStatement;
	}
	csStatement.ReleaseBuffer();
}

CString COdb::ProcessQuotes( CString csValue )
{
	//
	// If there is any chance the value contains quotes...
	// call this before placing quotes around the value
	//
	LPCTSTR szQuote = "\'";

	// Does the string contain any single quotes?
	if ( csValue.Find( szQuote ) >= 0 )
	{
		// Loop through every occurence of szQuote
		int n = csValue.SpanExcluding(szQuote).GetLength();
		while ( n < csValue.GetLength() )
		{
			// Insert the extra quote
			csValue = csValue.Left(n) + csValue[n] + csValue.Mid(n);

			// Increment n past the two quotes
			// This is where we start the next search from
			n += 2;

			// Note that csValue is now a char longer!
			// Check in case quote was the last char in the string
			if ( n >= csValue.GetLength() )
				break;

			// Increment span count to next quote or end of string
			n += csValue.Mid(n).SpanExcluding(szQuote).GetLength();
		}
	}

	return csValue;
}