Monday, December 14, 2015

QC SQL Query for "Requirement + Defects Matrix" for multiple projects

Select QC_RequirementID,
       JIRA_ID,
       Description,
       STATUS,
       QC_BUG_IDs,
       JIRA_BUG_IDs
  from (Select distinct req.RQ_REQ_ID as QC_RequirementID,
                        req.RQ_USER_01 as JIRA_ID,
                        req.RQ_REQ_NAME as Description,
                        req.RQ_REQ_REVIEWED as STATUS,
                        req.RQ_USER_07 as Release_Date,
                        BUG.Bg_Status as Bug_Status,
                        to_char(wm_concat(BUG.BG_BUG_ID)) as QC_BUG_IDs,
                        to_char(wm_concat(BUG.BG_USER_01)) as JIRA_BUG_IDs
          FROM Project1.Req req
          LEFT OUTER JOIN Project1.LINK LNK
            ON LNK.LN_ENTITY_ID = req.rq_req_id
           and LNK.LN_ENTITY_TYPE = 'REQ'
          LEFT OUTER JOIN Project1.BUG BUG
            ON LNK.LN_BUG_ID = BUG.BG_BUG_ID -- and BUG.Bg_Status <> 'Closed'
         group by req.RQ_REQ_ID,
                  req.RQ_USER_01,
                  req.RQ_REQ_NAME,
                  req.RQ_REQ_REVIEWED,
                  req.RQ_USER_07,
                  BUG.Bg_Status
        UNION ALL
        Select distinct req.RQ_REQ_ID as QC_RequirementID,
                        req.RQ_USER_01 as JIRA_ID,
                        req.RQ_REQ_NAME as Description,
                        req.RQ_REQ_REVIEWED as STATUS,
                        req.RQ_USER_07 as Release_Date,
                        BUG.Bg_Status as Bug_Status,
                        to_char(wm_concat(BUG.BG_BUG_ID)) as QC_BUG_IDs,
                        to_char(wm_concat(BUG.BG_USER_01)) as JIRA_BUG_IDs
          FROM Project2.Req req
          LEFT OUTER JOIN Project2.LINK LNK
            ON LNK.LN_ENTITY_ID = req.rq_req_id
           and LNK.LN_ENTITY_TYPE = 'REQ'
          LEFT OUTER JOIN Project2.BUG BUG
            ON LNK.LN_BUG_ID = BUG.BG_BUG_ID -- and BUG.Bg_Status <> 'Closed'
         group by req.RQ_REQ_ID,
                  req.RQ_USER_01,
                  req.RQ_REQ_NAME,
                  req.RQ_REQ_REVIEWED,
                  req.RQ_USER_07,
                  BUG.Bg_Status
        UNION ALL
        Select distinct req.RQ_REQ_ID as QC_RequirementID,
                        req.RQ_USER_01 as JIRA_ID,
                        req.RQ_REQ_NAME as Description,
                        req.RQ_REQ_REVIEWED as STATUS,
                        req.RQ_USER_07 as Release_Date,
                        BUG.Bg_Status as Bug_Status,
                        to_char(wm_concat(BUG.BG_BUG_ID)) as QC_BUG_IDs,
                        to_char(wm_concat(BUG.BG_USER_01)) as JIRA_BUG_IDs
          FROM Project3.Req req
          LEFT OUTER JOIN Project3.LINK LNK
            ON LNK.LN_ENTITY_ID = req.rq_req_id
           and LNK.LN_ENTITY_TYPE = 'REQ'
          LEFT OUTER JOIN Project3.BUG BUG
            ON LNK.LN_BUG_ID = BUG.BG_BUG_ID -- and BUG.Bg_Status <> 'Closed'
         group by req.RQ_REQ_ID,
                  req.RQ_USER_01,
                  req.RQ_REQ_NAME,
                  req.RQ_REQ_REVIEWED,
                  req.RQ_USER_07,
                  BUG.Bg_Status
        UNION ALL
        Select distinct req.RQ_REQ_ID as QC_RequirementID,
                        req.RQ_USER_01 as JIRA_ID,
                        req.RQ_REQ_NAME as Description,
                        req.RQ_REQ_REVIEWED as STATUS,
                        req.RQ_USER_07 as Release_Date,
                        BUG.Bg_Status as Bug_Status,
                        to_char(wm_concat(BUG.BG_BUG_ID)) as QC_BUG_IDs,
                        to_char(wm_concat(BUG.BG_USER_01)) as JIRA_BUG_IDs
          FROM Project4.Req req
          LEFT OUTER JOIN Project4.LINK LNK
            ON LNK.LN_ENTITY_ID = req.rq_req_id
           and LNK.LN_ENTITY_TYPE = 'REQ'
          LEFT OUTER JOIN Project4.BUG BUG
            ON LNK.LN_BUG_ID = BUG.BG_BUG_ID -- and BUG.Bg_Status <> 'Closed'
         group by req.RQ_REQ_ID,
                  req.RQ_USER_01,
                  req.RQ_REQ_NAME,
                  req.RQ_REQ_REVIEWED,
                  req.RQ_USER_07,
                  BUG.Bg_Status
        UNION ALL
        Select distinct req.RQ_REQ_ID as QC_RequirementID,
                        req.RQ_USER_01 as JIRA_ID,
                        req.RQ_REQ_NAME as Description,
                        req.RQ_REQ_REVIEWED as STATUS,
                        req.RQ_USER_07 as Release_Date,
                        BUG.Bg_Status as Bug_Status,
                        to_char(wm_concat(BUG.BG_BUG_ID)) as QC_BUG_IDs,
                        to_char(wm_concat(BUG.BG_USER_01)) as JIRA_BUG_IDs
          FROM Project5.Req req
         LEFT OUTER JOIN Project5.LINK LNK
            ON LNK.LN_ENTITY_ID = req.rq_req_id
           and LNK.LN_ENTITY_TYPE = 'REQ'
          LEFT OUTER JOIN Project5.BUG BUG
            ON LNK.LN_BUG_ID = BUG.BG_BUG_ID -- and BUG.Bg_Status <> 'Closed'
         group by req.RQ_REQ_ID,
                  req.RQ_USER_01,
                  req.RQ_REQ_NAME,
                  req.RQ_REQ_REVIEWED,
                  req.RQ_USER_07,
                  BUG.Bg_Status)
