Database Testing using QTP

Overview:

Database testing involves some knowledge of the given application and requires more defined plan of approach to test the data. To test database, we must have a database to test against.

We connect to the database to retrieve values from the table/record sets. Database operations are used to access the data from any database. As VB Script is object Based Language, it does not support external files directly. The External Files are Database, Text files, Excel files, and Word documents etc. If we want to work with external files, then we create objects by performing database operations. Disadvantage of using SQL directly is that it introduces a dependency upon the type of database used.

The following are the steps to be followed in the QTP script to access the data:

1.    Creating an object of ADODB.
 

2.    Defining the connection string.
 

3.    Opening the connection with the database.
 

4.    Executing the query.
 

5.    Accessing the data using the Recordset object.
 

6.    Closing the database connection.
 

7.    Releasing the memory occupied by the objects.

1. Creating an object of ADODB:

ADODB represents ActiveX data object database component. ActiveX Data Objects (ADO) is a set of component object model (COM) objects, which can be used to create a connection to a database through QTP. Through this connection, we can access and manipulate a database.

Syntax:

Set Objname=CreateObject("ADODB.Connection")

Here ADODB is ActiveX data object database component. Connection represents a physical connection to a data store. By using this, we can establish a connection to the specified database during Runtime. Connection object is the link between QTP and the database.

2. Defining the connection string:

The Connection strings are string variables. They contain database connection information. This Connection String related to particular connection can be set either with or without the DSN (Data Source Name).

In case of DSN: We create the DSN depending upon the data that we want to fetch from SQL server, Excel, Access etc and depending upon the drivers present in out system. 

In case of don't want to create DSN: Now, the Connection String will contain the complete information of what is contained in DSN. The benefit of this over DSN is that the connection since it can contain the complete network path of the database, so it can work on all systems.

3. Opening the connection with the database:

After the Connection string has been set, the next is opening the connection. Here, the Open method is used to establish or to open a physical connection to a data source. Once the connection is established, we can execute commands against the data source.

Syntax:

objDatabase.Open

4. Executing the query:

Here, we can execute specified DML Statements (Insert, Update, and Delete) on Connected database. We will write a query in a string variable and execute that query. Thus, it will return a Recordset object. 

Syntax:

StringQuery="select field1 from table"
Set objRecordset=objDatabase.Execute(stringQuery)

5. Accessing the data using the Recordset object:

Here, Recordset represents the entire set of records from a base table or the results of an executed command. At any point of time, this object refers to only a single record within the set as the current record. It points to one row out of the multiple rows of the query result.

For example, in general for looping down till the end through the Recordset, we use the following:

Do while objRecordset.EOF<> True
       Perform operation records
Loop

6. Closing the database connection:
After all the activities have been carried out on the connection object, we should close the open connection with the database.

Syntax:

objDatabase.Close

7. Releasing the memory occupied by the objects:

The final step is to free up the memory allocated to the connection and the Recordset variables.

Syntax:

Set objDatabase=Nothing