Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

SQL Server and Database Enumerator

0.00/5 (No votes)
25 Apr 2000 1  
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

    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