Broken Links and any Error links check from the web page with VB Script / UFT / QTP

Some times our customers may requests us to check for links which are broken from the web pages. This might be from the production sites or from the any of the web home page in pre-production sites.

Following code snippet can be used as quick solution to fulfill our requirement.

''#########################################################################
'' Use this code to verify all links are working fine in a already opened web page
''#########################################################################
On Error Resume Next
Set objBrowser = Description.Create
objBrowser("name").Value = "XXXXXXX"
objBrowser("CreationTime").Value = 0
Set objPage = Description.Create
objPage("title").Value = "XXXXX"

Set objLink = Description.Create
objLink("micclass").value = "Link"
objLink("html tag").value =  "A"

Set objBP = Browser(objBrowser).Page(objPage)

Set colLinkObjects = objBP.ChildObjects(objLink)

'Display count of all links

'Msgbox colLinkObjects.Count

inputFilesPath = "D:\Sample\WebLinksCheck.xlsx"
strSheetName = "Sheet1"

For i=2 to colLinkObjects.Count

    strLinkURL =  colLinkObjects(i).GetROProperty("url")
    strLinkName = colLinkObjects(i).GetROProperty("Name")
    ExportData2ExcelFile inputFilesPath,strSheetName,i,1,strLinkName
    ExportData2ExcelFile inputFilesPath,strSheetName,i,2,strLinkURL

    strVerifyMsg = VerifyLinksBroken(strLinkURL)
    ExportData2ExcelFile inputFilesPath,strSheetName,i,3,strVerifyMsg

Next

On Error GoTo 0

'=======================================================================
'Function: ExportData2ExcelFile()
' Description: Exporting runtime value to excel file
'Input: 
' =======================================================================

Public Function ExportData2ExcelFile(inputFilesPath,strSheetName,ColName,intRowNum,strValue)

  On Error Resume Next
'    inputFilesPath = Environment.Value("DataPath")

Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Visible = False
Set objWorkbook = ExcelObj.Workbooks.Open(inputFilesPath)
Set worksheet = objWorkbook.WorkSheets(strSheetName)

worksheet.cells(ColName,intRowNum).value  = strValue

objWorkbook.Save
objWorkbook.Close
ExcelObj.Application.Quit
Set objWorkbook = Nothing
Set ExcelObj = Nothing

On Error GoTo 0
End Function



Public Function VerifyLinksBroken(strURL)
On Error Resume Next

  'Create a WinHTTP Request using the link's URL
  Set objWinHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  objWinHTTP.Open "GET", strURL, False
  objWinHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MyApp 1.0; Windows NT 5.1)"

  'Send the Request to the Server and capture the response
  objWinHTTP.Send
  iReturnVal = objWinHTTP.Status
  strErrorMsg = objWinHTTP.StatusText

  'Find out if the Link exists or is broken
  If iReturnVal = 200 Then
    strMsg = "Link - " & strURL & " Exists"
  ElseIf iReturnVal = 404 Then
    strMsg =  "Link - " & strURL & " is Broken with 404 error"
  Else
    strMsg =  "Error Code - " & iReturnVal & ":"&strErrorMsg&": Problem with this page"
  End If

  Set objWinHTTP = Nothing

On Error GoTo 0
VerifyLinksBroken = strMsg
End Function

VBScript or UFT code to connect database and extract/update data

Following code can be used to connect to database to perform select/update queries.

Prerequisites:

1. Install DB client in the machine from where we are executing this piece of code
2. Establish DSN(Data Source Name)

Logic how VBScript code connects to DB:

DB client and WSH engine creates the interfaces/platforms separately but to make the bridge between both, we should have DSN which works as communication channel to perform the transactions.

strServerName= “abcd.com”

strDBName= “database”

strDBUserName="DBUserName"

strDBPWD= “DBPWD"

strSQL=  "Select * from employee "

If rc = micPass Then rc = DB_Execute_Query(strSQL,strServerName,strDBName,strDBUserName,strDBPWD)

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

'Function Name    : DB_Connection

'Description      : Function to establish the connection to database

'Input Parameters : strDataBaseUser,strDBPassword,strDataBaseName

'Output Parameters: None

'Createb By: Govardhan

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

Public Function DB_Connection(strServerName,strDBName,strDBUserName,strDBPWD)

On Error Resume Next

