SQL Queries for Quality Center Modules

One of the few good things [there are only few] about QC is that all the info is stored in a MSSQL DB, and can be easily accessed via QC OTA, Analysis Modules, etc. Here are a few samples.

A. Notes on some Components of QC Database -

• RELEASE_CYCLES - this table stores details about the releases created in the Release module.
• CYCLE - this table stores info about the test sets, and uses CY_ASSIGN_RCYC
• TESTCYCL - this table stores info about the instances of test runs, status, etc
• TEST - this table stores info about the test names, test types [manual, automated], designer, etc

 
B. Process to build SQL Queries for different Quality Center modules

All Analysis reports have to be built in the Dashboard >> Analysis module. There are 3 different types of reports that can be built, but Excel is the one we are focusing on right now, since, we can add SQL Queries only to Excel type reports.

Report type - Create a report to get the Status and Count of all the tests present under different Test Sets, for a particular Release Cycle.

Solution Steps -

• Create a Release Cycle under Management >> Release Module, based on the name of your current release. Eg., 'App_Release2_SIT'.
• Note down the value of 'Target Cycle ID' field populated in QC - this would get saved as CY_ASSIGN_RCYC field in the QC database, lets assume it as 1005.
• Create a folder in Test Lab as 'Parent_TL_Folder'. Create another subfolder called SIT under this parent folder, and assign the release cycle, 'App_Release2_SIT' to this folder.
• If there are 2 different cycles in your plan, one for Functional SIT and one for Regression, then create another cycle for Regression like 'App_Release2_Regression'. Then create a seperate folder for Regression tests, under the same 'Parent_TL_Folder' at the same level as 'SIT' folder, and assign this regression cycle 'App_Release2_Regression' to this folder.
• Now, create all the different Test Sets for this release under this Test Lab folder. Lets say there are 3 test sets called 'Artifact1' 'Artifact2' 'Artifact3'.
• Now, pull all your tests under these 3 tet sets, from the Test Plan.
• Once this all is done, all you need is the 'Target Cycle ID' for both the cycles [SIT and Regression] to query all the tests for the release.

C. Sample Queries -
--------------------------------------------------------------
--Query to get the execution Status and Count for all the tests under a given release cycle

SELECT CYCLE.CY_CYCLE ,
COUNT (*) AS "No of Tests" ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'PASSED' THEN 1 END ) AS 'Passed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'FAILED' THEN 1 END ) AS 'Failed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'BLOCKED' THEN 1 END ) AS 'Blocked' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NO RUN' THEN 1 END ) AS 'No Run' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS IN ( 'PASSED' , 'FAILED' ) THEN 1 END ) AS 'Executed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NOT COMPLETED' THEN 1 END ) AS 'Partially Executed'
FROM CYCLE
JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
--JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID    --Join with TEST table, can help us pull out TEST_NAME as well for the tests
WHERE 1=1
AND CYCLE.CY_ASSIGN_RCYC = '1005'
--AND CYCLE.CY_CYCLE LIKE 'BRD%'    --filter all the test sets starting with BRD
GROUP BY CYCLE.CY_CYCLE

--------------------------------------------------------------
-- Query to get the Name and ID of the Release Cycle

SELECT RCYC_NAME, RCYC_ID, *
FROM RELEASE_CYCLES
WHERE 1=1
AND RCYC_ID = '1005'

--------------------------------------------------------------
--Query to get Names of all the Test Sets under the chosen Release Cycle

SELECT CY_CYCLE AS "TEST_SET_NAME" , *
FROM CYCLE
WHERE 1=1
AND CY_ASSIGN_RCYC = '1005'
ORDER BY CY_CYCLE

--------------------------------------------------------------
--Query to get Defect related details for a Release

