sqldb2.cpp

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

// Gets the bookmark of the current cursor position
BOOL CSqlRecordset::GetBookmark(CSqlRecordset::SqlBookmark& rBookmark)
{
	ASSERT(IsScrollable());
	ASSERT(m_bUseBookmarks);
	SQLINTEGER nIndicator;

#ifndef TRACE_ROW_NUMBER
	if (!SqlCheck(::SQLGetData(GetHandle(), 0, SQL_C_LONG,
		&rBookmark, sizeof(SQLUINTEGER), &nIndicator)))
		return FALSE;
#else
	if (!SqlCheck(::SQLGetData(GetHandle(), 0, SQL_C_LONG,
		&rBookmark.nBookmark, sizeof(SQLUINTEGER), &nIndicator)))
		return FALSE;
	rBookmark.nRowNumber = m_nRowNumber + m_nRowIndex;
#endif

	return nIndicator != SQL_NULL_DATA;
}

// Moves to the position specified by the bookmark
BOOL CSqlRecordset::SetBookmark(const CSqlRecordset::SqlBookmark& rBookmark)
{
	ASSERT(IsScrollable());
	ASSERT(m_bUseBookmarks);
#ifndef TRACE_ROW_NUMBER
	if (!SetAttribute(SQL_ATTR_FETCH_BOOKMARK_PTR,
		(SQLPOINTER) &rBookmark))
		return FALSE;
	return FetchRowset(SQL_FETCH_BOOKMARK, 0);
#else
	if (!SetAttribute(SQL_ATTR_FETCH_BOOKMARK_PTR,
		(SQLPOINTER) &rBookmark.nBookmark))
		return FALSE;
	return FetchRowset(SQL_FETCH_BOOKMARK, rBookmark.nRowNumber);
#endif
}

// Updates the current row
BOOL CSqlRecordset::Update()
{
	ASSERT(!IsDeleted());
	ASSERT(IsUpdatable());
	if (WriteCache())			// copy the updates to cache
		return ExecuteSetPos(SQL_UPDATE, SQL_LOCK_NO_CHANGE);
	return TRUE;
}

// Deletes the current row
BOOL CSqlRecordset::Delete()
{
	ASSERT(!IsDeleted());
	ASSERT(IsUpdatable());
	return ExecuteSetPos(SQL_DELETE, SQL_LOCK_NO_CHANGE);
}

// Initializes the status of the result set.
void CSqlRecordset::InitResultset()
{
#ifdef TRACE_ROW_NUMBER
	m_nRowNumber = 0;
	m_nRowCount = GetRowCount();
#endif
	m_bBOF = TRUE;
	m_bEOF = FALSE;
}

// Builds the SQL statement to be executed
BOOL CSqlRecordset::BuildSQL(PCSTR pszCommand)
{
	if (sqlCmdTable == m_nCmdType)
	{
		// add column name to be selected
		m_strSQL = "SELECT ";
		if (!m_listFields.IsEmpty())
		{
			for (UINT n=0; n<m_listFields.GetSize(); n++)
			{
				m_strSQL += m_listFields.GetAt(n).m_strName;
				if (n < m_listFields.GetSize()-1)
					m_strSQL += ',';
			}
			m_strSQL += ' ';
		}
		else  m_strSQL += "* ";

		m_strSQL += "FROM ";
		m_strSQL += pszCommand;

		// add search condition:
		if (!m_strFilter.empty())
		{
			m_strSQL += " WHERE ";
			m_strSQL += m_strFilter;
		}

		if (!m_strSort.empty())
		{
			m_strSQL += " ORDER BY ";
			m_strSQL += m_strSort;
		}

		// determine if "FOR UPDATE" clause is required
		if (IsUpdatable())
		{
			DWORD dwPositionedStatements;
			SQLSMALLINT nSize;
			if (SqlCheck(::SQLGetInfo(m_pDB->GetHandle(), SQL_POSITIONED_STATEMENTS,
				&dwPositionedStatements, sizeof(dwPositionedStatements), &nSize)) &&
				(dwPositionedStatements & SQL_PS_SELECT_FOR_UPDATE) != 0)
				m_strSQL += " FOR UPDATE";
		}
	}
	else if (sqlCmdStoreProc == m_nCmdType)
	{
		m_strSQL = "{?=CALL ";	// use vendor escape clause to get return code
		m_strSQL += pszCommand;

		// add parameter list
		if (!m_listParams.IsEmpty())
		{
			m_strSQL += " (";
			for (UINT n=0; n<m_listParams.GetSize(); n++)
				m_strSQL += "?,";
			m_strSQL[m_strSQL.length()-1] = ')';
		}
		m_strSQL += '}';
	}
	else
	{
		m_strSQL = pszCommand;
	}

	return TRUE;
}

// Binds all parameters in parameter list to the statement handle
BOOL CSqlRecordset::BindParameters()
{
	if (m_nCmdType != sqlCmdStoreProc)
		return CSqlCommand::BindParameters();

	// add a parameter to retrieve the return code of the store proc
	CSqlParameter param;
	param.CreateParameter(SQL_INTEGER, SQL_PARAM_OUTPUT);
	m_listParams.Add(param);

	int nParams = (int)m_listParams.GetSize();
	if (!m_listParams[nParams-1].Bind(this, 1))
		return FALSE;

	// bind the other parameters
	nParams--;
	for (int n=0; n<nParams; n++)
		if (!m_listParams[n].Bind(this, n+2))
			return FALSE;
	return TRUE;
}

// Binds all fields in field list to the recordset
// Execute() must be called before this function.
BOOL CSqlRecordset::BindColumns()
{
	if (m_listFields.IsEmpty())
	{
		// get number of columns in the result set
		SQLSMALLINT nCols;
		if (!SqlCheck(::SQLNumResultCols(GetHandle(), &nCols)))
			return FALSE;

		if (!nCols)		// result set is empty
			return TRUE;

		// get column info from recordset
		CSqlField fd;
		for (SQLSMALLINT n=0; n<nCols; n++)
		{
			if (!fd.CreateField(this, n+1))
				return FALSE;
			m_listFields.Add(fd);
		}
	}
	else
	{
		// check if there's any file bindings
		for (int n=0; n<GetFields(); n++)
			if (m_listFields[n].m_bBindFile)
			{
				// don't cache file binding
				m_nCacheSize = 0;
				break;
			}
	}

	// allocate cache buffer
	ASSERT(!m_pRowset);
	ASSERT(GetFields() > 0);
	if (m_nCacheSize > 1)
		m_pRowset = new CSqlFieldCache[GetFields()];

	// set the size of rowset
	if (m_nCacheSize < 1 || !m_pRowset)
		m_nCacheSize = 1;
	if (!SetAttribute(SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN))
		return FALSE;
	if (!SetAttribute(SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)m_nCacheSize))
		return FALSE;

	if (CacheRowset())
	{
		// the actual rowset size may be changed by data source
		if (!GetAttribute(SQL_ATTR_ROW_ARRAY_SIZE, &m_nCacheSize))
			return FALSE;
	}

	// set the row status array pointer:
	ASSERT(m_nCacheSize >= 1);
	m_pRowStatus = new SQLUSMALLINT[m_nCacheSize];
	if (!m_pRowStatus)
		return FALSE;

	if (!SetAttribute(SQL_ATTR_ROW_STATUS_PTR, m_pRowStatus) ||
		!SetAttribute(SQL_ATTR_ROWS_FETCHED_PTR, &m_nRowsetSize))
		return FALSE;

	// bind columns
	for (int n=0; n<GetFields(); n++)
	{
		BOOL bOK;
		if (CacheRowset())
			bOK = m_pRowset[n].Create(this, n+1);
		else
			bOK = m_listFields[n].Bind(this, n+1);
		if (!bOK)
			return FALSE;
	}

	InitResultset();
	return TRUE;
}

// Unbinds fields
void CSqlRecordset::UnbindColumns()
{
	if (IsOpen())
	{
		SqlCheck(::SQLFreeStmt(GetHandle(), SQL_UNBIND));
		SetAttribute(SQL_ATTR_ROWS_FETCHED_PTR, NULL);
		SetAttribute(SQL_ATTR_ROW_STATUS_PTR, NULL);
	}

	delete[] m_pRowStatus;
	m_pRowStatus = NULL;
	delete[] m_pRowset;
	m_pRowset = NULL;

	ResetCache();
#ifdef TRACE_ROW_NUMBER
	m_nRowNumber = 0;
	m_nRowCount = 0;
#endif
}

