Microsoft Open Database Connectivity

Microsoft Open Database Connectivity (ODBC) The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs). ODBC is a low-level, high-performance interface that is designed specifically for relational data stores. The ODBC interface allows maximum interoperability—an application can access data in diverse DBMSs through a single interface. Moreover, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS.

ODBC Data Source Administrator The Microsoft® ODBC Data Source Administrator manages database drivers and data sources. On computers running the Microsoft Windows® 2000 operating system, this application is located in the Windows Control Panel under Administrative Tools. Its icon is named Data Sources (ODBC).

About Drivers and Data Sources Drivers are the components that process ODBC requests and return data to the application. If necessary, drivers modify an application's request into a form that is understood by the data source. You must use the driver's setup program to add or delete a driver from your computer. Data sources are the databases or files accessed by a driver and are identified by a data source name (DSN). Use the ODBC Data Source Administrator to add, configure, and delete data sources from your system. The types of data sources that can be used are described in the following table. Data source

Description

User DSNs are local to a computer and can be used only by the current user. They are registered in the HKEY_CURRENT_USER registry subtree. System DSNs are local to a computer rather than dedicated to a user. The system System or any user with privileges can use a data source set up with a system DSN. System DSNs are registered in the HKEY_LOCAL_MACHINE registry subtree. File DSNs are file-based sources that can be shared among all users who have the same drivers installed and therefore have access to the database. These data File sources need not be dedicated to a user nor be local to a computer. File data source names are not identified by dedicated registry entries; instead, they are identified by a file name with a .dsn extension. User

Managing Data Sources PRASHANT SHARMA : [email protected]

1

.Action

Description It is possible to add multiple data sources, each one associating a driver with some data you want to access by using that driver. Give each data source a name that uniquely identifies that data source. For example, if you create a data source for a set of dBASE files that contain customer information, you Adding data might name the data source "Customers." Applications typically display data source names for users to choose from. sources Adding a file data source is slightly different from adding user or system data sources. For more information, see the ODBC Data Source Administrator help file. Depending on your requirements, you might find it necessary to reconfigure Modifying data sources. You can reset options by clicking Configure in any driver data sources setup dialog box. Deleting data Click Remove after selecting a data source. sources

Setting ODBC Connection Pooling Options Connection pooling enables an application to use a connection from a pool of connections that do not need to be reestablished for each use. You can use the Connection Pooling tab of the ODBC Data Source Administrator dialog box to alter the connection Retry Wait time for any selected driver. You can also use it to enable and disable performance monitoring. Double-click a driver name to set the connection timeout period. At the driver level, connection pooling is enabled by the CPTimeout registry value. This selective per-driver enabling allows a system administrator to enable connection pooling for just the drivers that can support it. It is accomplished by setting the default value of CPTimeout during the driver's setup program. When the ODBC Driver Manager detects that a database server is unavailable, it waits for a certain amount of time before attempting to reconnect. You can adjust the waiting time by setting the Retry Wait time value (in seconds) in the Connection Pooling tab of the ODBC Data Source Administrator dialog box. Double-click a driver name to set the connection time-out period.

Performance Monitoring Performance monitoring tracks connection performance by recording a variety of statistics. These statistics can be customized by the developer to include items such as the following: •

The number of connections made to data sources per second.

PRASHANT SHARMA : [email protected]

2

• • • • • • •

The number of disconnections made from data sources per second. The number of connections to the pool per second. The number of disconnections from the pool per second. Connections currently active. The current number of connections being used by applications. Connections currently free. The current number of connections available for connection requests.

ODBC Test Microsoft® ODBC Test is an ODBC-enabled application that you can use to test ODBC drivers and the ODBC Driver Manager. ODBC 3.51 includes both ANSI and Unicodeenabled versions of ODBC Test.

ODBC Driver Architecture Driver writers must be aware that the driver architecture can affect whether an application can use DBMS-specific SQL.

File-based Drivers When the driver accesses the physical data directly, the driver acts as both driver and data source. The driver must process both ODBC calls and SQL statements. Developers of filebased drivers must write their own database engines. DBMS-Based Drivers When a separate database engine is used to access physical data, the driver processes only ODBC calls. It passes SQL statements to the database engine for processing. Network Architecture File and DBMS ODBC configurations can exist on a single network. PRASHANT SHARMA : [email protected]

3

Other Driver Architectures When a driver is required to work with a variety of data sources, it can be used as middleware. Heterogeneous join engine architecture can make the driver appear as a driver manager. Drivers can also be installed on servers, where they can be shared by a series of clients. Using 16-Bit and 32-Bit Applications with 32-Bit Drivers With the ODBC data access component, you can use 16-bit and 32-bit applications with 32-bit drivers. The Microsoft® Windows® 95/98 and Microsoft Windows NT®/Windows 2000 operating systems support the following combinations of applications and drivers: • •

16-bit applications with 32-bit drivers 32-bit applications with 32-bit drivers

Using a 32-bit application with a 16-bit driver is not supported. ODBC includes the ODBC components necessary to support the above configurations by "thunking" dynamic-link libraries (DLLs) to convert 16-bit addresses to 32-bit addresses and vice versa. The Setup program determines which operating system you are using and installs ODBC components required by that system. You can also choose to install the ODBC components used by all systems. In most cases, porting an application or driver from 16-bit to 32-bit involves five types of changes: • • • • •

Changes to message-handling code Changes because integers and handles are 32 bits Changes in calls to Windows application programming interfaces (APIs) Changes to make the driver thread-safe Changes to ODBC components

From an application or driver programming standpoint, the major difference between 16bit and 32-bit ODBC components is that they have different file names. From a system standpoint, the architecture of each application or driver connection is different and the tools used to manage data sources are different.

ODBC Driver for Oracle The Microsoft® ODBC Driver for Oracle allows you to connect your ODBC-compliant application to an Oracle database. The ODBC Driver for Oracle conforms to the Open Database Connectivity (ODBC) specification described in the ODBC Programmer's Reference. It allows access to PL/SQL packages, XA/DTC integration, and Oracle access from within Internet Information Services (IIS). PRASHANT SHARMA : [email protected]

4

Oracle RDBMS is a multiuser relational database management system that runs with various workstation and minicomputer operating systems. IBM-compatible computers running Microsoft Windows can communicate with Oracle database servers over a network. Supported networks include Microsoft LAN Manager, NetWare, VINES, DECnet, and any network that supports TCP/IP. The ODBC Driver for Oracle enables an application to access data in an Oracle database through the ODBC interface. The driver can access local Oracle databases or it can communicate with the network through SQL*Net. The following diagram details this application and driver architecture.

The ODBC Driver for Oracle complies with API Conformance Level 1 and SQL Conformance Level Core. It also supports some functions in API Conformance Level 2 and most of the grammar in the Core and Extended SQL conformance levels. The driver is ODBC 2.5 compliant and supports 32-bit systems. Oracle 7.3x is supported fully; Oracle8 has limited support. The ODBC Driver for Oracle does not support any of the new Oracle8 data types — Unicode data types, BLOBs, CLOBs, and so on — nor does it support Oracle's new Relational Object Model. For more information about supported data types, see Supported Data Types in this guide. To access Oracle data, the following components are required: • • •

The ODBC Driver for Oracle An Oracle RDBMS database Oracle Client Software

Additionally, for remote connections: •

A network that connects the computers that run the driver and the database. The network must support SQL*Net connections.

PRASHANT SHARMA : [email protected]

5