ODBC Programming Overview and Architecture
ODBC
ODBC (Open Database Connectivity) is an older technology designed to allow developers to create a common codebase which provides access to a variety of relational data stores. Its methods are exposed in a traditional, non- object – oriented, C language API. Visual Basic programmers can also call ODBC functions by declaring them in their code (this is a topic beyond the scope of this module).
Although the oldest of Microsoft’s current data access API’s, ODBC is still fully supported and is being ported to 64 bit platforms as well as being updated for the next major release of MDAC in approximately 18 months. Many Microsoft customers have very large codebases which still use ODBC. However, we should recommend to customers beginning new development projects to give strong consideration to using newer technologies such as OLE DB, as eventually ODBC will be deprecated.
The main concepts to know in ODBC are of the Driver and the Driver Manager. The ODBC Driver Manager (odbc32.dll) is the core piece of the ODBC infrastructure. When an application calls ODBC function calls, those calls first go through the Driver Manager. Individual data sources are exposed through ODBC Drivers. Microsoft ships several ODBC drivers with MDAC, including SQL Server ODBC driver, Oracle ODBC driver, and the Jet ODBC driver. After an application has made a call to the ODBC API, the Driver Manager directs the call to the appropriate ODBC driver, depending on the data source information in the connection string. A common scenario using the SQL Server ODBC driver is diagrammed below:
.
Connection Strings and DSN’s
When an ODBC application attempts to establish a connection to a data source, it passes to the ODBC Driver Manager a Connection String. The Connection String is a semi-colon delimited list of parameters which tell the Driver Manager what driver to load, and for that particular driver specifies various needed information, such as authentication information and database to use. An example of a connection string for SQL Server would be:
“Driver={SQL Server};Server=BigSQL;UID=sa;PWD=pass043#;Database=Pubs”
ODBC provides the DSN (Data Source Name) as an alternative to specifying this information in code. DSN’s are created on a particular machine through the “ODBC Data Source Administrator” utility. This utility is available through Control Panel in all Windows 9x systems and NT 4.0. On Windows 2000 and XP, the Data Source Administrator has been moved to the “Administrative Tools” folder. On all systems, you can additionally access this tool by going to Start->Run and typing “odbcad32” (for odbcad32.exe, the executable for ODBC Data Source Administrator). Data sources can also be created in code through the SQLConfigDataSource() method.
The DSN is essentially a collection of registry information which stores all the information which would otherwise be passed to a driver through the connection string. There are three types of DSN’s: User, System, and File. User DSN’s are created for a given user and are available only to that user. System DSN’s are globally available on a machine for all users. File DSN’s are different than User and System DSN’s, in that the connection parameters are not stored in the registry but rather in a file.
Demonstration:
1. Open ODBC Administrator (Start->Run; type “ODBCAD32”).
2. Click on the “User DSN” tab. These are the User DSN’s available on the system for the current user.
3. Click on the “System DSN” tab. These are the System DSN’s available on the system.
4. Click on the “File DSN” tab. These are the File DSN’s available on the system.
5. Click on the “Drivers” tab. These are the ODBC drivers currently available on the system. This tab is useful for checking versions of drivers, but should not be used as an absolute checkpoint. Instead, it is better to search for the driver by file name on the drive and check the version through Windows Explorer.
6. Open the registry editor (Start->Run; type “regedit”).
7. Examine the keys under HKLM(HKey_Local_Machine)\Software\ODBC\ODBC.INI. The keys listed under here are the System DSN’s (There’s some other keys as well).
8. Go back to ODBC Administrator. Click on the “System DSN” tab. Click “Add”. Select the SQL Server driver and click “Finish”. In the first tex box (DSN name) type “TestDSN1”. In the 3rd textbox (Server) type “(local)”. Click “Next”. Examine the options. Click “Next”. Examine the options. Click “Next”. Examine the options. Click “Finish”. Click the button to test the connection. This is a good simple way of testing connectivity between a client and data source for ODBC. Click “OK” twice.
9. Now go back to the registry editor. Click View->Refresh. Look again under HKLM(HKey_Local_Machine)\Software\ODBC\ODBC.INI. You should now see the “TestDSN1” listed. Expand that key and you can see the different values specified during DSN configuration. Also look under HKLM(HKey_Local_Machine)\Software\ODBC\ODBC.INI\ODBC Data Sources. You will see the new DSN name listed under there as well. The DSN name must be located under this key to be displayed by ODBC Data Source Administrator.
Should you choose to use a DSN rather than pass a full connection string in your application, the connection string will look something like this:
“DSN=TestDSN1”
The method of connecting without the use of a DSN (described earlier) is less common, and is called a “DSN-less Connection.”
Many times problems arise in using a DSN to connect due to permissions issues on the registry keys which store a DSN. Especially if a user is reporting a “Data Source Name not found and Default Driver specified” error message on connecting, then it is a worthwhile step to find out what user account the user is logged onto the machine with, and then check to make sure that user has permissions to access the registry keys for that DSN using REGEDT32.EXE.
A useful tool for determining if registry issues are a problem in a variety of circumstances is PROCESS MONITOR (REGMON), available at https://www.microsoft.com/technet/sysinternals/default.mspx. REGMON profiles all access to the registry on a system, and from the generated log you can determine if any attempted registry access failed.