QTP Database Testing : Connecting to an Access database and executing a query

QTP Database Testing : Connecting to an Access database and executing a query

One of the most intriguing and challenging tasks for a tester is to conduct a Database test and verify the query and the related outcome. It surely needs extensive knowledge on executing the underlying queries and verifying its correctness based on a host of parameters. The most important aspect is the correctness of the query and the optimized nature. This requires good knowledge in query writing, understanding the intricacies of the Database.

Given below is an instance of QTP usage by connecting to an Access Database and verifying the output.

The script goes like this.

Dim objCon,objRs
Set objCon=createobject(“ADODB.connection”)
Set objRs=createobject(“ADODB.recordset”)
objCon.provider=”microsoft.jet.oledb.4.0″
objCon.open”d:\Copy of FoodPlanner.mdb”
objRs.open”select*from CorHolidays”,objCon
datatable.GetSheet(1).addparameter “Date”,”1″
datatable.GetSheet(1).addparameter “Holidays”,”2″

row=1
Do While Not objRs.EOF
 datatable.SetCurrentRow(row)
 datatable.Value (1,1)=objRs.fields(“Fromdate”)
 datatable.Value(2,1)=objRs.fields(“HolidayDesc”)

 row=row+1
 objRs.movenext
Loop
DataTable.Export “d:\food.xls”
Set objRS = Nothing
Set objCon = Nothing

  
Explanation

# Notice the objects objCON and objRS. These are defined to accomodate the ADODB Connection and RecordSet. objCON for the Connection and objRS for the RecordSet.

# CreateObject : It is a latebinding method used for creating a connection and recordset object.

# objCON.Provider  : Provides the details related to the connecting DB. Here in this case, it is clearly visible and stated as “Microsoft.Jet.OLEDB.4.0” for the MS-Access DB.

# objCON.Open : Once the provider details are given, the user will need to open the relevant Access Database table.

# objRS.Open : This is used to open and execute the appropriate query

# Datatable.GetSheet(1).AddParameter : Used to create column parameters for the datatable output (is evident in the snapshot given later).

# Do while loop : In this loop, the individual rows from the Access database table are accessed and then passed to the Datatable.value(Column, Row).

# SetCurrentRow(row) : This statement will set the current row within the Datatable.

# Datatable.export : The runtime results within the Datatable are not visible and may be accomplished by exporting these values to an external excel file.

# set object = Nothing : Every object created using the CreateObject will need to be closed for efficient and effective release of objects.

Verifying MS-Access query using QTP
Verifying MS-Access query using QTP
Advertisements

2 thoughts on “QTP Database Testing : Connecting to an Access database and executing a query

  1. Hi Abhilash,
    Thanks for published this article.. Realy it was very usefull to me.. Thanks and best of luck for your service.

    Best Regards,
    Vengat.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s