I.T. Discussion Community!
-Collapse +Expand
V.FoxPro
Search V.FoxPro Group:

Advanced
-Collapse +Expand V.FoxPro Store

Prestwood eMagazine

November Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDesktop Data...Visual FoxPr...   Print This     
  From the March 2016 Issue of Prestwood eMag
 
V.FoxPro Visual FoxPro (VFP):
Win-32 API - Listing ODBC Data Source Names
 
Posted 15 years ago on 11/16/2004
Take Away: Demonstrates the use of the SQLDataSources() Windows API call to generate a list of DSNs with VFP
 Tags: VFP , DSN , ODBC , Win-32 API

KB100272

Knowledge Base Post - WinAPI with VFP

In this series of articles, we are examining specific techniques and providing code samples for VFP programmers to use to access the Windows API for some important tasks.

 

Visual FoxPro is an excellent platform for connecting to and manipulating remote data sources such as Microsoft SQL Server or Pervasive SQL via the ODBC (Open Data Base Connectivity) standard system.  Cooperating data providers furnish drivers for ODBC to examine and manipulate data in their proprietary systems.  Visual FoxPro can use these drivers to access this “remote” data within the VFP operating environment.  Although more modern data access technics such as ADO/OLE-DB are sometimes also supported by remote data servers, many times ODBC support is the only available pathway to accessing remote data.

 

The simplest technique for making a “remote data connection” via VFP is by referring to a previously setup ODBC Data Source Name (DSN).  These are connection configuration data residing on the user’s system (either in the registry or as stand-alone files) which specify things like the specific ODBC driver, database name, password and login ID for the remote data source as named DSN entities.  The connection to the remote data can be made simply by specifying the DSN name in a SQLCONNECT() command:

 

nReturnValue = SQLCONNECT(cMyDSNName)

 

nReturnValue is the “connection handle” returned by Windows: it will be -1 for failed connections and a positive integer for a successful connection to the remote data source.

 

This article describes a technique for viewing (to potentially select from) a list of the named DSNs along with their ODBC driver names currently installed on a particular system, so as to provide a choice of connections to various data sources.  The key to this technique is the use of the ODBC Win-32 API methods, in particular SQLDataSources().  This method and some others relating to ODBC functionality are part of the ODBC32.DLL component of the WinAPI.

 

The program below first DECLARES the SQLDataSources() function in the WinAPI  DLL to set up its use, then calls the function repeatedly to return a list of existing DSN names and associated ODBC drivers. Depending on the user’s access rights, these DSNs include those available from both the “User DSN” and “System DSN” tabs of the Windows ODBC Data Source Administrator app.  These can be put into a cursor or array and sorted or filtered to be used as a data source for a drop-down list for user selection.

 

 

 

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

In our next article, we will examine retrieving available SQL Servers names and database names via the Win/ODBC API in order to programmatically build a “DSN-less” connection to remote data.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1167A1
Enter key:
KB Post Contributed By Fred Stevens:
Senior Software Engineer with Prestwood Software -- Visual FoxPro, Visual Basic.NET developer
Visit Profile

 KB Article #100272 Counter
19792
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech