Uploading A Defect Using ALM OTA API and VBA

       Hi everyone! Today I am going to teach you how to upload a defect using OTA API. From my precious posts you can find the process of Connecting to QC ALM Using OTA API. After you have understood the basics of how to connect to QC, you will need to know about the Bug Factory and the parameters of a Bug Object. In case you have missed that, I strongly recommend check out my other post Pulling Defects from QC ALM using OTA API.

        So how do we update a defect? Well, for one the defect doesn't exist yet in QC ALM Database. So first we create a Bug using AddItem method and we add a null value. This creates a Bug Object. We use the properties or fields of the Bug Object to specify the parameters of the defect. And then using Post method we simply post the Bug to the QC Database! It's that simple!

        Here goes the code :

Sub GetOpenDefects()

    Dim msgStr as String
    Dim TDConn as TDAPIOLELib.TDConnection
    Dim BugStatus As String

    Dim BugProject As String

    Dim BugSummary As String

    Dim BugDescription As String

    Dim BugDetectedBy As String

    Dim BugAssignedTo As String

    Dim BugPriority As String

    Dim BugSeverity As String

    Set TDConn = CreateObject("TDApiOle80.TDConnection")
    
    TDConn.InitConnectionEx [QCbinpath]
    
    TDConn.Login [UserName], [Password]
    
    If TDConn.LoggedIn Then
    
        TDConn.Connect [Domain], [Project]
    
        If TDConn.Connected
            Dim BugFact as TDAPIOLELib.BugFactory
            Dim NewBug as TDAPIOLELib.Bug
            'Get the BugFactory 
            Set BugFact = TDConn.BugFactory
            'Get the New Bug Object
            Set NewBug = BugFact.AddItem("")

            'Set the New Bug Object parameters
            NewBug.Status = BugStatus
            NewBug.Project = BugProject
            NewBug.Summary = BugSummary
            NewBug.Description = BugDescription
            NewBug.DetectionDate= Today()
            NewBug.DetectedBy = BugDetectedBy
            NewBug.AssignedTo = BugAssignedTo

            NewBug.Priority = BugPriority   

            NewBug.Severity = BugSeverity

            'In case you have to update a custom field, add a line like this

            'NewBug.Field("BG_USER_XX")= "Some value"

            'Force the bug to update in QC

            NewBug.Post() 

            MsgBox("Bug is Updated!")
        Else: MsgBox("Failed to Connect to Project!")
        End If
    Else : MsgBox("Login Failed!")
    End If
    TDConn.DisconnectProject
    TDConn.Logout
    TDConn.ReleaseConnection
    TDConn = Nothing
End Sub

        The reason I used so many string variables for the values of the Bug Object parameters is that you can use user input options. So here you have a brand new code for updating defect in QC. Play around with a bit to learn more!
Thanks for visiting!

Pulling Defects From QC ALM Using OTA API

        Hi everyone! In my last post I have shown you to Connect to QC Using OTA API. Today we use the TDConnection Object to connect to QC and do something more. I was confused thinking about which of the topics to cover after we have established the connection to QC.  I wanted to portray how simply and easily we can use OTA without getting into a lot of coding jargon, yet giving a good glimpse of how OTA API worked. So finally I decided to show how to pull defects from QC.
        Now, before I get into the code, you need to know that everything(Test Plan,Test Set,Bugs) is handled in QC as a Factory, which is but a collection of Objects. For example, we have the TestSetFactory, TestFactory, BugFactory, etc about which I'll talk about in later posts.
        Each item in the factory can be accessed directly via Item Property of the Factory Object. For example - FactoryObject.Item([SomePrimaryKeyID]). However for easy manipulation, what we usually do is get a List of Objects from the Factory Object. There is also an option of using a Filter for populating the list. For a Filter Object, we set the filter on a field name and provide the desired value. However if we do not want to filter out our results, we can easily use a null string when we populate the list. Needless to say if no filter is required, there's absolutely no need to create a Filter Object.

        Here is the code:

Sub GetOpenDefects()

    Dim msgStr as String
    Dim TDConn as TDAPIOLELib.TDConnection
    
    Set TDConn = CreateObject("TDApiOle80.TDConnection")
    
    TDConn.InitConnectionEx [QCbinpath]
    
    TDConn.Login [UserName], [Password]
    
    If TDConn.LoggedIn Then
    
        TDConn.Connect [Domain], [Project]
    
        If TDConn.Connected
            Dim BugFact as TDAPIOLELib.BugFactory
            Dim BugFilter as TDAPIOLELib.Filter
            Dim BugList as TDAPIOLELib.List
            
            'Get the BugFactory 
            Set BugFact = TDConn.BugFactory
            'Get the Filter for the connection
            Set BugFilter = TDConn.Filter

            'Set the filter to the field
            BugFilter.Filter("BG_STATUS") = "Open"

            'Get/Populate a list of all the defects with status Open
            Set BugList = BugFact.NewList(BugFilter.Text) 

            'To Get a list of all the defects we can use null string
            'Set BugList = BugFact.NewList("") 

            'Iterate through all the defects.
            For Each aBug In BugList
            'Get a specified set of fields.
            msgStr = " Defect ID : " & aBug.Field("BG_BUG_ID") & vbNewLine &
                 "Summary : " & aBug.Field("BG_SUMMARY") & vbNewLine & 
                 "Detected By" & aBug.Field("BG_DETECTED_BY") & vbNewLine &
                 "Detected on Date" & aBug.Field("BG_DETECTION_DATE") & vbNewLine &
                 "Status" & aBug.Field("BG_STATUS") & vbNewLine &
                 "Subject" & aBug.Field("BG_SUBJECT") & vbNewLine &
                 "Severity" & aBug.Field("BG_SEVERITY") & vbNewLine &
                 "Priority" & aBug.Priority & vbNewLine &
                 "Assigned To" & aBug.Field("BG_RESPONSIBLE")
            MsgBox(msgStr)
            'Send a Mail if you would like
            TDConn.SendMail [Mail To], [Mail From], [Mail Subject], msgStr, NULL , "HTML"
            Next
        Else: MsgBox("Failed to Connect to Project!")
        End If
    Else : MsgBox("Login Failed!")
    End If
    TDConn.Logout
    TDConn.ReleaseConnection
    TDConn = Nothing
