SQL Server and Database Enumerator

Introduction

This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has.

The class CSQLInfoEnumerator encapsulates this functionality into it.

It has the functions:

BOOL EnumerateSQLServers();
BOOL EnumerateDatabase(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);
BOOL EnumerateDatabaseLanguage(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);

to perform this task. It fills in its result into the CStringArray m_szSQLServersArray, m_szSQLServerDatabaseArray, and m_szSQLServerLanguageArray data members respectively.

The heart of this class uses the function SQLBrowseConnect that enables you to build upon an incomplete connect string.

Example of a connect string:

ODBC;Driver={SQL Server};SERVER=MYSQLSERVER;APP=MFCAPP;WSID=San;DATABASE=mydb;UseProcForPrepare=0; UID=san;PWD=123

Note: A connect string is used to establish a database connection using the CDatabase Open or OpenEx member functions.

Passing an incomplete connect string such as ">Driver={SQL Server};." would cause retrieval of a list of SQL servers. When passed to the SQLBrowseConnect it would retrieve of a list of SQL servers as the server information is missing in the connect string. By passing "Driver={SQL Server};SERVER=MYSQLSERVER; APP=MFCAPP; WSID=San;UID=san;PWD=123;UseProcForPrepare=0;" it would retrieve a list of databases since the database information is missing. The RetrieveInformation function in the CSQLInfoEnumerator class encapuslates this function.

The function SQLDisconnect has to be called at the end of the SQLBrowseConnect browsing operation completion.

The complete function RetrieveInformation is as follows.

//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 the look up key is foundfill in the result set
			SQLDisconnect(hSQLHdbc);
		}
		SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
	}
	SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
}

The CSQLInfoEnumerator class requires linking with the odbc32.lib file.

The sample application attached uses the CSQLInfoEnumerator class to display the list of SQL Servers, databases and supported languages.

Limitations

When the list of languages supported by a particular SQL server is performed, it does not support character translation for the language name meaning you may see special characters and ?'s in the language listing for particular SQL Servers.

You can reach me at [email protected]