Relevant Codes (by Anshoo Arora)

A Test Development Resource for HP QuickTest Professional.

QTP: Creating a Highly Efficient Test-Data Dictionary

by Anshoo Arora on October 8, 2009


Creative Commons License photo credit: myuibe

This article discusses a simple way to use test data with the help of the Dictionary object. I know many of you store data in Excel, and some of you may read the entire spreadsheet as a range, or retrieve data through individual cells. Some of you also use Access, SQL Server, Text files etc. This concept is not really about storing test data effectively, but it will discuss how we can use that data in a way that is easily understood without referencing our data sources over and over again. Instead, you access your data source only once. Also, this article shows a highly efficient and fast performing approach of creating our test data dictionary.

Let’s first start with creating our Scripting.Dictionary, that will store the entire test data. In other words, our dictionary object will contain everything that we would otherwise retrieve from our data pool. 

Set oDataDict = CreateObject("Scripting.Dictionary")

Above, we created the dictionary object that is going to store our test data descriptively. Let’s now add a few entities to our dictionary manually: 

With oDataDict
	.Add "UserName", "test"
	.Add "Password", "test"
	.Add "TripType", "roundtrip"
	.Add "PassCount", "2"
	.Add "FromPort", "Frankfurt"
	.Add "FromMonth", "December"
	.Add "FromDay", "9"
	.Add "ToPort", "Paris"
	.Add "ToMonth", "December"
	.Add "ToDay", "27"
	.Add "ServClass", "Coach"
	.Add "Airline", "Unified Airlines"
End With

All the entities in the dictionary object above are for HP/Mercury Demo Flight Application.

Now, we can simply use our data dictionary to write values to our application: 

SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com", "", "", 3
 
With Browser("title:=Welcome: Mercury Tours").Page("micclass:=Page")
	If .Exist(5) Then
		.WebEdit("name:=userName").Set oDataDict("UserName")
		.WebEdit("name:=password").Set oDataDict("Password")
		.Image("name:=login").Click
	End If
End With
 
Set oDataDict = Nothing

Download Demo above.

This part was quite simple, but, there is more, a lot more. Now, how would we build this dictionary object if all our data was in Excel? What I would do in such a situation is read the entire spreadsheet in a range, and use the range-array to write values to my object. Even though the columns change, I would only need to rebuild my dictionary, and never mess with my action libraries. However, there is an even better approach, and that is with the use of ADO and it is explained in detail below.

With Excel, we can use the Class clsTestData below to build our dictionary with great performance and have the exact same dictionary as we created manually (but without much effort): 

'===============================================================
' Name: Class clsTestData
'
' Purpose: Creates a TestData Dictionary
'
' Functions:
'	BuildContext (Private)
'	Load (Public)
'
' Properties:
'	oDict
'
' Author: Anshoo Arora
'
' Version: 0.1
'===============================================================
Class clsTestData
 	Private mDict       'Local Instance of Scripting.Dictionary
	Public sWorkBook    'Excel WorkBook
	Public vSheet       'Excel WorkSheet
	Public iRow         'Excel Row where test data is contained

	'=======================================================
	' Name: Function Load
	'
	' Purpose: This function makes the TestData dictionary available
	'	to the test.
	'
	' Input:
	'	sWorkBook: Path to the Workbook where test data is stored
	'	vSheet   : Name of the Worksheet where the data is stored
	'	iRow     : Row where the data is retrieved from
	'
	' Output:
	' 	Object- Scripting.Dictionary
	'=======================================================
	Public Default Function Load(sWorkBook, vSheet, iRow)
 		With Me
			.sWorkBook = sWorkBook
			.vSheet = vSheet
			.iRow = iRow
		End With
 
		BuildContext : Set Load = oDict
	End Function
 
	'=======================================================
	' Name: Function BuildContext
	'
	' Purpose: This function does the core operation of building the
	'	test data dictionary.
	'
	' Input:
	'	None
	'
	' Output:
	' 	None
	'=======================================================
	Private Function BuildContext
		Dim oConn, oRS, arrData, x
		CONST adOpenStatic = 3
		CONST adLockOptimistic = 3
		CONST adCmdText = "&H0001"
 
		Set oConn = CreateObject("ADODB.Connection")
		Set oRS = CreateObject("ADODB.RecordSet")
 
		'Open Connection
		oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
					"Data Source=" & Me.sWorkBook & ";" & _
					"Extended Properties=""Excel 8.0;HDR=Yes;"";"
 
		'Query
		sQuery = "Select * From [" & Me.vSheet & "$]"
 
		'Run query against WorkBook
		oRS.Open sQuery, oConn, 3, 3, 1
 
		'Move RecordSet to the target Row
		For x = 2 to iRow - 1 : oRS.MoveNext : Next
 
		oDict = CreateObject("Scripting.Dictionary")
 
		'Use a For..Loop to Build Scripting.Dictionary
		For x = 0 to oRS.Fields.Count - 1
			With oDict
				.Add "" & oRS(x).Name, "" & oRS.Fields(x)
			End With
		Next
	End Function
 
	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property
	Private Property Get oDict()
		Set oDict = mDict
	End Property
End Class
Set mDataContext = New clsTestData

Download the Class: clsTestData

Since our class is ready, we can use it to retrieve data and build our dictionary from the table below:

Excel Table with Test Data

Excel Table with Test Data

Download Table

You might think that this will have a drastic impact on performance, and that your script will become slow. Actually, I have a table with 72 columns and it took me 0.3125 seconds to load the entire row to the dictionary.

We can use the below code to build our test data dictionary at runtime using the Table above: 

sWorkBook = "C:\Test.xls"        'WorkBook
vSheet = "Sheet1"                'WorkSheet
iRow = 2                         'Row Number

Set mDict = mDataContext.Load(sWorkBook, vSheet, iRow) 'Took 0.156 seconds to build

MsgBox mDict("Execute_Test")     'Y
MsgBox mDict("Scenario Name")    'Scenario 1
MsgBox mDict("Username")         'test
MsgBox mDict("Password")         'test
MsgBox mDict("Trip Type")        'roundtrip
MsgBox mDict("Passenger Count")  '1
MsgBox mDict("From Port")        'Frankfurt
MsgBox mDict("From Month")       'April
MsgBox mDict("From Day")         '10

Note: All names used in the Dictionary Object correspond to the column names in the Excel spreadsheet. Execute_Test, Scenario Name, Username etc. are exactly as shown in the heading of our Table above.

The Question: How and where should I use this?
Its recommended to load all test data at the very beginning of the test. The only difference here is, one single variable stores all your data in a descriptive way, so it becomes very easy to know what data is being input to our application’s fields. If you have a driver script and a class member that loads all the global variables and objects, then that would be an ideal place to load this dictionary. In my driver script, this is how I load my globals: 

'DataDict will contain all test data
'Its public status dictates that it can be accessed by other classes and functions
Public DataDict
 
Class clsDriver
 
    Public Default Function Run
        LoadGlobals
 
        'Driver Code

        UnloadGlobals
    End Function
 
    Private Function LoadGlobals
        Set DataDict = mDataContext.Load(sWorkBook, vSheet, iRow)
    End Function
 
    Private Function UnloadGlobals
        Set DataDict = Nothing
    End Function
 
End Class

Download Demo: This demo shows how this technique can be used in practice.

I hope this article will help you create a highly efficient way of dealing with your test data. Thanks for reading. :)

If you have any questions, please ask them in the comments section. If your information is to be treated as confidential, please use the Contact Form to send me an e-mail instead.

Tagged as: Dictionary, QTP, QTP Dictionary, QTP Test Data, QTP Test Data Dictionary, Test Data, TestData

{ 70 comments… read them below or add one }

1 MRT November 2, 2009 at 6:24 pm

Hi Anshoo. First off, your site is definitely one of the better reads out there with regards to QTP/VBscript. Always learn something new here. Your articles on classes finally convinced me to give it a try and I have a question that I hope you can help me with. I created a simple class to hold dynamic data. I am trying to access that data inside a function. Is creating a dictionary object (as above) or an array inside the class the only way to do so? Because when I try to access the data inside my function I do not get any value back. Below are the steps that I am following

