|
|
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="Generator" CONTENT="Microsoft Word 97">
<TITLE>SQL Server and Database Enumerator</TITLE>
<META NAME="Version" CONTENT="8.0.3410">
<META NAME="Date" CONTENT="10/11/96">
<META NAME="Template" CONTENT="C:\Program Files\Microsoft Office\Office\HTML.DOT">
</HEAD>
<BODY TEXT="#000000" LINK="#0000ff" VLINK="#800080" BGCOLOR="#ffffff">
<P ALIGN="CENTER"><BR>
<BR>
<B>SQL Server and Database Enumerator</P>
</B><P>This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. </P>
<P>The class CSQLInfoEnumerator encapsulates this functionality into it. </P>
<P>It has the functions 	</P>
<B><CODE><P>BOOL EnumerateSQLServers();</P>
<P>BOOL EnumerateDatabase(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);</P>
<P>BOOL EnumerateDatabaseLanguage(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd);</P>
</B></CODE><P>to perform this task. It fills in its result into the <CODE>CStringArray m_szSQLServersArray, m_szSQLServerDatabaseArray, m_szSQLServerLanguageArray data members respectively.</P>
</CODE><P>The heart of this class uses the function <B>SQLBrowseConnect</B> that enables to build upon an incomplete connect string. </P>
<I><FONT FACE="Courier New"><P>Example of a connect string:</P>
</FONT><FONT FACE="Courier New" SIZE=1><P>ODBC;Driver={SQL Server};SERVER=MYSQLSERVER;APP=MFCAPP;WSID=San;DATABASE=mydb;UseProcForPrepare=0; UID=san;PWD=123</P>
<P>Note: A connect string is used to establish a database connection using the CDatabase Open or OpenEx member functions.</P>
</I></FONT><P>So by passing a incomplete connect string example "<I><FONT FACE="Courier New" SIZE=1>Driver={SQL Server};</I></FONT>." 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 "<I><FONT FACE="Courier New" SIZE=1>Driver={SQL Server};SERVER=MYSQLSERVER; APP=MFCAPP; WSID=San;UID=san;PWD=123;UseProcForPrepare=0;" </I></FONT>would cause retrieval of the list of databases as the database information is missing. The RetrieveInformation function in the CSQLInfoEnumerator class encapuslates this function.</P>
<P>The function SQLDisconnect has to be called at the end of the SQLBrowseConnect browsing operation completion. </P>
<P>The complete function RetrieveInformation is as follows.</P>
<P><IMG SRC="line2.gif" WIDTH=573 HEIGHT=5></P>
<CODE><P>//Allocate the environment handle</P>
<P>m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);</P>
<P>if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO){</P>
<P>	//Set the environment attribute to SQL_OV_ODBC3</P>
<P>m_iRetcode = SQLSetEnvAttr(hSQLEnv,SQL_ATTR_ODBC_VERSION,</P>
<P>(void *)SQL_OV_ODBC3, 0);</P>
<P>if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) {</P>
<P>//Allocate a connection handle</P>
<P>m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);</P>
<P>if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO) {</P>
<P>CString szConnStrOut;</P>
<P>//Call SQLBrowseConnect for additional information</P>
<P>m_iRetcode = SQLBrowseConnect(hSQLHdbc, (SQLCHAR *)pszInputParam, SQL_NTS, (SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH,&sConnStrOut);</P>
<P>szConnStrOut.ReleaseBuffer();</P>
<P>//if the look up key is found</P>
<P>//fill in the result set</P>
<P>SQLDisconnect(hSQLHdbc);</P>
<P>}</P>
<P>SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);</P>
<P>}</P>
<P>SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);</P>
<P>}	</P>
</CODE><P><IMG SRC="line2.gif" WIDTH=573 HEIGHT=5></P>
<P>The CSQLInfoEnumerator class requires linking with the odbc32.lib file.</P>
<P>The sample application attached uses the CSQLInfoEnumerator class to display the list of SQL Servers, databases and supported languages.</P>
<P>Snapshot of sample application:</P>
<P><IMG SRC="sampleapp.jpg" WIDTH=350 HEIGHT=243></P>
<P>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.</P>
<P>You can reach me at <A HREF="mailto:SantoshRao@bigfoot.com">SantoshRao@bigfoot.com</A></P></BODY>
</HTML>
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
Submit an article or tip