// Executes SQLSetPos() to refresh, update, or delete rowset
BOOL CSqlRecordset::ExecuteSetPos(SQLUSMALLINT nOperation, SQLUSMALLINT nLockType)
{
	ASSERT(IsScrollable());
	ASSERT(CacheRowset() || !m_nRowIndex);

	// Preserve the rowset size (m_nRowsetSize) because SQLSetPos() might change its value.
	int nRowsetSize = m_nRowsetSize;
	BOOL bOK = SqlCheck(::SQLSetPos(GetHandle(), m_nRowIndex+1, nOperation, nLockType));
	m_nRowsetSize = nRowsetSize;
	if (!bOK)
		return FALSE;

	SQLUSMALLINT nStatus = GetRowStatus();
	if (nStatus == SQL_ROW_ERROR ||
		nStatus == SQL_ROW_NOROW)
		return FALSE;
	return TRUE;
}

// Fetchs a rowset from the recordset
BOOL CSqlRecordset::FetchRowset(SQLSMALLINT nOrientation, SQLINTEGER nRowPos)
{
	// When nOrientation is SQL_FETCH_PRIOR, and m_nRowNumber < Rowset Size, SQLFetchScroll
	// returns SQL_SUCCESS_WITH_INFO with SQLSTATE 01S06. In this case the returned
	// rowset overlaps the old rowset, and we need to adjust m_nRowNumber and m_nRowIndex

	// nNewIndex is the new value of m_nRowIndex when nOrientation == SQL_FETCH_PRIOR
	SQLINTEGER nNewIndex = 0;
#ifndef TRACE_ROW_NUMBER
	if (SQL_FETCH_PRIOR == nOrientation && CacheRowset())
	{
		ASSERT(0 == m_nRowIndex);
		GetAttribute(SQL_ATTR_ROW_NUMBER, &nNewIndex);
		if (nNewIndex > 1 && nNewIndex <= m_nCacheSize)		// the rowset will overlap
			nNewIndex--;
		else
			nNewIndex = 0;
	}
#else // TRACE_ROW_NUMBER
	// preserve the current rowset size before fetching
	int nRowCount = m_nRowsetSize;
	if (SQL_FETCH_BOOKMARK == nOrientation)
	{
		m_nRowNumber = nRowPos;	// the row number of the bookmark was preserved by GetBookmark()
		nRowPos = 0;			// this argument is only used for passing RowNumber here
	}

	if (SQL_FETCH_PRIOR == nOrientation && CacheRowset())
	{
		ASSERT(0 == m_nRowIndex);
		SQLINTEGER nRowNumber = 0;
		GetAttribute(SQL_ATTR_ROW_NUMBER, &nRowNumber);
		if (nRowNumber != 0)	// sometimes it works well
		{
			TRACE("Row info: %d, %d, %d\n", m_nRowCount, m_nRowNumber, nRowNumber);
			if (nRowNumber != m_nRowNumber)
				m_nRowNumber = nRowNumber;
		}
	}
#endif // TRACE_ROW_NUMBER

	// fetch the new rowset
	ResetCache();
	SQLRETURN nSqlRet = ::SQLFetchScroll(GetHandle(), nOrientation, nRowPos);
	if (!SqlCheck(nSqlRet))
	{
		m_bBOF = m_bEOF = TRUE;
		return FALSE;
	}

	// set the BOF/EOF flags
	if (SQL_NO_DATA_FOUND == nSqlRet)
	{
		if (SQL_FETCH_LAST == nOrientation ||
			SQL_FETCH_FIRST == nOrientation ||
			SQL_FETCH_BOOKMARK == nOrientation)
			m_bBOF = m_bEOF = TRUE;		// result set is empty
		else if (SQL_FETCH_PRIOR == nOrientation ||
			(SQL_FETCH_RELATIVE == nOrientation && nRowPos <= 0))
			m_bBOF = TRUE;
		else
			m_bEOF = TRUE;

#ifdef TRACE_ROW_NUMBER
		// this is the only way to know you've hit the end
		if (SQL_FETCH_NEXT == nOrientation)
		{
			m_nRowCount = m_nRowNumber + nRowCount - 1;
			if (m_nRowCount < 0)
				m_nRowCount = 0;
		}

		if (IsBOF())			// before start
			m_nRowNumber = 0;
		else					// after end
			m_nRowNumber = m_nRowCount + 1;
#endif
		ASSERT(!m_nRowsetSize);
		return TRUE;
	}

	m_bBOF = m_bEOF = FALSE;

#ifdef TRACE_ROW_NUMBER
	// determine the number of the first row in rowset
	switch (nOrientation)
	{
	case SQL_FETCH_FIRST:
		m_nRowNumber = 1;
		break;

	case SQL_FETCH_LAST:
		m_nRowNumber = m_nRowCount - m_nRowsetSize + 1;
		if (m_nRowNumber <= 0)
			m_nRowNumber = 1;
		break;

	case SQL_FETCH_NEXT:
		if (!m_nRowNumber)
			m_nRowNumber = 1;
		else
			m_nRowNumber += nRowCount;
		break;

	case SQL_FETCH_PRIOR:
		if (m_nRowNumber <= m_nRowsetSize)	// rowset overlapped
		{
			if (nSqlRet == SQL_SUCCESS_WITH_INFO)
				nNewIndex = m_nRowNumber - 1;
			m_nRowNumber = 1;
		}
		else
			m_nRowNumber -= m_nRowsetSize;
		break;

	case SQL_FETCH_RELATIVE:
		m_nRowNumber += nRowPos;
		break;

	case SQL_FETCH_BOOKMARK:
		//m_nRowNumber = nRowPos;
		break;

	//case SQL_FETCH_ABSOLUTE:
	default:
		ASSERT(FALSE);
		break;
	}

	// the row count could be changed
	nRowCount = m_nRowNumber + m_nRowsetSize - 1;
	if (m_nRowCount < nRowCount)
		m_nRowCount = nRowCount;

#endif // TRACE_ROW_NUMBER

	if (!CacheRowset())
		return TRUE;

	if (SQL_FETCH_LAST == nOrientation ||
		SQL_FETCH_PRIOR == nOrientation)
	{
		if (nNewIndex > 0 && nSqlRet == SQL_SUCCESS_WITH_INFO)
			return FetchCache(nNewIndex-1);		// rowset overlapped
		return FetchCache(m_nRowsetSize-1);
	}
	return FetchCache(0);
}

