Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version
Go to top

Database Visualization

, 31 May 2006
This article aims to create a simple tool for visualizing database tables and relations, a database map to refer to.
src.zip
src
msado15.tlh
msado15.tli
QueryBuilder.dsp
QueryBuilder.dsw
QueryBuilder.exe
QueryBuilder.clw
res
QueryBuilder.ico
logo.bmp
msado15.tlh
msado15.tli
Manifest
default1.bin
ListIcons.bmp
wingraphviz.zip
WinGraphViz
WinGraphviz_v1.02.25s.cab
WinGraphviz_v1.02.24.msi
WinGraphviz_v1.02.24.cab
WinGraphviz_v1.02.20.msi
WinGraphviz_v1.02.20.cab
dotguide.pdf
// SQLInfoEnumerator.cpp: implementation of the CSQLInfoEnumerator class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "SQLInfoEnumerator.h"

#include <afxdb.h>	// For CDBExceptions

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CSQLInfoEnumerator::CSQLInfoEnumerator()
{
	ClearAll();
}

CSQLInfoEnumerator::~CSQLInfoEnumerator()
{
	ClearAll();
}

/*
	Clear all internal storing CStringArrays
*/

BOOL CSQLInfoEnumerator::ClearAll()
{
	m_szSQLServersArray.RemoveAll();
	m_szSQLServerDatabaseArray.RemoveAll();
	m_szSQLServerLanguageArray.RemoveAll();
	return TRUE;
}

/*
	Retrieve Information of SQL Servers
	On Success the string contains identifier 
	SERVER:Server= followed by the list of SQL
	Servers
*/

BOOL CSQLInfoEnumerator::EnumerateSQLServers()
{
	//Browse Connect for SQL Server Driver defined servers
	//The return information would contain SERVER:Server= Keyword followed by 
	//{list of Servers} separated by the character ';'
	return RetrieveInformation(_T("Driver={SQL Server}"),_T("SERVER:Server="),m_szSQLServersArray);
}

/*
	Retrieve Information of databases in a SQL Server
	You have to provide the User Id and Password
	On Success the string contains identifier 
	DATABASE:Database= followed by the list of databases
*/
BOOL CSQLInfoEnumerator::EnumerateDatabase(LPCTSTR pszSQLServer, LPCTSTR pszUserId, LPCTSTR pszPwd)
{
	//Browse Connect for SQL Server Driver defined server using the authentication information
	//The return information would contain DATABASE:Database= Keyword followed by 
	//{list of databases} separated by the character ';'
	CString szInputParam;
	szInputParam.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,pszUserId,pszPwd);
	return RetrieveInformation(szInputParam,_T("DATABASE:Database="),m_szSQLServerDatabaseArray);
}

/*
	Retrieve Information of languages in a SQL Server
	You have to provide the User Id and Password
	On Success the string contains identifier 
	LANGUAGE:Language= followed by the list of languages
	Character Translation is not done, so you may see
	special characters and question marks in the list of
	languages text
*/

BOOL CSQLInfoEnumerator::EnumerateDatabaseLanguage(LPCTSTR pszSQLServer, LPCTSTR pszUserId, LPCTSTR pszPwd)
{
	CString szInputParam;
	//Browse Connect for SQL Server Driver defined server using the authentication information
	//The return information would contain LANGUAGE:Language= Keyword followed by 
	//{list of languages} separated by the character ';'
	szInputParam.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,pszUserId,pszPwd);
	return RetrieveInformation(szInputParam,_T("LANGUAGE:Language="),m_szSQLServerLanguageArray);
}

/*
	This Function Checks for retrieving additional information
	using the initial input information and returns true if the 
	look up key is found and fills ther result set into a string 
	array.
*/


BOOL CSQLInfoEnumerator::RetrieveInformation(LPCTSTR pszInputParam,LPCTSTR pszLookUpKey,CStringArray &szArray)
{
	SQLHENV       hSQLEnv;
	SQLHDBC       hSQLHdbc;
	short		  sConnStrOut;
	BOOL		  bReturn = FALSE;

	//Allocate the environment handle
	m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
	if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
	{
		//Set the environment attribute to SQL_OV_ODBC3
		m_iRetcode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
		if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) 
		{
			//Allocate a connection handle
			m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
			if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) 
			{
				CString szConnStrOut;
				//Call SQLBrowseConnect for additional information
				
				m_iRetcode = SQLBrowseConnect(hSQLHdbc, (SQLCHAR *)pszInputParam, SQL_NTS,
							 (SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH, &sConnStrOut);
				szConnStrOut.ReleaseBuffer();

				if (m_iRetcode == SQL_NEED_DATA || m_iRetcode == SQL_ERROR || m_iRetcode ==  SQL_INVALID_HANDLE)
				{
					CString strMsg(_T(""));
					CString strActualMsg(_T(""));
					CString strCheck(_T(""));

					char szMsg[255] = {0};

					strCheck = "Also verify your username,password and server name.";

					SDWORD sdwNative;
					SWORD swMsgLen;
					SQLError(hSQLEnv, hSQLHdbc, 0, 0, &sdwNative,(SQLTCHAR *)&szMsg, sizeof(szMsg), &swMsgLen);
					strActualMsg = szMsg;
					strMsg.Format("Error %d \nSQL message = %s\n\n%s", m_iRetcode, strActualMsg,strCheck);

					if(!strActualMsg.IsEmpty())
						MessageBox(NULL, strMsg, "Error", MB_OK | MB_ICONSTOP);
				}

				//if the look up key is found
				//fill in the result set

				int iFind = szConnStrOut.Find(pszLookUpKey);
				if(iFind != -1)
				{
					CString szLookUpKey = pszLookUpKey;
					szConnStrOut = szConnStrOut.Mid(iFind+szLookUpKey.GetLength());
					iFind = szConnStrOut.Find('{');
					if(iFind != -1)
					{
						szConnStrOut = szConnStrOut.Mid(iFind+1);
						iFind = szConnStrOut.Find('}');
						if(iFind != -1)
						{
							szConnStrOut = szConnStrOut.Left(iFind);
							FillupStringArray(szConnStrOut,szArray);
							bReturn = TRUE;
						}
					}
				}

				SQLDisconnect(hSQLHdbc);
			}
			SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
		}
		SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
	}	

	return TRUE;
}


/*Breaks up return information into a CStringArray for easy parsing*/

BOOL CSQLInfoEnumerator::FillupStringArray(LPCTSTR pszData,CStringArray &szArray,TCHAR chSep)
{
	CString szStr = pszData;
	CString szSep = chSep;
	szStr.TrimLeft();
	szStr.TrimRight();
	szArray.RemoveAll();
	int iStrLen = szStr.GetLength(),iSepPos,iSepLength=szSep.GetLength();
	if(iStrLen>0 )
	{
		if(szStr.Right(iSepLength) != szSep)
			szStr += szSep;
		iStrLen = szStr.GetLength();
	}
	else 
		return FALSE;
	
	CString szContentStr;
	while(!szStr.IsEmpty())
	{
		iSepPos = szStr.Find(szSep);
		szContentStr = szStr.Left(iSepPos);
		szContentStr.TrimLeft();
		szContentStr.TrimRight();
		if(!szContentStr.IsEmpty())
			szArray.Add(szContentStr);
		iStrLen  = iStrLen - (iSepPos + iSepLength);
		szStr = szStr.Right(iStrLen);
	}
	return TRUE;
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

VGirish
Founder
India India
No Biography provided

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 31 May 2006
Article Copyright 2006 by VGirish
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid