Retrieving local ODBC configuration data Professional

Information about ODBC drivers and ODBC data sources can be obtained from the ODBCDBE without connecting to a data source. This is accomplished by calling the DbeInfo() function and passing to it #define constants available in the ODBCDBE.CH file. The following sections demonstrate how to retrieve information about ODBC drivers and ODBC data sources available on a computer.

When an application program accesses data sources via the ODBCDBE, it relies on the existence of an appropriate ODBC driver being installed on a computer. If an application will be installed on computers unkown to the application programmer, it might be necessary to detect in the application's start-up routine if the required ODBC driver(s) exist on that computer.

The following program can be used to list all ODBC drivers available on a computer:

#include "Odbcdbe.ch" 

PROCEDURE DbeSys 
   DbeLoad( "ODBCDBE" ) 
   DbeSetDefault( "ODBCDBE" ) 
RETURN 

PROCEDURE Main 
   LOCAL aDriver, cDriverName, aAttributes, i, imax 

   aDriver := DbeInfo( COMPONENT_DATA, ODBCDBE_DRIVERS ) 
   imax    := Len( aDriver ) 
   
   FOR i:=1 TO imax 
      cDriverName := aDriver[i,1] 
      aAttributes := aDriver[i,2] 

      ? cDriverName 
      AEval( aAttributes, {|x,n| QOut(n,x) } ) 
   NEXT 
RETURN 

The #define constant ODBCDBE_DRIVERS causes DbeInfo() to return a 2-column array. The first column contains the names of available ODBC drivers and the second column contains arrays of attributes for the ODBC driver:

{ { cDriverName1, { xAttribute1, ... , xAttribute1N } }, ; 
  { cDriverName2, { xAttribute2, ... , xAttribute2N } }  ; 
} 

Elements of the array holding ODBC driver attributes can be accessed using #define constants listed in the following table.

ODBC Data Sources (DSNs) defined on a computer via the ODBC Data Source Administrator contain the information required to connect to a data source. If an application relies on DSNs or if it creates a connection string on its own is up to the application programmer. DSN usage is much more comfortable than connecting to a data source programmatically. The following code demonstrates how to retrieve DSNs defined on a computer.

#include "Odbcdbe.ch" 

PROCEDURE DbeSys 
   DbeLoad( "ODBCDBE" ) 
   DbeSetDefault( "ODBCDBE" ) 
RETURN 

PROCEDURE Main 
   LOCAL aDataSrc, i, imax 

   aDataSrc := DbeInfo( COMPONENT_DATA, ODBCDBE_DATASOURCES ) 
   imax     := Len( aDataSrc ) 

   FOR i:=1 TO imax 
      ? "Data source:", aDataSrc[i,1] 
      ? "ODBC driver:", aDataSrc[i,2] 
   NEXT 
RETURN 

DbeInfo() returns a 2-column array holding character strings. The first column describes a data source and the second column the corresponding ODBC driver.

{ { cDataSource1, cODBCDriver1 }, ; 
  { cDataSourceN, cODBCDriverN }  ; 
} 

The connection string is the most critical part in ODBCDBE usage when an application does not use a DSN defined on a computer but creates the connection string on its own. If a connection string is not properly formatted or if it does not contain all required data, the connection to a data source will fail, unless the ODBC driver prompts the user to enter missing data and the user knows the correct data.

The format of the connection string is usually documented in the manuals shipped with the software providing the data source. However, it can be a tedious search-job to find this information. An alternative is to query the ODBC driver and to obtain the correct format for a connection string from the driver. This is possible for all non-file-based data sources (usually, these are SQL DBMSs). It is not possible for file based data sources. The following program demonstrates this approach:

#include "Odbcdbe.ch" 

PROCEDURE DbeSys 
DbeLoad( "ODBCDBE" ) 
DbeSetDefault( "ODBCDBE" ) 
RETURN 

PROCEDURE Main 
LOCAL aDriver, cDriver, i, imax 

aDriver := DbeInfo( COMPONENT_DATA, ODBCDBE_DRIVERS ) 
imax    := Len( aDriver ) 

FOR i:=1 TO imax 
   IF aDriver[i,2,ODBC_DRIVER_FILEUSAGE] == 0 
      cDriver := aDriver[i,1] 
      // start trial 
      DbeInfo( COMPONENT_DATA, ; 
               ODBCDBE_SET_TRIAL_SERVER, ; 
              "DRIVER={" + cDriver +"};" ) 

      ? "--------", cDriver, "---------" 
      ? DbeInfo( COMPONENT_DATA, ODBCDBE_GET_TRIAL_SERVER ) 
      // stop trial 
      DbeInfo( COMPONENT_DATA, ODBCDBE_SET_TRIAL_SERVER) 
   ENDIF 
NEXT 
RETURN 

Passing ODBCDBE_SET_TRIAL_SERVER along with the name of an ODBC driver to DbeInfo() instructs the specified ODBC driver (client side) to prepare itself to retrieve the connection string format from the data source (server side).

When the ODBC driver is prepared, DbeInfo() receives ODBCDBE_GET_TRIAL_SERVER and returns the character string the ODBC driver has received from the data source. The following example is the result of querying Microsoft SQL Server and demonstrates formatting rules (NOTE: the result string below is split into 5 lines for better explanation. A connection string does not contain line breaks. The example below is effectively a "one-liner"):

01: SERVER:Server={ALASKA02,ALASKA04}; 
02: UID:Login ID=?; 
03: PWD:Password=?; 
04: *APP:AppName=?; 
05: *WSID:WorkStation ID=? 

The connection string format includes information about compulsory and optional data that must or may be provided when connecting to a data source. Formatting rules are:

DbeInfo( COMPONENT_DATA, ODBCDBE_SET_TRIAL_SERVER)