odb.cpp

VC下通过Oracle OCI方式连接数据库。

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

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

	m_po->Clear();
	return m_po->hr;
}

HRESULT COdb::Exec( const CString csStatement )
{
	//
	// Execute statement
	//
	m_csLog += csStatement + "\n";

	// Allocate statement handle
	OCIStmt *hpStatement = NULL;
	CheckErr( OCIHandleAlloc( m_po->hpEnv,
		(void**)&hpStatement,
		OCI_HTYPE_STMT,
		0,
		NULL )
		);

	// Associate statement string with handle
	CString csCleanStatement = csStatement;
	CleanWhitespace( csCleanStatement );
	CheckErr( OCIStmtPrepare( hpStatement,
		m_po->hpErr,
		(text *)csCleanStatement.GetBuffer(0),
		csCleanStatement.GetLength(),
		OCI_NTV_SYNTAX,
		OCI_DEFAULT )
		);

	// Execute statement
	CheckErr( OCIStmtExecute( m_po->hpContext,
		hpStatement,
		m_po->hpErr,
		1, // iters
		0, // row offset
		NULL, NULL, // snapshot in/out
		OCI_DEFAULT )
		);

	// Free statement handle
	if ( hpStatement )
		OCIHandleFree( 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,
		m_po->hpSelect, // prepared by previous function calls
		m_po->hpErr,
		0, // 'iters' i.e. max rows to fetch during this call
		0, 
		NULL, NULL, 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, // 'nrows' i.e. max rows
		OCI_FETCH_NEXT, 
		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;

		// TEMPORARY: create a results string
		for ( int iField=0; iField<m_po->rsSelect.paFields.GetSize(); ++iField )
		{
			OdbField* pField = (OdbField*)m_po->rsSelect.paFields[iField];
			if ( ! m_csResults.IsEmpty() )
				m_csResults += ", ";
			m_csResults += pField->csName + "=" + GetField( iField );
		}
	}

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

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