dbtable.cpp

Data Replication Prototype Using ADO

//////////////////////////////////////////////////////////////////////

bool CDBTable::DeleteRecord()
{
	try
	{
		return m_pRFind != NULL && SUCCEEDED(m_pRFind->Delete(adAffectCurrent));
	}
	catch (_com_error& e)
	{
		// Print COM errors. 
		TRACE("Error\n");
		TRACE("\tCode meaning = %s\n", e.ErrorMessage());
		TRACE("\tSource = %s\n", LPCTSTR(e.Source()));
		TRACE("\tDescription = %s\n", LPCTSTR(e.Description()));

		m_pRFind = NULL;
		m_dwFilter = DWORD(-1L);

		return false;
	}
}

bool CDBTable::DeleteRecords(DWORD filter)
{
	bool result = true;
	long i;

	if (m_dwDeleteFilter != filter)
	{
		wstring query(L"DELETE FROM ");
		query += GetTableName();
		if (filter != 0)
		{
			bool first = true;
			for (int i = 0; i < GetAtomCount(); i++)
			{
				DWORD id = GetAtomId(i);
				if ((id & filter) == id)
				{
					if (first)
					{
						query += L" WHERE ";
						first = false;
					}
					else 
						query += L" AND ";
					query += GetAtomName(i);
					query += L"=?";
				}
			}
		}

		if(FAILED(m_pCDelete.CreateInstance(__uuidof(Command))))
		{
			ASSERT(0); return false;
		}

		m_pCDelete->ActiveConnection = GetConnectionPtr();
		
		TRACE(CString(query.c_str()) + _T("\n"));
		m_pCDelete->CommandText = query.c_str();
		m_pCDelete->Prepared = true;

		
		m_dwDeleteFilter = filter;
	}
	
	std::vector<const _variant_t*> params;
	for (i = 0; i < GetAtomCount(); i++)
	{
		DWORD id = GetAtomId(i);
		if ((id & filter) == id)
		{
			MapValues::iterator iter = m_mapValues.find(GetAtomName(i));
			if (iter != m_mapValues.end())
			{
				params.push_back(&iter->second);
			}
		}
	}

	try
	{
		_variant_t vsa;
		// Create SafeArray Bounds and initialize the array
		if (params.size() > 0)
		{
			V_ARRAY(&vsa) = SafeArrayCreateVector( VT_VARIANT, 0, params.size() );
			vsa.vt = VT_VARIANT | VT_ARRAY;

			// Set the values for each element of the array
   			for(i = params.size(); i--; )
				CheckError(SafeArrayPutElement(V_ARRAY(&vsa), &i, (void*) params[i])); 
		}
		m_pCDelete->Execute(NULL, (VT_EMPTY != vsa.vt)? &vsa : NULL, adCmdText);
	}
	catch (_com_error& e)
	{
		result = false;

		// Print COM errors. 
		TRACE("Error\n");
		TRACE("\tCode meaning = %s\n", e.ErrorMessage());
		TRACE("\tSource = %s\n", LPCTSTR(e.Source()));
		TRACE("\tDescription = %s\n", LPCTSTR(e.Description()));
	}

	return result;
}

//////////////////////////////////////////////////////////////////////

bool CDBTable::WasModified() const
{
	if (NULL == m_pRFind)
	{
		ASSERT(0); return false;
	}

	long i;
	for (i = m_pRFind->Fields->Count; i--; )
	{
		FieldPtr item = m_pRFind->Fields->Item[i];
		_bstr_t bstrItemName(item->GetName());
		if (!bstrItemName)
		{
			ASSERT(0);
			continue;
		}
		const _variant_t& 
			var = const_cast<CDBTable*>(this)->m_mapValues[LPCWSTR(bstrItemName)];
		if (!(var.vt & VT_ARRAY) && var != item->Value)
			return true;
	}
	return false;
}

//////////////////////////////////////////////////////////////////////

bool CDBTable::AddRecord(bool bInitialize /*= true*/)
{
	if (bInitialize)
		InitData();
	m_bAddMode = true;
	return true;
}

bool CDBTable::EditRecord()
{
	ASSERT(m_pRFind != NULL);
	m_bAddMode = false;
	return true;
}

bool CDBTable::Update()
{
	bool result = false;
	LPCWSTR pstrPKName = GetColumnName(fltAutoNumber);
	if (m_bAddMode)
	for (bool second = false;; second = true)
	{
		if (NULL == m_pCAdd)
		{
			if (FAILED(m_pCAdd.CreateInstance(__uuidof(Command))))
			{
				ASSERT(0); return false;
			}
			m_pCAdd->ActiveConnection = GetConnectionPtr();
			m_pCAdd->Prepared = true;

			wstring query(L"INSERT INTO " + wstring(GetTableName()) + L" ( ");
			wstring values;
			bool first = true;
			_RecordsetPtr pRParams;

			for (MapValues::iterator iter = m_mapValues.begin()
			; iter != m_mapValues.end()
			; ++iter)
			{
				if (pstrPKName && iter->first == pstrPKName)
					continue;
				if (first)
				{
					first = false;
				}
				else {
					query += L", ";
					values += L",?";
				}
				query += iter->first;

				if (NULL == pRParams)
					pRParams = (m_pRFind != NULL)? m_pRFind
											: GetParamsRecordset();

				FieldPtr item = pRParams->Fields->Item[iter->first.c_str()];
				DataTypeEnum type = item->Type;
				_ParameterPtr pprm = m_pCAdd->CreateParameter(iter->first.c_str(), 
					type, adParamInput, 
					(adLongVarChar == type || adLongVarWChar == type)? 1 : item->DefinedSize);
				pprm->NumericScale = item->NumericScale;
				pprm->Precision = item->Precision;
				m_pCAdd->Parameters->Append(pprm);
			}
			if (pRParams != NULL && NULL == m_pRFind)
				pRParams->Close();

			query += L" ) VALUES ( ?" + values + L" )";
			if (pstrPKName && IsSqlServerDatabase())
				query += L"; SELECT SCOPE_IDENTITY()";//@@IDENTITY";

			TRACE(CString(query.c_str()) + _T("\n"));
			m_pCAdd->CommandText = query.c_str();
		}

		// Initialize and fill the SafeArray
		_variant_t vsa;
		// Create SafeArray Bounds and initialize the array
		V_ARRAY(&vsa) = SafeArrayCreateVector( VT_VARIANT, 0,  m_mapValues.size());
		vsa.vt = VT_VARIANT | VT_ARRAY;

		VARIANT* pParams = NULL;
		CheckError(SafeArrayAccessData(V_ARRAY(&vsa), (void**)&pParams));
		int i = 0;
		for (MapValues::iterator iter(m_mapValues.begin())
		; iter != m_mapValues.end(); ++iter)
		{
			if (pstrPKName && iter->first == pstrPKName)
				continue;

			if ((VT_ARRAY | VT_UI1) == V_VT(&iter->second))
			{
				m_pCAdd->Parameters->Item[iter->first.c_str()]->Size
					= V_ARRAY(&iter->second)->rgsabound[0].cElements + 1;
			}

			pParams[i++] = iter->second;
		}
		CheckError(SafeArrayUnaccessData(V_ARRAY(&vsa)));
		if (i != m_mapValues.size())
		{
			SAFEARRAYBOUND saboundNew = { i, 0 };
			CheckError(SafeArrayRedim(V_ARRAY(&vsa), &saboundNew));
		}

		_variant_t count;
		_RecordsetPtr res;
		HRESULT hr = m_pCAdd->raw_Execute(&count, &vsa, 0, &res);

		CheckError(SafeArrayZeroVector(V_ARRAY(&vsa)));

		if (FAILED(hr))
			if (second)
				_com_issue_errorex(hr, m_pCAdd, __uuidof(m_pCAdd));
			else
			{// Possibly deadlock
				FreeStatements();
				continue;
			}

		result = (1 == long(count));

		// Identity matters
		if (pstrPKName != NULL) 
		{
			if (IsAccessDatabase())
			{
				if (NULL == m_pCId)
				{
					if (FAILED(m_pCId.CreateInstance(__uuidof(Command))))
					{
						ASSERT(0); return false;
					}
					m_pCId->ActiveConnection = GetConnectionPtr();
					m_pCId->Prepared = true;
					m_pCId->CommandText = L"SELECT @@IDENTITY";
				}
				//_RecordsetPtr 
				res = m_pCId->Execute(NULL, NULL, 0);
				if (res->adoEOF) 
				{
					ASSERT(0); return false;
				}
			}
			else if (IsSqlServerDatabase())
			{
				_variant_t vtAffected;
				res = res->NextRecordset(&vtAffected);
			}
			m_mapValues[pstrPKName] = res->Fields->Item[0L]->Value;
		}
		break;
	}
	else
	{
		if (NULL == m_pRFind)
		{
			ASSERT(0); return false;
		}
		for (MapValues::iterator iter = m_mapValues.begin()
		; iter != m_mapValues.end()
		; ++iter)
		{
			if (pstrPKName && iter->first == pstrPKName)
				continue;
			m_pRFind->Fields->Item[iter->first.c_str()]->Value = iter->second;
		}
		result = SUCCEEDED(m_pRFind->Update());
	}
	m_bAddMode = false;
	return result;
}

//////////////////////////////////////////////////////////////////////

int CDBTable::IterateThruStrings(const IStringIterator& it, DWORD filter)
{
	int cnt = 0;

	for (int i = 0; i < GetAtomCount(); i++)
	{
		DWORD id = filter? GetAtomId(i) : 0;
		if ((id & filter) == id)
		{
			MapValues::iterator iter = m_mapValues.find(GetAtomName(i));
			if (iter != m_mapValues.end())
			{
				const _variant_t& val = iter->second;
				if (VT_BSTR == val.vt)
				{
					wstring buf(val.bstrVal);
					if (!it.VisitString(buf))
						return 0;
					iter->second = buf.c_str();
					++cnt;
				}
			}
		}
	}

	return cnt;
}

bool CDBTable::IsAccessDatabase()
{
	return !wcscmp(L"MS Jet", GetConnectionPtr()->Properties->Item[L"DBMS Name"]->Value.bstrVal);
}

bool CDBTable::IsSqlServerDatabase()
{
	return !wcscmp(L"Microsoft SQL Server", GetConnectionPtr()->Properties->Item[L"DBMS Name"]->Value.bstrVal);
}

_RecordsetPtr CDBTable::GetParamsRecordset()
{
	_RecordsetPtr pRParams;
	if(FAILED(pRParams.CreateInstance(__uuidof(Recordset))))
	{
		ASSERT(0);
		return NULL;
	}
	wstring query(L"SELECT * FROM " + wstring(GetTableName()) + L" WHERE 0=1");
	pRParams->Open(query.c_str(), _variant_t((IDispatch*)GetConnectionPtr()), adOpenForwardOnly, adLockReadOnly, 0);
	return pRParams;
}