selecttables.cpp

Interactive SQL Tool

// SelectTables.cpp : implementation file
//

#include "stdafx.h"
#include "QryTool.h"
#include "SelectTables.h"
#include "MainFrm.h"
#include "ChildFrm.h"

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

LPCTSTR g_szSection = _T("Tables");
LPCTSTR g_szViewsEntry = _T("Views");
LPCTSTR g_szSystemTablesEntry = _T("SystemTables");
LPCTSTR g_szTextOnlyEntry = _T("TextOnly");

extern LPCTSTR g_szOracle;
extern LPCTSTR g_szSQLAnyWhere;
extern LPCTSTR g_szASAnyWhere;
extern LPCTSTR g_szMSOLEDBProviderForODBCDrivers;
extern LPCTSTR g_szVisualFoxPro;
/////////////////////////////////////////////////////////////////////////////
// CSelectTables dialog

CSelectTables::CSelectTables(CWnd* pParent /*=NULL*/)
	: CDialog(CSelectTables::IDD, pParent)
{
	//{{AFX_DATA_INIT(CSelectTables)
	m_bTextOnly = FALSE;
	m_strSearch = _T("");
	//}}AFX_DATA_INIT

	m_bSort = true;
}

CSelectTables::~CSelectTables()
{
}

void CSelectTables::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CSelectTables)
	DDX_Control(pDX, IDC_TEXT_ONLY, m_buttonTextOnly);
	DDX_Control(pDX, IDC_SYSTEM_TABLES, m_buttonSystemTables);
	DDX_Control(pDX, IDC_VIEWS, m_buttonViews);
	DDX_Control(pDX, IDC_LIST1, m_ctrlList);
	DDX_Check(pDX, IDC_TEXT_ONLY, m_bTextOnly);
	DDX_Text(pDX, IDC_SEARCH, m_strSearch);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CSelectTables, CDialog)
	//{{AFX_MSG_MAP(CSelectTables)
	ON_NOTIFY(NM_DBLCLK, IDC_LIST1, OnDblclkList1)
	ON_NOTIFY(LVN_COLUMNCLICK, IDC_LIST1, OnColumnclickList1)
	ON_BN_CLICKED(IDC_SYSTEM_TABLES, OnSystemTables)
	ON_BN_CLICKED(IDC_VIEWS, OnViews)
	ON_EN_CHANGE(IDC_SEARCH, OnChangeSearch)
	ON_BN_CLICKED(IDC_TEXT_ONLY, OnTextOnly)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CSelectTables message handlers

