Automation Database Testing Using QTP

Introduction:

This documents deals with the automation of Database using QTP. This document gives VB script function and input sheet which help to automate n-Number of queries, compare the values of the queries and add the results in data sheet based on the queries and place it in a particular path.

Database Testing:

Below are the points which give the idea of how it works.

1.    Updating the input sheet (excel sheet) with relevant SQL queries mentioning the test case name of the queries or any name related to those queries. A column called ‘Execute’ will help to execute only those particular queries based on the ‘Y’ (Yes) or ‘N’ (No) type it is having.

2.    Importing the updated input sheet (excel sheet) into QTP

3.    Connection to Database

4.    Time response has been given to handle Database response time

5.    Executing the count queries which will fetch the total number of records.

6.    Executing the value queries which fetch the actual values to be compared.

7.    Adding new sheet in QTP data table based on the test case name or the name related to the queries in input sheet.

8.    Fetching the column names of the database tables and updating them in QTP data table

9.    Comparing the values of the tables and updating the same in respective column of QTP data table.

10. Updating the results in QTP data table itself and reporting the same in QTP report sheet if it is a fail case.

11. Once the execution and validation of table values are done, exporting the same output sheet into a particular path.

Advantages:

1.    Since the code is used as function which can be called by QTP from VBS file, the execution time of the script is very less than the actual one which is used in QTP Action Expert View itself.

2.    Since the values of data base tables are captured, validated and updated in QTP Data Table, it helps us to bring the database values in front of us.

3.    Pass and Fail results are updated in the QTP Data Table itself. It gives all the fail results in QTP report sheet mentioning the column and row name of QTP data table where the failed values have been updated.

4.    It helps us to execute n-Number of set of queries at a time and results are captured in different QTP data table sheets. Hence it helps us to differentiate the results of each set of queries.

5.    The person who wants to execute the query is required to update the input sheet. Hence it will not require modifying the code based on the query.

6.    The validations of all the database records are done in the first part of the script. Updating the results is done in the second part of the script. The performance of the script will increase when updation of the values in QTP Data table is less. Hence, it is advisable that capturing all the fail records in the data table and capturing few of the pass records, say 200 to 500, in the table will increase the performance. 

Below are the script, input sheet and results.

Input sheet:

Result Sheet:

Posted in: QTP Testing