Monday, August 26, 2013

Invoke Putty to Execute Commands - QTP

Call Fn_InitPutty ("C:\path\to\putty", "serverhost.com", "username", "password")


Function Fn_InitPutty(strPath, strPuttyUrl, strPuttyUser, strPuttyPass)
 Dim blnSuccess
 blnSuccess = false

    SystemUtil.Run ( strPath & "\putty.exe"), , , SHOW_MAXIMIZED
 print "strPath:"& strPath

 Window("regexpwndtitle:=PuTTY Configuration").WinEdit("attached text:=Host &Name \(or IP address\)","regexpwndclass:=Edit").SetSelection 0,28
 Window("regexpwndtitle:=PuTTY Configuration").WinEdit("attached text:=Host &Name \(or IP address\)","regexpwndclass:=Edit").Set strPuttyUrl
    Window("regexpwndtitle:=PuTTY Configuration").WinTreeView("regexpwndclass:=SysTreeView32").Select "Session;Logging"
    Window("regexpwndtitle:=PuTTY Configuration").WinEdit("attached text:=Log &file name:","regexpwndclass:=Edit").Set strPath + "\putty.log"
 Window("regexpwndtitle:=PuTTY Configuration").WinRadioButton("regexpwndtitle:=&Printable output").Set
    Window("regexpwndtitle:=PuTTY Configuration").WinRadioButton("regexpwndtitle:=Ask the user every time").Set
    Window("regexpwndtitle:=PuTTY Configuration").WinTreeView("regexpwndclass:=SysTreeView32").Expand "Connection;SSH"
 Window("regexpwndtitle:=PuTTY Configuration").WinTreeView("regexpwndclass:=SysTreeView32").Select "Connection;SSH;Auth"
    Window("regexpwndtitle:=PuTTY Configuration").WinCheckBox("text:=.* auth \(SSH-2\)").Set "OFF"
 Window("regexpwndtitle:=PuTTY Configuration").WinButton("text:=&Open").Click
 'If Putty security Alert pops up for the Certificate, click 'Yes'
 If Window("regexpwndtitle:= PuTTY Security Alert").Exist(5) Then
  Window("regexpwndtitle:= PuTTY Security Alert").WinButton("text:=.*Yes").Click
 End If
 If Dialog("regexpwndtitle:=PuTTY Log to File").Exist(5) Then
  Dialog("regexpwndtitle:=PuTTY Log to File").WinButton("regexpwndtitle:=&Yes").Click
 End If

    wait 2
 Window("regexpwndtitle:=PuTTY","regexpwndclass:=PuTTY").Type strPuttyUser
 Window("regexpwndtitle:=PuTTY","regexpwndclass:=PuTTY").Type micReturn
 wait 2
 Window("regexpwndtitle:=PuTTY","regexpwndclass:=PuTTY").Type strPuttyPass
 Window("regexpwndtitle:=PuTTY","regexpwndclass:=PuTTY").Type micReturn
    wait 5
End Function
Function Fn_SendCmdToPutty(strStringToType, strPath)
   wait 1
 Const TristateUseDefault = -2, ForReading = 1
 Dim objFSO, objPuttyLogFile, objPuttyLogFileTextStream, strPuttyLogFile, strTextLine
 If  Instr(1,strPath,"putty.log",1) > 0 Then
  strPuttyLogFile = strPath
 Else
  strPuttyLogFile = strPath + "\putty.log"
 End If
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objPuttyLogFile = objFSO.GetFile(strPuttyLogFile)
 Do
 Set objPuttyLogFileTextStream = objPuttyLogFile.OpenAsTextStream(ForReading, TristateUseDefault)
 Do while objPuttyLogFileTextStream.atendofstream<>true
  strTextLine= objPuttyLogFileTextStream.Readline
 Loop
 objPuttyLogFileTextStream.Close
 Loop until Right(strTextLine, 2) = "$ "
 Set objFSO = nothing
 Set objPuttyLogFile = nothing
 Set objPuttyLogFileTextStream = nothing
 'I f blnEnterInput = True then
 ' TeWindow("TeWindow").TeTextScreen("TeTextScreen").Type strStringToType
 ' TeWindow("TeWindow").TeTextScreen("TeTextScreen").Type micReturn
  ' End If
 Window("regexpwndclass:=PuTTY").Type strStringToType
 Window("regexpwndclass:=PuTTY").Type micReturn
End Function




 

DBConnection Excel Object - QTP

Public Function getDBConnectionExcelObject (strFilePath)
 Dim objConnection, objExcel, strExcelVersion, strVersion
 Set objExcel=CreateObject("Excel.Application")
 strExcelVersion=objExcel.Version
 Set objExcel = Nothing
 Set objConnection = CreateObject("ADODB.Connection")
 If strExcelVersion<=11.0 Then
  objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
  strVersion="8.0"
 ElseIf strExcelVersion>=12.0 Then
  objConnection.Provider = "Microsoft.ACE.OLEDB.12.0"
  strVersion="12.0"
 End If
 objConnection.ConnectionString ="Data Source= " & strFilePath &";" &"Extended Properties=Excel " & strVersion & ";"
 Set getDBConnectionExcelObject = objConnection
End Function

Update Cell Value In Excel - QTP

