List installed data sources 2

[torturedmind]

Hello all,

A few days ago, I had a need to list down all Data Source Names (DSN) installed on a local computer. Luckily, I found this cool article that precisely describes how to do it. The code from the article is not properly formatted so I took the liberty to fix it in a more "human-readable" format. I sure hope this will help anyone in need of it. All credits go to Mr. Fred Stevens. Here it is:

PROCEDURE GetDSNs
	* creates cursor DSNList - available DSNs on user's system
	* open WinAPI external dll (odbc32.dll) to use its functions
	* in VFP function calls
	* params are passed by value as a default
	* DECLARE - DLL command - specifies params & params by reference (@ notation)
	DECLARE INTEGER SQLDataSources IN odbc32.DLL ;
		INTEGER nODBCHandle, ;
		SHORT nDirection, ;
		STRING @szDSN, ;
		INTEGER nDSNMax, ;
		INTEGER @pcbDSN, ;
		STRING @szDescription, ;
		INTEGER nDBTypeMax, ;
		INTEGER @pcbDescriptionn

	* here is the odbc api call decoded:
	*	returnvalue = SQLDataSources(
	*	odbc handle,
	*	list position,
	*	@dsn name,
	*	max size,
	*	@actual size,
	*	@databasetype,
	*	max size,
	*	@actual size
	* returns a DSN name & size and associated ODBC driver, name, and size
	* initialize memvars
	nFirstDSN = 2
	nNextDSN = 1
	szdsn = SPACE(128)
	nDSNMax = 128
	pcbdsn = 0
	szdescription = SPACE(128)
	nDBTypeMax = 128
	pcbdescriptionn = 0

	* build a cursor to hold return values
	* capture DSN name and ODBC driver
	* the field sizes may have to be extended to accommodate very large DSN names
	CREATE CURSOR DSNList ;
		(dsn C(40), driver C(40))

	* now we use VFP function SYS(3053) to obtain the ODBC “environment handle
	* necessary as a parameter for the SQLDataSources method call. If the ODBC
	* environment is not yet open, this function call will open it and return the handle.
	* the function returns a character type which we convert to an integer
	nODBCHandle = INT(VAL(SYS(3053)))

	* the SQLDataSources function requires different parameters for the first available
	* DSN name versus the names that follow in the list. The memvar nDirection will be
	* set at the beginning, then changed during following iterations to accommodate this.
	* set up initial loop conditions
	nDirection = nFirstDSN && 2
	iRetVal = 0 && success

	* call the SQLDataSources function repeatedly, putting returned DSN names and drivers
	* into results cursor
	* start at first in list and loop until result indicates failure
	DO WHILE iRetVal = 0 && no errors
		iRetVal = SQLDataSources(nODBCHandle, ;
			nDirection, ;
			@szdsn, ;
			nDSNMax, ;
			@pcbdsn, ;
			@szdescription, ;
			nDBTypeMax, ;
			@pcbdescriptionn)

		* since the memvars szDSN and szDescription were passed as parameters by reference,
		* they now contain the DSN data returned by the SQLDataSources function which can
		* then be passed to the results cursor:
		m.dsn = LEFT(szDSN,pcbDSN)
		m.driver = LEFT(szDescription,pcbdescriptionn)

		IF iRetVal = 0
			INSERT INTO DSNList FROM MEMVAR
		ENDIF

		* set direction flag to "next" instead of "first"
		nDirection = nNextDSN && 1
	ENDDO

	CLEAR DLLS 'SQLDataSources' && release DLL function from memory

	* now the list can be examined
	* or used as a data source for a listbox control.
	SELECT DSNList
	BROWSE
ENDPROC

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Mike Lewis]

Kilroy, it works perfectly. Thanks for posting it, and thanks to Mr Stevens for sharing it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Bryan - Gendev]

Could this be modified to include ocx or dll files hopefully installed when I run my setup file.

I am having some problems getting files installed under WIN 8 and with a 'secret' code call on my app I could get a user to run the routine so that I can see what's happening on their PC?

Thanks

GenDev

 

[Mike Lewis]

Could this be modified to include ocx or dll files hopefully installed when I run my setup file.

No. Kilroy's code relies on the SQLDataSources() function, which has nothing to do with DLLs or OCXs.

Your problem is unrelated to what Kilroy was suggesting. I suggest you start a new thread. With luck, you'll get some good answers.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads