65.9K
CodeProject is changing. Read more.
Home

SQL Server and Database Enumerator

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.18/5 (9 votes)

Apr 26, 2000

CPOL
viewsIcon

117460

downloadIcon

3861

A class that helps enumerate SQL servers, databases and languages.

  • Download demo executable - 8 Kb
  • Download source files - 33 Kb
  • Sample Image - SQLSrvEnumerator.jpg

    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 SantoshRao@bigfoot.com