QTP E-books

You can download the QTP E-books and related material from here.
Just click on the links to download..

• What you Study in QTP
• Descriptive Programming in QTP
• QTP Framework - II
• Winrunner Vs QTP
  

check more infomation about AUTOMATION FRAMEWORKS

Using Database Functions

The code below contains a set of useful functions that can be used in QuickTest Professional.

'Example of how to use functions.

''******************************************************************************************

' Example of how to use DSN created for the database of sample Flight application.

''******************************************************************************************

SQL="SELECT * FROM ORDERS"

connection_string="QT_Flight32"

isConnected = db_connect ( curConnection ,connection_string )

If isConnected = 0 then

       ' Execute the basic SQL statement

       set myrs=db_execute_query( curConnection , SQL )

       ' Report the query and the connection string

       Reporter.ReportEvent micInfo ,"Executed query and created recordset ","Connection_string is ==> " & connection_string & " SQL query is ===> " & SQL

       ' Show the number of rows in the table using a record set

       msgBox " Quantity of rows in queried DB ( db_get_rows_count )==> " & db_get_rows_count( myrs )

       ' Show the number of rows in the table using a new SQL statement

       msgBox " Quantity of rows in queried DB (db_get_rows_count_SQL ) ==> " & db_get_rows_count_SQL( curConnection , "SELECT COUNT(*) FROM ORDERS" )

       ' Change a value of a field in an existing row

       rc = db_set_field_value (curConnection, "ORDERS" , "Agents_Name" , "test", "Agents_Name", "AGENT_TESTER")

       ' Examples of how to retrieve values from the table

       msgBox "val row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )

       msgBox "val row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )

       msgBox "val row 1 col Name: " & db_get_field_value( myrs , 1 , "Agents_Name" )

       msgBox "val SQL row 1 col Name: " & db_get_field_value_SQL( curConnection , "ORDERS" , 1 , "Agents_Name" )

       db_disconnect curConnection

End If

''******************************************************************************************

' Database Functions library

''******************************************************************************************

'db_connect

' ---------------

' The function creates a new connection session to a database.

' curSession - The session name (string)

' connection_string - A connection string

' for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=abc123"

''******************************************************************************************

Function db_connect( byRef curSession ,connection_string)

       dim connection

       on error Resume next

       ' Opening connection

       set connection = CreateObject("ADODB.Connection")

       If Err.Number <> 0 then

              db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description

              err.clear

              Exit Function

       End If

       connection.Open connection_string

       If Err.Number <> 0 then

              db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description

              err.clear

              Exit Function

       End If

       set curSession=connection

       db_connect=0

End Function

''******************************************************************************************

' db_disconnect

' ---------------------

' The function disconnects from the database and deletes the session.

' curSession - the session name (string)

''******************************************************************************************

Function db_disconnect( byRef curSession )

       curSession.close

       set curSession = Nothing

End Function

''******************************************************************************************

' db_execute_query

' ---------------------------

' The function executes an SQL statement.

' Note that a db_connect for (arg1) must be called before this function

' curSession - The session name (string)

' SQL - An SQL statement

''******************************************************************************************

Function db_execute_query ( byRef curSession , SQL)

       set rs = curSession.Execute( SQL )

       set db_execute_query = rs

End Function

''******************************************************************************************

' db_get_rows_count

' ----------------------------

' The function returns the number of rows in the record set

' curRS - Variable, containing a record set, that contains all values that retrieved from the database by query execution

''******************************************************************************************

Function db_get_rows_count( byRef curRS )

       dim rows

       rows = 0

       curRS.MoveFirst

       Do Until curRS.EOF

              rows = rows+1

              curRS.MoveNext

       Loop

       db_get_rows_count = rows

End Function

''******************************************************************************************

' db_get_rows_count_SQL

' ------------------------------------

' The function returns the number of rows that are the result of a given SQL statement

' curSession - The session name (string)

' CountSQL - SQL statement

''******************************************************************************************

Function db_get_rows_count_SQL( byRef curSession ,CountSQL )

       dim cur_rs

       set cur_rs = curSession.Execute( CountSQL )

       db_get_rows_count_SQL = cur_rs.fields(0).value

End Function

''******************************************************************************************

' db_get_field_value_SQL

' -----------------------------------

' curSession - Variable that denotes the current active connection

' tableName - Name of the table, from which the value should be retrieved

' rowIndex - Row number

' colName - The column name

''******************************************************************************************

Function db_get_field_value_SQL( curSession , tableName , rowIndex , colName )

       dim rs

       SQL = " select " & colName & " from " & tableName

       set rs = curSession.Execute( SQL )

       rs.move rowIndex

       db_get_field_value_SQL = rs.fields(colName).value

End Function

''******************************************************************************************

' db_get_field_value

' --------------------------

' The function returns the value of a single item of an executed query.

' Note that a db_execute_query for (arg1) must called before this function

' curRecordSet - Variable, containing a record set, that contains all values retrieved from the database by query execution

' rowIndex - The row index number (zero-based)

' colIndex - The column index number (zero-based) or the column name.

' returned values

' -1 - Requested field index more than exists more than once in record set

''******************************************************************************************

Function db_get_field_value( curRecordSet , rowIndex , colIndex )

       dim curRow

       curRecordSet.MoveFirst

       count_fields = curRecordSet.fields.count-1

       If ( TypeName(colIndex)<> "String" ) and ( count_fields < colIndex ) then

              db_get_field_value = -1 'requested field index exists more than once in recordset

       Else

              curRecordSet.Move rowIndex

              db_get_field_value = curRecordSet.fields(colIndex).Value

       End If

End Function

''******************************************************************************************

' db_set_field_value

' ---------------------------

' The function changes the value of a field according to a search criteria.

' We search for a certain row according to a column name and the desired vale, then we change a value in that row according

' to a desired columns

' curConnection - The session name (string)

' tableName - Name of the table from which the value should be retrieved

' colFind - The column which to search for the criteria

' colFindValue - The value for which to search in the column

' colChange - The column in which we want to change the value

' colChangeValue - The new value

' returned values

' -1 - Requested field index that does not exist in the recordset

''******************************************************************************************

Function db_set_field_value(curConnection, tableName , colFind , colFindValue, colChange, colChangeValue)

       dim curRow

       dim updateSQL

       dim checkSQL

       checkSQL = "select * from Details"

       set myrs1 = db_execute_query( curConnection , SQL )

       myrs1.MoveFirst

       count_fields = myrs1.fields.count

       If ( TypeName(colFind)<> "String" ) or ( TypeName(colChange)<> "String" ) then

              db_set_field_value = -1 'requested field index that does not exists in the record set

       Else

              updateSQL = "UPDATE " & tableName & " SET " & colChange & "='" & colChangeValue & "' WHERE " & colFind & "='" & colFindValue & "'"

              set myrs1 = db_execute_query( curConnection , updateSQL )

              db_set_field_value = 1 'operation suceeded

       End If

End Function

''******************************************************************************************

' db_add_row

' -----------------

' The function adds a new row to the desired table

' curConnection - Variable, containing a recordset, that contains all the values to be retrieved from the database by query execution

' tableName - Name of the table, from which the value should be retrieved

' values - Array that contains values to be entered in a new row to the table

' Note: The function must receive values for all the columns in the table.

' returned value.

' -1 - The number of values to be entered to the table does not match the number of columns

' 1 - Execution of the query succeed and the data was entered to the table

''******************************************************************************************

Function db_add_row(curConnection, tableName , byRef values)

       dim i

       dim updateSQL

       dim myrs1

       updateSQL = "INSERT INTO " & tableName & " VALUES ("

       arrLen = UBound (values) - LBound (values) + 1

       set myrs1=db_execute_query( curConnection , SQL )

       myrs1.MoveFirst

       count_fields = myrs1.fields.count

       ' Check whether the number of values match the number of columns

       If arrLen <> count_fields then

              db_add_row = -1

       Else

              For i = 0 to arrLen-1

                     updateSQL = updateSQL & values (i)

                     If i <> arrLen-1 then

                            updateSQL = updateSQL & ","

                     End If

              Next

              updateSQL = updateSQL & ")"

              set myrs1 = db_execute_query( curConnection , updateSQL )

              db_add_row = 1

       End If

End Function

''******************************************************************************************

' represent_values_of_RecordSet

' ---------------------------------------------

' The function reports all the values of fields in a record set

' curRS - Variable, containing the recordset, that contains all the values that were retrieved from the database by the query execution

''******************************************************************************************

Function represent_values_of_RecordSet( myrs)

       dim curRowString

       myrs.MoveFirst

       reporter.ReportEvent 4,"Fields quantity" , myrs.fields.count

       count_fields = myrs.fields.count-1

       curRow=0

       Do Until myrs.EOF

              curRowString= ""

              curRow = curRow+1

              For ii=0 to count_fields

                     curRowString = curRowString& "Field " &"==> " & myrs.fields(ii).Name &" : Value ==>" & myrs.fields(ii).Value & vbCrLf

              Next

              myrs.MoveNext

              reporter.ReportEvent 4,"Current row"& curRow , curRowString

       Loop

End Function

 

 

Using the File System Object (FSO)

The following code includes a set of complex and simple functions to serve as examples of the possible uses and applications of Microsoft FSO.

dim oFSO

' Create the file system object

set oFSO = CreateObject ("Scripting.FileSystemObject")

'Option Explicit

' *******************************************************************************************

' Create a new txt file

' Parameters:

' FilePath - location of the file and its name

' *******************************************************************************************

Function CreateFile (FilePath)

       ' Variable that will hold the new file object

       dim NewFile

       ' Create the new text ile

       set NewFile = oFSO.CreateTextFile(FilePath, True)

       set CreateFile = NewFile

       End Function

' *******************************************************************************************

' Check if a specific file exist

' Parameters:

' FilePath - Location of the file and its name

' *******************************************************************************************

Function CheckFileExists (FilePath)

       ' Check if the file exists

       CheckFileExists = oFSO.FileExists(FilePath)

End Function

' *******************************************************************************************

' Write data to file

' Parameters:

' FileRef - Reference to the file

' str - Data to be written to the file

*******************************************************************************************

Function WriteToFile (byref FileRef,str)

       ' Write str to the text file

       FileRef.WriteLine(str)

End Function

' *******************************************************************************************

' Read line from file

' Parameters:

' FileRef - reference to the file

' *******************************************************************************************

Function ReadLineFromFile (byref FileRef)

       ' Read line from text file

       ReadLineFromFile = FileRef.ReadLine

End Function

' *******************************************************************************************

' Closes an open file.

' Parameters:

' FileRef - Reference to the file

' *******************************************************************************************

Function CloseFile (byref FileRef)

       FileRef.close

End Function

'******************************************************************************************

' Opens a specified file and returns an object that can be used to

' read from, write to, or append to the file.

' Parameters:

' FilePath - Location of the file and its name

' mode options are:

' ForReading - 1

' ForWriting - 2

' ForAppending - 8

' *******************************************************************************************

Function OpenFile (FilePath,mode)

' Open the txt file and return the File object

set OpenFile = oFSO.OpenTextFile(FilePath, mode, True)

End Function

' *******************************************************************************************

' Closes an open file.

' Parameters:

' FilePathSource - Location of the source file and its name

' FilePathDest - Location of the destination file and its name

' *******************************************************************************************

Sub FileCopy ( FilePathSource,FilePathDest)

       ' copy source file to destination file

       oFSO.CopyFile FilePathSource, FilePathDest

End Sub

' *******************************************************************************************

' Delete a file.

' Parameters:

' FilePath - Location of the file to be deleted

' *******************************************************************************************

Sub FileDelete ( FilePath)

       ' Copy source file to destination file

       oFSO.DeleteFile ( FilePath)

End Sub

' *******************************************************************************************

' Compare two text files.

'

' Parameters:

' FilePath1 - Location of the first file to be compared

' FilePath2 - Location of the second file to be compared

' FilePathDiff - Location of the differences file

' ignoreWhiteSpace - Controls whether or ignore differences in white space characters

' true - Ignore differences in white space

' false - Do not ignore difference in white space

' Return Value: true if files are identical, false otherwise'

' *******************************************************************************************

Function FileCompare (byref FilePath1, byref FilePath2, byref FilePathDiff, ignoreWhiteSpace)

       dim differentFiles

       differentFiles = false

       dim f1, f2, f_diff

       ' Open the files

       set f1 = OpenFile(FilePath1,1)

       set f2 = OpenFile(FilePath2,1)

       set f_diff = OpenFile(FilePathDiff,8)

       dim rowCountF1, rowCountF2

       rowCountF1 = 0

       rowCountF2 = 0

       dim str

       ' Count how many lines there are in the first file

       While not f1.AtEndOfStream

              str = ReadLineFromFile(f1)

              rowCountF1= rowCountF1 + 1

       Wend

       ' Count how many lines there are in the second file

       While not f2.AtEndOfStream

              str = ReadLineFromFile(f2)

              rowCountF2= rowCountF2 + 1

       Wend

       ' Re-open the files to go back to the first line in the files

       set f1 = OpenFile(FilePath1,1)

       set f2 = OpenFile(FilePath2,1)

       ' compare the number of lines in the two files.

       ' assign biggerFile - The file that contain more lines

       ' assign smallerFile - The file that contain fewer lines

       dim biggerFile, smallerFile

       set biggerFile = f1

       set smallerFile = f2

       If ( rowCountF1 < rowCountF2) Then

              set smallerFile = f1

              set biggerFile = f2

       End If

       dim lineNum,str1, str2

       lineNum = 1

       str = "Line" & vbTab & "File1" & vbTab & vbTab & "File2"

       WriteToFile f_diff,str

       ' Loop on all the lines in the smaller file

       While not smallerFile.AtEndOfStream

              ' read line from both files

              str1 = ReadLineFromFile(f1)

              str2 = ReadLineFromFile(f2)

       ' Check if we need to ignore white spaces, if yes, trim the two lines

       If Not ignoreWhiteSpace Then

              Trim(str1)

              Trim(str2)

       End If

       ' If there is a difference between the two lines, write them to the differences file

       If not (str1 = str2) Then

              differentFiles = true

              str = lineNum & vbTab & str1 & vbTab & vbTab & str2

              WriteToFile f_diff,str

       End If

       lineNum = lineNum + 1

       Wend

       ' Loop through the bigger lines, to write its line to the different file

       While not biggerFile.AtEndOfStream

              str1 = ReadLineFromFile(biggerFile)

              str = lineNum & vbTab & "" & vbTab & vbTab & str2

              WriteToFile f_diff,str

              lineNum = lineNum + 1

       Wend

       FileCompare = Not differentFiles

End function

' ************** Example of using these functions **********************

FilePath1 = "D:\temp\FSO\txt1.txt"

FilePath2 = "D:\temp\FSO\txt2.txt"

FilePathDiff = "D:\temp\FSO\txt_diff.txt"

d = FileCompare(FilePath1,FilePath2,FilePathDiff,false)

FilePath = "D:\temp\FSO\txt.txt"

set fold = FolderCreate ( "D:\temp\FSO

set f = OpenFile(FilePath,8)

' = WriteToFile(f,"test line")

d = CloseFile(f)

set f = CreateFile(FilePath)

Fexist= CheckFileExists(FilePath)

d = WriteToFile(f,"first line")

d = WriteToFile(f,"second line")

d = CloseFile(f)

FileCopy "D:\temp\FSO\txt.txt","D:\temp\FSO\txt1.txt"

FileDelete "D:\temp\FSO\txt1.txt"

Using Microsoft Outlook to Send Email

The code below illustrates two methods for sending email using the VBScript code in QuickTest Professional, and two different COM objects.

' Example 1

Function SendMail(SendTo, Subject, Body, Attachment)

       Set ol=CreateObject("Outlook.Application")

       Set Mail=ol.CreateItem(0)

       Mail.to=SendTo

       Mail.Subject=Subject

       Mail.Body=Body

       If (Attachment <> "") Then

              Mail.Attachments.Add(Attachment)

       End If

       Mail.Send

       ol.Quit

       Set Mail = Nothing

       Set ol = Nothing

End Function

' Example 2

Function SendMail(SendFrom, SendTo, Subject, Body)

       Set objMail=CreateObject("CDONTS.Newmail")

       ObjMail.From = SendFrom

       ObjMail.To = SendTo

       ObjMail.Subject = Subject

       ObjMail.Body = Body

       ObjMail.Send

       Set objMail = Nothing

End Function

 

Using Microsoft Word Spell Check

The following code shows a function for checking the number of spelling and grammar errors in a string.

Function NumberOfSpellErrors(strText)

       Dim objMsWord

       Set objMsWord = CreateObject("Word.Application")

       objMsWord.WordBasic.FileNew

       objMsWord.WordBasic.Insert strText

       NumberOfSpellErrors = objMsWord.ActiveDocument.SpellingErrors.Count

       objMsWord.Documents.Close (False)

       objMsWord.Quit ' close the application

       Set objMsWord = Nothing' Clear object memory

End Function

' The following function uses the Spell errors function to check a specific property

' of all the objects with a given description which are under a given parent

Sub CheckAllObjects(ParentObj, ObjDesc, PropName)

       Dim ObjCol, idx, PropValue, OldReportMode

       OldReportMode = Reporter.Filter

       Reporter.Filter = 2 ' Report only errors

       If (IsNull(ParentObj)) Then

              Set ObjCol = Desktop.ChildObjects(ObjDesc)

       Else

              Set ObjCol = ParentObj.ChildObjects(ObjDesc)

       End If

       For idx=0 to ObjCol.count-1

              PropValue = ObjCol.Item(idx).GetROProperty(PropName)

              RetVal = NumberOfSpellErrors(PropValue) ' The actual spell check result

              If (RetVal > 0) Then

                     ReportText = "Object #" & idx+1 & ": The '" & PropName & "' Property has " & RetVal & " spell errors (" & PropValue & ")"

                     Reporter.ReportEvent 1, "Spell Check", ReportText

              End If

       Next

       Reporter.Filter = OldReportMode

End Sub

'''''''''''''''''''''''''''''''''''''

' An example of usage:

' Go over all the static objects in the Login window of the Flight Application

' and for each object check the text for spelling and grammatical errors

'''''''''''''''''''''''''''''''''''''

' Go over all the links in the page and report all the ones that fail the spellcheck

Set Desc = Description.Create()

Desc("nativeclass").Value = "Static"

Set Obj = Dialog("nativeclass:=#32770", "text:=Login")

' Invoke the Flight Application before calling the function

CheckAllObjects Obj, Desc, "text"