Database Checkpoint and QTP Part 3 – Retrieving Data

In Part1 we saw the wizard method to use Database checkpoint.  In Part2 we learned how to connect to a database using scripts in QTP. In this part we will see how we can retrieve data from database using scripts.

Similar to connection object, you can use ADODB.Recordset to get the recordset object.

Set MyRecordSet = CreateObject("ADODB.Recordset")

Before starting let us first understand the properties and methods of recordset object which will help further.

Don’t worry if you feel this listing is too big, you may never use many of these, all this is for your reference in case you need. The most commonly used property and functions are:

• EOF – to identify no records returned. You run a query and need to determine the pointer is at last record or not or no records has been returned.
• Open – To retrieve the RecordSet we use open method which requires two arguments – connection object and command object

Syntax for this would be

<RecordSetName>.Open source, connection, cursor, lock, type

• Source – is actually the command object, it could be a sql statement
• Connection – is the connection object connecting the database
• Cursor – optional parameter to define recordset cursor type (default – Forward only)
• Lock – optional parameter to set the lock type property(default – Read Only)
• Type – optional parameter to define the command type (default unknown(8))

For example

Set oRecordSet = CreateObject("ADODB.Recordset")
oRecordSet.Open “Select * from Orders”,oConnection, adOpenStatic

here oConnection is connection session to the database , we have used the static cursor which allows us to open the record in readonly mode, and not used a lock type – so it will use the default lock.

a generic function for retrieving the recordset would be like

Function 2

Function getRecordset(strSQL)
	Dim oConnection, oRecordSet
	Set oConnection = CreateObject("ADODB.Connection")
	Set oRecordSet = CreateObject("ADODB.Recordset")
	oConnection = getConnection()
	oRecordSet.Open strSQL,oConnection, adOpenStatic	Set getRecordset = oRecordSet
End Function

Let us now try this to retrieve data from Orders table of Flight application database.

To get the customer name from table Orders with Order Number 1 will need to execute the query –

Select * from Orders where Order_Number = 1

Example 2

Set MyRecordset = getRecordset("Select * from Orders where Order_Number = 1")
If  MyRecordset.EOF <> True  Then
	msgbox MyRecordset.Fields("Customer_Name").Value
End If

Result

In this example we are using the function 2 by passing the required query and we get the result recordset in the MyRecordset object. Then we are checking whether there is some records there or not using the EOF property.

In the same way we can use other methods as well for different operations

Move to next record – We have a method MoveNext which we can use in a loop until the max record in recordset. Below example illustrates the use of this method to get all the records from table

Example 3

Set MyRecordset = getRecordset("Select * from Orders")
Do while MyRecordset.EOF <> True
	print MyRecordset.Fields("Customer_Name").Value
	MyRecordset.MoveNext
Loop

Result

In the same way you can use other methods and properties of RecordSet object to achieve your task. I have listed her some example which you may encounter in your test.

Function 3 – Number of Records We can traverse through all the records in the recordset to get the number of records in recordset.

Function getRecordCount(ByRef RecordSet)
	Dim Rows
	Rows = 0
    	RecordSet.MoveFirst
   	 Do Until RecordSet.EOF
        		Rows = Rows+1
        		RecordSet.MoveNext
    	Loop
   	 getRecordCount = Rows
End Function

Function 4 – Execute Query

If you need to update, insert or delete records from database, you can use this function. But if you are working on the database for application under test, you should not perform such actions until unless it is required or a database used for QTP.

Function ExecuteQuery(strSQL)
   	On Error Resume Next
  	 Set oConnection = CreateObject("ADODB.Connection")
	oConnection = getConnection()
	oConnection.Execute strSQL
	 If Err.Number <> 0 then
        		ExecuteQuery =  False
       		 Exit Function
    	End If
	ExecuteQuery = True
End Function

Example 4 – Retrieve the column names from a table

Set MyRecordset = getRecordset("Select * from Orders")
nColumns = MyRecordset.Fields.Count
For n = 0 to nColumns - 1
	Print MyRecordset.Fields(n).Name
Next

Example 5 – Import Database Table into Data table

‘ Get recordset
Set MyRecordset = getRecordset("Select * from Orders")
‘Get number of columns in table
nColumns = MyRecordset.Fields.Count
‘Add a sheet in your datatable
Datatable.AddSheet ("DBImport")
For n = 0 to nColumns – 1
‘Get column name
		ParamName = MyRecordset.Fields(n).Name
		‘Add DTParameter
		Datatable.GetSheet("DBImport").AddParameter  ParamName,""
		nRow = 1
		MyRecordset.MoveFirst
		‘Retrieve and place data in data table
		Do while MyRecordset.EOF <> True
			Datatable.SetCurrentRow(nRow)
			Datatable(ParamName,"DBImport") = MyRecordset.Fields(ParamName)
			nRow = nRow + 1
			MyRecordset.MoveNext
		Loop
Next

Apart from this, if you need to connect to a excel file using ADODB connection, you will have to specify a different connection string, which will be like

" DRIVER={Microsoft Excel Driver (*.xls)};DBQ= FileName ;Readonly=True"

With this Saket concludes the series on Database Checkpoint and QTP.

Have you downloaded the FREE Optimizing QTP eBook yet? Get It Now!

I'm sure you have already subscribed to our feed but just in case not. I recommend you to subscribe via RSS feed. You can also subscribe by Email and have new QTP articles sent directly to your inbox.