SELECT
BG_BUG_ID AS "DEFET ID" ,
BG_SEVERITY AS "SEVERITY" ,
BG_STATUS AS "STATUS" ,
BG_SUMMARY AS "SUMMARY" ,
BG_PRIORITY AS "PRIORITY" ,
BG_RESPONSIBLE AS "ASSIGNED TO" ,
BG_USER_11 AS "DETECTED BY" ,
BG_DETECTION_DATE AS "DETECTED ON" ,
BG_DEV_COMMENTS AS "COMMENTS"
FROM BUG
WHERE 1=1
AND BG_STATUS NOT IN ('Closed', Deferred' , 'TestReady' )
AND BG_USER_12 = 'SIT ENV'
AND BG_USER_13 = 'App_Release2_SIT'

--------------------------------------------------------------
--Query to get BPT Components for a Test. 
--The below query gets the design steps of the components part of a BPT test in the order they are in the test. Just add the TestID where shown.

SELECT
C.CO_NAME || '[' || BT.BC_CO_INSTANCE || ']',
CS.CS_STEP_NAME ,
CS.CS_DESCRIPTION,
CS.CS_EXPECTED
FROM BPTEST_TO_COMPONENTS BT,
COMPONENT C,
COMPONENT_STEP CS
WHERE 1=1
AND BT.BC_PARENT_ID = <testIDhere>
AND BT.BC_CO_ID = C.CO_ID
AND C.CO_ID = CS.CS_COMPONENT_ID
ORDER BY BT.BC_ORDER, CS.CS_STEP_ORDER

--------------------------------------------------------------

How to work with QC OTA - Example 3 - Function to Run a Query on QC Database

'------------------------------------
' Function Name:         funcRunQueryOnOC
' Description:               This function will run a query on the QC database and then return the recordset containing the results
' Input Parameters:     sQCQuery - The query to be run on the QC database
' Output Parameters:  The QC RecordSet Object
' Author:                      Ashish Jaiswal
'------------------------------------

Function funcRunQueryOnOC(sQCQuery)

    'Getting the QC Connection Object. Refer earlier post about the implementation of this function.
    Set oALMConnObj = funcGetALMConnectionObj (sQCServer, sQCUsername, sQCPassword, sQCDomain, sQCProject)

    'Getting the Command object to run the query
    Set oQCCommand = oALMConnObj.Command

    oQCCommand.CommandText = sQCQuery
    'Sample query: "Select * from Bug where BG_BUG_ID = 9295"

    Set oQCRecordSet = oQCCommand.Execute

    'Now returning the recordset
    Set funcRunQueryOnOC = oQCRecordSet

End Function 
'------------------------------------

How to work with QC OTA - Example 2 - Get a List of All Defects in QC

Code to get a list of all the details for all the defects in QC Defects Module


''Complete path of the file where the info from QC needs to be written
sQCLogFilePath = "C:\Automation\Defects_List.csv"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(sQCLogFilePath)

''Getting the QC Connection Object. Refer earlier post about the implementation of this function
Set oALMConnObj = funcGetALMConnectionObj (sQCServer, sQCUsername, sQCPassword, sQCDomain, sQCProject)

''Getting the Bug Factory object
Set oBugFactory = oALMConnObj.BugFactory

Set oBugList = oBugFactory.NewList("")

oFile.WriteLine("Defect ID" & "," & "Summary")

For Each oBug In oBugList

oFile.WriteLine(oBug.ID & "," & oBug.Summary)
''We can add other fields as: Bug.Status, Bug.Priority, Bug.AssignedTo

Next

Set oFile = Nothing
Set oFSO = Nothing
Set oBugFactory = Nothing
Set oALMConnObj = Nothing

How to work with QC OTA - Example 1 - Get Defect ID and the size of Attachments in each defect

More often than not, we get weird requests, like getting a list of all the defects in QC and the size of attachments for each of them, but thankfully we have QC OTA to help us ease the pain. Use the below code to get a file with Defect ID and the size of all the attachments for them.


''Complete path of the file where the info from QC needs to be written
sQCLogFilePath = "C:\Automation\Attachments_List.csv"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(sQCLogFilePath)

''Getting the QC Connection Object. Refer earlier post about the implementation of this function
Set oALMConnObj = funcGetALMConnectionObj (sQCServer, sQCUsername, sQCPassword, sQCDomain, sQCProject)

''Getting the Bug Factory object
Set oBugFactory = oALMConnObj.BugFactory

''Defining the filters for the Defects
Set oBugFilter = oBugFactory.Filter
oBugFilter.Filter("BG_STATUS") = "Closed"
oBugFilter.Filter("BG_ATTACHMENT") = "Y"
oBugFilter.Filter("BG_DETECTED_BY") = "ASHISH.JAISWAL"

Set oBugList = oBugFilter.NewList

oFile.WriteLine("Defect ID" & "," & "Total Attachment Size")

For Each oBug In oBugList

        Set oBugAttachments = oBug.Attachments
        Set oBugAttachmentList = oBugAttachments.NewList("")

        iTotalAttachmentSize = 0
        For Each oBugAttachment In oBugAttachmentList
            iTotalAttachmentSize = iTotalAttachmentSize + oBugAttachment.FileSize
        Next

        oFile.WriteLine(oBug.ID & "," & iTotalAttachmentSize)

        Set oBugAttachmentList = Nothing
        Set oBugAttachments = Nothing

Next

Set oFile = Nothing
Set oFSO = Nothing
Set oBugFilter = Nothing
Set oBugFactory = Nothing
Set oALMConnObj = Nothing

Connect to ALM-QC via VbS or QTP

You can use the following code to connect to ALM\QC via pure VbScript, without using QTP, even though this code would work from QTP as well.

Prerequisite:

• ALM\QC should be installed

Code -

'''----------------------------------------------------------------------------------------------------------------------
'' Function Name:            funcGetALMConnectionObj
'' Description:                   This will connect to the ALM\QC server via either pure VbS or QTP
'' Input Parameters:        sQCServer
''                                         sQCUsername
''                                         sQCPassword
''                                         sQCDomain
''                                         sQCProject
'' Output Parameters:     oALMConnObj [The ALM Connection Object]
'' Author:                          Ashish Jaiswal
'''----------------------------------------------------------------------------------------------------------------------
Function funcGetALMConnectionObj (sQCServer, sQCUsername, sQCPassword, sQCDomain, sQCProject)

    ''Defining the Parent QC OTA Object
    Set oALMConnObj = CreateObject("TDAPIOLE80.TDConnection")

    ''Initiating the connection to the QC Server
    oALMConnObj.InitConnectionEx sQCServer

    ''Logging in
    oALMConnObj.Login sQCUsername, sQCPassword

    ''Connecting to the required project and domain
    oALMConnObj.Connect sQCDomain, sQCProject

    ''Returning the object
    Set funcGetALMConnectionObj = oALMConnObj

End Function
'''----------------------------------------------------------------------------------------------------------------------

Usage:

''Defining the connection parameters for QC
sQCServer = "http://ealm11.anyorg.net/qcbin/"
sQCUsername = "username"
sQCPassword = "password"
sQCDomain = "domain"
sQCProject = "projectname"

Set oALMConnObj = funcGetALMConnectionObj (sQCServer, sQCUsername, sQCPassword, sQCDomain, sQCProject)

''Proceeding ahead only if connected
If oALMConnObj.Connected Then

    MsgBox "Connected to QC!"
    MsgBox oALMConnObj.ProjectName

Else

    MsgBox "Not connected to QC"

End If
'''----------------------------------------------------------------------------------------------------------------------

Common troubleshooting measures -

• Register the OTAClient.dl
• For this put the OTAClient.dll in the "C:\Windows\System32" drive, if its not already there
• Run the following command to [re]register the dll from the Run window

                      RegSvr32 "C:\Windows\System32\OTAClient.dll"

• If you are getting an error something like 'ActiveX can't create object - TDConnection' then try running the connection code by putting in a .vbs file, from the Run window as follows, via the WScript.exe -

           C:\Windows\SysWOW64\wscript.exe "C:\Automation\QC-Connection.vbs"