childfrm.cpp

Interactive SQL Tool

		m_nTimerID = SetTimer(1, 1000, NULL);
		m_strExecutionTime = "Exec time: 00:00:00";
		m_wndStatusBar.SetPaneText(m_nExecutionTimePaneNo, m_strExecutionTime);

		m_strStatusText = "Results grid #0 of 0; 0 row(s); 0 col(s)";
		m_wndStatusBar.SetPaneText(2, m_strStatusText);

		m_strMessages.erase();
		m_strProcText.erase();
			
		m_bExecuting = TRUE;
		m_bCanceling = FALSE;
		m_bCanceled = FALSE;
		if(m_bIsTSQLSupported)
			m_comboBox.EnableWindow(FALSE);
		
		long nStart = -1;
		long nEnd = -1;
		m_pQryView->GetRichEditCtrl().GetSel(nStart, nEnd);
		int nBuff = nEnd-nStart;
		CStringEx sSQL;
		if(nBuff == 0)
			m_pQryView->GetRichEditCtrl().GetWindowText(sSQL);
		else if(nBuff > 20000)
		{
			m_pQryView->GetRichEditCtrl().GetWindowText(sSQL);
			sSQL = sSQL.Mid(nStart, nBuff);
		}
		else
			sSQL = m_pQryView->GetRichEditCtrl().GetSelText();

		CDocument* pDoc = GetActiveDocument();
		ASSERT(pDoc != NULL);
		CString sBuff = sSQL;
		CString sTitle = pDoc->GetTitle();
		nBuff = sTitle.Find(_T(" - ("));
		if(nBuff != -1)
			sTitle = sTitle.Left(nBuff);
		sBuff.Replace(_T("\n"), _T(" "));
		sBuff.Replace(_T("\r"), _T(" "));
		if(sBuff.GetLength() > 15)
			sTitle = sTitle + _T(" - (") + sBuff.Left(15) + _T("...)");
		else
			sTitle = sTitle + _T(" - (") + sBuff + _T(")");
		pDoc->SetTitle(sTitle);
		
		if(m_bIsTSQLSupported)
		{
			LPCTSTR lpszHelpText = _T("sp_helptext ");
			int nPos = sSQL.FindNoCase(lpszHelpText);
			if(nPos != -1)
			{
				if(sSQL.Mid(0, _tcslen(lpszHelpText)) == lpszHelpText)
				{
					m_bProcText = TRUE;
					CString sObjName = sSQL.Mid(nPos + _tcslen(lpszHelpText));
					nPos = sObjName.FindOneOf(_T("\n\r\t "));
					if(nPos != -1)				
						sObjName = sObjName.Left(nPos);
					m_pResultView->m_strObjName = sObjName;
					if(!m_strDBMS.CompareNoCase(g_szSQLAnyWhere) ||
						!m_strDBMS.CompareNoCase(g_szASAnyWhere))
					{
						sSQL.Format(_T("SELECT text FROM syscomments where id = object_id('%s') "),
							(LPCTSTR)sObjName);
						sSQL += _T("order by number, colid2, colid");
					}
				}
			}
		}

		if(m_bProcText)
		{
			m_strStatusText = "# of lines of text: 0";
			m_wndStatusBar.SetPaneText(2, m_strStatusText);
		}

		if(!ExecuteSQL(sSQL))
			TRACE(_T("Error executing SQL: %s\n"), (LPCTSTR)sSQL);
	}
}

BOOL CChildFrame::ExecuteSQL(const CStringEx& sSQL)
{
	BOOL bRet = TRUE;

	CloseRecordset();

	try
	{
		m_strSQL = sSQL;
		long lOption = ADODB::adAsyncExecute;
		if(!m_bSupports_adAsyncExecute)
			lOption = ADODB::adCmdText;
		BSTR bstrSQL = m_strSQL.AllocSysString();
		HRESULT hr = m_ptrRecordset->Open(
				bstrSQL,
				m_ptrConnection.GetInterfacePtr(),
				ADODB::adOpenForwardOnly,
				ADODB::adLockReadOnly,
				lOption
				);
		::SysFreeString(bstrSQL);
		if(FAILED(hr))
			_com_issue_error(hr);
	}
	catch(const _com_error& e)
	{
		bRet = FALSE;

		CString sBuff = GetProviderError();
		if(!sBuff.IsEmpty())
			m_strMessages = sBuff;
		else
			m_strMessages = GetComError(e);
	}
	catch(...)
	{
		bRet = FALSE;

		m_strMessages = _T("Errors occurred.");
	}

	if(!bRet)
	{
		if(m_bProcText)
			OnExecProcComplete(true, -1);
		else
			OnExecutionComplete(true, -1);
	}

	return bRet;
}