// Fetchs a specified row from the rowset cache:
BOOL CSqlRecordset::FetchCache(SQLINTEGER nRowIndex)
{
	ASSERT(TestCache(nRowIndex));
	ASSERT(CacheRowset());
	m_nRowIndex = nRowIndex;

	SQLUSMALLINT nStatus = GetRowStatus();
	if (nStatus == SQL_ROW_ERROR)
		return Refresh();		// try to refresh the current row
	ASSERT(nStatus != SQL_ROW_NOROW);
	if (nStatus == SQL_ROW_NOROW)
		return FALSE;

	ReadCache();
	return TRUE;
}

// Copies value from cache to all binding fields
void CSqlRecordset::ReadCache()
{
	if (!CacheRowset())
		return;

	for (int n=0; n<GetFields(); n++)
		m_pRowset[n].Read(m_nRowIndex);
}

// Copies value from from binding fields to cache
BOOL CSqlRecordset::WriteCache()
{
	if (!CacheRowset())
		return TRUE;

	BOOL bUpdate = FALSE;
	for (int n=0; n<GetFields(); n++)
	{
		if (m_pRowset[n].Write(m_nRowIndex))
			bUpdate = TRUE;
	}
	return bUpdate;
}

//////////////////////////////////////////////////////////////////////
// class CSqlErrorInfo - Represents a DB2 diagnostic record info
//////////////////////////////////////////////////////////////////////

CSqlErrorInfo::CSqlErrorInfo() :
	m_nErrorType(sqlInfo)
{
}

CSqlErrorInfo::CSqlErrorInfo(const CSqlErrorInfo& rEI) :
	m_nErrorType(sqlInfo)
{
	*this = rEI;
}

CSqlErrorInfo& CSqlErrorInfo::operator=(const CSqlErrorInfo& rEI)
{
	if (this != &rEI)
	{
		m_nErrorType = rEI.m_nErrorType;
		m_strDesc = rEI.m_strDesc;
		::memcpy(m_szSqlState, rEI.m_szSqlState, SQL_SQLSTATE_SIZE+1);
		m_nCode = rEI.m_nCode;
	}
	return *this;
}