Enumerating Data Sources

How can I enumerate the ODBC data sources on a given server programatically? I'd like to present the user with a choice of data sources. I'm using VC6 & MFC's ODBC classes (CDatabase, CRecordset, etc.) but will get dirty if I have to.

Any suggestions?

Dave Relyea

[Mahanthi]

Hi,
I have used the following code ( slightly modified from what i have used ).


void GetDataSources(BSTR * pbstrDataSources)
{

CComBSTR bstrDataSources;

HENV EnvHandle;
SQLRETURN rc = SQL_SUCCESS;

rc = SQLAllocEnv( &EnvHandle );

if( rc == SQL_ERROR )
{
return;
}

char DataSources [31];
char Description [255];
SQLSMALLINT DS_Size;
SQLSMALLINT DescSize;

for( int i=0; i<5; i++ )
{
rc = SQLDataSources(EnvHandle,
SQL_FETCH_NEXT,
(unsigned char *)DataSources,
sizeof( DataSources ),
&DS_Size,
(unsigned char *)Description,
sizeof( Description ),
&DescSize );

// Pack the Parameter to return
if( rc != SQL_NO_DATA )
{
CComBSTR bstrDS( DataSources );
bstrDataSources.Append( bstrDS );
}
}

*pbstrDataSources = BSTR( bstrDataSources );

// Free the ODBC environment
if( EnvHandle != NULL )
{
SQLFreeHandle( SQL_HANDLE_ENV, EnvHandle );
}

return;
}


[email protected]

Well, I found a way, so I thought I'd answer my own post. If I look in the registry in this key:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\odbc.ini\ODBC Data Sources

the item names will be the names of the registered ODBC data sources.

In VB, you can add the "ODBC Driver & Data Source Name Functions" in the project references, create a DSN object, and then call the GetDataSourceList function. Since that is probably a COM object, I could probably create that and call the function, however, I'd rather not have an additional file to distribute.

If looking at the registry does the trick, then fair enough. However, I am still in search of a better way.

Dave Relyea

Mahanthi,

Thanks. That did the trick. I modified your function slightly to make it return a comma delimited list of system DSNs.

Dave Relyea

rc = SQLDataSources(EnvHandle,
SQL_FETCH_FIRST_SYSTEM,
(unsigned char *)DataSources,
sizeof( DataSources ),
&DS_Size,
(unsigned char *)Description,
sizeof( Description ),
&DescSize );

while ( rc != SQL_NO_DATA )
{
CComBSTR bstrDS( DataSources );
if ( bstrDataSources.Length() )
bstrDataSources += ",";
bstrDataSources.Append( bstrDS );

rc = SQLDataSources(EnvHandle,
SQL_FETCH_NEXT,
(unsigned char *)DataSources,
sizeof( DataSources ),
&DS_Size,
(unsigned char *)Description,
sizeof( Description ),
&DescSize );
}