Click here to Skip to main content
13,002,676 members (58,686 online)
Click here to Skip to main content
Add your own
alternative version

Stats

99.6K views
3.5K downloads
43 bookmarked
Posted 25 Apr 2000

SQL Server and Database Enumerator

, 25 Apr 2000
Rate this:
Please Sign up or sign in to vote.
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Share

    About the Author

    Santosh Rao
    Architect
    India India
    1993 started with Computers

    BE(Computer Science) and MS (Software Systems)

    Industry Experience: 10 Years

    C, C++, VC++(MFC), .NET, C#, MTS, Queuing, ASP.NET, AJAX, Java, J2EE, SunOne, JMS

    Banking, Insurance & Pension,Health Care

    You may also be interested in...

    Comments and Discussions

     
    GeneralThanks Pin
    PJ Arends7-Jan-11 7:18
    memberPJ Arends7-Jan-11 7:18 
    AnswerDrivers for later generations of SQL-server Pin
    Jonas Hammarberg16-May-09 2:48
    memberJonas Hammarberg16-May-09 2:48 
    QuestionUsage of code in commercial offering? Pin
    JHey20084-Nov-08 15:11
    memberJHey20084-Nov-08 15:11 
    GeneralOther databases Pin
    Flora PL10-Sep-04 3:08
    memberFlora PL10-Sep-04 3:08 
    GeneralCannot retrieve list of Databases on Trusted connections Pin
    Anonymous25-Oct-02 0:09
    sussAnonymous25-Oct-02 0:09 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) Pin
    Jacques Beaurain4-Feb-03 11:40
    sussJacques Beaurain4-Feb-03 11:40 
    I changed the class to handle trusted connections.
    I also added code to retrieve/test login information
    and fixed the member to return false on errors.
    The commented functions are an external error handler.
    Here is the code (This includes the language fix).

    Hope someone finds this useful.

    <code>

    // SQLInfoEnumerator.h: interface for the CSQLInfoEnumerator class.
    //
    //////////////////////////////////////////////////////////////////////

    #if !defined(AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_)
    #define AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_

    #if _MSC_VER > 1000
    #pragma once
    #endif // _MSC_VER > 1000

    #define MAX_RET_LENGTH 4824

    class CSQLInfoEnumerator
    {
    public:
    CSQLInfoEnumerator();
    virtual ~CSQLInfoEnumerator();
    int m_iRetcode;
    H_AP *m_hAP;

    public:
    CStringArray m_szSQLServersArray;
    CStringArray m_szSQLServerDatabaseArray;
    CStringArray m_szSQLServerLanguageArray;
    public:
    BOOL EnumerateDatabase(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd, BOOL bWinAuth);
    BOOL EnumerateDatabaseLanguage(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd, BOOL bWinAuth);
    BOOL EnumerateSQLServers();
    BOOL RetrieveLogin(LPCTSTR pszSQLServer, CString &szUID, CString &szPWD, BOOL &bWinAuth, short sPrompt, HWND hWnd);
    protected:
    BOOL RetrieveInformation(LPCTSTR pszInputParam,LPCTSTR pszLookUpKey,CStringArray &szArray, BOOL bWinAuth);
    BOOL FillupStringArray(LPCTSTR pszData,CStringArray &szArray,TCHAR chSep = ',');
    BOOL ClearAll();
    };

    #endif // !defined(AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_)


    //=============================================================================
    // SQLInfoEnumerator.cpp: implementation of the CSQLInfoEnumerator class.
    //
    //////////////////////////////////////////////////////////////////////

    #include "stdafx.h"

    #include <sys.h>
    #include <geosoft.h>
    #include <ap.h>

    #include "geomfcsql.h"

    #include "sqlinfoenumerator.h"

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

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

    /*
    Author: Santosh Rao
    EMail Id: SantoshRao@bigfoot.com
    Date: Dec 31st 1999
    Comment: Simple Class to enable enumeration
    of SQL Servers and their databases and supported
    languages.
    You may use this code in any way you want,
    You can remove my copyright too.
    But dont sue me in court please, i cant afford it.

    */

    CSQLInfoEnumerator::CSQLInfoEnumerator()
    {
    m_hAP = NULL;
    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:"),m_szSQLServersArray, FALSE);
    }

    /*
    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, BOOL bWinAuth)
    {
    //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:"),m_szSQLServerDatabaseArray, bWinAuth);
    }

    /*
    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, BOOL bWinAuth)
    {
    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:"),m_szSQLServerLanguageArray, bWinAuth);
    }

    /*
    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, BOOL bWinAuth)
    {
    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 (bWinAuth)
    SQLSetConnectOption(hSQLHdbc, SQL_INTEGRATED_SECURITY, SQL_IS_ON);

    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 found
    //fill in the result set
    int iFind = szConnStrOut.Find(pszLookUpKey);
    TRACE(szConnStrOut);
    if(iFind != -1)
    {
    int iFind2 = szConnStrOut.Find('=', iFind);
    if(iFind2 != -1)
    {
    for(int i=iFind;i<iFind2;i++)
    {
    char c = szConnStrOut[i];

    if(!isalnum(c) && c != ':')
    {
    break;
    }
    }
    if(i == iFind2)
    {
    CString szLookUpKey = pszLookUpKey;
    szConnStrOut = szConnStrOut.Mid(iFind2+1);

    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);
    }

    // if (!bReturn)
    // ProcessODBCMessagesMFCSQL(m_hAP,
    // SQL_HANDLE_DBC,
    // hSQLHdbc,
    // "CSQLInfoEnumerator::RetrieveLogin", FALSE);
    return bReturn;
    }

    BOOL CSQLInfoEnumerator::RetrieveLogin(LPCTSTR pszSQLServer, CString &szUID, CString &szPWD, BOOL &bWinAuth, short sPrompt, HWND hWnd)
    {
    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 szConnStrIn;
    CString szConnStrOut;
    int iFind;

    szConnStrIn.GetBuffer(MAX_RET_LENGTH);

    if (bWinAuth)
    szConnStrIn.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s;Trusted_Connection=yes",pszSQLServer,szUID,szPWD);
    else
    szConnStrIn.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,szUID,szPWD);

    //Call SQLDriverConnect for user information
    m_iRetcode = SQLDriverConnect(hSQLHdbc, hWnd, (SQLCHAR *) (szConnStrIn.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH,
    (SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH, &sConnStrOut, sPrompt);

    szConnStrIn.ReleaseBuffer();
    szConnStrOut.ReleaseBuffer();

    if (m_iRetcode == SQL_SUCCESS_WITH_INFO || m_iRetcode == SQL_SUCCESS) {
    iFind = szConnStrOut.Find("UID");
    if(iFind != -1)
    {
    szUID = szConnStrOut.Mid(iFind+4);
    iFind = szUID.Find('=');
    if(iFind != -1)
    {
    szUID = szUID.Mid(iFind+1);
    iFind = szUID.Find(';');
    if(iFind != -1)
    szUID = szUID.Left(iFind);
    }
    }

    iFind = szConnStrOut.Find("PWD");
    if(iFind != -1)
    {
    szPWD = szConnStrOut.Mid(iFind+4);
    iFind = szPWD.Find('=');
    if(iFind != -1)
    {
    szPWD = szPWD.Mid(iFind+1);
    iFind = szPWD.Find(';');
    if(iFind != -1)
    szPWD = szPWD.Left(iFind);
    }
    }

    bWinAuth = FALSE;
    iFind = szConnStrOut.Find("Trusted_Connection");
    if(iFind != -1)
    {
    szPWD = szConnStrOut.Mid(iFind+4);
    iFind = szPWD.Find('=');
    if(iFind != -1)
    {
    szPWD = szPWD.Mid(iFind+1);
    iFind = szPWD.Find(';');
    if(iFind != -1) {
    szPWD = szPWD.Left(iFind);
    }
    szPWD.MakeLower();
    iFind = szPWD.Find("yes");
    if(iFind != -1)
    bWinAuth = TRUE;
    }
    }

    bReturn = TRUE;
    }
    SQLDisconnect(hSQLHdbc);
    }
    SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
    }

    // if (!bReturn)
    // ProcessODBCMessagesMFCSQL(m_hAP,
    // SQL_HANDLE_DBC,
    // hSQLHdbc,
    // "CSQLInfoEnumerator::RetrieveLogin", FALSE);


    return bReturn;
    }

    /*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;
    }

    </code>
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) Pin
    BSR15-Jan-04 2:44
    memberBSR15-Jan-04 2:44 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) Pin
    BSR15-Jan-04 2:45
    memberBSR15-Jan-04 2:45 
    GeneralRe: Cannot retrieve list of Databases on Trusted connections (Fixed, Added RetrieveLogin) Pin
    jbeaurain15-Jan-04 3:15
    memberjbeaurain15-Jan-04 3:15 
    GeneralInternationalization BUG & Repair in SQL Server and Database Enumerator Pin
    gbalog17-Jul-02 2:03
    membergbalog17-Jul-02 2:03 
    GeneralThe modifyed File is here for Internationalization BUG & Repair in SQL Server and Database Enumerator Pin
    gbalog17-Jul-02 2:04
    membergbalog17-Jul-02 2:04 
    GeneralRe: The modifyed File is here for Internationalization BUG & Repair in SQL Server and Database Enumerator Pin
    Santosh Rao17-Jul-02 18:57
    memberSantosh Rao17-Jul-02 18:57 
    GeneralSQLBrowseConnect Pin
    Simone Giannecchini16-Dec-01 1:55
    memberSimone Giannecchini16-Dec-01 1:55 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    Permalink | Advertise | Privacy | Terms of Use | Mobile
    Web02 | 2.8.170626.1 | Last Updated 26 Apr 2000
    Article Copyright 2000 by Santosh Rao
    Everything else Copyright © CodeProject, 1999-2017
    Layout: fixed | fluid