Function Fn_UpdateCellValueInExcel(strFilePath,strUID,strSheetName,strColumnName,strColumnValue)
 Dim objConnection, strSQL, objRecordSet, intRow, strUniqueValue, strSystemTempDir

 'Setting default values of FilePath and SheetName
 If strSheetName = "" Then
  strSheetName = "Sheet1"
 End If

 Set objConnection = getDBConnectionExcelObject (strFilePath)  'Available in another post
 objConnection.Open

 strSQL = "UPDATE [" & strSheetName & "$]"&" SET "&strColumnName&"= '" &strColumnValue & "' WHERE TestCaseName = '" &strUID & "'"
 objConnection.Execute strSQL

 closeDBConnectionObject(objConnection)
 CustomReporter micInfo, "Parameter Updated info","Parameter: " &strColumnName &"of Test Case:" &strTestCaseName &"Has been updated to:" &strColumnValue
End Function

Fetch Values From Excel - QTP

'Capture the input price from datasheet

strExpectedPrice = Fn_FetchValuesFromExcel (strTestCaseDataFile,"Parameters","Price","TestCaseName",strTestCaseName)


Function Fn_FetchValuesFromExcel(strFilePath, strSheetName, strSelectFields, strWhereClauseFields, strWhereClauseVals)
   Dim objConnection, strSQL, objRecordSet, intRow, strUniqueValue
   Set objConnection = getDBConnectionExcelObject (strFilePath)
   objConnection.Open
  
   arrSelect = split(strSelectFields,"|")
   strSelectFields = arrSelect(0)
   For i=1 to ubound(arrSelect)
    strSelectFields =  strSelectFields & "," & arrSelect(i)
   Next
   arrWhereFields = split(strWhereClauseFields,"|")
   arrWhereVals = split(strWhereClauseVals,"|")
  
   Set objRecordSet = CreateObject("ADODB.Recordset")
   If ubound(arrWhereFields)>1 Then
    strSQL =  "SELECT "&strSelectFields&" FROM [" & strSheetName & "$]"&" WHERE " & arrWhereFields(0) & " = '" &arrWhereVals(0) & "' And " &arrWhereFields(1)& " = '" &arrWhereVals(1) & "'"
   Else
   strSQL =  "SELECT "&strSelectFields&" FROM [" & strSheetName & "$]"&" WHERE " & arrWhereFields(0) & " = '" &arrWhereVals(0) &"'"
   End If
  
      objRecordSet.Open strSQL, objConnection,3
   intCount = objRecordSet.Fields.Count
   arrReturnValues = objRecordSet.Fields.Item(0)
   For i=1 to intCount-1
  arrReturnValues = arrReturnValues &  "|" & objRecordSet.Fields.Item(i) 
   Next
  
       objRecordSet.Close
       Set objRecordSet = Nothing
   
    Call closeDBConnectionObject (objConnection)
   CustomReporter micInfo, "Fetch Values Info","Values: " &arrReturnValues & " have been successfully fetched for Where Clause values: " & strWhereClause &" From Path: " &strFilePath &" For Sheet: " &strSheetName
   Fn_FetchValuesFromExcel = arrReturnValues
End Function

Public Function getDBConnectionExcelObject (strFilePath)
 Dim objConnection, objExcel, strExcelVersion, strVersion
 Set objExcel=CreateObject("Excel.Application")
 strExcelVersion=objExcel.Version
 Set objExcel = Nothing
 Set objConnection = CreateObject("ADODB.Connection")
 If strExcelVersion<=11.0 Then
  objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
  strVersion="8.0"
 ElseIf strExcelVersion>=12.0 Then
  objConnection.Provider = "Microsoft.ACE.OLEDB.12.0"
  strVersion="12.0"
 End If
 objConnection.ConnectionString ="Data Source= " & strFilePath &";" &"Extended Properties=Excel " & strVersion & ";"
 Set getDBConnectionExcelObject = objConnection
End Function

Execute Dos Command - QTP

ExecuteDosCommand "cmd /K C:\MyProjects\somefolder\scripts\somescript.bat Testcase1"


Sub ExecuteDosCommand(strCommand)
 ' create the shell object
 Set objShell = CreateObject("wscript.shell")

 ' run the command
 objShell.run strCommand

 ' destroy the object
 Set objShell = Nothing
End Sub 'ExecuteDosCommand

Custom Reporter using Dictionary - QTP

'Custom reporter function using Dictionary object and sending data to qtp reporting
Public function CustomReporterDict
 ' create a dictionary object
 Set objDict = CreateObject("Scripting.Dictionary")

 ' set the object properties
 objDict("Status") = strStatus
 objDict("PlainTextNodeName") = strStepName
 objDict("StepHtmlInfo") = now & " : " & strMessage
 objDict("DllIconIndex") = 206
 objDict("DllIconSelIndex") = 206
 objDict("DllPAth") = "C:\Program Files\HP\QuickTest Professional\bin\ContextManager.dll"

 ' report the custom entry
 Reporter.LogEvent "User", objDict, Reporter.GetContext
End Function

CustomWait - QTP

Public Function CustomWait(waitLimit, expectedObj)
 responseReceived = false
 startTime = Now()
 While Not responseReceived
  wait(1)
  If expectedObj.Exist(0) Then
   responseReceived = true
   CustomReporter micInfo,  "Object found: " & expectedObj.ToString , "Waited for " & DateDiff ("s", startTime, Now()) & " seconds"
  End If 
  If DateDiff ("s", startTime, Now()) > waitLimit Then
   CustomReporter micFail,  "Object not found: " & expectedObj.ToString , "The test failed due to missing object, waited for " & waitLimit & " seconds"
   ExitTest(0)
  Else
   CustomReporter micInfo,  "Waiting for Object: " & expectedObj.ToString , "Waited for " & DateDiff ("s", startTime, Now()) & " seconds"
  End If 
 Wend
End Function