Q110507: HOWTO: Configure ODBC Data Sources on the Fly
Article: Q110507
Product(s): Microsoft C Compiler
Version(s): 1.5,2.0,2.1,2.5,2.6
Operating System(s):
Keyword(s): kbDatabase kbMFC kbODBC kbVC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbm
Last Modified: 11-JUN-2002
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Data Access Components versions 1.5, 2.0, 2.1, 2.5, 2.6, 2.7
-------------------------------------------------------------------------------
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
Q256986 Description of the Microsoft Windows Registry
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");
Note that the data source is actually a directory (C:\EXCELDIR). The Excel driver
has directories as its data sources, and files as the individual tables (one
table per .XLS file).
For additional information on creating tables, please see the following
article(s) in the Microsoft Knowledge Base:
Q110508 Creating Tables with Foundation Database Classes
The information below discusses the parameters that need to be passed to the
SQLConfigDataSource() ODBC API function. To use the SQLConfigDataSource()
function, you must include the ODBCINST.H header file and use the ODBCINST.LIB
import library.
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 article can
be found by searching for "SQLConfigDataSource" and selecting the article titled
'FAQ: Programatically Configuring an ODBC Data Source'.
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 values are available in ODBC 2.53.0 or later 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 registry
entries for an already configured data source (perhaps one that has been
configured by the ODBC Administrator program):
WARNING: If you use Registry Editor incorrectly, you may cause serious
problems that may require you to reinstall your operating system. Microsoft
cannot guarantee that you can solve problems that result from using Registry
Editor incorrectly. Use Registry Editor at your own risk.
1. Using regedit go to "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\" (without the
quotation marks)
2. Find the hive that corresponds to your data source name. There you will find
the keyword pairs. Warning, manually editing registry values can cause system
instability and perhaps an unrecoverable failure.
You might also want to examine the documentation for the specific driver you are
going to use. Useful information may be found in the online help for the driver,
which can be accessed by running the ODBC Administrator: click Add, select the
driver name, and click OK. When the information for creating a new data source
comes up for that particular driver, select Help. This will open the help file
for that particular driver, which generally contains important information
concerning the use of the driver.
REFERENCES
==========
ODBC Programmer's Reference and SDK Guide (available in Books Online).
Additional query words: datasource
======================================================================
Keywords : kbDatabase kbMFC kbODBC kbVC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270
Technology : kbAudDeveloper kbMDACSearch kbMDAC150 kbMDAC200 kbMDAC210 kbMDAC250 kbMDAC260 kbMDAC270
Version : :1.5,2.0,2.1,2.5,2.6
Issue type : kbhowto
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.