CQryView* CChildFrame::GetQryView()
{
	return static_cast<CQryView*>(m_wndSplitter.GetPane(0, 0));
}

CResultView* CChildFrame::GetResultView()
{
	CResultView* pResultView = NULL;
	if(m_wndSplitter.m_hWnd != NULL)
		pResultView = static_cast<CResultView*>(m_wndSplitter.GetPane(1, 0));

	return pResultView;
}

void CChildFrame::OnSqlSelectTables() 
{
	CWaitCursor wait;

	CSelectTables dlg;
	if(dlg.DoModal() == IDOK)
	{
		CString sBuff;
		m_pQryView->GetRichEditCtrl().GetWindowText(sBuff);
		int nStart = sBuff.GetLength();
		sBuff += dlg.m_strSQL;
		int nEnd = sBuff.GetLength();
		m_pQryView->GetRichEditCtrl().SetWindowText(sBuff);
		m_pQryView->GetRichEditCtrl().SetSel(nStart+2, nEnd);
		if(dlg.m_bTextOnly && !dlg.m_strType.CompareNoCase(_T("VIEW")) &&
			(m_bIsTSQLSupported || !m_strDBMS.CompareNoCase(g_szOracle) ||
			m_bIsJetDriver))
		{
			m_pResultView->m_strObjName = dlg.m_strObjName;
			m_bProcText = TRUE;
		}
		OnSqlExecute();
	}
}

void CChildFrame::OnUpdateSqlSelectTables(CCmdUI* pCmdUI) 
{
	pCmdUI->Enable(m_lConnectionState == ADODB::adStateOpen && !m_bExecuting);
}

void CChildFrame::OnExceptionClear(const CString& sMsg)
{
	m_pResultView->SetRedraw(FALSE);
	
	ClearGridList();
	
	m_pResultView->GetRichEditCtrl().SetWindowText(sMsg);
	m_pResultView->GetRichEditCtrl().SetSel(0, 0); // Scroll up!
	m_pResultView->SetRedraw(TRUE);
	
	m_nID = 2000;
	m_nIndex = 0;

	m_bExecuteCompleteHandled = FALSE;
	
	m_bCloseMainFrameAfterCancel = FALSE;
	m_bDisconnectAllAfterCancel = FALSE;
	m_bCloseAfterCancel = FALSE;
	
	if(m_nTimerID != 0)
	{
		KillTimer(m_nTimerID);
		m_nTimerID = 0;
	}
	
	m_pResultView->Invalidate();
	m_pResultView->UpdateWindow();
}

BOOL CChildFrame::FillCombo()
{
	BOOL bRet = TRUE;

	CString sMsg;

	m_comboBox.ResetContent();

	try
	{
		ADODB::_RecordsetPtr ptrRS = m_ptrConnection->OpenSchema(
						ADODB::adSchemaCatalogs
						);
		if(ptrRS != NULL)
		{
			if(ptrRS->adoBOF)
			{
				m_comboBox.AddString(g_szNoData);
				m_comboBox.SetCurSel(0);
			}
			else
			{
				CString sDBName;
				while(!ptrRS->adoEOF)
				{
					sDBName = (LPCTSTR)(_bstr_t)ptrRS->Fields->
						GetItem(_variant_t((long)0))->Value;
					if(sDBName.Find(' ') != -1)
						sDBName = "[" + sDBName + "]";
					m_comboBox.AddString(sDBName);
					
					ptrRS->MoveNext();
				}
			}

			m_comboBox.InsertString(m_comboBox.GetCount(), g_szRefresh);
			m_comboBox.EnableWindow();
		}
	}
	catch(const _com_error& e)
	{
		UNUSED_ALWAYS(e);

		sMsg = GetProviderError();
		if(!sMsg.IsEmpty())
			sMsg = "Provider Error";
		else
			sMsg = "_com_error";

		bRet = FALSE;
	}

	if(bRet)
	{
		if(m_comboBox.GetCount())
			if(!SelectDatabase())
				TRACE(_T("Error selecting database context.\n"));
	}
	else
	{
		m_comboBox.AddString(sMsg);
		m_comboBox.SetCurSel(0);
	}

	return bRet;
}

bool CChildFrame::PutInitialCatalog(LPCTSTR lpszInitialCatalog)
{
	bool bRet = true;

	try
	{
		CloseRecordset();

		long lOption = ADODB::adCmdText;
		CString strSQL = "use " + CString(lpszInitialCatalog);
		HRESULT hr = m_ptrRecordset->Open(
				_bstr_t((LPCTSTR)strSQL),
				m_ptrConnection.GetInterfacePtr(),
				ADODB::adOpenForwardOnly,
				ADODB::adLockReadOnly,
				lOption
				);
		if(FAILED(hr))
			_com_issue_error(hr);
		
		CloseRecordset();

		CString sDBName = lpszInitialCatalog;
		CString sBuff = sDBName;
		if(sDBName.Find(_T("[")) != -1)
		{
			sBuff = sDBName.Mid(1);
			int nPos = sBuff.Find(_T("]"));
			if(nPos != -1)
				sBuff = sBuff.Left(nPos);
		}
		ADODB::PropertiesPtr ptrProperties = m_ptrConnection->GetProperties();
		ADODB::PropertyPtr ptrProperty = ptrProperties->GetItem("Current Catalog");
		hr = ptrProperty->put_Value(_variant_t((LPCTSTR)sBuff));
		if(FAILED(hr))
			_com_issue_error(hr);
	}
	catch(const _com_error& e)
	{
		CString sMsg = GetProviderError();
		if(sMsg.IsEmpty())
			sMsg = GetComError(e);

		TRACE(_T("%s\n"), (LPCTSTR)sMsg);

		bRet = false;
	}
	catch(...)
	{
		TRACE(_T("Errors occurred.\n"));

		bRet = false;
	}

	return bRet;
}

void CChildFrame::OnSelChangeDatabase()
{
	CWaitCursor wait;

	CString sBuff, sDBName, sMsg;
	m_comboBox.GetWindowText(sDBName);
	if(!sDBName.CompareNoCase(g_szNoData) ||
		!sDBName.CompareNoCase(g_szNotSupported))
		;
	else if(!sDBName.CompareNoCase(g_szRefresh))
	{
		if(!FillCombo())
			TRACE(_T("Error filling combo.\n"));
	}
	else
	{
		if(sDBName != m_strDBName)
		{
			try
			{
				CloseRecordset();

				long lOption = ADODB::adCmdText;
				CString strSQL = "use " + sDBName;
				HRESULT hr = m_ptrRecordset->Open(
						_bstr_t((LPCTSTR)strSQL),
						m_ptrConnection.GetInterfacePtr(),
						ADODB::adOpenForwardOnly,
						ADODB::adLockReadOnly,
						lOption
						);
				if(FAILED(hr))
					_com_issue_error(hr);

				CloseRecordset();

				sBuff = sDBName;
				if(sDBName.Find(_T("[")) != -1)
				{
					sBuff = sDBName.Mid(1);
					int nPos = sBuff.Find(_T("]"));
					if(nPos != -1)
						sBuff = sBuff.Left(nPos);
				}
				ADODB::PropertiesPtr ptrProperties = m_ptrConnection->GetProperties();
				ADODB::PropertyPtr ptrProperty = ptrProperties->GetItem("Current Catalog");
				hr = ptrProperty->put_Value(_variant_t((LPCTSTR)sBuff));
				if(FAILED(hr))
					_com_issue_error(hr);
				m_comboBox.SelectString(-1, (m_strDBName = (LPCTSTR)sDBName));
			}
			catch(const _com_error& e)
			{
				m_comboBox.SelectString(-1, m_strDBName); // the Old one
				
				sMsg = GetProviderError();
				if(sMsg.IsEmpty())
					sMsg = GetComError(e);

				AfxMessageBox(sMsg);
			}
			catch(...)
			{
				m_comboBox.SelectString(-1, m_strDBName); // the Old one

				AfxMessageBox(_T("Errors occurred."));
			}
		}
	}
}

void CChildFrame::OnSqlSelectProcedures() 
{
	CWaitCursor wait;

	CProceduresDlg dlg;
	if(dlg.DoModal() == IDOK)
	{
		CStringEx sBuff;
		CRichEditCtrl& edit = m_pQryView->GetRichEditCtrl();
		edit.GetWindowText(sBuff);
		sBuff += "\r\n\r\n";
		int nStart = sBuff.GetLength();
		
		CString sSQL;
		if(!m_strDBMS.CompareNoCase(g_szSybaseOpenServer) ||
		   !m_strDBMS.CompareNoCase(g_szMSSQLServer) ||
		   !m_strDBMS.CompareNoCase(g_szMicrosoftSQLServer) ||
		   !m_strDBMS.CompareNoCase(g_szSQLAnyWhere) ||
			!m_strDBMS.CompareNoCase(g_szASAnyWhere)
		   )
			sSQL = "sp_helptext " + dlg.m_strProcedureName;
		else if(!m_strDBMS.CompareNoCase(g_szOracle))
		{
			sSQL = "select TEXT from ALL_SOURCE where type = 'PROCEDURE' and NAME = '";
			sSQL += dlg.m_strProcedureName + "'" ;
		}
		else
			sSQL += dlg.m_strProcedureName + " // Procedure text not supported";

		sBuff += sSQL;
		int nEnd = sBuff.GetLength();
		edit.SetWindowText(sBuff);
		edit.SetSel(nStart, nEnd);
		m_pResultView->m_strObjName = dlg.m_strProcedureName;
		m_bProcText = TRUE;
		OnSqlExecute();
	}
}

void CChildFrame::OnUpdateSqlSelectProcedures(CCmdUI* pCmdUI) 
{
	pCmdUI->Enable(m_lConnectionState == ADODB::adStateOpen && !m_bExecuting);
}

void CChildFrame::OnSqlObjects() 
{
	if(!m_bExecuting)
	{
		CRect rect;
		UINT nIndex = m_wndToolBar.GetToolBarCtrl().CommandToIndex(ID_SQL_OBJECTS);
		m_wndToolBar.GetToolBarCtrl().GetItemRect(nIndex, &rect);
		rect.top = rect.bottom;
		m_wndToolBar.ClientToScreen(&rect.TopLeft());
		
		CMenu menu;
		if(menu.CreatePopupMenu())
		{
			DWORD dwFlags = MF_ENABLED | MF_STRING;
			menu.AppendMenu(dwFlags, ID_SQL_SELECT_TABLES_EX, _T("&Tables...\tF8"));
			menu.AppendMenu(dwFlags, ID_SQL_SELECT_PROCEDURES, _T("&Procedures...\tF9"));
			menu.TrackPopupMenu(TPM_LEFTALIGN | TPM_LEFTBUTTON, rect.left, rect.top, this);
		}
	}
}

void CChildFrame::ClearGridList()
{
	for(POSITION pos = m_GridList.GetHeadPosition(); pos != NULL;)
	{
		CMSFlexGrid* pGridCtrl =  (CMSFlexGrid*)m_GridList.GetNext(pos);
		if(pGridCtrl != NULL)
		{
			pGridCtrl->DestroyWindow();
			delete pGridCtrl;
			pGridCtrl = NULL;
		}
	}
	
	if(!m_GridList.IsEmpty())
	{
		m_GridList.RemoveAll();
		m_nGridCount = 0;
	}

	CResultView* pResultView = GetResultView();
	if(pResultView != NULL)
		pResultView->m_pGridCtrl = NULL;
}

void CChildFrame::OnUpdateIndicatorPaneTextZero(CCmdUI* pCmdUI)
{
	if(!m_bExecuting)
		if(m_strPaneTextZero.IsEmpty())
			m_strPaneTextZero = "Ready";
	pCmdUI->SetText(m_strPaneTextZero);
	pCmdUI->Enable(TRUE);
}