1) Generate dynamic data.
2) Call the class and store the value.
3) Call a function that will use the value in the class, by calling that class to retrieve the value. The value is not being returned inside the class.

So, what am I doing wrong here? Thanks.

Reply

2 MRT November 2, 2009 at 6:33 pm

Small typo. Step 3 should say

3) Call a function that will use the value in the class, by calling thhe class to retrieve the value. The value is not being returned inside the FUNCTION.

3 MRT November 4, 2009 at 8:03 pm

Hi Anshoo
I had initially tried the method you suggest above and had run into issues. The code finally worked, but only when I used the ExecuteFile method to load the libraries. For some reason, if you associate the library as a test resource, the code will not work. Thanks for all your help in resolving this.

Reply

4 Anshoo Arora November 4, 2009 at 8:08 pm

Hi again MRT,

Actually, there is a way to make it work even when you associate your libraries as test resources. Here is what I do in my library:

Public TestCaller

Class clsTest
        Public intTest

        Public Function Test
                Test = intTest
        End Function
End Class

Set TestCaller = New clsTest

Here is what i would do in a test:

TestCaller.intTest = 10
MsgBox TestCaller.Test

However, I do not want you to switch what you’re comfortable with just because I prefer the above way :). I would like you to follow an approach that best suits you, and I’m glad that its working for you now.

5 Amit Saran January 22, 2010 at 9:29 am

Hi Anshu

I just read this post. It’s really awesome. I like the way you elaborate the things using the code, so we the people get to know real idea and can do some practical stuff.
Your blog is really helping us. So please carry on.

Thanks

Reply

6 Anshoo Arora January 24, 2010 at 11:09 am

Hi Amit,

Thanks for your kind words. I’m also very glad to have great readers like yourself who share such encouraging words that keep me going.

Thanks :)

7 MRT March 4, 2010 at 6:55 pm

Hi Anshoo
I have sent you an email since the code in question is a bit confidential. Thanks.

MRT

Reply

8 qtptester March 12, 2010 at 9:24 am

Hi Anshoo,
Its a very intersting article.i tried at my end and it was working as expected.i wanted to tweak this code to suit my requiremetn and for that i needed your help.

the excel sheet i am importing has a unique column say empid.i wanted to access the dictionary using this empid and not by rownumber.
is this possible

Reply

9 Anshoo Arora March 12, 2010 at 2:18 pm

Sure is possible, my friend. There you go:

'=========================================
' Name: Class clsMasterData
'
' Purpose: Creates a test data context. More information on this
'	class can be found at Relevant Codes:
'	http://relevantcodes.com/qtp-creating-a-highly-efficient-test-data-dictionary/
'
' Functions:
'	BuildContext (Private)
'	Load (Public)
'
' Properties:
'	oDict
'
' Methods:
'
' Author: Anshoo Arora
'
' Start: October 02, 2009
'
' Modified:
'=========================================
Class clsTestData
 	Private mDict       'Local Instance of Scripting.Dictionary
	Public sWorkBook    'Excel WorkBook
	Public vSheet       'Excel WorkSheet
	Public iRow         'Excel Row where test data is contained
	Public vEMPID

	'=======================================================
	' Name: Function Load
	'
	' Purpose: This function makes the TestData dictionary available
	'	to the test.
	'
	' Input:
	'	sWorkBook Path to the Workbook where test data is stored
	'	vSheet    Name of the Worksheet where the data is stored
	'
	' Output:
	' 	Object Scripting.Dictionary
	'=======================================================
	Public Default Function Load(sWorkBook, vSheet, vEMPID)
 		With Me
			.sWorkBook = sWorkBook
			.vSheet = vSheet
			.vEMPID = vEMPID
		End With

		BuildContext : Set Load = oDict
	End Function

	'=======================================================
	' Name: Function BuildContext
	'
	' Purpose: This function does the core operation of building the
	'	test data dictionary.
	'
	' Input:
	'	None
	'
	' Output:
	' 	None
	'=======================================================
	Private Function BuildContext
		Dim oConn, oRS, arrData, x, oWSH, sQuery, iRow

		Set oConn = CreateObject("ADODB.Connection")
		Set oRS = CreateObject("ADODB.RecordSet")

		'On Error Resume Next
			'Open Connection
			oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
						"Data Source=" & Me.sWorkBook & ";" & _
						"Extended Properties=""Excel 8.0;HDR=Yes;"";"

			'Query
			sQuery = "Select * From [" & Me.vSheet & "$]"

			'Run query against WorkBook
			oRS.Open sQuery, oConn, 3, 3, 1

			If Err.Number <> 0 Then
				Set oWSH = CreateObject("Scripting.Shell")
				oWSH.Popup "Error With the TestData File.", 5, "RunTime Error!", (0)
			End If
		On Error Goto 0

		'Increment x depending upon the column
		'COlumn 1 in Excel: x = 0
		'Column 2 in Excel: x = 1
		' ... and so on ...
		x = 0

		Do Until oRS.EOF
			If Trim(LCase(CStr(oRS.Fields(x)))) = LCase(CStr("1234")) Then
				iRow = x + 1
				Exit Do
			End If
			oRS.MoveNext
		Loop

		Msgbox iRow

		'Move RecordSet to the target Row
		For x = 2 to iRow - 1 : oRS.MoveNext : Next

		oDict = CreateObject("Scripting.Dictionary")

        'Use a For..Loop to Build Scripting.Dictionary
		For x = 0 to oRS.Fields.Count - 1
			'Print "" & oRS(x).Name & " -> " & oRS.Fields(x)
			With oDict
				.Add "" & oRS(x).Name, "" & oRS.Fields(x)
			End With
		Next
	End Function

	'=======================================================
	' Name: Property Get oDict
	'
	' Purpose:
	'
	' Input:
	'	None
	'
	' Output:
	' 	Object- Scripting.Dictionary
	'=======================================================
	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property
	Private Property Get oDict()
		Set oDict = mDict
	End Property
End Class

Function MasterData
	Set MasterData = New clsTestData
End Function

Set dicTestData = MasterData.Load("C:\Test.xls", "Test", "1234")

10 reena March 15, 2010 at 1:34 am

Hi Anshoo,
i have twol excel sheets .one excel is master data sheet and another is generated by interface.data in both these sheets are of employees for ex employee id,gender,first name etc.the requirement is to compare the interface file with master file .to verify all records along with their fields are present in master excel.
i tried with vb scripting but its taking a lot amount of time.so i was thinking that if i can create two dictionaries on these two files and then compare these two dictionaries.is this possible

Reply

11 Anshoo Arora March 16, 2010 at 10:13 am

Hi Reena,

Please see if the below function works at a better performance:

Public xlApp

