Download demo executable - 8 Kb
Download source files - 33 Kb
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.
m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
m_iRetcode = SQLSetEnvAttr(hSQLEnv,SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
CString szConnStrOut;
m_iRetcode = SQLBrowseConnect(hSQLHdbc, (SQLCHAR *)pszInputParam, SQL_NTS,
(SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)),
MAX_RET_LENGTH,&sConnStrOut);
szConnStrOut.ReleaseBuffer();
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