void CChildFrame::OnUpdateIndicatorExecTime(CCmdUI* pCmdUI)
{
	pCmdUI->SetText(m_strExecutionTime);
	pCmdUI->Enable(TRUE);
}

void CChildFrame::OnUpdateIndicatorResultSetInfo(CCmdUI* pCmdUI)
{
	if(m_strStatusText.CompareNoCase(g_szMessages) != 0 &&
		m_strProcText.length())
	{
		m_strStatusText.Format(
				_T("# of lines of text: %d"),
				m_pResultView->GetRichEditCtrl().GetLineCount()
				);
	}
	pCmdUI->SetText(m_strStatusText);
	pCmdUI->Enable(TRUE);
}

void CChildFrame::OnUpdateIndicatorPosition(CCmdUI* pCmdUI)
{
	CView* pView = GetActiveView();
	bool bGrid = false;
	if(pView != NULL && pView->IsKindOf(RUNTIME_CLASS(CResultView)))
	{
		bGrid = m_pResultView->m_pGridCtrl != NULL &&
			::IsWindow(m_pResultView->m_pGridCtrl->m_hWnd) &&
			m_pResultView->m_pGridCtrl->IsWindowVisible();
		if(bGrid)
			m_strLineColInfo.Format(
				_T("Ln %d, Col %d"),
				m_pResultView->m_pGridCtrl->GetRow(),
				m_pResultView->m_pGridCtrl->GetCol()+1
				);
	}
	if(!bGrid)
	{
		CRichEditCtrl* pRichEditCtrl = NULL;
		if(pView != NULL)
		{
			if(pView->IsKindOf(RUNTIME_CLASS(CQryView)))
				pRichEditCtrl = &m_pQryView->GetRichEditCtrl();
			else if(pView->IsKindOf(RUNTIME_CLASS(CResultView)))
				pRichEditCtrl = &m_pResultView->GetRichEditCtrl();
		}
		if(pRichEditCtrl != NULL)
		{
			long lStart = -1;
			long lEnd = -1;
			pRichEditCtrl->GetSel(lStart, lEnd);
			m_strLineColInfo.Format(
				_T("Ln %d, Col %d"),
				pRichEditCtrl->LineFromChar(lStart)+1,
				((lEnd-lStart) >= 0 ? lEnd-pRichEditCtrl->LineIndex(-1) :
					lStart-pRichEditCtrl->LineIndex(-1))+1
				);
		}
	}
	
	pCmdUI->SetText(m_strLineColInfo);
	pCmdUI->Enable(TRUE);
}

void CChildFrame::OnViewPrevResultSet() 
{
	if(m_nIndex > 1)
	{
		POSITION pos = m_GridList.FindIndex(m_nIndex-2);
		DisplayResults((CMSFlexGrid*)m_GridList.GetAt(pos), --m_nIndex);
	}
}

void CChildFrame::OnViewNextResultSet() 
{
	if(m_nIndex > 0 && m_nIndex < m_nGridCount)
	{ 
		POSITION pos = m_GridList.FindIndex(m_nIndex);
		DisplayResults((CMSFlexGrid*)m_GridList.GetAt(pos), ++m_nIndex);
	}
}

void CChildFrame::OnViewFirstResultSet() 
{
	if(m_nIndex > 1)
	{
		m_nIndex = 0;
		POSITION pos = m_GridList.FindIndex(m_nIndex);
		DisplayResults((CMSFlexGrid*)m_GridList.GetAt(pos), ++m_nIndex);
	}
}

void CChildFrame::OnViewLastResultSet() 
{
	if(m_nIndex > 0 && m_nIndex < m_nGridCount)
	{
		m_nIndex = m_nGridCount;
		POSITION pos = m_GridList.FindIndex(--m_nIndex);
		DisplayResults((CMSFlexGrid*)m_GridList.GetAt(pos), ++m_nIndex);
	}
}

void CChildFrame::OnUpdateViewPrevResultSet(CCmdUI* pCmdUI) 
{
	pCmdUI->Enable(m_nIndex > 1);
}

void CChildFrame::OnUpdateViewNextResultSet(CCmdUI* pCmdUI)