Class clsCompareXL
    Private oExcel
    Private arrRangeUno, arrRangeDos
    Private oDict

    Public Function AreEqual(sWorkBookUno, vSheetUno, sWorkBookDos, vSheetDos)
        Dim oWorkBookUno, oWorkBookDos

        AreEqual = False

        Set oWorkBookUno = oExcel.WorkBooks.Open(sWorkBookUno)
        Set oWorkBookDos = oExcel.WorkBooks.Open(sWorkBookDos)

        arrRangeUno = oWorkBookUno.WorkSheets(vSheetUno).UsedRange.Value
        arrRangeDos = oWorkBookDos.WorkSheets(vSheetDos).UsedRange.Value

        If Not CellsFound > 0 Then AreEqual = True

        oWorkBookUno.Close
        oWorkBookDos.Close
    End Function

    Public Sub FindDifferences(sWorkBookUno, vSheetUno, sWorkBookDos, vSheetDos)
        If Not AreEqual(sWorkBookUno, vSheetUno, sWorkBookDos, vSheetDos) Then
            Dim Keys, iKey, iRow, iCol

            Keys = oDict.Keys

            Set oWorkBookUno = oExcel.WorkBooks.Open(sWorkBookUno)
            Set oWorkBookDos = oExcel.WorkBooks.Open(sWorkBookDos)

            Set oSheetUno = oWorkBookUno.WorkSheets(vSheetUno)
            Set oSheetDos = oWorkBookDos.WorkSheets(vSheetDos)

            For Each iKey in Keys
                iRow = CInt(Split(iKey, "|")(0))
                iCol = CInt(Split(iKey, "|")(1))

                oSheetUno.Rows(iRow).Columns(iCol).Interior.ColorIndex = 3
                oSheetDos.Rows(iRow).Columns(iCol).Interior.ColorIndex = 3
            Next

            With oWorkBookUno
                .Save : .Close
            End With

            With oWorkBookDos
                .Save : .Close
            End With
        End If
    End Sub

    Private Function CellsFound()
        CellsFound = 0

        Set oDict = CreateObject("Scripting.Dictionary")

        iBoundsUno = UBound(arrRangeUno, 2)
        iBoundsDos = UBound(arrRangeDos, 2)

        If iBoundsUno <> iBoundsDos Then
            Reporter.ReportEvent micWarning, "Compare", "Unequal Range."
        End If

        For iCellUno = 1 to UBound(arrRangeUno, 1)
            For iCellDos = 1 to UBound(arrRangeUno, 2)
                If arrRangeUno(iCellUno, iCellDos) <> arrRangeDos(iCellUno, iCellDos) Then
                    oDict.Add iCellUno & "|" & iCellDos, ""
                End If
            Next
        Next

        CellsFound = oDict.Count
    End Function

    Private Sub Class_Initialize()
        Dim bInit: bInit = False

        If IsObject(xlApp) Then
            If Not xlApp Is Nothing Then
                bInit = True
            End If
        End If

        If bInit = False Then Set xlApp = CreateObject("Excel.Application")

        Set oExcel = xlApp
    End Sub
End Class

It uses the Excel.Application object as a Singleton so you will have to close the instance when you are done with all Excel operations. Usage:

Set Sheets = New clsCompareXL
Sheets.FindDifferences "C:\Test1.xls", "Sheet1", "C:\Test2.xls", "Sheet1"

'Release the Singleton Object
xlApp.Quit
Set xlApp = Nothing

12 Harendra March 16, 2010 at 5:32 am

Hello Anshoo,

I was trying to execute the code written by you for “Creating a Highly Efficient Test-Data Dictionary” . But I am getting an error.. Please help me out. where am i doing mistake… Thanks!

Error-
The Microsoft Jet database engine cannot open the file ‘C:\Documents and Settings\a0110087\Desktop’. It is already opened exclusively by another user, or you need permission to view its data.

Line (69): “oConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” &”Data Source=” & Me.sWorkBook & “;” & “Extended Properties=”"Excel 8.0;HDR=Yes;”";”".

Reply

13 Anshoo Arora March 16, 2010 at 10:51 am

Hi Harendra,

I think the error occurs because you are not binding to the Excel WorkBook. Instead, the path points to the Desktop. Your file should be:

C:\Documents and Settings\a0110087\Desktop\FileName.xls

14 Harendra March 16, 2010 at 5:43 am

adding comments ….

Sorry Please ignore above Error..
I am getting Error –

Could not find installable ISAM.

Line (69): “oConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” &”Data Source=” & Me.sWorkBook & “;” & “Extended Properties=”"Excel 2002;HDR=Yes;”";”".

Reply

15 Anshoo Arora March 16, 2010 at 10:55 am

Harendra,

Please see this article on MSDN with the resolution to this issue: http://support.microsoft.com/kb/209805

Alternatively, you can read this thread which also references to the above article: http://it.toolbox.com/blogs/sharepoint-blog/could-not-find-installable-isam-17062

16 reena March 17, 2010 at 2:44 am

Hi Anshoo,

thanks for your help.it works fine when record in both excel are in same sequence.but in cases where sequence changes it marks failure.in my particular case needless of sequence i just had to check whether recors of say paricular emp exist in both excels.

Reply

17 Anshoo Arora March 17, 2010 at 12:26 pm

What kind of record? This seems like a complete redesign of the code I had provided above.

Can you share with me the code that you are currently using to give me a better understanding of what is required?

18 Sam March 18, 2010 at 12:10 am

Hi Anshoo,

Thanks for providing very detailed script for test data, I would like to know the below things -

1. Approach :- If we need only 10 values to pass in a web application as test data, then the Data driven facility provided by QTP is more suitable, right?, And if our script require more than 50 values then this Class object method will be useful, Please let me know if it sounds correct?
So my question is – If these 2 scripts are part of same project , then these different approches are fine OR should we follow consistent methodology across all scripts for specific project ?

2. Do we need to attach the Class (clsTestData.cls) in File>Settng>Resources ? ,

3. You have also discussed some other approach like- execute file method, can you please detail of this method..

Thanks for your time, your guidance is really appreciated..
Sam

Reply

19 Anshoo Arora March 18, 2010 at 1:10 pm

Hi Sam,

Good questions.

For #1: I know i say this a lot, but there is no correct answer to this. It depends. It depends on your experience and comfort level working with the DataTable as opposed to Excel. I know this is quite vague and let me try to explain my thoughts on this.

The last time I used the DataTable in one of my Automation duties was nearly 3 years ago, and the reason behind such a big time-gap is the limitation and performance overhead of the DataTable object. Using Excel can be a little complicated to begin with, but offers a lot of flexibility and performance the DataTable just cannot match. So, to really answer your question more from a personal standpoint, I would use Excel in both scenarios, mainly because I know my scripts will run faster, I will have more control over how I utilize the data and moreover, an Excel spreadsheet can incorporate custom Macros giving users more control over how they arrange and browser over data.

#2: Yes, you can attach the file as a Test Resource.

and #3: apart from attaching the file as a Test Resource, you can use ExecuteFile to load the library file if you choose not to associate it using the following syntax:

ExecuteFile "C:\TestFile.vbs"

I hope this is helpful.

-Anshoo

20 reena March 18, 2010 at 1:40 am

Anshoo,

My requirement is to verify that records in Interface excel sheet matches with master data record.interface excel sheet is just a subset of master data sheet,so records in master sheet wotn have the same sequence.both these sheets have employee data.Currently I first take the empid column of interface sheet and verify its existence in master data .if found then I verify all other columns for that particular emp id.

Normally records are around 30 thousand in number.so I was looking at a faster method to do all verifications

Reply

21 Anshoo Arora March 18, 2010 at 1:28 pm

Reena,

That is surely a very large Spreadsheet to work with and I can see it will take a very long time to ensure all the mismatched values are checked. To be able to verify the subset of data, the above function can easily be customized to work with given rows instead of the entire Excel Range. I will try to modify the code tonight to give you a better picture; will post the code soon.

22 Martin Mansfield March 18, 2010 at 11:51 am

Anshoo,

I find this to be a great way to facilitate data loads in data-driven testing. My question is about multi-select fields in Siebel and Web applications. We use Excel spreadsheets where we can separate multiple entries in a cell for a picklist by a vbCrLf (Ctrl-Enter in Excel) or just use commas to separate them. My question is which one is more efficient for loading data into the class dictionary object. We can use a regular expression to find if commas exist in any of the Excel cells and that signals the need to parse and peform a .set loop for multi-selection fields. The loop construct around vbCrLf is cleaner but appears to run more slowly.

I wish my data were always as clean and simple as the Mercury Tours demo program, but we have lists and selection fields that change dynamically.

Can you recommend generally what might be a better way to do this if you know of one?

Regards,

Martin

Reply

23 Anshoo Arora March 18, 2010 at 1:26 pm

Martin,

You are right in saying that real-life application and demo applications are worlds apart. The reason why I chose the demo application was that it would be accessible to everyone and give me a better chance to explain this concept in more detail.

I think, and I am saying this without prior testing it, one way you could do it is separating the 2 values using a sort of delimiter and standardize that delimiter across all your tests. In other words, suppose you have to select 2 values in the PickList object using this method and the NameList holds the following 2 values:

DataDict("NameList") = "Anna;John"

I have used the ; character because you will generally not see it used between list items and its quite safe to work with it as well. Now, to select the 2 values, you can create and register a custom function:

Function SelectX(Object, inValue)
	If Object.Exist(1) Then
		arrItems = Split(inValue, ";")

		For ix = 0 to arrItems
			Object.ExtendSelect arrItems(ix)
		Next
	End If
End Function

RegisterUserFunc "WebList", "SelectX", "SelectX"

The above function can be extended or re-written for the Siebel Environment as well. Now, the following function can be used throughout Lists:

Browser("").Page("").WebList("").SelectX DataDict("NameList")

I hope you find this helpful. If you have any questions, please use the comments section and I will try to respond asap.

-Anshoo

24 MichaelTP March 24, 2010 at 11:06 am

Hi Anshoo
I like your article because my code using many of the DataTable objects and functions has severe performance problems and should run faster by factor 1000! Now I hope this article gets me closer to this goal. But I have to learn more about the OO Programming syntax like “BuildContext : Set Load = oDict”. Can you explain what this line exactly does or where I can find some decent explanation about the meaning of the colon ‘:’ between the function name ‘BuildContext’ and the statement ‘Set Load = oDict’ ? Thanks in advance for a decent hint or link, cheers Michael

Reply

25 Anshoo Arora March 24, 2010 at 1:50 pm

Michael,

The : divides 2 statements and enables them to be written on the same line. In other words:

BuildContext : Set Load = oDict

is equivalent to:

BuildContext
Set Load = oDict

The code BuildContext : Set Load = oDict points to the Private Function BuildContext and the dictionary object that resides in BuildContext, which is oDict. Since everything is within the same class, all procedures and functions can access oDict as it is a region level property:

Private Property Let oDict(ByVal Val)
	Set mDict = Val
End Property
Private Property Get oDict()
	Set oDict = mDict
End Property

26 MichaelTP March 26, 2010 at 11:36 am

Hi Anshoo! The following code isn’t working yet, but should allow me and the reader’s of this blog to understand why. Basicly I used the concept described by your article to replace a slow DataTable object against something intirely held in random access memory. My difficulty is to understand, after an item is copied to or from the local data dictionary, why its methods like mDict.Key(intRow) in the clsParameter class isn’t working as expected. May be you’ve got the clue. I’m using QTP 9.5.
Thanks in advance.
Have a nice weekend,
Michael

Option Explicit
'===============================================================
' Name: Class clsDriver
'
' Purpose: Drives a TestData Dictionary
'
' Functions:
'	BuildContext (Private)
'	Load (Public)
'
' Properties:
'	oDict
'
' Author: Anshoo Arora & Michael T. Pilawa
'
' Version: 0.1
'===============================================================
Class clsDriver
	Dim sWorkBook,vSheet,iRow

	   Public  Sub Class_Initialize

	   End Sub

    Public Default Function Run
        LoadGlobals

        'Driver Code
		sWorkBook = GetLocalPath()&"\"&GetWorkBook()
		vSheet = "Tabelle1"                'WorkSheet
		'iRow = 20                         'Row Number

        UnloadGlobals
    End Function

    Private Function LoadGlobals
'        Set DataDict = mDataContext.Load(sWorkBook, vSheet, iRow)
    End Function

    Private Function UnloadGlobals
        Set DataDict = Nothing
    End Function

End Class'clsDriver
'===============================================================
' Name: Class clsTestData
'
' Purpose: Creates a TestData Dictionary
'
' Functions:
'	BuildContext (Private)
'	Load (Public)
'
' Properties:
'	oDict
'
' Author: Anshoo Arora
'
' Version: 0.1
'===============================================================
Class clsTestData
 	Private mDict       'Local Instance of Scripting.Dictionary
	Private sWorkBook    'Excel WorkBook
	Private vSheet       'Excel WorkSheet
	Private iRow         'Excel Row where test data is contained
	Private oSheet

	Public  Sub Class_Initialize
			Set oDict = CreateObject("Scripting.Dictionary")
	End Sub

	'=======================================================
	' Name: Function Load
	'
	' Purpose: This function makes the TestData dictionary available
	'	to the test.
	'
	' Input:
	'	sWorkBook: Path to the Workbook where test data is stored
	'	vSheet   : Name of the Worksheet where the data is stored
	'	iRow     : Row where the data is retrieved from
	'
	' Output:
	' 	Object- Scripting.Dictionary
	'=======================================================
	Public Default Function Load(sWorkBook, vSheet, iRow)
 		With Me
			.sWorkBook = sWorkBook
			.vSheet = vSheet
			.iRow = iRow
		End With

		BuildContext
		Set Load = oDict
	End Function'Load

	'=======================================================
	' Name: Function BuildContext
	'
	' Purpose: This function does the core operation of building the
	'	test data dictionary.
	'
	' Input:
	'	None
	'
	' Output:
	' 	None
	'=======================================================
	Private Function BuildContext
		Dim oConn, oRS, arrData, x
		CONST adOpenStatic = 3
		CONST adLockOptimistic = 3
		CONST adCmdText = "&H0001"

		Set oConn = CreateObject("ADODB.Connection")
		Set oRS = CreateObject("ADODB.RecordSet")

		'Open Connection
		oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
					"Data Source=" & Me.sWorkBook & ";" & _
					"Extended Properties=""Excel 8.0;HDR=Yes;"";"

		'Query
		sQuery = "Select * From [" & Me.vSheet & "$]"

		'Run query against WorkBook
		oRS.Open sQuery, oConn, 3, 3, 1

		'Move RecordSet to the target Row
		For x = 2 to iRow - 1
			 oRS.MoveNext
		 Next

		Set oDict = CreateObject("Scripting.Dictionary")

		'Use a For..Loop to Build Scripting.Dictionary
		For x = 0 to oRS.Fields.Count - 1
			With oDict
				.Add "" & oRS(x).Name, "" & oRS.Fields(x)
			End With
		Next
	End Function'BuildContext

	Public Function GetSheet(ByVal strSheetName)
	   Dim intReturnValue
		Set GetSheet=Nothing
		Err.clear
		With oDict
			On Error Resume Next
			intReturnValue=StrComp(strSheetName,.Key( Cstr(strSheetName)))
			If Cbool(Not isEmpty(intReturnValue)) AND Cbool(0=intReturnValue) Then
				Set oSheet=.Item(strSheetName)
				Set GetSheet=oSheet
			Else
				Err.Raise vbObjectError + 1, strSheetName&" is no sheet"    ' Raise Object Error #1.
			End If
		End With
	End Function'GetSheet

	Public Function AddSheet(ByVal strSheetName)
		On Error Resume Next
		AddSheet=GetSheet(strSheetName)
		If  0err.number Then
			'das Sheet existiert noch nicht
            Set oSheet=New clsSheet
			oSheet.Name=strSheetName
			With oDict
				.Add Cstr(strSheetName), oSheet
			End With
			Set AddSheet=oSheet
		End If
	End Function'AddSheet

	Public Function GetCurrentRow
		GetCurrentRow=iRow
	End Function

	Public Function SetCurrentRow(ByVal intActualRow)
		iRow=intActualRow
	End Function

	Private Property Set oDict(ByVal Val)
		Set mDict = Val
	End Property'Set

	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property'Let

	Private Property Get oDict()
		Set oDict = mDict
	End Property'Get

End Class'clsTestData
'===============================================================
Class clsSheet
	Private mDict       'Local Instance of Scripting.Dictionary
	Private ddSpalte
	Private strSpaltenName
	Private intRow
	Private strSheetName

	Public  Sub Class_Initialize
		Set oDict = CreateObject("Scripting.Dictionary")
	End Sub

	Public Function GetRowCount
		Dim intRowCount,arraySpalten
	   	intRowCount=0
		With oDict
		   If .Exists(strSheetName) Then
			    arraySpalten=.Item(strSheetName).Items
				For each ddSpalte In arraySpalten
					If intRowCount<Ubound(ddSpalte.Items) Then
						intRowCount=Ubound(ddSpalte.Items)
					End If
				Next
			End If
		End With
		GetRowCount=intRowCount
	End Function

	Public Sub AddParameter(ByVal strParameterName, ByVal strValue)
		On Error Resume Next
		strSpaltenName=strParameterName
		Call GetParameter(strParameterName)
		If  0err.number Then
			Set ddSpalte=new clsParameter
			With ddSpalte
				.Name=strParameterName
				.Value=strValue
			End with
			oDict.Add strParameterName, ddSpalte
		End If
	End Sub'AddParameter

	Public Function GetParameter(ByVal strParameterName)
		Dim intReturnValue,strReturnValue
		Set GetParameter=Nothing
		Err.clear
        With oDict
			On Error Resume Next
			For Each strReturnValue In .Keys
				If  Not isEmpty(strReturnValue) Then
					intReturnValue=StrComp(strParameterName,strReturnValue)
					If 0=intReturnValue Then
						Set GetParameter=.Item(strParameterName)
						intRow=Ubound(.Items)
						Exit Function
					End If
				End If
			Next
			Err.Raise vbObjectError + 1, strParameterName &" is no parameter"    ' Raise Object Error #1.
		End With
	End Function'GetParameter

	Public Property Set Name(ByVal strVal)
			strSheetName=strVal
			mDict.Key(intRow)=strSheetName
	End Property'Set Name

	Public Property Let Name(ByVal strVal)
			strSheetName=strVal
			mDict.Key(intRow)=strSheetName
	End Property'Let Name

	Public Property Get Name()
		Dim arrayOfKeys
		arrayOfKeys=mDict.Keys
		strSheetName=arrayOfKeys(intRow)
		Set Name = strSheetName
	End Property'Get Name

	Private Property Set oDict(ByVal Val)
		Set mDict = Val
	End Property'Set

	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property'Let

	Private Property Get oDict()
		Set oDict = mDict
	End Property'Get

End Class'clsSheet
'===============================================================
Class clsParameter
	Private mDict       'Local Instance of Scripting.Dictionary
	Private intRow

	Public  Sub Class_Initialize
		oDict = CreateObject("Scripting.Dictionary")
		intRow=oDict.Count
	End Sub

	Public Function ValueByRow(ByVal intActualRow)
		With oDict
			If .Exists(intActualRow) Then
				ValueByRow=.Value
			Else
				.Add intActualRow,"-"
				intRow=intActualRow
				ValueByRow=Value()
			End If
	   End With
	End Function

	Public Property Set Name(ByVal strVal)
			mDict.Key(intRow)=strVal
	End Property'Set Name

	Public Property Let Name(ByVal strVal)
			mDict.Key(intRow)=strVal
	End Property'Let Name

	Public Property Get Name()
	   Dim arrayOfParameterValues, strParameterName
	   arrayOfParameterValues=mDict.Keys
	   strParameterName=arrayOfParameterValues(intRow)
		Name=strParameterName
	End Property'Get Name

	Public Property Set Value(ByRef strVal)
	   Dim strKey
	   With mDict
			strKey=.Key(intRow)
			.Item(strKey)=strVal
	   End with
	End Property'Let Value

	Public Property Let Value(ByRef strVal)
	   Dim strKey
	   With mDict
			strKey=.Key(intRow)
			.Item(strKey)=strVal
	   End with
	End Property'Let Value

	Public Property Get Value()
			Set Value=mDict.Item(oDict.Name)
	End Property'Get Value

	Private Property Set oDict(ByVal Val)
		Set mDict = Val
	End Property'Set oDict

	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property'Let oDict

	Private Property Get oDict()
		Set oDict = mDict
	End Property'Get oDict

End Class'clsParameter
'===============================================================
'===============================================================

'===============================================================
'===============================================================
Function DicDemo
   Dim a,k, d, i,j,p, s,w   ' Create some variables.
   Dim r(50)

	For j=0 to Ubound(r)
		Set r(j) = CreateObject("Scripting.Dictionary")
		For p=1 to 33
			r(j).Add "Parameter"&Cstr(p), "Wert(p"&p& ")="&"r"&Cstr(j)&"c"&Cstr(p)
		Next
    Next
'
'   d.Add "a", "Athens"   ' Add some keys and items.
'   d.Add "b", "Belgrade"
'   d.Add "c", "Cairo"
	For j=0 to Ubound(r)
		a = r(j).Items   ' Get the items.
		k = r(j).Keys ' Get the keys.
		s=""
		For i = 0 To r(j).Count -1 ' Iterate the array.
			s = s & a(i) & "," ' Create return string.
		Next
		w =w&""&s
		r(j).RemoveAll
	Next
	DicDemo=w
End Function
'===============================================================
'===============================================================
'msgbox DicDemo

Public DataDict
Dim objSheet,strSheetId,strParameterName,strDnValue,intRowCount,boolColExists

Set DataDict=New clsTestData
strSheetId="Reference.ldif"
strParameterName="DN"
strDnValue="DN1234"
If Not SheetExists(strSheetId) Then
		Set objSheet=DataDict.AddSheet(strSheetId)
		With objSheet
			intRowCount=.GetRowCount
			.AddParameter strParameterName, strDnValue
			With .GetParameter(strParameterName)
				If  0=err.number Then
					strParameterName=.Name
					If  0=err.number AND 0<Len(strParameterName) Then'strCol kann auch numerisch sein! err.number muss beim Fehlerfall ungleich 0 sein!
							.Value=strDnValue
							msgbox strParameterName&"="& .Value
							boolColExists=True
					Else
							boolColExists=False
					End If
				End If
			End with
		End with
End If

Reply

27 Anshoo Arora March 27, 2010 at 10:32 pm

Hi Michael,

I tried executing this code, but didn’t have the function SheetExists to test it as a whole.

Also, I saw a few places where you have written:

If 0err.number Then

This should be:

If Err.Number = 0 Then

Also, the following:

        Private Property Set oDict(ByVal Val)
		Set mDict = Val
	End Property'Set oDict

	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property'Let oDict

	Private Property Get oDict()
		Set oDict = mDict
	End Property'Get oDict

You should either use Set or Get, not both for the same property statement:

	Private Property Let oDict(ByVal Val)
		Set mDict = Val
	End Property'Let oDict

	Private Property Get oDict()
		Set oDict = mDict
	End Property'Get oDict

28 MichaelTP March 28, 2010 at 2:44 pm

Thank you very much Anshoo. I’ll give it another trial on Monday. The main problem for me derives from strParameterName=.Name because I’m not sure from your example and those found in the QTP help files, whether mDict will handle a value of type Object of one of my classes. If the data dictionary can only handle strings, this code will never work, because I need a replacement of an Excel sheet in random access memory in order to speed up the code by factor 1000. Sorting and searching in cells should happen within a few hundred millisecons instead of tens of minutes as it is the case with the DataTable object. If you’d replace the If Not SheetExists(strSheetId) Then by If True Then would the Parameter object’s “.Name” method give back the string “DN1234″ assigned by .AddParameter strParameterName, strDnValue?

Reply

29 Anshoo Arora March 28, 2010 at 11:37 pm

Michael,

You can store data of any type in a Scripting.Dictionary or an array. For example:

Set oDict = CreateObject("Scripting.Dictionary")

With oDict
    .Add "Browser", Browser("title:=Relevant Codes")
    .Add "String", "Relevant Codes"
    .Add "Integer", 9
    .Add "Array", arrArray
End With

This article shows an advanced use of Dictionary Objects.

30 MichaelTP April 12, 2010 at 1:50 am

By the way, in order to avoid misunderstanding for the other readers, the If 0err.number Then should read If 0<>err.number then or If Not 0=err.number Then. Kind regards, Michael

Reply

31 Neelakantan G S April 14, 2010 at 2:05 am

Hi Anshoo,
Why do we require this kind of logic? what benefits it has than data table parameter or environmental parameter

Regards
Neelakantan G S

Reply

32 Anshoo Arora April 14, 2010 at 3:28 pm

The main benefit is performance, which the DataTable object just cannot match. Not sure how this approach can be compared to environment parameters, but a lot of them would need to be created to store Excel Ranges.

33 MichaelTP April 28, 2010 at 2:14 pm

@Neelakantan G S
Please be aware, that above shown code is not fully functional. I found a few bugs and flaws and did a few enhancements in order to fully replace the DataTable object in the slow VBscript code. Anyway, I’ll end up with a considerable speed-up-factor (around 500 x) that makes it worthwhile to consider data dictionaries as a replacement for DataTable objects where speed of execution is crucial.

34 KenT December 16, 2010 at 2:57 pm

Anshoo,
Great site you have here, my friend. Very helpful. When I read the article on Advanced QTP on using the ADODB connection to read Excel files into arrays, I too thought that it would be a good place to use the Dictionary object… by the time I finished browsing around that day I had found this article of yours. I think it’s a great implementation of the idea.

I just have a question/observation that’s in line with Sam’s question above. I am wondering where or how it is you’re seeing such performance benefits? You repeat it often, and MichaelTP says his code is 500 times faster? x500!!!

I am trying this out with data like I’ll be using in real tests (multiple sheets per workbook, many columns per sheet). I am not seeing this speed in my testing out this class. I run the same timer on
a)loading a row into a dictionary and writing out a value
a)importing a sheet into the local data table, setting a row and writing out a value
The DataTable is faster. .9 seconds to importDataTable and 1.25 seconds to load Dictionary.

Obviously what makes the difference here is that your test data is only a few columns, while my real data will very often be 100+ columns.

There are other benefits that are keeping me interested.
a) can use .xlsx files
b) have more fexibility with setting up datasheets (can make them easier to work with)
c) can have access to multiple dictionaries each representing different sheets at once, instead of importing sheets multiple times

I haven’t used this class in a full script yet. There may be other benefits as well. And the time may even out some over a large script. But I wanted to touch base with you on this before I get very far with it, as I might be missing something in my considerations on performance.

Thanks again for the site,
Ken

Reply

35 Anshoo Arora December 23, 2010 at 2:01 pm

Hi Ken,

Can you please direct me to that link? I am no sure what dimensions were included to test performance, but a DataTable will definitely be slower in comparison to a variable holding data.

PS. ImportSheet vs Loading a Dictionary are 2 different concepts – its about data retrieval and using that data in your script.

Thanks!

36 KenT December 23, 2010 at 2:56 pm

Hi Anshoo,
When you say to direct you to that link, I am guessing you are talking about the Advanced QTP article I reffered to. Here’s that url: http://www.advancedqtp.com/knowledge-base/articles/ms-office4/universal-class-for-data-manipulations/

I’ve been tinkering with this for a little bit. And a couple of things weren’t quite a good fit for my scripts in using your class. I typically retrieve a rowcount and use it for my mainloop, navigating AUT screens and shifting datasheets and calling actions while staying on the current row number. The only way I could figure out to put a function in the class to return a rowcount is to generate a recordset and get rowcount, then begin the loop and then call the class again and generate another recordset in order to load a row into the dictionary. Also the regenerating the same recordset over and over each time I want a new row kind of bothers me. Just doesn’t seem efficient, at least in the environment and design I’m working with. If I am writing out a log or multiple logs or something to excel (API not ADO) in a script then I try to design it so that I create an instance of excel at the beginning and use it throughout the script and then kill it at the end, rather than create a new instance of Excel each time I create a new workbook or whatever the case may be.
This is sort of how I decided to modify your class. I’m writing one class to return a recordset to the action, which can be used to get rowcount, refer to previous or later rows… that will stay in memory throughout the life of the script. And then another class to load dictionary with the current row, using that recordset. This way an ADO call generating a recordset will only need to happen once per Excel Sheet.
I feel like this will be more efficient for me, based on how my scripts work and how my datasheets are designed. Very little difference in code from what you have, just a rearrangement of code and a different logical approach.
Any feedback you have on this will be welcome.

Once again, thanks so much for your work… it’s a great source of information.
Ken

Reply

37 Anshoo Arora December 23, 2010 at 3:25 pm

Ken,

I was referring to the link by Michael that you mentioned in your post above concerning comparison between performance between the DT and the ADO style dictionary.

The approach that I use for my data-driven tests is using ADO (or Excel Range) to read the entire sheet and building Test Dictionary objects for each iterations. So, just as you stated, my script also loops multiple times, depending upon the number of the times the test name appears in the Excel table. Each time the script loops, it generates a new dictionary object for that row seamlessly.

The class shown in this code is extremely basic so its not very time consuming to modify it for different uses. There can be several variations to this and infinite number of upgrades.

38 AK December 28, 2010 at 9:22 pm

HI Anshoo,
I am new to QTP, but i am well versed with VB Scripting.I just wanted to know how to use this class u have created above and Test.xls so that i can execute this.What Steps I have to follow to execute this via QTP.Please tell me from Step 1.I mean how to exceute this excel and class combination.You can also provide me one of ur article if u have made anythng over this.Like made a class in QTP or outside QTP.Looking for ur response asap.
Thanks!!

Reply

39 Anshoo Arora January 2, 2011 at 7:09 pm

AK,

You can copy the class in your QTP editor along with:

sWorkBook = "C:\Test.xls"        'WorkBook
vSheet = "Sheet1"                'WorkSheet
iRow = 2                         'Row Number

Set mDict = mDataContext.Load(sWorkBook, vSheet, iRow) 'Took 0.156 seconds to build

MsgBox mDict("Execute_Test")     'Y
MsgBox mDict("Scenario Name")    'Scenario 1
MsgBox mDict("Username")         'test
MsgBox mDict("Password")         'test
MsgBox mDict("Trip Type")        'roundtrip
MsgBox mDict("Passenger Count")  '1
MsgBox mDict("From Port")        'Frankfurt
MsgBox mDict("From Month")       'April
MsgBox mDict("From Day")         '10

Press the ‘F5′ key and view the outputs. Also, make sure the Excel path is correct.

40 AK January 30, 2011 at 2:04 pm

Hi Anshoo…
Thanks a ton from your response.
You have associated ClsTestData when u have created the test i.e. not at runtime.If i need to dynamically associate this class i.e. via code then what steps should i follow.

Thanks!!

Reply

41 Anshoo Arora February 19, 2011 at 7:01 am

AK, you can either use ExecuteFile or LoadFunctionLibrary (if you’re using QTP 11.0).

42 MichaelTP March 1, 2011 at 12:12 pm

Hi Anshoo
wih great success we’re using above mentioned code to read our parameter values from Excel workbook sheets into the global data dictionary. Unfortunately, the life expectation of the data dictionary is restricted to the duration of the Business Process Testing (BPT) components that we’re using. When two of such components want to use the same data dictionary, there is no such place to create the data dictionary for the lifetime of the entire test which is composed of those BPT components. Is there any way to reverse the storage of data dictionary content such that the data is persistent? I think of an upload of data dictionary content to the original workbook sheet, followed by a re-creation of the data dictionary with that persistent data in the following BPT component. Any ideas how to do that?
Best regards
MichaelTP

Reply

43 Anshoo Arora March 2, 2011 at 9:54 am

Michael,

Haven’t had an opportunity to use BPT components, but if you’re loading the data dictionary through a function library, it should be persistent for all actions + all components since Function libraries are associated at the test level.

44 SRJ March 23, 2011 at 7:53 am

Hi Anshoo,
Thanks for bringing out this article, I’d like to take this opportunity to thank you for coming out with excellent articles on QTP.

I have a question below:

The above code using Dictionary object retrieves only one row of data from the excel (i.e iRow=2). Is it possible to retrieve all the rows from the excel sheet.

I’m asking this because i need to develop a code to fetch records(multiple rows) from a table. Do you think it’s a good approach and possible to retrieve records using dictionary object, if not, what approach would you suggest (writing to an excel etc)?

Thanks in advance.

Reply

45 prasad April 26, 2011 at 6:38 am

Great work..Keep going..For the same function i was using datatable, which takes a lot of time with increasing data..Now the performance is good when i use this..

Reply

46 Shri May 22, 2011 at 12:12 am

Hi Michael/ Anshoo,

The Global dictionary concept in spite of adding it to the Registry is not helping me. I am unable to use the dictionary contents across the BPT Components. In one of my start up components i am initializing and filling up contents in the Dictionary (so as to avoid repeated DB calls to keep filling up the dictionary), but i observe that when the 2nd BPT component in sequence is invoked the Global Dictionary contents gets flushed out. Please suggest.

Reply

47 MichaelTP May 22, 2011 at 3:38 am

http://www.qtp10.com/2009/12/dictionary-object-and-qtp.html explains how to register the global dict in your windows registry. After running the registration script and restarting QTP all BPT components can use the data you fill into the GlobalDict Object because it is held persistent in the operating system as long as it is powered on.

48 Shri May 22, 2011 at 10:48 am

Hi Michael,

I have tried the following but to no avail. Instead of the code piece to update the registry i have manually made the entry in the registry for the global dictionary object under the reserved objects in QTP and set the ProgID for the Global dictionary key to Scripting.Dictionary followed by restarting QTP and running the components once again but did not help my case.
Have u given this a try? I am not sure why the contents get lost when the 2nd component gets invoked..Could you suggest if there is any small catch here which i might be missing upon..
Thanks in advance

Reply

49 MichaelTP May 22, 2011 at 12:01 pm

The code runs at the test automation system of my team. I do not have their code at hand, since I am at home today. Just make sure you use the registered Object of GlobalDict and not any other instance. You can veryfy the correct registration of your GlobalDict if you but a dot behind GlobalDict in an QTP script and QTP’s intellisense offers you a pop up box with the same methods as if it were a native object of one your libraries.

50 Anonymous May 30, 2011 at 12:09 pm

Hi Micheal,

Verified that the intellisense is active for the global dictionary object(ensures its registered) still facing the same issue. Also consuming the same registered global dictionary object and inserting items into it after retrieving the recordset object.
Please let me know if u have any other suggestions.I can copy paste my code for you next time in case dats required. Read some other articles which also say that the Global dictionary object does not work fine with BPT components.
Thanks a lot 4 ur support

Reply

51 MichaelTP May 30, 2011 at 3:41 pm

Without knowing your code its impossible to find the error. Some people tend to use the set method for setting up a local dictionary that will not do a deep copy of the global dict. Instead use the global dictionary with the same object identifier that you used to register it. What is registered in the registry is also available after filling it with keys and data. Both remains in the windows system no matter which BPT component is running. Start filling your global dict with simple keys and string data that can be easily retrieved from any BPT component. Debug using the msgbox function and try it also when starting the business process testing test from within the QC test lab. At least QC11 will instantiate a QTP11 wrapper action to embrace all BPT components in the test with a script. Try to fill your data dict in the first component an to read it from there, too. If this works, try to read it from the next ajacent component. Make sure that your scripts are part of the same app area and that this app area is part of all your components. Make sure QTP has the correct search paths set in order to find your script libraries. Keep scripts simple and stupid in the beginning, such that you learn most from every change you make to your environment. Unfortunately I cannot send you any code snippet, because this would offend my contract’s terms and conditions.

Reply

52 Shreejit June 10, 2011 at 9:15 pm

Hi Michael,

Thnks a ton for your support. I am home currently so do not have access to the code. I wl bring it to your notice soon so if you could help me pin point my mistakes it would be great.
The pain point is this:
We are using some base asset code which is very adhoc. There are some standard functions which connect to the database and retrieve items from the database. The problem is for every business component run the database gets hit and items retrieved once again stored in a local dictionary object and then consumed in the respective individual business component. This looks very adhoc since the same dataset is being pulled out repeatedly for the entire duration of the script. For our client we would like to do the following:
1. Hit the database in one of our startup components (pull the data and keep it in the Global Dictionary).
2. In all business components called later on consume this Global Dictionary (avoid making the recordset object and db connection once again which is currently in place)
3. The other challenge is we cannot modify any of the business components since there are more than 1600 of them which have code in the following format:
dctParameters(local dictionary object in business component) = GetPageData (some input)
‘Later dctParameters.Item(“xyz”) is consumed in the component

GetPageData ()
{
‘This method connects to the Database and returns a dictionary object and is kept in a common qfl file.
}
————————————————————————-
This is how we tried to modify the GetPageData()
{
‘The method was modified to fill contents in the Global Dictionary Object (set up in the registry) in our Startup component and the method
now returns the Global Dictionary object
‘Next time this method gets called from the 2nd BUSINESS COMPONENT we check the count property of the Global Dictionary object.
If we see the count > 0 then jus directly return the Global Dictionary object without making any DB connection or record set object or anything
}
Problem:
When running in debug mode when the 2nd component is invoked..i see the contents of the GlobalDictionary object have been wiped out.
So the above logic naturally fails. The first component works smoothly and seen that the GlobalDictionary Object is smoothly filled out.

I hope you get what the attempt is. In case you stil want to see the code i will try to share it next time but i think this is pretty straightforward to understand. So the only thing i could thnk of was a limitation to the entire BPT concept. But i am unable to trap it as u suggest it should work fine. Thnks a lot for your time and support.

I am open to any other suggestions you could give me to change the qfl file.Datatable anything but we cannot make any changes in the individual business components since you have seen the exact nature of the volume here and it would not be feasible for us.
Thanks,

Shri

Reply

53 Getafix August 24, 2011 at 4:19 pm

Hi Anshoo,

First of all , many appreciations for creating such a wonderful pool of knowledge to the QTP community and automation test engineers! Thanks so much and keep up the good work!

I am learning QTP and please pardon for any silly questions:)

In thefirst code sample after the EndWith there is’ Set oDataDict = Nothing’

Could you please explain why the need of this statement?

Reply

54 Anshoo Arora September 12, 2011 at 10:18 pm

Getafix: In case the code errors out, we can always check if it did by checking if the output is a dictionary or Nothing.

55 Jbel September 15, 2011 at 6:19 pm

Hi Anshoo,
I am very new to QTP without a training and I need to automate the following.
I have an application that creates a shippment id.
Each run shipment Id has to be unique. I using local data sheet. How do I make this script with every run enter new shipment id. Either increment value or give random name. It has to be 32 characters though, always. Thank you much!

If DataTable.GlobalSheet.GetCurrentRow=1 Then
SystemUtil.Run “iexplore.exe”,”http://vmappqa02/sapphire/logon.jsp”
End If
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"
Browser(“Clinical LIMS v1.4″).Page(“Add Shipment_2″).Frame(“maint_iframe”).WebEdit(“pr0_u_airbillnumber”).Set DataTable(“ShipmentNum”, dtGlobalSheet)
Browser(“Clinical LIMS v1.4″).Page(“Add Shipment_2″).WebButton(“Save”).Click
Browser(“Clinical LIMS v1.4″).Page(“Edit Shipment SH000000000601″).Frame(“maint_iframe”).WebButton(“Add”).Click
Browser(“Clinical LIMS v1.4″).Page(“Edit Shipment SH000000000601″).Frame(“maint_iframe”).WebEdit(“WebEdit”).Click
Browser(“Clinical LIMS v1.4″).Page(“Edit Shipment SH000000000601″).Frame(“maint_iframe”).WebEdit(“WebEdit”).Set DataTable(“TRID”, dtGlobalSheet)
Browser(“Clinical LIMS v1.4″).Page(“Edit Shipment SH000000000601″).WebButton(“Save”).Click

Reply

56 Anshoo Arora September 24, 2011 at 8:35 pm

Jbel: you can do so by making sure your DataTable column “ShipmentNum” has multiple rows with different data in each. Also, you can set your Iterations to run on all rows from Action Call Properties.

57 ritika January 17, 2012 at 2:24 am

Hi Anshoo,

Thanks for sharing so much and such valuable information on QTP. I am beginner and I can script using OR but have just started using DP. But I am confused a lot..And I didnt understand anything from from your article “Creating a Highly Efficient Test-Data Dictionary”. As you said in one of ur article that

“I don’t use Description Object much is because in large projects, when you have thousands of lines of code, it can sometime become tedious to refer to Description Objects by continuously scrolling up and down to find what each description says. A better technique, in my opinion, is to have a single Dictionary that contains all these descriptions in a separate library (see below). ”

But I am not getting how and where have you included the description objects in the data dictionary. e.g. I have created below description objects :
‘Creating a Page Description.
Set oPage = Description.Create
oPage(“title”).Value = “Google”

”Creating a Link Description.
Set oLink = Description.Create
oLink( “html tag”).Value = “A”
oLink( “innertext” ).Value = “Images”

”Creating a Google Image Description.
Set oGoogImage = Description.Create
oGoogImage( “file name”).Value = “images_logo_lg.gif”
oGoogImage( “html tag”).Value = “IMG”

Now how can i create a data dictionary and use them in my script instead of creating N no. of description objects and get confused. Could you please explain me the same with a simple example.
Also where can I read about Classes in VB …
Please help.

Thanks in advance
Ritika

Reply

58 ritika January 17, 2012 at 4:15 am

Hi Anshoo,

Thanks for sharing so much and such valuable information on QTP. I am beginner and I can script using OR but have just started using DP. But I am confused a lot..And I didnt understand anything from from your article “Creating a Highly Efficient Test-Data Dictionary”. As you said in one of ur article that

“I don’t use Description Object much is because in large projects, when you have thousands of lines of code, it can sometime become tedious to refer to Description Objects by continuously scrolling up and down to find what each description says. A better technique, in my opinion, is to have a single Dictionary that contains all these descriptions in a separate library (see below). ”

But I am not getting how and where have you included the description objects in the data dictionary. e.g. I have created below description objects :
‘Creating a Page Description.
Set oPage = Description.Create
oPage(“title”).Value = “Google”

”Creating a Link Description.
Set oLink = Description.Create
oLink( “html tag”).Value = “A”
oLink( “innertext” ).Value = “Images”

”Creating a Google Image Description.
Set oGoogImage = Description.Create
oGoogImage( “file name”).Value = “images_logo_lg.gif”
oGoogImage( “html tag”).Value = “IMG”

Now how can i create a data dictionary and use them in my script instead of creating N no. of description objects and get confused. Could you please explain me the same with a simple example.
Also where can I read about Classes in VB …
Please help.

Thanks in advance
Ritika

Reply

59 Anshoo Arora November 2, 2009 at 7:31 pm

Hi MRT,

Can you please share your code with me? You can e-mail me at anshoo.arora[at]relevantcodes[dot]com. If its not confidential, you can post it here as well since it will help other readers who might be battling with a similar issue.

PS. Thank you. I am really glad that you found this website useful. :)

Reply

60 MRT November 3, 2009 at 7:21 pm

Hi Anshoo
This is my initial foray into classes so the code is embarrassingly simple. All I have done is create a class to hold data. Create a function to create a new instance of this class. Create a sub that calls the class and tries to retrieve data stored in the class. Please see below for the code.

Code stored in Function library

Class HoldData
    Public FolderLocation
End Class

Function oTest
   Set oTest = New HoldData
End Function

Sub oLoc
   Set GetTestData = oTest
   Print GetTestData.FolderLocation
End Sub

In the main code, I have created a new instance of the test, assigned a value to the class, retrieved the value of the class to make sure that it is indeed saved. The value is returned successfully. Call the sub to retrieve the value again. This is where it fails.

Code in Main script

Set GetTestData = oTest
GetTestData.FolderLocation = "C:\Documents and Settings\User1\Desktop"
Print GetTestData.FolderLocation
Call oLoc

Reply

61 Anshoo Arora November 3, 2009 at 7:31 pm

Hi MRT,

If you run this, you will notice that it runs perfectly:

Class HoldData
    Public FolderLocation
End Class

Function oTest
   Set oTest = New HoldData
End Function

Sub oLoc
   'Set GetTestData = oTest
   MsgBox "2 " & GetTestData.FolderLocation
End Sub

Set GetTestData = oTest
GetTestData.FolderLocation = "C:\Documents and Settings\User1\Desktop"
MsgBox "1 " &  GetTestData.FolderLocation
Call oLoc

The only issue in your code is the following line in “oLoc” procedure:

Set GetTestData = oTest

When the procedure is called, before it can print something, a new instance of the class is created, therefore, the old instance is destroyed along with the value that the old instance held. If you comment the above statement out, you will notice that the same instance remains and returns the correct value. I am not quite sure, but I see a lot of people who use classes tend to load libraries through ExecuteFile instead of associating them as test resources.. I think that may work in this situation too, but I’m not entirely sure.

I hope this helps. :)

Reply

62 Sam March 22, 2010 at 8:52 am

Many Thanks Anshoo,

~SAM

Reply

63 Anshoo Arora March 23, 2010 at 9:56 am

:)

Reply

64 MichaelTP March 25, 2010 at 4:38 am

Thank you very much Anshoo! I should have guessed this, because you’re using the colon also in For x = 2 to iRow - 1 : oRS.MoveNext : Next and in .Save : .Close, too. I was’t sure what VBscript syntax items are supported by QTP 9.5 that we’re using here. Is there any hint given by HP on what version of Visual Basic is implemented in their VBscript interpreter?

Reply

65 Anshoo Arora March 27, 2010 at 10:46 pm

Good question, Michael, and I’m not quite sure :(

I think this might give you a rough idea though.

Reply

66 MichaelTP April 11, 2010 at 1:52 pm

Anshoo, thanks for the many hints very much. There was only one mistake apart from the If 0err.number Then which is due to the html suppression of < (unequal sign) in the edit box of this blog and should habe been If 0err.number Then. And that error is crucial to the success of your and of course any code derived from that,i.e. the construction of a new object like the one from class clsParameter must contain the Set within the

Public  Sub Class_Initialize
		oDict = CreateObject("Scripting.Dictionary")
		intRow=oDict.Count
End Sub

such that it becomes

Public  Sub Class_Initialize
		Set oDict = CreateObject("Scripting.Dictionary")
		intRow=oDict.Count
End Sub

Unfortunately I was too blind to see that error for a long time. But now my code works!
Again, thank you very much for your bright examples!!!
Best regards
Michael

Reply

67 Anshoo Arora April 12, 2010 at 12:21 am

Glad its working now :)

Reply

68 MichaelTP April 12, 2010 at 9:40 am

Hi Anshoo
just want to let you know about the fact, that my test runs faster by factor 1’500 now!!!
I only had to replace the DataTable methods against the DataDict methods. Further optimization of my ancient script code is possible now, but will not contribute as much to the performance gain like the exchange of DataTable by DataDict objects.
Again, thank’s so much for your contributions in this thread!
Best regards
Michael

Reply

69 Anshoo Arora April 13, 2010 at 1:54 pm

I’m glad this post helped you, Michael. You’re most welcome :)

Reply

70 Anonymous March 2, 2011 at 2:15 pm

Thanks for your prompt reply, dear Anshoo! In fact, the business process test is another type of test than the ordinary qtp test. the Business Process test has neither scripts nor Actions. It calls one BPT component after the other and each BPT component is associated to an application area which in turn is associated to function libs and other resources such as Add-Ins, tables, etc.

During runtime, the Business Process Test will load these libraries and their lifetime ends with the end of each BPT component. The only three ways that we can use to exchange parameters between the BPT components is either the integrated input and output parameter mechanism, which is kinda nasty in mass production of tests, or using the DataTable object’s “Global Table” sheet (no dtGlobalSheet existing), since we figured out that there is a temporary QTP test created by the Business Process Test to embrace the execution of the instantiated BPT components, or last but not least using the Windows System’s registry to accomodate the data dictionary that you’ve explained in this blog. The latter has the advantage of being compatible with the same methods you’ve described above, thus you can read and write very fast directly from within your function library by using the COM of Windows as long as QTP lives. In conjunction with your above mentioned ADO stream load mechanism you’ll have a very powerful multilateral data exchange mechanism for unlimited number of parameters, rows and sheets. All you have to do is some little modification of your registry described at learnqtp
Thanks and best regards
MichaelTP

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post: