sqldb2.cpp

以OLE DB风格访问DB2数据库的C++类源码

//////////////////////////////////////////////////////////////////////
//
// DB2 Access Object Version 1.0
//
// Developer: Jeff Lee
// Jan 10, 2003
//
//////////////////////////////////////////////////////////////////////

#include "Sqldb2.h"

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

#pragma warning(disable:4786)
#pragma warning(disable:4312)

//////////////////////////////////////////////////////////////////////
// class CSqlObject - base class to wrap DB2 CLI handles
//////////////////////////////////////////////////////////////////////

CSqlObject::CSqlObject() :
	m_nRefs(1),
	m_nHandleType(SQL_UNKNOWN_TYPE),
	m_hSql(SQL_NULL_HANDLE),
	m_pDB(NULL)
{
}

CSqlObject::~CSqlObject()
{
	Close();
}

// Closes the handle and releases resources
void CSqlObject::Close()
{
	// delete all the remained error objects:
	while (!m_listErrors.empty())
	{
		CSqlErrorInfo *pEI = m_listErrors.front();
		m_listErrors.pop_front();
		ASSERT(pEI != NULL);
		delete pEI;
	}

	// close the handle
	if (m_hSql != SQL_NULL_HANDLE)
	{
		::SQLFreeHandle(m_nHandleType, m_hSql);
		m_hSql = SQL_NULL_HANDLE;
		m_nHandleType = SQL_UNKNOWN_TYPE;
	}

	DetachDatabase();
}

// Sets a attribute of the handle
BOOL CSqlObject::SetAttribute(SQLINTEGER nAttr, SQLPOINTER pValue, SQLINTEGER nValueSize)
{
	ASSERT(IsOpen());
	SQLRETURN nSqlRet;
	switch (m_nHandleType)
	{
	case SQL_HANDLE_DBC:
		nSqlRet = ::SQLSetConnectAttr(m_hSql, nAttr, pValue, nValueSize);
		break;

	case SQL_HANDLE_STMT:
		nSqlRet = ::SQLSetStmtAttr(m_hSql, nAttr, pValue, nValueSize);
		break;

	default:
		ASSERT(FALSE);
		break;
	}
	return SqlCheck(nSqlRet);
}

// Gets the current setting of a attribute of the handle
BOOL CSqlObject::GetAttribute(SQLINTEGER nAttr, SQLPOINTER pValue,
							  SQLINTEGER nBuffSize, SQLINTEGER* pnValueSize)
{
	ASSERT(IsOpen());
	SQLRETURN nSqlRet;
	switch (m_nHandleType)
	{
	case SQL_HANDLE_DBC:
		nSqlRet = ::SQLGetConnectAttr(m_hSql, nAttr, pValue, nBuffSize, pnValueSize);
		break;

	case SQL_HANDLE_STMT:
		nSqlRet = ::SQLGetStmtAttr(m_hSql, nAttr, pValue, nBuffSize, pnValueSize);
		break;

	default:
		ASSERT(FALSE);
		break;
	}
	return SqlCheck(nSqlRet);
}

// Decrements the reference count. Frees resources if reference count reaches 0.
int CSqlObject::Release()
{
	int nRefs = --m_nRefs;
	if (!nRefs)
		delete this;
	return nRefs;
}

// Attachs the object to a database connection
void CSqlObject::AttachDatabase(CSqlDatabase* pDB)
{
	ASSERT(pDB != NULL);
	if (this != pDB)
	{
		pDB->AddRef();
		AddRef();
		DetachDatabase();
		pDB->m_listObjects.insert(this);
	}
	m_pDB = pDB;
}

// Detachs the object from the database connection
void CSqlObject::DetachDatabase()
{
	if (m_pDB != NULL && m_pDB != this)
	{
		m_pDB->m_listObjects.erase(this);
		m_pDB->Release();
		m_pDB = NULL;
		Release();
	}
}

// Gets the last-error occured on this object
BOOL CSqlObject::GetLastError(CSqlErrorInfo& rErrorInfo)
{
	if (!m_listErrors.empty())
	{
		CSqlErrorInfo* pEI = m_listErrors.front();
		ASSERT(pEI != NULL);
		rErrorInfo = *pEI;
		m_listErrors.pop_front();
		delete pEI;
		return TRUE;
	}
	return FALSE;
}

// Checks the return code of a DB2 CLI function call
BOOL CSqlObject::SqlCheck(SQLRETURN nSqlRet)
{
	switch (nSqlRet)
	{
	case SQL_INVALID_HANDLE:
		TRACE("Invalid handle %08x\n", m_hSql);
		ASSERT(FALSE);		// it's a programmatic error
		break;

	case SQL_SUCCESS:
	case SQL_STILL_EXECUTING:
	case SQL_NEED_DATA:
	case SQL_NO_DATA_FOUND:
		return TRUE;

	case SQL_SUCCESS_WITH_INFO:
		GetErrorInfo();
		return TRUE;

	case SQL_ERROR:
	default:
		GetErrorInfo();
		break;
	}

	return FALSE;
}

// Gets diagnostic records of the previous DB2 CLI API call, and adds them to error list
void CSqlObject::GetErrorInfo()
{
	SQLSMALLINT nSize, nRec = 1;
	SQLCHAR szMsg[SQL_MAX_MESSAGE_LENGTH+1];
	SQLCHAR szState[SQL_SQLSTATE_SIZE+1];
	SQLINTEGER nCode;

	ASSERT(m_hSql != SQL_NULL_HANDLE);
	while(::SQLGetDiagRec(m_nHandleType, m_hSql, nRec, szState, &nCode,
		szMsg, SQL_MAX_MESSAGE_LENGTH+1, &nSize) == SQL_SUCCESS)
	{
		CSqlErrorInfo* pdbe = new CSqlErrorInfo;
		pdbe->m_nCode = nCode;
		pdbe->m_strDesc = (const char*)szMsg;
		::memcpy(pdbe->m_szSqlState, szState, SQL_SQLSTATE_SIZE+1);
		pdbe->m_nErrorType =
			(szState[0] == '0' && szState[1] == '1') ?
			CSqlErrorInfo::sqlInfo : CSqlErrorInfo::sqlError;

		OnSqlError(pdbe);
		nRec++;
	}
}

void CSqlObject::OnSqlError(CSqlErrorInfo* pErrorInfo)
{
	m_listErrors.push_back(pErrorInfo);
}

//////////////////////////////////////////////////////////////////////
// class CSqlDatabase - Represents a connection to a database
//////////////////////////////////////////////////////////////////////

SQLHANDLE CSqlDatabase::m_henv = SQL_NULL_HANDLE;

CSqlDatabase::CSqlDatabase() :
	m_dwOption(0)
{
}

CSqlDatabase::~CSqlDatabase()
{
	Close();
}

// Global initialization - allocates and initializes the DB2 environment handle
BOOL CSqlDatabase::Initialize(BOOL bMultiThread)
{
	if (SQL_NULL_HANDLE == m_henv)
	{
		if (!bMultiThread)
			SetEnvAttr(SQL_ATTR_PROCESSCTL,
			SQL_PROCESSCTL_NOTHREAD | SQL_PROCESSCTL_NOFORK);

		SQLRETURN nSqlRet = ::SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
		if (nSqlRet != SQL_SUCCESS)
		{
			TRACE0("Fail to allocate DB2 CLI environment handle");
			return FALSE;
		}
	}
	return TRUE;
}

// Global cleanup - closes the environment handle and frees any resources
void CSqlDatabase::Uninitialize()
{
	if (m_henv != SQL_NULL_HANDLE)
	{
		::SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
		m_henv = SQL_NULL_HANDLE;
	}
}

// Sets the attribute of DB2 CLI environment
BOOL CSqlDatabase::SetEnvAttr(SQLINTEGER nAttr, SQLINTEGER nValue)
{
	SQLRETURN nSqlRet = ::SQLSetEnvAttr(m_henv, nAttr, (SQLPOINTER) nValue, 0);
	return nSqlRet == SQL_SUCCESS;
}

// Gets the attribute of DB2 CLI environment
BOOL CSqlDatabase::GetEnvAttr(SQLINTEGER nAttr, SQLINTEGER& nValue)
{
	SQLRETURN nSqlRet = ::SQLGetEnvAttr(m_henv, nAttr, (SQLPOINTER) &nValue, 0, NULL);
	return nSqlRet == SQL_SUCCESS;
}

// Establishes a connection to a database
// Arguments:
//	dwOption - Specifies the attribute of the database connection. It can be 0 or
//		a combination of the following values:
//		readOnly:
//			Specifies the connection is read-only. By default it is read-write
//		manualCommit:
//			The application must manually, explicitly commit or rollback transactions
//			with CommitTrans() or RollbackTrans() calls. By default DB2 implicitly
//			commits each statement automatically.
//		autoUnlock:
//			Specifies the read locks are released when the cursor is closed.
//			By default the read locks are not released automatically.
//	dwTxnIsolation - Sets the transaction isolation level. See DB2 documentation
//		about Transaction Isolation Level.
BOOL CSqlDatabase::Connect(PCSTR pszDB, PCSTR pszUser, PCSTR pszPwd,
						   DWORD dwOption, DWORD dwTxnIsolation)
{
	// close the existing connection
	Close();
	AttachDatabase(this);

	// allocate environment handle
	//if (!Initialize())
	//	return FALSE;

	// allocate a connection handle
	m_nHandleType = SQL_HANDLE_DBC;
	ASSERT(m_henv != SQL_NULL_HANDLE);
	SQLRETURN nSqlRet = ::SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hSql);
	if (!SqlCheck(nSqlRet))
		return FALSE;

	// connect to the database
	nSqlRet = ::SQLConnect(m_hSql,
		(SQLCHAR*)pszDB, SQL_NTS,
		(SQLCHAR*)pszUser, SQL_NTS,
		(SQLCHAR*)pszPwd, SQL_NTS);
	if (!SqlCheck(nSqlRet))
		return FALSE;

	// set connection attributes:
	m_dwOption = dwOption;
	if (dwOption & readOnly)
		SetAttribute(SQL_ATTR_ACCESS_MODE, (SQLPOINTER)SQL_MODE_READ_ONLY);
	if (dwOption & manualCommit)
		SetAttribute(SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF);
	if (dwOption & autoUnlock)
		SetAttribute(SQL_ATTR_CLOSE_BEHAVIOR, (SQLPOINTER)SQL_CC_RELEASE);
	if (!SetAttribute(SQL_ATTR_TXN_ISOLATION, (SQLPOINTER)dwTxnIsolation))
		return FALSE;

	return TRUE;
}

// Closes a database connection
void CSqlDatabase::Close()
{
	// close all open statement objects
	while (!m_listObjects.empty())
	{
		CSqlObject* pSqlObj = *m_listObjects.begin();
		ASSERT(pSqlObj != NULL);
		pSqlObj->Close();
	}

	if (m_hSql != SQL_NULL_HANDLE)
		::SQLDisconnect(m_hSql);
	CSqlObject::Close();
}

// Begins a new transaction
BOOL CSqlDatabase::BeginTrans()
{
	if (!(m_dwOption & manualCommit))
		return SqlCheck(SetAttribute(SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF));
	return TRUE;
}

// Saves any changes and ends the current transaction
BOOL CSqlDatabase::CommitTrans()
{
	BOOL bOK = SqlCheck(::SQLTransact(m_henv, m_hSql, SQL_COMMIT));
	if (!(m_dwOption & manualCommit))
		SetAttribute(SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON);
	return bOK;
}

// Cancels any changes made during the current transaction and ends the transaction
BOOL CSqlDatabase::RollbackTrans()
{
	BOOL bOK = SqlCheck(::SQLTransact(m_henv, m_hSql, SQL_ROLLBACK));
	if (!(m_dwOption & manualCommit))
		SetAttribute(SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON);
	return bOK;
}

//////////////////////////////////////////////////////////////////////
// class CSqlCommand - Defines a specific command (SQL statement)
//////////////////////////////////////////////////////////////////////

CSqlCommand::CSqlCommand() :
	m_dwOption(0),
	m_listParams(16)
{
}

CSqlCommand::~CSqlCommand()
{
	Close();
}

// Creates and initializes a CSqlCommand object
// Arguments:
//	pDB - The pointer to the database connection that the command runs upon.
//	dwOption - Specifies the command option. It can be 0 or a combination of
//		the following values:
//		execDirect:
//			Executes command directly without preparation. By default the SQL
//			statement is sent to DBMS to be prepared (equivalent to compile).
//			Use this option if the command is to be executed only once.
//		autoCloseCursor:
//			Automatically closes an open cursor if a second cursor is opened.
//			By default, DB2 CLI doesn't chain the close and open statements.
//		nonScanEscape:
//			Disables the scan of SQL string for escape clauses. This will
//			eliminate some of the scan overhead.
//			See DB2 documentation about Vendor Escape Clauses.
//		preFetch:
//			Tells the server to prefetch the next block of data immediately
//			after sending the current block.
BOOL CSqlCommand::Create(CSqlDatabase* pDB, DWORD dwOption)
{
	// attach to database connect
	ASSERT(pDB != NULL);
	ASSERT(pDB->IsOpen());
	Close();
	AttachDatabase(pDB);

	// allocate stmt handle
	m_nHandleType = SQL_HANDLE_STMT;
	SQLRETURN nSqlRet = ::SQLAllocHandle(SQL_HANDLE_STMT, m_pDB->GetHandle(), &m_hSql);
	if (!m_pDB->SqlCheck(nSqlRet))
		return FALSE;

	// set command option
	m_dwOption = dwOption;
	if (m_dwOption & autoCloseCursor)
		SetAttribute(SQL_ATTR_CLOSEOPEN, (SQLPOINTER)1);
	if (m_dwOption & nonScanEscape)
		SetAttribute(SQL_ATTR_NOSCAN, (SQLPOINTER) SQL_NOSCAN_ON);
	if (m_dwOption & preFetch)
		SetAttribute(SQL_ATTR_PREFETCH, (SQLPOINTER) SQL_PREFETCH_ON);

	SetAttribute(SQL_ATTR_DEFERRED_PREPARE, (SQLPOINTER)SQL_DEFERRED_PREPARE_ON);
	SetAttribute(SQL_ATTR_CURSOR_HOLD, (SQLPOINTER) SQL_CURSOR_HOLD_ON);
	return TRUE;
}

// Closes the command object
void CSqlCommand::Close()
{
	//if (m_hSql != SQL_NULL_HANDLE)
	//{
	//	::SQLFreeStmt(GetHandle(), SQL_UNBIND);
	//	::SQLFreeStmt(GetHandle(), SQL_RESET_PARAMS);
	//	::SQLFreeStmt(GetHandle(), SQL_CLOSE);
	//}
	m_listParams.RemoveAll();
	CSqlObject::Close();
}

// Unbinds parameters and resets the command handle for reuse
void CSqlCommand::Reset()
{
	if (IsOpen())
	{
		// close any open cursors
		if (!(m_dwOption & autoCloseCursor))
			::SQLFreeStmt(GetHandle(), SQL_CLOSE);
		// unbind parameters
		SqlCheck(::SQLFreeStmt(GetHandle(), SQL_RESET_PARAMS));
	}

	m_strSQL.erase();
	m_listParams.RemoveAll();
}

// Specifies SQL statement to be executed, and prepare the statetment
// if execDirect option not specified
BOOL CSqlCommand::SetCommand(PCSTR lpszSQL)
{
	if (!BindParameters())
		return FALSE;

	if (m_strSQL != lpszSQL)
		m_strSQL = lpszSQL;
	return (m_dwOption & execDirect) ? TRUE :
		SqlCheck(::SQLPrepare(m_hSql, (SQLCHAR*)lpszSQL, SQL_NTS));
}

// Executes the SQL statement
BOOL CSqlCommand::Execute()
{
	ASSERT(IsOpen());
	TRACE("Execute SQL statement:\n%s\n", m_strSQL.data());
	return SqlCheck((m_dwOption & execDirect) ?
		::SQLExecDirect(m_hSql, (SQLCHAR*)m_strSQL.data(), SQL_NTS) :
		::SQLExecute(m_hSql));
}

// Returns the count of rows that were affected by the SQL statement
// SELECT, UPDATE, INSERT, or DELETE
SQLINTEGER CSqlCommand::GetRowCount()
{
	SQLINTEGER nRowCount;
	if (!SqlCheck(::SQLRowCount(m_hSql, &nRowCount)))
		nRowCount = -1;
	else if (nRowCount < 0)
		nRowCount = 0;
	return nRowCount;
}

// Binds all parameters in the parameter list to the statement handle
BOOL CSqlCommand::BindParameters()
{
	ASSERT(IsOpen());
	for (int n=0; n<(int)m_listParams.GetSize(); n++)
		if (!m_listParams[n].Bind(this, n+1))
			return FALSE;
	return TRUE;
}