Open Database Connectivity

ODBC or Open Database Connectivity is an open standard application programming interface used for accessing hetrogenous datasources.

Imagine you have written several PHP programs which makes a connection to an Oracle database. For some reason now you have decided to use MYSQL instead of Oracle. Supposing you have used a proprietary connection to Oracle, then it would take a lot of coding effort to modify each of the PHP programs to make it connect to MYSQL as it already has connect strings to Oracle.

Now ODBC provides seamless open database connectivity using simple APIs to a variety of data sources such as MYSQL, MS-ACCESS, MSSQL, Oracle etc.,One has to create a ODBC connection for the specific datasource. This can be done using the ODBC dialog box which is accessible from the control panel. One requires a ODBC driver to be installed via Dynamic Link Libraries. There are three types of DSNs (Data Source Names), namely User DSN, System DSN and File DSN.

After ensuring that the driver required to connect to a data source is present one needs to add the data source by manually adding a data source from the ODBC dialog box in the control panel. After adding a database one has to configure it. The method to configure a database varies from one database to another. For example it is different for Oracle than MS-ACCESS as Oracle has a layer of TNS (Transparent Network Substrate). In the case of Oracle the TNS service name is mandatory to create a ODBC connection between client and server. In the case of Microsoft Access one has to just specify the network location of the access database and the name of the database. The ODBC manager accessible from the control panel will take care of all these issues while attempting to create a DSN name.

Here is an example of a PHP program connecting to a MYSQL database via a DSN named as "TestDSN".



$conn=odbc_connect('TestDSN','','');

$sql="SELECT * FROM Employee";

$cursor =odbc_exec($conn,$sql)

One may also connect a PHP program to a database using a DSNLESS connection. The disadvantage of using a DSN based connect string in a server - client system is that one requires to configure the DSN or multiple DSNs being used in the server side scripts( such as PHP) on client computers that or multiple client computers. This is because when a client computer encounters an ODBC string in a PHP program the DSN string has to be configured in the local client. This may not be possible to do when there are a lot of users using such programs. In such cases one uses DSN Less connections where the server's IP address /database name, user id and password are directly mentioned in the connect string.


 By Srinivasa Gopal

Article Source: Open Database Connectivity