where Release_Date = '2015-12-11';


--select distinct dt.OWNER from dba_tables dt where dt.OWNER like '%XXXXXXX%';


Tuesday, August 26, 2014

Read Complete Excel into Multi-Dimensional Dictionary object by ADO


Public Function Func_ReadXL_IN_Dictionary(xlFilePath,xlSheetName)
                   
                    On error resume next                                        
                    Dim ADO,rs,rowCounter,TCName   
                    Set ADO=CreateObject("ADODB.Connection")
                    Set objDict=CreateObject("Scripting.Dictionary")               
                    ADO.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="& xlFilePath &";Readonly=True"
                    Set rs=createobject("ADODB.recordset")
                    rs.Open "Select * from [" & xlSheetName & "$]",ADO
                    Dim LoopCounter:LoopCounter=0       
                   
                    DO
'                        rowCounter=0
                        LoopCounter=LoopCounter+1
                        rowCounter=0
                        For each field in rs.Fields   
                            rowCounter=rowCounter+1                               
                            If LoopCounter=1 Then
                                if rowCounter=1 Then                                       
                                        TCName=    field.name
                                        objDict.Add TCName,CreateObject("Scripting.Dictionary")                                   
                                         objDict(TCName).add field.name,field.name
                               
                                 Else
                                        If isempty(field.name)=False Then                               
                                            objDict(TCName).add field.name,field.name
                                        End If       
                                 End if
                            else
                                if rowCounter=1 Then                                       
                                        TCName=    field.value
                                        objDict.Add TCName,CreateObject("Scripting.Dictionary")                                   
                                         objDict(TCName).add field.name,field.value
                                Else   
                                    If isempty(field.value)=False Then                               
                                        objDict(TCName).add field.name,field.value
                                    End If       
                                End if    
                            End If
                           
                        Next
                    If LoopCounter <> 1 Then                              
                        rs.MoveNext
                    End If
           
                    Loop until rs.EOF

                    Set rs=Nothing
                    Set ADO=Nothing
                   
                    set Func_ReadXL_IN_Dictionary=objDict                   
                    Set objDict=Nothing
                    On error goto 0
End Function

Thursday, May 8, 2014

How to read complete Excel sheet into Multi-Dimensional dictionary objects


Following is the sheet that need to be fetched into the Multi-Dimensional dictionary object

'Excel File 








'Function
'========================================================================
' Description:  This function will read the entire excel sheet and capture the sheet into multidimensioanl dictinoary object
'Dependencies: First row of the sheet should be the unique identifier
' Input: xlFilePath: Excel File which is to be read by the script
' xlSheetName: SheetName which is to be read by the script
'Output: The objDict will be returned
'========================================================================

Public Function Func_ReadXL_IN_Dictionary(xlFilePath,xlSheetName)


' open and read excel file
Dim objXL,objWrkBk,objWrkSht
Dim objDict

Set objXL=CreateObject("Excel.Application")
Set objWrkBk=objXL.Workbooks.Open(xlFilePath)
Set objWrkSht=objWrkBk.Worksheets(xlSheetName)

Set objDict=CreateObject("Scripting.Dictionary")

' copy all excel data into the dictionary object
 Dim rCount,cCount ' get the row and column count of the worksheet
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count

'Copy all Data cell by cell
Dim row,col
For row =2 to rCount
objDict.Add objWrkSht.cells(row,1).value,CreateObject("Scripting.Dictionary")
For col=1 to cCount
          objDict (objWrkSht.cells(row,1).value).add objWrkSht.cells(1,col).value,                    objWrkSht.cells(row,col).value
Next
Next          

set Func_ReadXL_IN_Dictionary=objDict
'Release objects
objWrkBk.Close
objXL.Quit
Set objWrkSht= Nothing
Set objWrkBk=Nothing
Set objXL=Nothing
Set objDict=Nothing

End Function



' USE THIS FUNCTION AS FOLLOWS


Dim xlFilePath:xlFilePath="D:\...\MasterFile.xlsx"
Dim xlSheetName:xlSheetName="MasterFile"
Dim objDict: objDict=objMasterFile

Dim objMasterF
set objMasterF = Func_ReadXL_IN_Dictionary (xlFilePath,xlSheetName)
a=objMasterF.keys
b=objMasterF.items

For i=0 to objMasterF.count-1
print a(i)        
c=objMasterF(a(i)).items
For j=0 to objMasterF(a(i)).count-1
print c(j)
Next
Next


'OUTPUT




Saturday, July 20, 2013

Collection

According to MSDN a Collection object is an ordered set of items that can be referred to as a unit. The Collection object provides a convenient way to refer to a related group of items as a single object. The items, or members, in a collection need only be related by the fact that they exist in the collection. Members of a collection don't have to share the same data type.

Once a collection is created, members can be added using the Add method and removed using the Remove method. Specific members can be returned from the collection using the Item method, while the entire collection can be iterated using the For Each...Next statement.

It means that a collection is a COM object that has 3 properties and 2 methods: Add, Remove, Count, Item and _NewEnum, though in technical literature you may find that only Count and _NewEnum are necessary for the object to be called the collection. 

in QTP: 
Collections methods (add, remove) don't work
_NewEnum i have not seen any practicale application yet. but when we do for each it used internally. 
 item: 
Following two statements are equal as item is the default property:

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

Set cLiks=Browser("Gmail: Email from Google").Page("Gmail: Email from Google").ChildObjects(desc)

For i=0 to  cLiks.count

print cLiks(i).getroproperty("text")  '- Same as below

print cLiks.item(i).getroproperty("text") '- same as above
Next


 

Friday, November 16, 2012

Selenium + Excel

import java.util.List;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.Select;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import jxl.*;
import jxl.read.biff.BiffException;

import jxl.write.*;
import jxl.write.biff.RowsExceededException;


public class Medical {

      
      