Set Conn = CreateObject ("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

''' 'Create the string which is the command for connection

'''' strCon="Driver={PostgreSQL ANSI};Server=abc.com;Port=5432;Database=database;UID=server;PWD=server;"

strCon="Driver={PostgreSQL ANSI};Server="&strServerName&";Port=5432;Database="&strDBName&";UID="&strDBUserName&";PWD="&strDBPWD

Conn.ConnectionString = strCon

Conn.Open()

If Err.Number <> 0 then

rc = micFail

Reporter.ReportEvent rc,"Connect to database","Unable to connect to database due to following error," &_

vbcrlf & "Error Code: " & Err.Number &_

vbcrlf & "Error Description: " & Err.Description

Err.Clear

Else

rc = micPass

Reporter.ReportEvent rc,"Connect to database - "&strDBName &" - was successful","Connection successful"

Environment("DB_Connection_"&strDBName)= Conn

Environment("DB_Recordset_"&strDBName)= rs

End If

On Error GoTo 0

DB_Connection = rc

End Function

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

'Function Name    : DB_Execute_Query

'Description      : Function to execute a query and check if successfully executed or not

'Input Parameters : strSql,strDataBaseUser,strDBPassword,strDataBaseName

'Output Parameters: 0- Error,1 - Successful

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

Public Function DB_Execute_Query(strSql, strServerName,strDBName,strDBUserName,strDBPWD)

On Error Resume Next

DB_Connection strServerName,strDBName,strDBUserName,strDBPWD

Set Conn = Environment("DB_Connection_"&strDBName)

Set rs = Environment("DB_Recordset_"&strDBName)  

   

Set rs = Conn.Execute (strSql)

If Err.Number <> 0 then

Reporter.ReportEvent micFail,"Query the database","Following error while querying the database," &_

vbcrlf & "Sql Statement: " & strSql &_

vbcrlf & "Error Code: " & Err.Number &_

vbcrlf & "Error Description: " & Err.Description

Err.Clear

rc  = micFail

DB_Execute_Query = rc

Else

rc = micPass

Reporter.ReportEvent micPass,  "Execute Sql Query","User should able to execute the sql query and the query is" & strSql ," Query Executed succefully"

DB_Execute_Query = rc

End If

'Close Connection and Recordset objects

DB_Close Conn,rs

On Error GoTo 0

DB_Execute_Query = rc

End Function

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

'Function Name    : DB_Close

'Description      : Function to disconnect the DB connection

'Input Parameters : strDataBaseName,RecordSet 

'Output Parameters: None

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

Public Function DB_Close(strConnection,StrRecordSet)

On Error Resume Next

Set StrRecordSet= nothing

strConnection.close

    Set strConnection = nothing

On Error GoTo 0

End Function

UFT/QTP Concurrent License utilization Log file generation

Unlike ALM users administration from SiteAdmin, UFT concurrent users administration is not so user friendly. There is not direct option to administrate users of UFT who are using licenses currently.

Following steps can help us who is using when. We can share this report to management, so that it allows offline administration on the available licenses.

Please follow in below steps for QTP Concurrent Licenses utilization Log file generation:

Step 1.  Install all the utils for Sentinal from QTP/UFT software

Step 2. Search for lsusage.exe (thats what we would use to convert our log to CSV format)
(Assume its at C:\Documents and Settings\"User"\LicenseUtils\lsusage.exe

Step 3. Open Command prompt navigate to C:\Documents and Settings\"User"\LicenseUtils

Step 4. Type in lsusage.exe -l C:\Windows\system32\lservsta -c C:\results.csv and hit return
Verify C:\results.csv has been created.

Step 5. Insert the following headers to the csv starting from Cell  A1 through to Cell Q1
FeatureName Version DayOfWeek DateOfTrans TimeOfDay LoggingType KeysInUsed UsageTime UserName HostName LmLibVer NoOfLicenses ClientLockedStr QueueKeyId AbsPos GrpPos
GrpName and save it.

Step 6. Column G KeysInUsed represents the number of concurrent licenses at any specific time frame within the log.

Step 7. Open the ReportDB.mdb file from the utils folder.

Step 8 import Results.csv
( Tables>>New>>Import Table>>OK>>Browse to C:\Results.csv
(You would need to select Text files from the  Files of type dropdown)
>>IMPORT Select Delimited>>Comma>>check First Row Contiains Fiels names>>Ina a new Table>>Next>>Let Access choose..>> name the table as "LServlog">>Finish

VBScript Data base function-Execute SQL query in UFT

Function uf_DB_ORA_ExecuteQuery(ByVal DSNName, User, Password, QueryText)
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3

    connectionString ="DSN=" & DSNName  & ";UID=" & User & ";PWD=" & Password

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.RecordSet")
   
    objConnection.Open connectionString

    If Instr(1,UCase(QueryText),"UPDATE") > 0 OR Instr(1,UCase(QueryText),"DELETE") > 0 OR Instr(1,UCase(QueryText),"INSERT") > 0Then
        objConnection.Execute(QueryText)
        objConnection.Execute("Commit;")

        If Err.Number <> 0 Then rc = micFail Else rc = micPass
        uf_DB_ORA_ExecuteQuery = rc

    ElseIf Instr(1,UCase(QueryText),"SELECT") > 0 Then
        objRecordSet.Open QueryText, objConnection
        Set uf_DB_ORA_ExecuteQuery = objRecordSet
    End If

    Set objConnection=Nothing
    Set objRecordSet = Nothing
End Function