BOOL CSelectTables::OnInitDialog() 
{
	CWaitCursor wait;

	CMainFrame* pFrame = (CMainFrame*)AfxGetMainWnd();
	ASSERT(pFrame);
	m_pChildFrame = (CChildFrame*)pFrame->MDIGetActive();
	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("Please wait..."));

	CDialog::OnInitDialog();

	LV_COLUMN lvcColumn;
	lvcColumn.mask = LVCF_FMT | LVCF_SUBITEM | LVCF_TEXT | LVCF_WIDTH;
	lvcColumn.fmt = LVCFMT_LEFT;

	CRect rect;
	m_ctrlList.GetClientRect(&rect);
	int nWidth = rect.Size().cx/4;
	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Name");
	lvcColumn.iSubItem = 0;
	m_ctrlList.InsertColumn(0, &lvcColumn);

	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Type");
	lvcColumn.iSubItem = 1;
	m_ctrlList.InsertColumn(1, &lvcColumn);

	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Schema");
	lvcColumn.iSubItem = 2;
	m_ctrlList.InsertColumn(2, &lvcColumn);

	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Catalog");
	lvcColumn.iSubItem = 3;
	m_ctrlList.InsertColumn(3, &lvcColumn);

	CWinApp* pApp = AfxGetApp();
	BOOL bCheck = pApp->GetProfileInt(g_szSection, g_szSystemTablesEntry, 0);
	m_buttonSystemTables.SetCheck(bCheck);

	bCheck = pApp->GetProfileInt(g_szSection, g_szViewsEntry, 0);
	m_buttonViews.SetCheck(bCheck);
	
	bCheck = pApp->GetProfileInt(g_szSection, g_szTextOnlyEntry, 0);
	m_buttonTextOnly.SetCheck(bCheck);
	m_buttonTextOnly.EnableWindow(m_buttonViews.GetCheck());

	if(!PopulateListEx())
		SendMessage(WM_CLOSE);
	else
	{
		OnTextOnly();

		m_ctrlList.SetFullRowSel(TRUE);
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

bool CSelectTables::PopulateListEx()
{
	CWaitCursor wait;

	bool bRet = true;

	m_ctrlList.DeleteAllItems();

	try
	{
		if(!m_pChildFrame->m_strProviderName.CompareNoCase(
			g_szMSOLEDBProviderForODBCDrivers))
		{
			if(!ApplyCriteriaAndPopulate())
				TRACE(_T("Error populating tables.\n"));
		}
		else
		{
			if(!PopulateList())
				TRACE(_T("Error populating tables.\n"));
		}
	}
	catch(const _com_error& e)
	{
		bRet = false;

		CString sMsg = m_pChildFrame->GetProviderError();
		if(sMsg.IsEmpty())
			sMsg = m_pChildFrame->GetComError(e);

		AfxMessageBox(sMsg);
	}
	catch(CMemoryException* e)
	{
		bRet = false;

		if(e)
			e->Delete();

		AfxMessageBox(_T("Out-of-memory."));
	}
	catch(...)
	{
		bRet = false;

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

	if(!m_ctrlList.GetItemCount())
	{
		GetDlgItem(IDOK)->EnableWindow(FALSE);
		GetDlgItem(IDCANCEL)->SetWindowText(_T("&Close"));
	}
	else
	{
		GetDlgItem(IDOK)->EnableWindow(TRUE);
		GetDlgItem(IDCANCEL)->SetWindowText(_T("Cancel"));
	}

	return bRet;
}

bool CSelectTables::ApplyCriteriaAndPopulate()
{
	CWaitCursor wait;

	bool bRet = true;

	char lpszType[64];
	strcpy(lpszType, "'TABLE'");
	if(m_buttonViews.GetCheck())
		strcat(lpszType, ",'VIEW'");
	if(m_buttonSystemTables.GetCheck())
		strcat(lpszType, ",'SYSTEM TABLE'");
	_bstr_t bstrTableType(lpszType);
	const int nSize = 4;
	_variant_t varCriteria[nSize];
	varCriteria[0].vt = VT_EMPTY;
	varCriteria[1].vt = VT_EMPTY;
	varCriteria[2].vt = VT_EMPTY;
	varCriteria[3].vt = VT_BSTR;
	varCriteria[3].bstrVal = bstrTableType;
	
	SAFEARRAYBOUND rgsabound[1];
	rgsabound[0].lLbound = 0;
	rgsabound[0].cElements = nSize;
	SAFEARRAY* pSA = ::SafeArrayCreate(VT_VARIANT, 1, rgsabound);
	ASSERT(pSA != NULL);
	
	// Fill the safe array.
	HRESULT hr = S_OK;
	for(long lIndex = 0; lIndex < nSize; lIndex++)
	{
		hr  = ::SafeArrayPutElement(pSA, &lIndex, &varCriteria[lIndex]);
		if(FAILED(hr))
			_com_issue_error(hr);
	}
	
	// Initialize variant with safearray.
	VARIANT varData;
	varData.vt = VT_VARIANT | VT_ARRAY;
	V_ARRAY(&varData) = pSA;
	ADODB::_RecordsetPtr ptrRS = m_pChildFrame->m_ptrConnection->OpenSchema(
		ADODB::adSchemaTables, varData);
	if(!ptrRS->adoBOF)
	{
		CString sName, sType, sSchema, sCatalog;
		int nPos = -1;
		int nIndex = 0;
		while(!ptrRS->adoEOF)
		{
			sName = CHelpers::CrackStrVariant(ptrRS->
				GetCollect(L"TABLE_NAME"));
			sType = CHelpers::CrackStrVariant(ptrRS->
				GetCollect(L"TABLE_TYPE"));
			// Alternatively...
			sSchema = CHelpers::CrackStrVariant(ptrRS->Fields->
				GetItem(_variant_t((long)1))->Value);
			sCatalog = CHelpers::CrackStrVariant(ptrRS->Fields->
				GetItem(_variant_t((long)0))->Value);

			if(m_pChildFrame->m_bIsJetDriver)
				sName = "[" + sName + "]";
			else
			{
				nPos = sName.Find(' ');
				if(nPos != -1) // MS SQL Server scenario
					sName = "[" + sName + "]";
			}
			
			CHelpers::Insert(&m_ctrlList, sName + "|" + sType + "|" +
				sSchema + "|" + sCatalog + "|", -1, nIndex++);

			hr = ptrRS->MoveNext();
			if(FAILED(hr))
				_com_issue_error(hr);
		}

		m_ctrlList.SetItemState(0, LVIS_SELECTED | LVIS_FOCUSED,
				LVIS_SELECTED | LVIS_FOCUSED);

		if(pSA != NULL)
			::SafeArrayDestroy(pSA);
	}

	return bRet;
}

bool CSelectTables::PopulateList()
{
	bool bRet = true;

	ADODB::_RecordsetPtr ptrRS = m_pChildFrame->m_ptrConnection->OpenSchema(
			ADODB::adSchemaTables);
	if(!ptrRS->adoBOF)
	{
		CString sName, sType, sSchema, sCatalog, sBuff;
		HRESULT hr = S_OK;
		int nPos = -1;
		int nIndex = 0;
		while(!ptrRS->adoEOF)
		{
			sType = CHelpers::CrackStrVariant(ptrRS->
				GetCollect(L"TABLE_TYPE"));
			if((!sType.CompareNoCase(_T("VIEW")) &&
				m_buttonViews.GetCheck()) ||
				(!sType.CompareNoCase(_T("SYSTEM TABLE")) && 
				m_buttonSystemTables.GetCheck()) ||
				!sType.CompareNoCase(_T("TABLE")))
			{
				sName = CHelpers::CrackStrVariant(ptrRS->
					GetCollect(L"TABLE_NAME"));
				// Alternatively...
				sSchema = CHelpers::CrackStrVariant(ptrRS->Fields->
					GetItem(_variant_t((long)1))->Value);
				sCatalog = CHelpers::CrackStrVariant(ptrRS->Fields->
					GetItem(_variant_t((long)0))->Value);

				if(m_pChildFrame->m_bIsJetDriver)
					sName = _T("[") + sName + _T("]");
				else
				{
					nPos = sName.Find(' ');
					if(nPos != -1) // MS SQL Server scenario
						sName = _T("[") + sName + _T("]");
				}
				
				CHelpers::Insert(&m_ctrlList, sName + "|" + sType + "|" +
					sSchema + "|" + sCatalog + "|", -1, nIndex++);
			}

			hr = ptrRS->MoveNext();
			if(FAILED(hr))
				_com_issue_error(hr);
		}

		m_ctrlList.SetItemState(0, LVIS_SELECTED | LVIS_FOCUSED,
				LVIS_SELECTED | LVIS_FOCUSED);
	}

	return bRet;
}

void CSelectTables::OnOK() 
{
	CWaitCursor wait;

	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("Please wait..."));

	UpdateData();

	bool bRet = true;

	int nItem = m_ctrlList.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
	if(nItem == -1)
		nItem = m_ctrlList.GetNextItem(-1, LVNI_ALL | LVNI_FOCUSED);
	m_strSQL = "\n\n";
	m_strType = m_ctrlList.GetItemText(nItem, 1);
	CString sTableName = m_ctrlList.GetItemText(nItem, 0);
	if(m_bTextOnly && !m_strType.CompareNoCase(_T("VIEW")))
	{
		if(!m_pChildFrame->m_strDBMS.CompareNoCase(g_szOracle))
		{
			m_strSQL += "select TEXT from ALL_SOURCE where type = 'VIEW' and NAME = '";
			m_strSQL += sTableName + "'\n\n" ;
		}
		else if(m_pChildFrame->m_bIsTSQLSupported)
		{
			if(m_pChildFrame->m_bIsTSQLSupported)
				m_strSQL += "sp_helptext " + sTableName + "\n\n";
		}
		else
			m_strSQL += sTableName + " // View text not supported\n\n";

		m_strObjName = sTableName;
	}
	else
	{
		CString sColumns, sSQL;
		while(nItem  != -1 )
		{
			sTableName = m_ctrlList.GetItemText(nItem, 0);
			bRet = GetColumnList(sTableName, sColumns);
			if(!bRet)
				break;
			else
			{
				sSQL = "SELECT " + sColumns + " FROM ";
				sSQL += sTableName;
				if(!m_pChildFrame->m_strDBMS.CompareNoCase(g_szVisualFoxPro))
					sSQL += ";";
				sSQL += "\n\n";
				m_strSQL += sSQL;
				nItem = m_ctrlList.GetNextItem(nItem, LVNI_ALL | LVNI_SELECTED);
			}
		}
	}

	if(bRet)
	{
		m_strSQL = m_strSQL.Left(m_strSQL.GetLength()-2);
			
		CWinApp* pApp = AfxGetApp();
		pApp->WriteProfileInt(g_szSection, g_szViewsEntry, m_buttonViews.GetCheck());
		pApp->WriteProfileInt(g_szSection, g_szSystemTablesEntry,
			m_buttonSystemTables.GetCheck());
		pApp->WriteProfileInt(g_szSection, g_szTextOnlyEntry, m_buttonTextOnly.GetCheck());
		
		CDialog::OnOK();
	}

	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("")); 
}