        /**
         * @param args
         * @throws InterruptedException
         * @throws IOException
         * @throws BiffException
         * @throws WriteException
         * @throws RowsExceededException
         */
        public static void main(String[] args) throws InterruptedException, BiffException, IOException, RowsExceededException, WriteException {
              
//read data from config file
                Workbook workbook = Workbook.getWorkbook(new File("D:\\AJ\\XYZ.xls"));
                Sheet sheet = workbook.getSheet("Configuration");
              
                String URL = sheet.getCell(0,1).getContents();
                String UserName = sheet.getCell(1,1).getContents();
                String Pwd = sheet.getCell(2,1).getContents();
              
                Sheet shtUser = workbook.getSheet("User");
                String strUserLastName = shtUser.getCell(0,1).getContents();
              
                Sheet shtSummaryTable = workbook.getSheet("SummaryTable");
                //int cntCol=shtSummaryTable.getColumns();
                //int rowCnt=shtSummaryTable.getRows();
              
      
               
//Write result

                WritableWorkbook result = Workbook.createWorkbook(new File("D:\\AJ\\Result.xls"));
                WritableSheet shtResult = result.createSheet("TestCase",0);
              
                Label Header1 = new Label(0, 0, "TestCaseNo");
                Label Header2 = new Label(1,0, "TestCaseDescription");
                Label Header3 = new Label(2, 0, "Result");
                shtResult.addCell(Header1);
                shtResult.addCell(Header2);
                shtResult.addCell(Header3);          
                          
              
              

              
              
//Invoke firefox and login into the application
WebDriver f1=new FirefoxDriver();
f1.get(URL);
//f1.get("http://www.caisis.org/demo/Login.aspx");
f1.findElement(By.xpath(".//*[@id='userName']")).sendKeys(UserName);
f1.findElement(By.xpath(".//*[@id='password']")).sendKeys(Pwd);
f1.findElement(By.xpath(".//*[@id='enterButton']")).click();

f1.findElement(By.xpath(".//*[@id='PageHeader1_searchWords']")).sendKeys(strUserLastName);
f1.findElement(By.xpath(".//*[@id='searchBtn']")).click();
f1.findElement(By.xpath(".//*[@id='PageHeader1_rptHeaderTabs__ctl1_TabCenterTD']")).click();

Label row11 = new Label(0, 1, "Caisis_TC_01");
Label row12 = new Label(1, 1, "Login");
Label row13 = new Label(2, 1, "Pass");
shtResult.addCell(row11);
shtResult.addCell(row12);
shtResult.addCell(row13);


String whandle = f1.getWindowHandle();
 //Go to Operation room details and submit details for the room
f1.findElement(By.xpath(".//*[@id='menu']/li[3]/a")).click();
f1.findElement(By.xpath(".//*[@id='menu']/li[3]/ul/li[1]/a")).click();

WebElement theFrame = f1.findElement(By.name("DataEntryFrame"));
f1.switchTo().frame(theFrame);
WebElement chkBox= f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpPending']"));
chkBox.click();

f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpDateText']")).sendKeys("11/30/2012");

Select droplist = new Select(f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpService']"))); 
droplist.selectByVisibleText("GMT");


f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpCaseSurgeon']")).sendKeys("Ceaser Johnson");
f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpAdmitDateText']")).sendKeys("11/30/2012");
f1.findElement(By.xpath(".//*[@id='_ctl3__ctl0_OperatingRoomDetails_OpDischargeDateText']")).sendKeys("12/01/2012");
f1.findElement(By.xpath(".//*[@id='SaveBtn']")).click();
f1.switchTo().window(whandle);

Label row21 = new Label(0, 2, "Caisis_TC_02");
Label row22 = new Label(1, 2, "Enter Operating Room Details ");
Label row23 = new Label(2, 2, "Pass");
shtResult.addCell(row21);
shtResult.addCell(row22);
shtResult.addCell(row23);

//Go to Patients summary page and verify the summary is populated

f1.findElement(By.xpath(".//*[@id='menu']/li[1]/a")).click();
f1.findElement(By.xpath(".//*[@id='menu']/li[1]/ul/li[1]/a")).click();


                //Switch to Summary
WebElement SummaryFrame = f1.findElement(By.name("ChronListFrame"));
f1.switchTo().frame(SummaryFrame);


                // read data from the table
WebElement table_element = f1.findElement(By.xpath(".//*[@id='chronListTable']"));
List tr_collection=table_element.findElements(By.xpath("id('chronListTable')/tbody/tr"));

                 //for(int rowCounter = 2;rowCounter<=rowCnt ; rowCounter++)
                 //{
                        // String strDate=shtSummaryTable.getCell(0,rowCounter-1).getContents();
                         //String strVariable=shtSummaryTable.getCell(1,rowCounter-1).getContents();
                         //String strValue=shtSummaryTable.getCell(2,rowCounter-1).getContents();
                         //String strQuality=shtSummaryTable.getCell(3,rowCounter-1).getContents();                     
                 //}



//System.out.println("NUMBER OF ROWS IN THIS TABLE = "+tr_collection.size());
int row_num,col_num;
row_num=1;
col_num=0;
for(WebElement trElement : tr_collection)
{
    List td_collection=trElement.findElements(By.xpath("td"));
  //  System.out.println("NUMBER OF COLUMNS="+td_collection.size());
    col_num=0;
    for(WebElement tdElement : td_collection)
    {
         if (shtSummaryTable.getCell(col_num,row_num).getContents().trim().equals(tdElement.getText().trim()))
                         {
                                         
                         System.out.println("Matched");
                         }
         else
         {
                 System.out.println("UnMatched");
                 System.out.println (tdElement.getText().trim());
                        System.out.println(shtSummaryTable.getCell(col_num,row_num).getContents().trim());
         }
        //System.out.println("row # "+row_num+", col # "+col_num+ "text="+tdElement.getText());
        col_num++;
    }
    if (row_num==4)
                 {
                         break;
                 }
    row_num++;
}             
              
Label row31 = new Label(0, 3, "Caisis_TC_03");
Label row32 = new Label(1, 3, "Summary Table Matching ");
Label row33 = new Label(2, 3, "Pass");
shtResult.addCell(row31);
shtResult.addCell(row32);
shtResult.addCell(row33);

                workbook.close();
                result.write();
                result.close();
              
//f1.quit();
        }

}

Wednesday, July 25, 2012

Code to export QTP OR into txt format

Set Repo= CreateObject("Mercury.ObjectRepositoryUtil")


Repo.Load "C:\Users\ajindal\Desktop\POC\Kmantra.tsr"

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.OpenTextFile("C:\Users\ajindal\Desktop\POC\KMantraOR.txt", 8, True)

Set TOCollection = Repo.GetAllObjects

For i = 0 To TOCollection.Count - 1

Set TestObject = TOCollection.Item(i)

Msg = Repo.GetLogicalName(TestObject) & vbNewLine

Set PropertiesCollection = TestObject.GetTOProperties()


For n = 0 To PropertiesCollection.Count - 1

Set Property = PropertiesCollection.Item(n)

Msg = Msg & Property.Name & "-" & Property.Value & vbNewLine

Next

f.Writeline Msg

Next

f.Close

Set fso=Nothing

Set Repo=Nothing

Monday, May 7, 2012

Copy Data from one xls to another

Very often i face problems when excel files become very big due to formatting and other invalid links and it become it quite cumbersome to transfer these files or even open these files and work on your system.

I developed a very small and easy code to copy all data from all sheets into another workbook. You can use this code by changing the file names. Let me know if you need any help to change the same.

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
'Create excel object
Set objXL=CreateObject("Excel.Application")
'open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\Ashish\Desktop\MTC\CW_Job_Centre_Master_Test_Cases_USA+Mexico_Full_Cycle_R_3.3.5.xls")


'Create new workbook where all the data will be copied
Dim objNewWorkBook,objNewWorkSheet
Set objNewWorkBook=objXL.Workbooks.Add()


' Get all the sheet names one by one and copy them into the 2nd workbook
Dim shts, varSheets
Set shts=objWrkBk.Sheets ' get the sheets collection
For each varSheets in shts
    Set objWrkSht=objWrkBk.Worksheets(varSheets.name) 'open the first sheet
Dim rCount,cCount ' get the row and column count of the worksheet
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count

Set objNewWorkSheet=objNewWorkBook.Worksheets.Add () ' create the 2nd sheet
objNewWorkSheet.name=varSheets.name ' Give the same name as of first sheet

'Copy all Data cell by cell
Dim row,col
For row=1 to rCount
For col=1 to cCount
objNewWorkSheet.cells(row,col)=objWrkSht.cells(row,col)
Next
Next

msgbox varSheets.name

Next


'Save as the 2nd workbook
Dim wrkBkName:wrkBkName=Year(Now)&Month(Now)&Day(Now)& Hour(Now)& Minute(Now) & Second(Now)
objNewWorkBook.saveas "C:\Users\Ashish\Desktop\MTC\" & wrkBkName & ".xls"

''Release objects
objNewWorkBook.save
objNewWorkBook.close
objWrkBk.Close
objXL.Quit
Set objNewWorkBook=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing

Monday, December 12, 2011

Print Logical Name & Physical properties from OR

Following program will return the class name, logical name and all physical properties of a object placed in OR:

Set RepositoryFrom = CreateObject("Mercury.ObjectRepositoryUtil")
RepositoryFrom.Load ""   ' Write your OR path here
Dim TOCollection,Msg
Set TOCollection = RepositoryFrom.GetAllObjects
For i = 0 To TOCollection.Count - 1
               Set TestObject = TOCollection.Item(i)
               ln = RepositoryFrom.GetLogicalName(TestObject)
               cn= TestObject.GetTOProperty("Class Name")
              Set PropertiesCollection = TestObject.GetTOProperties()
              Msg=""
              For n=0 To PropertiesCollection.Count - 1
              Set Property = PropertiesCollection.Item(n)
              Msg = Msg & Property.Name & "-" & Property.Value & vbNewLine
Next
print cn & vbnewline & ln &vbnewline & Msg & VBnewline & VBnewline & VBnewline
Next

Friday, December 9, 2011

Index & Location Ordinal Identifiers

What is the difference between index and location ordinal identifier.
Index is assigned based upon the HTML source code while the location goes from top to bottom and then left to right.
Ordinal identifier is always specified with respect to the other elements/objects in the application.

 

Tuesday, December 6, 2011

Silverlight- How to get headers from the datagrid

I was automating one Silverlight application and struck into the columns headers. for some Columns qtp was not able to fetch the correct header value. It was only giving back numerical values while my columns were having strings (shown in below figures). It was simply returning the column no:

Following is my application:
I was trying to get the column headers and then the whole table to store into two dimensional array. When i executed the following statement it was giving weird results.

For i =1 to 15
print Browser("JobCenter").Page("JobCenter").SlvWindow("MainPage").SlvTable("revenueAtAGlanceDashboard").SlvTable("CountriesRevenueAtAGlancDashboard").GetCellProperty(1,i,"colname") & " Col no - " & i
Next
 
For col no 7 and up it was giving col no; instead of heading. Not sure what was the issue and even developers said they can't help as they can't find a specific region for this behaviour.

I looked around on msdn and went through datagrid controls. Through some research and googling i understood that we can also fetch the columns headers by using datagrid columns collection but no such material is available on the internet.

I create first the columns collections objects:

Dim objColHeader:Set objColHeader=Browser("JobCenter").Page("JobCenter").SlvWindow("MainPage").SlvTable("revenueAtAGlanceDashboard").SlvTable("CountriesRevenueAtAGlancDashboard").Object.Columns

then by using its item and header property i was able to fine the header name:

Dim colName:colName=objColHeader.Item(cCounter).Header

results:
By executing the following statement:
Set objColHeader=Browser("JobCenter").Page("JobCenter").SlvWindow("MainPage").SlvTable("revenueAtAGlanceDashboard").SlvTable("CountriesRevenueAtAGlancDashboard").Object.Columns

For i =1 to 15
colName=objColHeader.Item(i).Header
print colName
Next
 


















Moreover this method is much faster then by simply taking data from getcelldata. The 2nd method took only 2.4 secs

Thursday, April 21, 2011

ADO & Flat File

Text files can also be accessed by using the ADO objects. Following is the code for the same:



'Connnection Objects
Dim conn
Set conn=CreateObject("ADODB.connection")
'Create the DSN for the text file, defualt directory is poiting to the location where flat file is kept
conn.open "DefaultDir=C:\Users\sandeep\Desktop\Blog R & D\ADO Flat File;Driver={Driver da Microsoft para arquivos texto (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=C:\Users\sandeep\Desktop\Blog R & D\ADO Flat File\Flt.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

'Recordset object
Dim rs
Set rs=CreateObject("Adodb.recordset")
strQry="Select * from SampleTextFile.txt"  ' Execute query
rs.Open strQry,conn

' Loop and get the records from the flat file
Dim counter
Do
counter= counter+1
For i=0 to rs.Fields.Count-1
If counter=1 Then
print rs(i).name
print rs(i).value
else
print rs(i).value
End If

Next
rs.MoveNext

Loop until rs.EOF

Set rs=Nothing
Set conn=Nothing


'Output Log












'Input Text File Log

Tuesday, April 19, 2011

Test batch runner by using ADO

Excel files can also be accessed by using ADO objects. Similarly Test Batch runner can be created by using the ADO & AOM objects. Following is the code for the test batch runner by using ADO:


'********************* Launch QTP ****************************
Dim qtp
Set qtp=CreateObject("QuickTest.Application")
qtp.Launch
qtp.Visible=True

'*************** Open Excel file by using ADO objects **************
Dim ADO
Set ADO=CreateObject("ADODB.Connection")
ADO.Open "DSN=Excel Files;DBQ=C:\Users\sandeep\Desktop\Training\TestBatchRunner\TBR.xlsx;DefaultDir=C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\DESKTOP\FILES;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

'**************Fetch data from excel file by using ADO***************
Set rs=createobject("ADODB.recordset")
rs.Open "Select * from [Sheet1$]",ADO
Dim counter:counter=0
Dim colCounter
Dim varScriptRunStatus
Dim scriptName
Do                      
counter=counter+1
colCounter=0
For each field in rs.Fields
colCounter=colCounter+1
If counter=1 Then                                                                                                                                        
If colCounter=2 Then
varScriptRunStatus=field.name
End If
else
If isnull(field.value)=False Then
If colCounter=2 Then
scriptName=field.value
End If
'*****************Open and execute scripts ****************************
If colCounter=3 and (lcase(varScriptRunStatus)=lcase(field.value) or lcase( varScriptRunStatus)="all")Then                                                                                                                                                                                                                
qtp.Open scriptName,True
qtp.Test.Run
End If                                                                                                                                                
End If
end If                                                
Next
rs.MoveNext
Loop until rs.EOF

'Release objects
Set rs=Nothing
Set ADO=Nothing
Set qtp=Nothing

Snapshot of the Data File:


Sunday, March 27, 2011

Excel files and QTP

Excel files are the integrated part of the software Testing Process specially automation testing.  QTP uses excel files as default data files. It’s very easy to maintain test data into Excel files. Number of files can vary from one to many in any testing Project. There are several ways to access the excel files into the QTP:

First-QTP Default files: By default each QTP script has a default excel file. For a script with single action (Action1) we will have two sheets. One is global which can be used to access data across multiple actions; another is per Action Sheet with the sheet name as action Name (Action 1).

Example: As shown in the below snapshot we have a new script with only one Action named as “Action1” 


  As shown below this script has only two excel sheets. One is Global and another is “Action1”


As the no of actions will increase into the script number of sheets will also increase correspondingly. All these sheets and there data can be accessed by QTP default methods.
For example: if we have the data as shown below in Global sheet then any of the mehtods written in below script can be used to access the data. There output is also shown in the print screen window

Similarly we can access the data from the local action sheets too. Method written in line no 6 can be used to access data from any of the sheets. As shown in line no 8 we have fetched the data from Global sheet. Thus it enables us to fetch data from any sheet which is available at run time in the script.


Utility Objects:  By using utility objects we can import excel files at the run time , we can add data into the sheets and then export them before exiting the script. It enables us to save the data which is added at run time into the sheet. Following are the excel utility objects these will be explained in other post.


1. DataTable Object
2. DTParameter Object
3. DTSheet Object
Excel Objects (Excel. Application): Another method to access the excel files is by using COM objects. Following are the sample codes to access xls files by using VBscript. These codes can be run as it is in QTP.

Example 1: Following is the example of opening and writing data into xls file.

xls file before writing the data:



Code:

' Open and Write data into a xls sheet

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
'Create excel object
Set objXL=CreateObject("Excel.Application")
'open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")
'Open excel sheet
Set objWrkSht=objXL.ActiveWorkbook.Worksheets("TextOr")
objWrkSht.cells(1,1)=" Hello World"
'Release objects
objWrkBk.Save
objXL.Quit
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing

'xls file after executing the above script:










Example 2: How to copy data from one xls file to another.

This example shows how to create a duplicate copy of the excel file. This code also demonstrates the following:

1. How to get the all sheet names from a collection

2. How to get row count & column count

3. How to create a new workbook and worksheet at run time & How to save them.


' How to copy data from one workbook to another

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
'Create excel object
Set objXL=CreateObject("Excel.Application")
'open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")


'Create new workbook where all the data will be copied
Dim objNewWorkBook,objNewWorkSheet
Set objNewWorkBook=objXL.Workbooks.Add()


' Get all the sheet names one by one and copy them into the 2nd workbook
Dim shts, varSheets
Set shts=objWrkBk.Sheets ' get the sheets collection
For each varSheets in shts
    Set objWrkSht=objWrkBk.Worksheets(varSheets.name) 'open the first sheet
Dim rCount,cCount ' get the row and column count of the worksheet
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count

Set objNewWorkSheet=objNewWorkBook.Worksheets.Add () ' create the 2nd sheet
objNewWorkSheet.name=varSheets.name ' Give the same name as of first sheet

'Copy all Data cell by cell
Dim row,col
For row=1 to rCount
For col=1 to cCount
objNewWorkSheet.cells(row,col)=objWrkSht.cells(row,col)
Next
Next

Next


'Save as the 2nd workbook
Dim wrkBkName:wrkBkName=Year(Now)&Month(Now)&Day(Now)& Hour(Now)& Minute(Now) & Second(Now)
objNewWorkBook.saveas "C:\Users\sandeep\Desktop\Training\Excel\" & wrkBkName & ".xlsx"

''Release objects
objNewWorkBook.save
objNewWorkBook.close
objWrkBk.Close
objXL.Quit
Set objNewWorkBook=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing


Note:

1. Cell be cell code will take quite a long time to create copies of the excel file. To do it faster copy all code in a vbs file and double click it.
2. To change the order of the sheets use the optional parameters in “worksheets.add() “


Example 3: How to find text in the workbook:

There are two methods find the same. Both are demonstrated below

Input:


Code:

'' How to find  text in the workbook

'Declare Variables
Dim objXL,objWrkBk,objWrkSht
''Create excel object
Set objXL=CreateObject("Excel.Application")
''open workbook
Set objWrkBk=objXL.Workbooks.Open("C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx")

''Open excel sheet
Set objWrkSht=objXL.ActiveWorkbook.Worksheets("XYZ")

Dim Text2Found:Text2Found="eye liner"
' First & recommended method
Dim objFind:Set objFind=objWrkSht.usedrange.find(Text2Found)
If  objFind is nothing Then
print "text not found"
else
print " Row no from First Method: " & objFind.Row ' find out the row no
print " Column no from First Method: " & objFind.column ' find out the col no
End If

' 2nd method
Dim rCount,cCount
rCount=objWrkSht.usedrange.rows.count
cCount=objWrkSht.usedrange.columns.count
Dim row,col
Dim flgTextFound:flgTextFound=False
For row=1 to rCount
For col=1 to cCount
If lcase(objWrkSht.cells(row,col))=Text2Found Then
print " Row no from 2nd Method: "  & row
print  " Column no from 2nd Method: "  & col
flgTextFound=True
Exit For
End If
Next
If flgTextFound Then
Exit For
End If
Next

'Release objects
objXL.Quit
Set objWrkSht=Nothing
Set objWrkBk=Nothing
Set objXL=Nothing

Output:
















Excel by using ADO: Another method to access excel files is by using ADO objects
Following example demonstrates how to use ADO objects to fetch data from the xls file.

Input File:






Code:



Dim ado
Set ado=CreateObject("ADODB.connection")
ado.open "DSN=Excel Files;DBQ=C:\Users\sandeep\Desktop\Training\Excel\ExcelClass-1 - Exam.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
Set rs=CreateObject("ADODB.Recordset")
rs.Open "Select Sample  from [Sorting$]",ado


Do until rs.EOF 
For each rec in rs.Fields
If isnull(rec.value) then 
Exit do 
else
print rec.value
end if 
Next
rs.MoveNext
Loop


rs.Close
ado.Close
Set rs=nothing
Set ado=nothing







Output:




Tuesday, January 18, 2011

Kill Prcoess By Name without any Popup

While developing scripts several times i have noticed that when trying to close the objects due to unforseen error a popup comes up which asks for the confirmation of closing the process or killing the process.
Many a time we require to cleanup the environment by killing all excel & iexplore process before starting the execution. I use the following function to do the same with out giving any popup.

'Process Name= Name of the process from Task manager : Example: Excel.exe

Call KillProcessByName ("Excel.exe")  ' this call with kill the opened excel files irrespective of there status

Public Function KillProcessByName(ProcessName)

Dim objWMIService,colProcessList,objProcess ' Define variables

Set objWMIService =GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2")

Set colProcessList =objWMIService.ExecQuery ("Select " & "* from Win32_Process Where Name =" &"'" &ProcessName & "'")     ' Select the process by name

For Each objProcess in colProcessList
     On error resume next
     objProcess.Terminate()   ' Terminate the process
     On error goto 0
Next

'Release the objects
Set objWMIService =Nothing
Set colProcessList =Nothing

End Function

Kindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology

Saturday, January 15, 2011

Synchronization - Wait|Exist|Do while|Sync

1. Wait Statement: It will wait for the specified amount of time. We can specify the time in both seconds and milliseconds. But its necessary to define time in the wait statement even it is zero otherwise it will give you an error.

Example: wait, wait () both will give errors
while  wait(0), wait 0, wait 0, 10000 are valid statements.

also wait (0, 10000) will give an error and wait 0,10000 is a valid statement.

The disadvantage of wait statements is that it is not applied on a condition. If this statements is encountered by the compiler then it will wait for the specified amount of time.

2. Exist Property: A very useful property when dealing with the dynamic objects which takes time to download on the web pages.As the name suggests this property checks if a objects exist before doing operations on the object. This property can be used almost all the objects. It is used as follows:

A=Browser("").Page("").Frame("").webedit("Login").exist(09)

The above exist statement will check if the "Login" object exists on the WebPage or not. If it exist then it return True to "A: and if not then it will return False to "A". In real time we generally checks if some objects exist or not before doing some operation on it.

Example:


If Browser("JobCenter").Page("JobCenter").WebButton(strKeywordName).Exist(10) then Browser("JobCenter").Page("JobCenter").WebButton(strKeywordName).Click end if 


Difference between wait and Exist Statement:
1. Execution will wait specifically for the  amount specified in Wait statement. If wait(10) is define then the QTP will wait for 10 seconds. No matter what happens. So if we have a script which run 100 time then naturally it is going to increase the run time significantly.
Exist: Execution will move to next statement as soon as Exist return a True. So if Exist(10) is specified and the condition become true on the very first second then QTP will move to next statement. Thus we have directly saved the 9 second.

2. Wait is a statement while Exist is a property. By then help of wait we pause the execution for certain amount of time while Exist checks if a specified objects exists or not for the specified time. Thus we can use it in the loops and take various decisions.

Note:
1. There is no point of using the exist property when we are taking it value back in a variable. So following statement will throw Error.
B("").P("").WebEdit("").Exist - Error
B("").P("").WebEdit("").Exist(10) - Error

while following statements will work fine

a=B("").P("").WebEdit("").Exist - Good 
a= B("").P("").WebEdit("").Exist(10) - Good


2.Exist can't be used with the empty braces. It will give u error.However we can use it with the "0" in Exist which says don't wait if the objects Exist or don't exist.
a=B("").P("").WebEdit("").Exist() - Error
a=B("").P("").WebEdit("").Exist(0) - Good

3. Exist statement with Empty braces will wait for the object synchronization time i.e. if the time is not specified in the exist statement then it will take the time from object synchronization timeout.

a=B("").P("").WebEdit("").Exist  - will wait for Object synchronization time by default 20 secs
a=B("").P("").WebEdit("").Exist(0)  - will wait for zero secs
a=B("").P("").WebEdit("").Exist (10) - will wait for 10 secs

3. Do While: Do while loops are best suited when objects ares dynamics, objects are visible based upon state of some other objects. For example generally in application we have submit forms/submit details pages/upload attachments etc; in these cases the next page generally depends upon the size, number, connection speed & server response time. So we have a simple Do while loop here which will wait till the next page is not visible and we can specify the timeout period in the loop to; just in case something goes wrong.

