What is ODBC?

Why ODBC?

The concept of ODBC is important for database connectivity because it provides a mechanism in which the developer can insulate themselves from the inherent difficulties in learning how to connect to every possible type of database that exists. Using ODBC, the developer simply connects to an ODBC datasource and lets the ODBC connection specify the database it wants to connect to.

Still unclear? Well lets take a second look at the process of an application connecting to the database. As shown in Figure 1, the developers writes code to connect to a specific database, if connection details about a database changes, the use of a configuration file will make it easy for the application to adapt to the change.

    -------    --------

              
     APP --->  DB
              
    -------    --------
  
       V
    --------
    config
     file
    -------

Figure 1: Direct connection to a database using settings from a configuration file

What happens when the actual type of database changes? Well, this is more complicated because the connection details of different databases expectedly are different. MS Access, for example is a file based database and as such would require you to specify the Access database file from your filesystem. Oracle, which is a connection based database will include such details as the host, port, username and password that will be used to connect to the database. Unless a large amount of code has been written to support all possible types of databases that an application can encounter, the application will no longer be able to connect to a database. In technical parlance, your application now has a vendor dependency. If you start out with Oracle, and Oracle were to raise their rates tomorrow, you'd have no choice but to pay it. But, another way exists and that is ODBC.
ODBC leaves the task of database configuration to the user, or an installer, and allows the developer to worry about connecting with the ODBC datasource. This way, during runtime, a user can change the connection details about their applications easily (Just as can be done in the previous example), but they can also change the type of database being connect to. Figure 2 shows the chart of a database that is being connect to through the use of an ODBC datasource.

    -------    --------     ----------------

                        SQL Server DB
     APP ---> ODBC ---->---------------
                          Oracle DB
    -------    --------     ----------------

Figure 2: Indirect connection to a database using ODBC

As long as the data in the database internal structures (tables, fields, relationships etc) remains the same, the application will be able to continue using the database.

You will find that this technology works out perfectly for any automated testing tool. It is impossible for the developers of QTP to know the type of database you will use in you applications. In fact, it is possible for your application to be using a DBMS that was created after QTP was released. So how can QTP connect to it, well, using ODBC, QTP simply must know how to connect to a datasource defined on your computer. The tester will then complete the connection by connecting the datasource to the actual database that is used in you application.

This means that to use ODBC during your testing, you will need to create an ODBC data source. We will go through the process of creating thus, but before we begin we must discuss some items that you will need. When dealing with ODBC, you need to know at least three details, including:

Data Source Name - A unique data source name (DSN) that you will use to refer to your database on the machine. Think of the DSN as an alias for the database. While the DSN can really be anything, it is often best to specify a name that bears some relation to the real database, or the AUT.

CAUTION: Don't forget that you must create the same DSN on all machines you intend to run your test on. Without doing so, your test will not have a valid connection to the database and as such, will fail.

Data Source Type - The type of data source which you intend to create. There are two major types
* Machine Datsource
* File Datasource

Database Driver installed - You can only create an ODBC data source for a database that has the appropriate drivers installed on your system. You can easily check the list of drivers you have installed by clicking on the Drivers tab of the ODBC dialog.

You will find that the choice of drivers chosen determines the type of details that will be requested from you during the process of adding your datasource. Access, for instance being a file based database provides a button that allows you to select the database file from the filesystem while SQL Server, a connection based database, request such details as the host on which the database is running. Expectedly, you cannot conclude the creation of an ODBC data source if you do not have all the necessary details.