End Sub

        So here we have the code to get defects from QC. However I have not added ant error handler this time. Instead I have used if-else to handle the errors. That's not a good way I know, but it works! One thing to be noted is that, the items in the Lists are also Objects and each Object has certain fields. Here I have accessed most of the fields of a Defect by the field-names in the QC Database. However, almost all of them can be accessed as reference to the Bug Object. For example:

1. aBug.Field("BG_BUG_ID")  is same as  aBug.ID,
2. aBug.Field("BG_SUMMARY") is same as aBug.Summary,
3. aBug.Field("BG_DECTECTED_BY") is same as aBug.DetectedBy, etc.

        However, not all fields you can see in the UI can be accessed similarly. Once a HP QC ALM is licensed and installed, the licensee has the right to add Custom Fields to the existing databases in ALM. These custom new fields will be referenced as BG_USER_XX for the Bug Object, where XX stands for numbers like BG_USER_01. In such situations, you will need to use the Field property of the Bug Object in order to retrieve the value of the field.
        So you have a brand new code to play with. Go ahead and explore new possibilities and when you do, keep me posted!
Thanks for visiting! For queries/bugs/thoughts on the matter, please leave a comment below.

Introduction to HP Quality Center OTA API and Connecting to QC by VBA

        Today I am going to talk about HP Quality Center OTA API. Now for the newbies, HP Quality Center(QC) is a test management tool. There's a good tutorial here for the theoretical part  and manual management of QC. But Manual is Boring!
        So here we are, trying to automate various tedious job, usually done manually. It is a time-consuming process and there is always a possibility of human error. Since we all like to have our deliverables finished by EOD, automating QC is a great start.
        Now QC is basically a web based software, and like all web based soft-wares, it has its own logic set up as URL requests. However, for automating QC in a desktop environment, we are provided with an Object Oriented API (Application Programming Interface).
        Open Test Architecture or OTA is available in HP QC/ALM(Application Lifecycle Management). Download this by following these simple steps:

        1) Log on to HP ALM
        2) Go to Help -> ALM Tools -> HP ALM Connectivity -> Download HP ALM Connectivity
        3) A pop up appears to download TDConnect.exe
        4) Save the file on desktop and Double-Click to run this installer

        And your API Library is installed in your desktop, available to be used in your coding. Now we begin coding for connecting your VBA Project to QC. First I'll explain about the API. The main OTA Library package for ALM is referenced by the name TDAPIOLELib. Every usable class/interface can referenced from the same. Many people have the habit of initializing variables for a QC Project as Object. But this complicates things unnecessarily. If every variable is set as an Object, we would not be able to use the code completion features of VB Editor. Hence, a newbie will not know where to go from a given line of code as there will be no suggestions to help them. Plus if in future, the code was to be updated, the developer might have forgotten what the heck he had written in the past!
        Neways, coming back to our topic at hand that is Connecting to QC, we have to use the TDConnection Class. First we begin a connection with the Server URL of the QC. Now QC can be installed in a local machine, running on local host or on a web server. The logic remains the same. Then after a connection is initiated, we send in the login credentials, that is the user name and password. Once the user is logged in, we have an option of retrieving the Domain and the Project mapped to the user and give the user an option to select from the list. Or we can just take that as an input and directly connect to the project. Once this  is done, we are ready for other CRUD operations using objects/database commands, provided the user has access to the same. Here is the VBA Code:

Dim TDConn as TDAPIOLELib.TDConnection : TDConn = Nothing
Dim QCUrl, QCUsername, QCPassword, QCDomain, QCProject as String

' I am taking them as preset values. 
' User input with required validations is a better way to handle this.
QCUrl = "http://XXXX/qcbin"
QCUsername = "MyUser"
QCPassword = "MyPwd"
QCDomain = "MyDomain" 
QCProject = "MyProject"

' Now the real code begins.
'Add an Error Handler for bugs
On Error goto ERRHANDLER
' Create an instance/object of the TDConnection class
Set TDConn = CreateObject("TDApiOle80.TDConnection") 
' I quite haven't figured out why its TDApiOle80 and not TDAPIOLELib.
' But its TDApiOle80 for VBA and TDAPIOLELib for VB.Net
TDConn.InitConnectionEx QCUrl
If TDConn.Connected then
    TDConn.Login QCUsername, QCPassword
    If TDConn.IsLoggedIn then
        TDConn.Connect QCDomain, QCProject
        If TDConn.ProjectConnected then
            MsgBox "QC is Connected!"
        Else
            err.raise vbObject+1, "QC Project could not be connected!", "QC Project"
        End If
    Else
        err.raise vbObject+1, "Login Failed! Please check the Username/Password","Login"
    End If
Else
    err.raise vbObject+1, "Could not connect to the Server!", "Server Error"
End If

ERRHANDLER:
    msgbox err.Number & " : " & err.Description

        Well there you go. You now have the code to connect to QC. Have Fun exploring the possibilities! For details about the flags I have used, like Connected, IsLoggedIn and ProjectConnected refer to the OTA Reference available in QC/ALM Help.

Thanks and keep visiting!