sqldb2.cpp

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

//////////////////////////////////////////////////////////////////////
// class CSqlLocator - Represents a locator to operate BLOB data
//////////////////////////////////////////////////////////////////////

CSqlLocator::CSqlLocator() :
	m_nType(SQL_UNKNOWN_TYPE),
	m_nLocator(0)
{
}

// Initializes a locator object
// Arguments:
//	pDB - Specifies the database connection that the command to manipulate locator runs upon.
//	rVariant - Specifies the column or parameter whose data type is locator
BOOL CSqlLocator::Open(CSqlDatabase* pDB, const CSqlVariant& rVariant)
{
	if (!m_cmd.IsOpen() && m_cmd.GetDatabase() != pDB)
	{
		if (!m_cmd.Create((pDB), CSqlCommand::execDirect))
			return FALSE;
	}

	m_nType = rVariant.GetDataType();
	m_nLocator = (int) rVariant;
	ASSERT(m_nType == SQL_C_BLOB_LOCATOR ||
		m_nType == SQL_C_CLOB_LOCATOR ||
		m_nType == SQL_C_DBCLOB_LOCATOR);
	return TRUE;
}

// Returns the length of the LOB data
SQLINTEGER CSqlLocator::GetLength()
{
	SQLINTEGER nLength, nIndicator;
	if (!m_cmd.SqlCheck(::SQLGetLength(m_cmd.GetHandle(), m_nType,
		m_nLocator, &nLength, &nIndicator)))
		return -1;
	return nLength;
}

// Gets a portion from the LOB data
SQLINTEGER CSqlLocator::GetSubString(SQLPOINTER pBuff, SQLINTEGER nStartPos, SQLINTEGER nLength)
{
	SQLSMALLINT nTargetType;
	SQLINTEGER nBuffSize = nLength + 1;		// assume the size is enough
	SQLINTEGER nStringLength, nIndicator;
	switch (m_nType)
	{
	case SQL_C_BLOB_LOCATOR:
		nTargetType = SQL_C_BINARY;
		break;
	case SQL_C_CLOB_LOCATOR:
		nTargetType = SQL_C_CHAR;
		break;
	case SQL_C_DBCLOB_LOCATOR:
		nTargetType = SQL_C_DBCHAR;
		nBuffSize <<= 1;	// double-bytes string
		break;
	default:
		ASSERT(FALSE);
	}

	if (!m_cmd.SqlCheck(::SQLGetSubString(m_cmd.GetHandle(), m_nType, m_nLocator,
		nStartPos, nLength, nTargetType, pBuff, nBuffSize, &nStringLength, &nIndicator)))
		return -1;
	return nStringLength;
}

// Frees the locator object
BOOL CSqlLocator::Free()
{
	if (m_cmd.IsOpen())
	{
		if (m_nType != SQL_UNKNOWN_TYPE)
		{
			m_cmd.Reset();
			CSqlParameter param;
			param.CreateParameter(m_nType, SQL_PARAM_INPUT);
			m_cmd.Parameters().Add(param);
			param = (int) m_nLocator;

			m_cmd.SetCommand("FREE LOCATOR ?");
			if (!m_cmd.Execute())
				return FALSE;

			m_nType = SQL_UNKNOWN_TYPE;
			m_nLocator = 0;
		}
		m_cmd.Close();
	}
	return TRUE;
}

//////////////////////////////////////////////////////////////////////
// class CSqlFieldCache - the internal class to cache a column
//////////////////////////////////////////////////////////////////////

class CSqlFieldCache
{
public:
	CSqlFieldCache();
	CSqlFieldCache(const CSqlFieldCache& rData);
	~CSqlFieldCache();

private:
	CSqlField* m_pfd;			// the field object associated with this cache
	SQLINTEGER m_nFieldSize;	// field length
	SQLSMALLINT m_nCacheSize;	// number of rows in the cache

	CFixedString<SQLCHAR> m_fields;
	SQLINTEGER* m_pIndicators;	// array of indicators

public:
	BOOL Create(CSqlRecordset* pSet, int nOrdinal);
	void Destroy();
	CSqlFieldCache& operator=(const CSqlFieldCache& rData);

	SQLINTEGER& GetIndicator(SQLSMALLINT nIndex) const;
	SQLCHAR* GetField(SQLSMALLINT nIndex) const;
	void Read(SQLSMALLINT nIndex) const;
	BOOL Write(SQLSMALLINT nIndex);
};