 Example:

Dim Counter:Counter=0


Browser("").Page("").Frame("").Link("").Click
Do
If Browser("").Page("").Frame("").WebTable(""").Exist(5) Then
         If trim(Browser("").Page("").Frame("").WebTable("").getcelldata(1,1))= Then 
                    Exit Do
         end if    
end if        
Counter= Counter+1   'counter for tracking the no of attempts

If Counter=5  Then
      Reporter.ReportEvent micFail,"", ""
      ExitActionIteration
elseif Counter=2 then
      Browser("").Page("").Frame("").Link("").Click  ' submit upload webtable request again
end If
Loop until Counter    > 5   ' exit in case webtable is not found after 5 attempts

The above loop wait for the webtable to upload after clicking on a particular link. The loop keeps on checking some specific value ( Header of the webtable) till specified amount of time. If the webtable has not appeared the script click on the link again to do submit the upload webtable request again. If the webtable is not uploaded after 5 attempts then it will report the fail and skips to the next action iteration.

4 Sync Method: We can use the Browser and Page sync methods to synchronize the Browser and Page navigation

The following sync statements will wait for the browser to complete its navigation i.e. till the browser status bar displays "Done"
Browser("").sync

The following sync statements will wait for the page to download i.e. displayed. Whenever we navigate to a page the page gets download, the page sync wait till the page is displayed
Browser("").Page("").sync

Monday, December 13, 2010

Solution: QTP is recognizing web objects as win objects

One of the following may be causing problem:

1. IE- Tabbed browsing should be disabled.

2. IE - Tools - Add-ons - Manage Add-ons - BHOManager Class ADD-On should be enabled.

3. Start - QTP - Tools - Register New Browser Control - IE7 exe should be registered.

4. IE- Security Settings - Uncheck the checkbox Enable Advance Security

5. Application is opened before QTP. Application should be opened only after qtp

6. dll files are corrupted. Reregister then by going thru:  Programs -> QTP -> Tools -> Additional installation requirements

Friday, December 3, 2010

QTP File Menu Missing problem

QTP File Menu options generally sometimes goes to hidden or missing mode. We are not able to see them. We generally try to repair/reinstall QTP, restart system, check the memory size but nothing seems to work. A very simple solution of this problem is as follows:

1. Open QTP
2. Right click in the QTP toolbar
3. Go to customize
4. Toolbars
5. Reset All

And the problem should be solved now.

Saturday, November 27, 2010

QTP Certification M39 & M16

HP has changed the Functional testing certification exam numbers. Previously it was M16 now for QTP 10 they have changed it to M39. Also giving a exam in India is very cheap as compared to US. The examination fee here in US is 170 $ where as when i have checked the same for India it is 70$. So its better to give the exam  in india itself :-)

Sunday, June 27, 2010

Difference between different QTP versions ( 9.2, 9.5, 10.0)

Between QTP 9.5 & earlier versions 

1. Checkpoints and Output values can be seen & edited in the OR while same was not possible in the earlier versions of QTP.
2. Installation file is a single one bundled with all add-ins, previously we have to download add-ins separately.
3. Movie recorder was introduced in the results
4. Web add-in extensibility add- in was introduced
5. Maintenance run mode was introduced
6. It also supports the 64 bit windows edition while the earlier were supporting only 32 bit systems only
7. Bitmap tolerance feature was introduced in QTP 9.5

Between QTP 10.0 & Earlier versions 
1. Delphi add-in extensibility is introduced
2. System performance parameters can be monitored
3. COM objects for the Bitmap checkpoints has been introduced
4. Complete UI changes
5. Version control with the help of QC 10.0
6. To Do pane
7. Call actions dynamically
8. From QTP results we can directly go on to the script lines
9. All test resources can be save on one go from QC

Friday, June 25, 2010

Test Batch Runner - Run QTP Scripts from xls file

To run QTP Scripts from a xls file we need to use QTP AOM. First we have to write following code in a vbs file and save it anywhere on your system.

Option Explicit

Dim qtp 
Set qtp =CreateObject("QuickTest.Application")
qtp.Launch
qtp.Visible=True


Dim xl,bk,s
Set xl=createobject("Excel.application")
Set bk=xl.Workbooks.Open("D:\QTPScripts\Test Batch Runner\TestBatchRunner.xls")
Set s=xl.ActiveWorkbook.Worksheets("Sheet1")
Dim RowNo
RowNo=2
While trim(lcase(s.cells(RowNo,1)))<>"end"
qtp.Open s.cells RowNo,1
Dim qtpTest

'Assign the data table path 
qtp.Test.Settings.Resources.DataTablePath = s.cells(RowNo,2)

' Results
Dim qtpResults
                        Set qtpResults = CreateObject("QuickTest.RunResultsOptions") 
'run the test
qtp.Test.Run  qtpResults,True
RowNo=RowNo+1
Wend
'Release Objects
Set qtp=nothing
bk.Close
Set s=Nothing
Set bk=nothing
Set xl=nothing


change the workbook path as per your workbook.
Either use the following format for your xls file





or adjust the script as per ur file format. 

Double click the vbs file and it should run all the scripts one by one.