SQL Server and Database Enumerator

SQL Server and Database Enumerator

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, m_szSQLServerLanguageArray data members respectively.

The heart of this class uses the function SQLBrowseConnect that enables 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.

So by passing a incomplete connect string example "Driver={SQL Server};." would cause retrieving of list of SQL servers when passed to the SQLBrowseConnect would cause retrieving of the list of SQL servers as the server information is missing in the connect string , or by passing "Driver={SQL Server};SERVER=MYSQLSERVER; APP=MFCAPP; WSID=San;UID=san;PWD=123;UseProcForPrepare=0;" would cause retrieval of the list of databases as 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 found
//fill 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.

Snapshot of sample application:

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

You can reach me at SantoshRao@bigfoot.com