A Component to List the System DSNs on a Web Server
By Mike Shaffer
At various times during the development cycle for a project, I often need to examine the structure of databases
that are sitting on my servers. I wanted to have a tool that would allow me to quickly and easily choose from
a list of registered DSN's and then show me a list of all tables within that database, as well as the layout
of each table.
This article is about a VB ActiveX component that returns a list of system DSN's. This may be a useful tool
for some of you. For example, in the article Creating a SQL Test Page,
Geno Timlin showed how a user could select a particular database through a list box of DSNs.
However, these selectable DSNs had to be hard coded into the ASP page.
This component would assist with Geno's application, automatically populating the list box with all of the
DSNs available on the Web server.
To obtain a list of registered DSN's on the web server (either PWS or IIS), I created a VB5 component (this code would also work
with VB6). You will have to download the source code, compile it into an ActiveX DLL, and register it on
the web server using regsvr32. Or you can simply download the pre-built DLL and register it on
the web server using regsvr32. (To learn more about using regsvr32 to register
components on the web server, be sure to check out this article.)
The component contains a single function, GetDSNs() which returns a list of the System DSNs on the
web server in a delimited string. The delimiter used is the pipe. An example output from this function would
be:
MQIS (SQL Server)|4Guys (SQL Server)|forum (SQL Server)|classified (SQL Server)|
|
This delimited string can then be parsed to create a select box! There is an example
of this on 4Guys for you to check out before downloading the source and DLL. The source code for the application
can be viewed at the bottom of the article, or downloaded as a VB project!
Happy Programming!
Attachments:
See the component in action
Download the DSNList2.dll
Source Code:
Download the DSNList.cls Class
Download the DSNList.vbp Project
Following is the source code for the VB component.
<![CDATA[
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: :::
'::: TITLE: DSNLister (ActiveX DLL) :::
'::: AUTHOR: Mike Shaffer :::
'::: DATE: 21-Dec-99 :::
'::: PURPOSE: Returns a list of DSN's with a vertical bar as :::
'::: a delimiter and a tab character separating the :::
'::: DSN name and the driver spec. :::
'::: :::
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Option Explicit
Private Const SQL_SUCCESS As Long = 0 ' ODBC Success
Private Const SQL_ERROR As Long = -1 ' ODBC Error
Private Const SQL_FETCH_NEXT As Long = 1 ' ODBC Move Next
Private Declare Function SQLDataSources Lib "ODBC32.DLL" _
(ByVal hEnv As Long, ByVal fDirection _
As Integer, ByVal szDSN As String, _
ByVal cbDSNMax As Integer, pcbDSN As Integer, _
ByVal szDescription As String, ByVal cbDescriptionMax _
As Integer, pcbDescription As Integer) As Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" _
(env As Long) As Integer
'
Public Function GetDSNs() As String
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: :::
'::: This routine does the actual work :::
'::: :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'
Dim intRetCode As Integer ' the return code
Dim strDSNItem As String ' the dsn name
Dim strDRVItem As String ' the driver name
Dim strDSN As String ' the formatted dsn name
Dim intDSNLen As Integer ' the length of the dsn name
Dim intDRVLen As Integer ' the length of the driver name
Dim hEnv As Long ' handle to the environment
Dim strTemp As String ' Tempspace
Dim strDSNTemp As String ' Tempspace
On Error Resume Next
If (SQLAllocEnv(hEnv) <> SQL_ERROR) Then
Do
strDSNItem = Space$(1024)
strDRVItem = Space$(1024)
intRetCode = SQLDataSources(hEnv, SQL_FETCH_NEXT, strDSNItem, _
Len(strDSNItem), intDSNLen, strDRVItem, _
Len(strDRVItem), intDRVLen)
strDSN = Left$(strDSNItem, intDSNLen)
If (Len(strDSN) > 0) And (strDSN <> Space$(intDSNLen)) Then
strDSNTemp = strDSN & vbTab & "(" & _
Left$(strDRVItem, intDRVLen) & ")|"
' Check for dupes...
If InStr(strTemp, strDSNTemp) = 0 Then
strTemp = strTemp & strDSNTemp
End If
End If
Loop Until intRetCode <> SQL_SUCCESS
End If
GetDSNs = strTemp
End Function
]]>
|