Get list of System DSNs

[SteveDingle]

Heya All,

Just wondering if there is a simple way to get a list of the System DSN installed on a given PC

Thanks,
Steve

 

[bborissov]

try:

CLEAR

strComputer = "." 
objWMIService = GetObject("winmgmts:\\" + strComputer + "\root\CIMV2") 
colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ODBCDataSourceSpecification",,48) 
For Each objItem in colItems 
    IF objItem.Registration == 1
       ? "DataSource: ", objItem.DataSource,;
                         objItem.Caption,;
                         objItem.Name,;
                         objItem.Registration
Next

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SteveDingle]

Hello Borislav,

Thanks for the code. A couple of comments/questions

>> IF objItem.Registration == 1

Got a data type error on this so changed to "1" and it worked ok....after putting in the ENDIF ;-)

I use ZoneAlarm and this code triggered a request from WMI to access the internet, which I denied. Any way to stop that from happening?

Also, it took about 12 seconds to get the list. Any way I can speed up the process? My end result, which I should have mentioned, was to provide a list for users to select from.

I can put up a "Please Wait" type message but would prefer obviously if I could get this info quicker.

Thanks again for the code as it does what I need it to do!

Steve

 

[bborissov]

I don't know why this query is so slow. Maybe it is faster directly to read the registry. All System DSNs are registered under
\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SteveDingle]

Thanks Borislav, will give that a go

 

[MarciaAkins]

Heya Steve!

Just wondering if there is a simple way to get a list of the System DSN installed on a given PC

Take a look at the solution samples under the Windows API category. There is a sample that reads ODBC registry values.


Marcia G. Akins

http://www.TightlineComputers.com

 

[SteveDingle]

Heya Marcia... how's things?

Thansk for pointer, I always forget to check sample solutions, not just help file

Tooldes,
Steve

 

[MarciaAkins]

Heya Marcia... how's things?

Things are busy. Andy chance that you guys will make it over here for Southwest Fox?


Marcia G. Akins

http://www.TightlineComputers.com

 

[Steve Bowman]

Try this

PARAMETERS lcOdbcDriver, lReturnList

IF TYPE("lReturnList")<>"L"
	lReturnList=.f.
ENDIF

IF TYPE("lcOdbcDriver")="C"
	lcOdbcDriver=UPPER(ALLTRIM(lcOdbcDriver))
ELSE
	lcOdbcDriver=""
ENDIF
lcReturnText=""
lReturnValue=.f.
lcDrivers = SPACE(1000)
lnSize    = 1000
lnNewSize = 0

DECLARE Integer SQLGetInstalledDrivers in ;
 odbccp32.dll String @, Short, Short @

*** Find all driveers installed in the DLL ***
SQLGetInstalledDrivers(@lcDrivers, lnSize, @lnNewSize)
IF lnNewSize > 0
 lcDrivers = SPACE(lnNewSize)
 lnSize    = lnNewSize
 
 IF SQLGetInstalledDrivers(@lcDrivers, lnSize, @lnNewSize) = 1
  lReturnValue=IIF(OCCURS(lcOdbcDriver,ALLTRIM(UPPER(lcDrivers)))>0,.t.,.f.)
  lcReturnText=lcDrivers
 ENDIF
ENDIF
IF lReturnList
	RETURN CHRTRAN(lcReturnText,CHR(0),CHR(10))
ELSE 
	RETURN lReturnValue
ENDIF

The function has two parameters
? getdsn("cDSNName","lReturnList")
the function was originally designed to test for a spacific DSN installed I added the ReturnList parameter later

? getdsn("",.t.) will produce what you are looking for
modify the code and you could populate a drolpown

Steve Bowman
Independent Technology, Inc.
CA, USA

 

[SteveDingle]

Heya Marcia,

Nah, no US trips this year. Will be in Germany tho, probably try to have a post-conference weekend excursion again.

tc,
Steve

 

[SteveDingle]

Heya Steve,

Thanks for the code but I was looking for a list of the defined System DSN's, when I run this it give me a list of the driver installed. Which I will put in my bag of tricks

tc,
steve

 

[Steve Bowman]

You are right, my bad. I have one very simular to this for DSN's. I will find and post. If you still need it

Steve Bowman
Independent Technology, Inc.
CA, USA

 

[Steve Bowman]

Here it is, this is NOT ALL my own work. I have only modified it for my use. I forget exactly where I got it and can not give credit where credit is due

? getdsnlist("LIST")
? getdsnlist("TABLE")
this is real nice work and the TABLE function return all the details of the DSN's