void CSelectTables::OnDblclkList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	CWaitCursor wait;

	if(GetDlgItem(IDOK)->IsWindowEnabled())
		OnOK();

	*pResult = 0; // Framework stuff
}

void CSelectTables::OnColumnclickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	CWaitCursor wait;

	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;

	CSortColumn sort(&m_ctrlList, pNMListView->iSubItem, false);
	sort.Sort(m_bSort = !m_bSort);
	
	*pResult = 0; // Framework stuff
}

void CSelectTables::OnSystemTables() 
{
	CWaitCursor wait;

	UpdateData();

	m_ctrlList.SetRedraw(FALSE);
	PopulateListEx();
	m_ctrlList.SetRedraw();
}

void CSelectTables::OnViews() 
{
	OnSystemTables();
	m_buttonTextOnly.EnableWindow(m_buttonViews.GetCheck());
	if(m_buttonTextOnly.IsWindowEnabled())
		OnTextOnly();
	else
	{
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
}

bool CSelectTables::GetColumnList(const CString& sTN, CString& sColumns)
{
	CString sTableName = sTN;

	bool bRet = true;

	sColumns.Empty();

	try
	{
		if(sTableName.Find('[') != -1)
		{
			int nLength = sTableName.GetLength(); 
			sTableName = sTableName.Mid(1, nLength-2);
		}
		
		_bstr_t bstrTableName((LPCTSTR)sTableName);
		const int nSize = 3;
		_variant_t varCriteria[nSize];
		varCriteria[0].vt = VT_EMPTY;
		varCriteria[1].vt = VT_EMPTY;
		varCriteria[2].vt = VT_BSTR;
		varCriteria[2].bstrVal = bstrTableName;

		SAFEARRAYBOUND rgsabound[1];
		rgsabound[0].lLbound = 0;
		rgsabound[0].cElements = nSize;
		HRESULT hr = S_OK;
		SAFEARRAY* pSA = ::SafeArrayCreate(VT_VARIANT, 1, rgsabound);
		ASSERT(pSA != NULL);

		// Fill the safe array.
		for(long lIndex = 0; lIndex < nSize; lIndex++)
		{
			hr  = ::SafeArrayPutElement(pSA, &lIndex, &varCriteria[lIndex]);
			if(FAILED(hr))
				_com_issue_error(hr);
		}
		
		// Initialize variant with safearray.
		VARIANT varData;
		varData.vt = VT_VARIANT | VT_ARRAY;
		V_ARRAY(&varData) = pSA;
		ADODB::_RecordsetPtr ptrRS = m_pChildFrame->m_ptrConnection->OpenSchema(
			ADODB::adSchemaColumns, varData);
		if(!ptrRS->adoBOF)
		{
			CString sColumn;
			int nPos = -1;
			while(!ptrRS->adoEOF)
			{
				sColumn = (LPCTSTR)(_bstr_t)ptrRS->Fields->
					GetItem(_variant_t((long)3))->Value;
				
				if(m_pChildFrame->m_bIsJetDriver)
				{
					nPos = sColumn.Find('.');
					if(nPos == -1)
						sColumns +=	"[" + sColumn + "], ";
					else
					{
						sColumns += "[" + sColumn.Left(nPos) + "].";
						sColumns +=	"[" + sColumn.Mid(nPos+1) + "], ";
					}
				}
				else
				{
					nPos = sColumn.Find(' ');
					if(nPos != -1) // MS SQL Server scenario
						sColumns +=	"[" + sColumn + "], ";
					else // All other
						sColumns +=	sColumn + ", ";
				}

				hr = ptrRS->MoveNext();
				if(FAILED(hr))
					_com_issue_error(hr);
			}

			if(!sColumns.IsEmpty())
				sColumns = sColumns.Left(sColumns.GetLength()-2);
		}

		if(pSA != NULL)
			::SafeArrayDestroy(pSA);
	}
	catch(const _com_error& e)
	{
		bRet = false;

		CString sMsg = m_pChildFrame->GetProviderError();
		if(sMsg.IsEmpty())
			sMsg = m_pChildFrame->GetComError(e);

		AfxMessageBox(sMsg);
	}
	catch(CMemoryException* e)
	{
		bRet = false;

		if(e)
			e->Delete();

		AfxMessageBox(_T("Out-of-memory."));
	}
	catch(...)
	{
		bRet = false;

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

void CSelectTables::OnChangeSearch() 
{
	UpdateData();

	LVFINDINFO findInfo;
	findInfo.flags = LVFI_PARTIAL | LVFI_STRING;
	findInfo.psz = (LPCTSTR)m_strSearch;
	int nItem = m_ctrlList.FindItem(&findInfo);
	m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
	m_ctrlList.SetItemState(nItem, LVIS_SELECTED, LVIS_SELECTED);
	if(m_pChildFrame->m_bIsMultiSetSupported)
		m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	m_ctrlList.EnsureVisible(nItem, TRUE);
}

void CSelectTables::OnTextOnly() 
{
	if(m_buttonTextOnly.GetCheck())
		m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
	else
	{
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
	
	m_ctrlList.SetFocus();
}