CSqlFieldCache::CSqlFieldCache() :
	m_pfd(NULL),
	m_nFieldSize(0),
	m_nCacheSize(0),
	m_pIndicators(NULL)
{
}

CSqlFieldCache::CSqlFieldCache(const CSqlFieldCache& rData)
{
	*this = rData;
}

CSqlFieldCache::~CSqlFieldCache()
{
	Destroy();
}

// Creates a field cache, allocates field array, and binds to recordset
BOOL CSqlFieldCache::Create(CSqlRecordset* pSet, int nOrdinal)
{
	Destroy();
	ASSERT(pSet != NULL);
	ASSERT(nOrdinal > 0 && nOrdinal <= (int)pSet->Fields().GetSize());
	m_pfd = &(pSet->Fields().GetAt(nOrdinal-1));

	ASSERT(!m_pfd->IsDefault());
	ASSERT(pSet->GetCacheSize() >= 1);
	ASSERT(!m_pfd->m_bBindFile);	// not supported

	// allocate cache buffer
	m_nFieldSize = m_pfd->GetMaxSize();
	m_nCacheSize = pSet->GetCacheSize();

	int nFieldsSize = m_nFieldSize * m_nCacheSize;
	if (!m_fields.Alloc(nFieldsSize + sizeof(SQLINTEGER) * m_nCacheSize))
		return FALSE;
	m_pIndicators = (SQLINTEGER*) (m_fields.GetData() + nFieldsSize);

	// bind to recordset
	if (m_pfd->m_nOrdinal != 0)
		nOrdinal = m_pfd->m_nOrdinal;
	return pSet->SqlCheck(::SQLBindCol(pSet->GetHandle(), nOrdinal,
		m_pfd->m_pData->nDataType, m_fields.GetData(), m_nFieldSize, m_pIndicators));
}

// Frees the cache buffer
void CSqlFieldCache::Destroy()
{
	m_fields.Free();
	m_pIndicators = NULL;
	m_nCacheSize = 0;
	m_pfd = NULL;
}

CSqlFieldCache& CSqlFieldCache::operator=(const CSqlFieldCache& rData)
{
	if (this != &rData)
	{
		m_fields = rData.m_fields;
		m_pfd = rData.m_pfd;
		m_nFieldSize = rData.m_nFieldSize;
		m_nCacheSize = rData.m_nCacheSize;
		m_pIndicators = rData.m_pIndicators;
	}
	return *this;
}

SQLINTEGER& CSqlFieldCache::GetIndicator(SQLSMALLINT nIndex) const
{
	ASSERT(nIndex < m_nCacheSize && nIndex >= 0);
	return m_pIndicators[nIndex];
}

SQLCHAR* CSqlFieldCache::GetField(SQLSMALLINT nIndex) const
{
	ASSERT(nIndex < m_nCacheSize && nIndex >= 0);
	return m_fields.GetData()+nIndex*m_nFieldSize;
}

// Reads the data from cache to the corresponding field object
void CSqlFieldCache::Read(SQLSMALLINT nIndex) const
{
	ASSERT(m_pfd != NULL);
	m_pfd->m_pData->nIndicator = GetIndicator(nIndex);
	::memcpy(m_pfd->m_pData->pVal, GetField(nIndex), m_nFieldSize);
}

// Writes the value of the corresponding field object to cache buffer
BOOL CSqlFieldCache::Write(SQLSMALLINT nIndex)
{
	ASSERT(m_pfd != NULL);
	// this function is called only for updating row with SQLSetPos().
	//GetIndicator(nIndex) = m_pfd->GetLength();
	//::memcpy(GetField(nIndex), m_pfd->GetBuffer(), m_nFieldSize);
	//return TRUE;
	if (m_pfd->GetLength() != GetIndicator(nIndex) ||
		::memcmp(GetField(nIndex), m_pfd->GetBuffer(), m_pfd->GetLength()))
	{
		GetIndicator(nIndex) = m_pfd->GetLength();
		::memcpy(GetField(nIndex), m_pfd->GetBuffer(), m_nFieldSize);
		return TRUE;
	}

	GetIndicator(nIndex) = SQL_COLUMN_IGNORE;
	return FALSE;
}

//////////////////////////////////////////////////////////////////////
// class CSqlRecordset - Represents a set of records selected from
// table(s) or the results of an executed command
//////////////////////////////////////////////////////////////////////

CSqlRecordset::CSqlRecordset() :
	m_nCmdType(sqlCmdSQL),
	m_bUseBookmarks(FALSE),
	m_nCursorType(SQL_CURSOR_STATIC),
	m_nMaxRows(0),
	m_bBOF(FALSE),
	m_bEOF(FALSE),
	m_nCacheSize(0),
	m_pRowset(NULL),
	m_pRowStatus(NULL)
{
}

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

// Opens a recordset by executing a query, store procedure or generic SQL statement.
// Arguments:
//	pszCommand - String pointer of the command to be executed.
//	nCmdType - Specifies how the command argument nCmdType should be interpreted:
//		sqlCmdSQL:
//			Evaluates the command as a SQL statement.
//		sqlCmdTable:
//			Evaluates the command as a table name.
//		sqlCmdStoreProc:
//			Evaluates the command as a store procedure name.
//	nCursorType - Specifies the type of cursor.
//		SQL_CURSOR_FORWARD_ONLY
//		SQL_CURSOR_STATIC (default)
//		SQL_CURSOR_KEYSET_DRIVEN
//		See DB2 documentation about Cursor Type
//	bUseBookmarks - Specifies whether enable bookmark.
//		This argument is ignored if nCursorType is set to SQL_CURSOR_FORWARD_ONLY.
BOOL CSqlRecordset::Open(PCSTR pszCommand, int nCmdType, DWORD nCursorType, BOOL bUseBookmarks)
{
	ASSERT(IsOpen());			// make sure Create() has been called

	ASSERT(nCmdType != sqlCmdStoreProc ||
		nCursorType == SQL_CURSOR_FORWARD_ONLY);
	if (sqlCmdStoreProc == nCmdType)
	{
		//nCursorType = SQL_CURSOR_FORWARD_ONLY;
		// we must use vendor escape clause to get return code of store proc
		SetAttribute(SQL_ATTR_NOSCAN, (SQLPOINTER) SQL_NOSCAN_OFF);
	}
	m_nCursorType = nCursorType;
	::SQLFreeStmt(GetHandle(), SQL_CLOSE);
	if (!SetAttribute(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)nCursorType))
		return FALSE;

	if (!IsScrollable())
	{
		ASSERT(!bUseBookmarks);
		bUseBookmarks = FALSE;
	}
	m_bUseBookmarks = bUseBookmarks;
	if (!SetAttribute(SQL_ATTR_USE_BOOKMARKS,
		(SQLPOINTER) (bUseBookmarks ? SQL_UB_VARIABLE : SQL_UB_OFF)))
		return FALSE;

	m_nCmdType = nCmdType;
	BuildSQL(pszCommand);		// build SQL statement
	if (!SetCommand(m_strSQL.data()))	// bind parameters
		return FALSE;

	UnbindColumns();
	ResetCache();
	if (!Execute())				// execute the command
		return FALSE;

	return BindColumns();		// bind columns
}

// Re-executes the current command to refresh the entire result set.
BOOL CSqlRecordset::Requery()
{
	if (!(m_dwOption & autoCloseCursor) && IsOpen())
		::SQLFreeStmt(GetHandle(), SQL_CLOSE);

	ResetCache();
	if (!Execute())
		return FALSE;

	InitResultset();
	return TRUE;
}

// Clears the current recordset, and returns the next recordset by advancing
// through a series of commands
BOOL CSqlRecordset::NextRecordset()
{
	UnbindColumns();
	m_listFields.RemoveAll();

	SQLRETURN nSqlRet = ::SQLMoreResults(GetHandle());
	if (nSqlRet == SQL_NO_DATA_FOUND)
		return FALSE;
	if (!SqlCheck(nSqlRet))
		return FALSE;

	return BindColumns();
}

// Closes the object and releases any resources.
void CSqlRecordset::Close()
{
	UnbindColumns();
	m_listFields.RemoveAll();
	CSqlCommand::Close();
}

// Resets the command handle for reuse
void CSqlRecordset::Reset()
{
	UnbindColumns();
	ResetCache();
	m_listFields.RemoveAll();
	CSqlCommand::Reset();
}

// Retrieves all the parameters associated with a procedure and add them to parameter list.
// This function must be called before Open().
BOOL CSqlRecordset::AutoBindProcParams(PCSTR pszProcName, PCSTR pszSchemaName)
{
	ASSERT(IsOpen());			// ensure Create() has been called
	ASSERT(!GetFields());
	Reset();

//#ifdef _DEBUG
//	CSqlDatabase::SetEnvAttr(SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA, SQL_FALSE);
//#endif
	SetAttribute(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_FORWARD_ONLY);
	BOOL bOK = FALSE;
	if (SqlCheck(::SQLProcedureColumns(GetHandle(), NULL, 0,
		(SQLCHAR*) pszSchemaName, (pszSchemaName ? SQL_NTS : 0),
		(SQLCHAR*) pszProcName, SQL_NTS,
		(SQLCHAR*) "%", SQL_NTS)))
	{
		// bind necessary columns
		CSqlField fd;
		fd.CreateField(this, 4);	// parameter name
		m_listFields.Add(fd);
		fd.CreateField(this, 5);	// input/output type
		m_listFields.Add(fd);
		fd.CreateField(this, 6);	// SQL data type
		m_listFields.Add(fd);
		fd.CreateField(this, 8);	// precision
		m_listFields.Add(fd);
		fd.CreateField(this, 10);	// scale
		m_listFields.Add(fd);
		fd.CreateField(this, 18);	// ordinal number of the parameter
		m_listFields.Add(fd);

		if (BindColumns())
		{
			bOK = MoveNext();
			while (bOK && !IsEOF())
			{
				CSqlParameter param;
				param.CreateParameter(
					(short) m_listFields[2],
					(short) m_listFields[1],
					(int) m_listFields[3],
					(short) m_listFields[4]);
				m_listParams.Add(param);
				bOK = MoveNext();
			}

			if (!bOK)
				m_listParams.RemoveAll();
		}
	}

	UnbindColumns();
	m_listFields.RemoveAll();
	::SQLFreeStmt(GetHandle(), SQL_CLOSE);
	return bOK;
}

// Specifies the maximum number of rows to return from a query.
// The default value 0 means no limit.
// This function must be called before Open().
BOOL CSqlRecordset::SetMaxRows(DWORD nMaxRows)
{
	if (nMaxRows == m_nMaxRows)
		return TRUE;

	m_nMaxRows = nMaxRows;
	if (nMaxRows != 0)
		SetAttribute(SQL_ATTR_OPTIMIZE_FOR_NROWS, (SQLPOINTER)nMaxRows);
	return SetAttribute(SQL_ATTR_MAX_ROWS, (SQLPOINTER)nMaxRows);
}

// Specifies the number of rows would be cached in the local memory.
// This function must be called before Open().
void CSqlRecordset::SetCacheSize(int nCacheSize)
{
	ASSERT(!m_pRowset);
	m_nCacheSize = nCacheSize;
}

// Returns the return code (specified by a RETURN statement) of a stored procedure
int CSqlRecordset::GetRetStatus() const
{
	if (m_nCmdType != sqlCmdStoreProc || m_listParams.IsEmpty())
	{
		ASSERT(FALSE);
		return 0;
	}
	return (int)m_listParams[m_listParams.GetSize()-1];
}

// Moves the cursor position to the row nOffset from the current row.
BOOL CSqlRecordset::Move(SQLINTEGER nOffset, BOOL bRefresh)
{
	ASSERT(IsScrollable());
	SQLINTEGER nPos = m_nRowIndex + nOffset;
	if (!bRefresh && TestCache(nPos))
		return FetchCache(nPos);

	return FetchRowset(SQL_FETCH_RELATIVE, nPos);
}

// Moves to the first row
BOOL CSqlRecordset::MoveFirst()
{
	ASSERT(IsScrollable());
	return FetchRowset(SQL_FETCH_FIRST, 0);
}

// Moves to the last row
BOOL CSqlRecordset::MoveLast()
{
	ASSERT(IsScrollable());
	return FetchRowset(SQL_FETCH_LAST, 0);
}

// Moves to the previous row
BOOL CSqlRecordset::MovePrevious()
{
	ASSERT(IsScrollable());
	SQLINTEGER nPos = m_nRowIndex - 1;
	if (TestCache(nPos))
		return FetchCache(nPos);

	return FetchRowset(SQL_FETCH_PRIOR, 0);
}

// Moves to the next row
BOOL CSqlRecordset::MoveNext()
{
	SQLINTEGER nPos = m_nRowIndex + 1;
	if (TestCache(nPos))
		return FetchCache(nPos);

	return FetchRowset(SQL_FETCH_NEXT, 0);
}

// Refreshes the status of current row
BOOL CSqlRecordset::Refresh()
{
	ASSERT(!IsDeleted());
	if (!ExecuteSetPos(SQL_REFRESH, SQL_LOCK_NO_CHANGE))
		return FALSE;

	ReadCache();
	return TRUE;
}