HOWTO: Configure ODBC Data Sources on the Fly

• The Microsoft Foundation Classes (MFC), included with:

      - Microsoft Visual C++ for Windows, version 1.5, 1.51, 1.52
      - Microsoft Visual C++, 32-bit Edition, versions 2.0, 2.1, 2.2,
        4.0, 4.1, 4.2
      - Enterprise Edition of Microsoft Visual C++, for Windows, version 4.2
  

SUMMARY

You can configure ODBC (Open Database Connectivity) data source names programmatically. This gives you flexibility to export data without forcing the user to explicitly use the ODBC Administrator or other programs to specify the names of data sources. This might, for example, enable your program to use the ODBC API (application programming interface) to export an .XLS file. To do this, use the SQLConfigDataSource() function.

The following example uses SQLConfigDataSource to create a new Excel data source called "New Excel Data Source":

   SQLConfigDataSource(NULL,ODBC_ADD_DSN,
     (LPSTR) "Excel Files (*.xls)",
     (LPSTR) "DSN=New Excel Data Source\0"
     "Description=New Excel Data Source\0"
     "FileType=Excel\0"
     "DataDirectory=C:\\EXCELDIR\0"
     "MaxScanRows=20\0");

For additional information on creating tables, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110508
   TITLE     : Creating Tables with Foundation Database Classes

NOTE: For 32-bit applications, you must still include ODBCINST.H header file, however you must now link with ODBCCP32.lib

NOTE: The information contained within this article is duplicated in the

'Programming with MFC Encyclopedia' shipped with Visual C++ 4.0. The

MORE INFORMATION

NOTE: This article was originally written for the 16-bit ODBC components only. The 16-bit ODBC components use INI files to store information on configured datasources (ODBC.INI) and installed drivers (ODBCINST.INI). The 32-bit ODBC components no longer use INI files but, instead, write such information to the registry. System datasource information and installed driver information is stored in HKEY_LOCAL_MACHINE\SOFTWARE\ ODBC\ in ODBC.INI and ODBCINST.INI, respectively. Non-System datasources are stored in HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. In the remainder of this article, references to ODBC.INI should be interpreted as referring to the appropriate section of the registry if you are using the 32 bit ODBC components.

An ODBC data source name can be created using the ODBC Administrator program or similar utility. However, sometimes it is desirable to create a data source name directly from your application so that access can be obtained without requiring the user to run a separate utility.

The ODBC Administrator (typically installed in the Windows Control Panel) creates a new data source by putting entries in the ODBC.INI file. This file is queried by the ODBC Driver Manager to obtain the required information about the data source. It's important to know what information needs to be placed in the ODBC.INI because you'll need to supply it with the call to SQLConfigDataSource().

Although this information could be written directly to the ODBC.INI file [without using SQLConfigDataSource()], any application that does this is relying on the current technique that the Driver Manager uses to maintain its data. If a later revision to the ODBC Driver Manager implements record keeping about data sources in a different way, then any application that used this technique would be broken. It is generally advisable to use an API function when one is provided.

Below, you will find an explanation of the parameters of the SQLConfigDataSource() function. Much of the information is taken from the ODBC API Programmer's Reference supplied with Visual C++ version 1.5.

   Function prototype:
   BOOL SQLConfigDataSource(HWND hwndParent,UINT fRequest,
                            LPCSTR lpszDriver,
                            LPCSTR lpszAttributes);

   hwndParent - This is the window that will be used as the
     owner of any dialog boxes which are created by either the
     Driver Manager or the specific ODBC Driver to obtain
     additional information from the user about the new data
     source. If there is not enough information provided in the
     lpszAttributes parameter, a dialog box will appear.
     This parameter may be NULL, see the reference for specifics.

   fRequest - The operation to be performed. Possible values are:
                      ODBC_ADD_DSN: Add new user data
                        source.
                      ODBC_CONFIG_DSN: Modify an
                        existing data source.
                      ODBC_REMOVE_DSN: Remove an
                        existing data source.
     The following are available in ODBC 2.5 32 bit only:
                      ODBC_ADD_SYS_DSN: Add a new
                        system data source.
                      ODBC_CONFIG_SYS_DSN: Modify
                        an existing system data
                        source.
                      ODBC_REMOVE_SYS_DSN: Remove
                        an existing system data
                        source.

   lpszDriver - Driver description. As the documentation mentions,
      this is the name presented to the users rather than the
      physical driver (the DLL). You can determine the description
      of a driver using the ODBC Administrator program as follows:

      1. Run the ODBC Administrator program.
      2. Choose Add. This will give you a list of installed drivers.

      The list contains driver descriptions. It is this description
      that you will use as the lpszDriver parameter. Note that the
      ENTIRE description is used [for example, "Excel Files (*.xls)"]
      including the file extension and parentheses if they exist
      in the description.

      Optionally, you can examine the file ODBCINST.INI, which
      contains a list of all driver entries and descriptions
      in the section [ODBC Drivers].

   lpszAttributes - List of attributes in the form "keyname=value".
      These strings are separated by null terminators with two
      consecutive null terminators at the end of the list.
      These attributes will primarily be default driver-specific
      entries, which go into the ODBC.INI file for the new data
      source. One important key, which is not mentioned in the
      ODBC API reference for this function, is "DSN" which
      specifies the name of the new data source. The rest
      of the entries are specific to the driver
      for the new data source. Often times it is not necessary
      to supply ALL of the entries because the driver can prompt
      the user (if hwndParent is not NULL) with dialog boxes
      for the new values. You might want to explicitly supply
      default values so that the user is not prompted.

      One way to find the keynames and their values is to examine
      the ODBC.INI file for an already configured data source (perhaps
      one that has been configured by the ODBC Administrator program):

      1. Open the ODBC.INI file.
      2. Find the section marked by [<data source name>] where <data
         source name> is a data source that has already been configured
         with the desired settings for the driver you intend to use.
         The lines following this line will be of the form "keyname=value".
         These are exactly the entries you will want to use in your
         lpszAttributes parameter.

REFERENCES

ODBC Programmer's Reference and SDK Guide (available in Books Online).