The ODBC specification defines the API for connecting to a database. The ODBC (Open Database Connectivity) is client side interfaces used to communicate to a database on the same or different server. Basically, this software is the glue between an application, web server, or application server and the database. This software was first released in Sept 1992 by the SQL Access Group and is currently at version 3.x.

• 1.0 (1993) first version of ODBC
• 2.0 (1994) change to API core and new data types
• 3.0 (1995) new APIs and descriptor handles
• 3.5 (1997) UNICODE introduction

The current Oracle ODBC driver conforms to the ODBC 3.51 specifications. It supports all core APIs and a subset of Level 1 and Level 2 functions. Microsoft supplies the Driver Manager components for the Windows Platform. For Linux, the recommended Driver Manager is unixODBC found at http://www.unixodbc.org. Theh ODBC is part of the Oracle Instant Client installation and can be found at http://www.oracle.com/technology/tech/oci/instantclient/ .

The ODBC interface consists of

• ODBC Driver Manager
• ODBC Driver
• ODBC libraries

The ODBC Driver Manager can be configured with

• odbcinst.ini - list of ODBC drivers
• odbc.ini and .odbc.ini - list of data sources
  • format of a data source is as follows

[ODBC_datasource_name]
Driver = driver_name
Description = description_of_data_source
attribute1 = value
.
.
attributex = value

• some example of the attributes are

ServerPort = myhost:1521
TargetDSN = address of database
LogonUser = username
LoginAuth = password
UID = dbuser

• a sample odbc.ini configuration would look like

[myDatabase]
Driver = OOB
ServerPort = mydatabase.oracle.com:1521
TargetDSN = mydatabase
LoginUser = userID
LoginAuth = password

A sample connection in PHP would look like

my $CONNECT = "DRIVER={OOB};ServerPort=mydatabase.oracle.com:1521" . "TargetDSN=mydatabase;LoginUser=userID;LoginAuth=password;"; my $db->connect("dbi:ODBC:$CONNECT", "dbuser", "dbpassword"); if (!$db) { print "$DBI::error\n$DBI::errstr\n$DBI::state"; } else { print "Successful connection"; }

A sample connection in C for the same ODBC driver would look like

#include <stdio.h> #include <sql.h> #include <sqlext.h> main() { SQLHENV env; SQLHDBC dbc; SQLSTMT stmt; SQLRETURN ret; SQLCHAR out_str[1024]; int len_out_str; /* allocate handle */ SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); /* connect ODBC 3.x */ SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV-ODBC3, 0); /* allocate active handle */ SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc); /* connect to database */ ret = SQLDriverConnect(dbc, NULL, "DSN=mydatabase", SQL_HTS, outstr, sizeof(outstr), &len_out_str), SQL_DRIVER_COMPLETE); if (SQL_SUCCEEDED(ret)) { printf(" good connection\n"); } else { printf(" connection failed\n"); }

You can configure the ODBC driver to fail over another database using the attribute AlternateServers to define secondary or standby databases in the event that the first database connection goes down. This feature also needs to be configured in the ODBC Driver Manager to enable failover and define the number of retry attempts and timeouts before the secondary site is selected. The failover is not limited to a single server but can be a chain of servers.

A sample ODBC configuration on WindowsXP looks like

1) download the Oracle Database 10g Client Release 2 from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201winsoft.html


2) unzip the file 10201_client_win32
3) execute the setup file from the install image

4) install the software - Next


5) select InstantClient - Next


6) select location to install - Next


7) verify that prerequisites are good - Next


8) Install


9) configure data source in Administrative Tools on Windows XP

10) Add a User DSN

11) select the ODBC driver we just installed

12) define the data source name

At this point you should have an ODBC connection that corresponds to a listener running on your database server. You can connect using either the Data Source Name (DSN) or TNS Service Name.