Parameters caction
Note choices are LIST, TABLE
If Type("cAction")<>"C"
	cAction="LIST"
Else
	cAction=Upper(cAction)
EndIf
If cAction<>"LIST" .and. cAction<>"TABLE"
	cAction="LIST"
EndIf

nCurrentWorkArea=Select(0)

#DEFINE ODBC_BOTH_DSN   0  && BOTH may not work as expected 
#DEFINE ODBC_USER_DSN   1 
#DEFINE ODBC_SYSTEM_DSN 2 
DO decl 

LOCAL nConfigMode, cSources, cSource, cParamNames, cParam, ii, jj 

* saving current value for SQLConfigMode 
nConfigMode = GetConfigMode() 
= SQLSetConfigMode(ODBC_SYSTEM_DSN) 

cSources = Chr(0) + GetPS("ODBC Data Sources", Null) + Chr(0) 
CREATE CURSOR csResult (odbcsource C(30), prmname C(20), prmvalue C(200)) 

ii=1 
DO WHILE .T. 
    cSource = GetSubstr(cSources, ii, Chr(0)) 
    IF EMPTY(cSource) 
        EXIT 
    ENDIF 

    cParamNames = GetPS(cSource, Null) 
    INSERT INTO csResult VALUES (cSource, "All parameters",; 
        STRTRAN(cParamNames, Chr(0), " ")) 

    cParamNames = Chr(0) + GetPS(cSource, Null) + Chr(0) 
    jj = 1 
    DO WHILE .T. 
        cParam = GetSubstr(cParamNames, jj, Chr(0)) 
        IF EMPTY(cParam) 
            EXIT 
        ENDIF 
        = AddParam(cSource, cParam) 
        jj = jj + 1 
    ENDDO 
    ii = ii + 1 
ENDDO 

* restoring SQLConfigMode value 
= SQLSetConfigMode(m.nConfigMode) 

If cAction="LIST"
	Select odbcsource DISTINCT from csResult into cursor dsnresult
	cOdbcdsnlist=""
	Scan
		cOdbcdsnlist=cOdbcdsnlist+Alltrim(dsnresult.odbcsource)+Chr(10)
	EndScan
	retValue=Left(Alltrim(cOdbcdsnlist),Len(Alltrim(cOdbcdsnlist))-1)
	Use in csResult
	Use in dsnresult
	Select (nCurrentWorkArea)
	Return (retValue)
Else
	GO TOP 
	BROWSE NORMAL NOWAIT 
EndIf
* end of main 

PROCEDURE AddParam(cKey, pname) 
    LOCAL pvalue 
    pvalue = GetPS(cKey, m.pname) 
    pvalue = STRTRAN(pvalue, Chr(0),"") 
    IF Not EMPTY(pvalue) 
        INSERT INTO csResult VALUES (m.cKey,; 
            m.pname, m.pvalue) 
    ENDIF 

FUNCTION GetPS(section, entry) 
    LOCAL cBuffer, nLen 
    cBuffer = Repli(Chr(0), 250) 
    nLen = SQLGetPrivateProfileString(section, entry, "",; 
        @cBuffer, Len(cBuffer), "ODBC.INI") 
RETURN Iif(nLen=0, "", SUBSTR(cBuffer, 1, nLen)) 

FUNCTION GetSubstr(cSource, nIndex, cChar) 
    LOCAL nPos1, nPos2 
    nPos1 = AT(cChar, cSource, nIndex) 
    nPos2 = AT(cChar, cSource, nIndex+1) 
    IF MIN(nPos1, nPos2) <> 0 
        RETURN SUBSTR(cSource, nPos1+1, nPos2-nPos1-1) 
    ENDIF 
RETURN "" 

FUNCTION GetConfigMode 
    LOCAL nMode 
    nMode=0 
    = SQLGetConfigMode(@nMode) 
RETURN m.nMode 

PROCEDURE decl 
    DECLARE INTEGER SQLSetConfigMode IN odbccp32 LONG wConfigMode 
    DECLARE INTEGER SQLGetConfigMode IN odbccp32 LONG @pwConfigMode 
      
    DECLARE INTEGER SQLGetPrivateProfileString IN odbccp32; 
        STRING lpszSection, STRING lpszEntry, STRING lpszDefault,; 
        STRING @RetBuffer, INTEGER cbRetBuffer, STRING lpszFilename

Steve Bowman
Independent Technology, Inc.
CA, USA

 

[SteveDingle]

Heya Steve,

Sorry for dealy in the "thanks", seems to be just what the doctor ordered , will give it a run

tc,
steve