Connecting ALM/QC using HP’s OTA (Open Test Architecture) API – Advanced

· by · in ALM/QC. ·

Hello,

In this post, we will learn about how to create a connection to ALM using OTA API.

1. Introduction

Open Test Architecture is an API shipped with HP ALM/QC, for automating background tasks like modifying bulk field values or downloading all of the attachments from the defects module. We can also use this API for Generating bulk reports, custom graphs and have the functionality built which is not available otherwise using the ALM/QC front end application.

OTA API’s object will be downloaded and registered in the machine when we open ALM for the first time. Once the components are registered, you can use the OTA COM Type Library for connecting ALM using any programming language which supports COM objects.

we will use Excel 2010 for connecting and accessing ALM/QC using OTA API.

2. Connecting to ALM using OTA API

Below are the steps for using OTA API for ALM Connection

A) Open Excel and Click on Developer ribbon and Click Visual Basic.

B) Click on Tools-> References and Check “OTA COM Type Library”.

OTA COM LIBRARY REFERENCE EXCEL

 

Once we have the reference for this library, we can use the following code for connecting to ALM/QC

Dim ErrorString As String

'Create Connection Object of OTA API
Dim OQCConnection As New TDAPIOLELib.TDConnection

Sub TestALMOTAConnection()
 If ConnectALM("http://yourQCServer/qcbin", "yourusername", "password", "yourdomainname", "yourprojectnane") = True Then
 MsgBox "Connected to ALM"

 If DisConnectALM = True Then
 MsgBox "Project Disconnected Successfuly"
 Else
 MsgBox ErrString
 End If

 Else
 MsgBox ErrorString
 End If
End Sub

Public Function ConnectALM(URL As String, UserName As String, Password As String, Domain As String, Project As String)

 On Error GoTo Err_Handler

 ErrorString = ""

 'Initiate connection with ALM Server
 OQCConnection.InitConnectionEx URL

 'Connect to User

 OQCConnection.Login UserName, Password

 'Connect to Domain and Project
 OQCConnection.Connect Domain, Project

 '"Successfully Connected to the project, Enjoy!!!"

 ConnectALM = True

 Exit Function

Err_Handler:

 'Check if the connection is successfull
 If OQCConnection.Connected = False Then
 ErrorString = "QC Server URL Initialization failed!!!"
 ConnectALM = False
 Exit Function
 End If

 'Check if Login is Successfull
 If OQCConnection.LoggedIn = False Then
 ErrorString = "Unable to Login to ALM using the provided Credentials"
 ConnectALM = False
 Exit Function
 End If

 'Check if project is connected
 If OQCConnection.ProjectConnected = False Then
 ErrorString = "Unable to Connect to the Domain or Project"
 ConnectALM = False
 End If

End Function

Public Function DisConnectALM()
 On Error GoTo Err_Handler

 ErrString = ""

 If OQCConnection.ProjectConnected Then
 OQCConnection.Disconnect
 End If

 If OQCConnection.LoggedIn Then
 OQCConnection.Logout
 End If

 If OQCConnection.Connected Then
 OQCConnection.ReleaseConnection
 End If

 'Logged Out of QC

 DisConnectQC = True

 Exit Function
Err_Handler:

 If OQCConnection.Connected Then
 ErrString = "Error Occurred while Disconnecting the QC Connection Object"
 End If

 If OQCConnection.ProjectConnected Then
 ErrString = "Error Occurred while disconnecting project"
 End If

 If OQCConnection.LoggedIn Then
 ErrString = "Error Occurred while Loggin out the User"
 End If

 DisConnectQC = False
End Function

Once, you copy the above code, update the variables for a call to the function ConnectALM and execute TestOTAALMConnection function. If everything works fine then you should see a message ‘Connected to ALM’.

Now, let’s understand how the code works here,

Variables used

1) ALM URL: For connecting to ALM using OTA API, we must use the URL till qcbin instead of full ALM URL, e.g. http://YourServer/qcbin.

All other variables should be provided with the values, you are using for connecting to ALM/QC

Functions used

1) InitConnectionEx: This function is used for initializing the connection between ALM and your code. If ALM server is accepting connections.

2) Connected: This property will be false if the connection initialization is not completed.

2) Login: Login functions accepts ALM Username and password and used for login to the ALM/QC Server.

3) LoggedIn: This property is used for identifying, if user login was successful or not in the previous step. Login function will through error and we will have to use

4) Connect: This function will accept domain name and project name and will connect to ALM Project.

5) ProjectConnected: This function will provide details about if the Connection to the project was successful.

Now let’s see how to disconnect ALM connection, which will be similar to Logout functionality using the ALM GUI.

For Logging out of ALM we have to call the corresponding logout functions to InitConnectionEx, Login and Connect. These functions are used in the DisConnectALM function. Below is the description of the function used for disconnecting ALM

Functions

1) Disconnect: This function will disconnect the ALM Project.

2) Logout: This function will log out the user connected.

3) ReleaseConnection: This function will release the ALM connection object, after calling this function we will loose all of the references with ALM server.

Below is the table for showing the method property relationship of the functions and properties used above

       Method (Initial) Init-ConnectionEx Login Connect Disconnect Logout Release-Connection
Property
LoggedIn Error FALSE TRUE TRUE TRUE FALSE Error
Connected FALSE TRUE TRUE TRUE TRUE TRUE FALSE
ServerName Empty String Server Name Server Name Server Name Server Name Server Name Empty String
ProjectName Error Empty String Empty String Project Name Empty String Empty String Error
Project-Connected Error FALSE FALSE TRUE FALSE FALSE Error

We will discuss about accessing and changing specific ALM modules in the later posts.

Thanks

45 responses to “Connecting ALM/QC using HP’s OTA (Open Test Architecture) API – Advanced”

  1. Pingback: DOWNLOADING ATTACHMENTS FROM ALM/QC, Tests and Design Steps module | Automation Insights·

  2. Pingback: Filtering Test from Test Plan using Test ID (TS_TEST_ID) in ALM/QC | Automation Insights·

  3. Pingback: Filtering Defects using Defect ID (BG_BUG_ID) in ALM/QC | Automation Insights·

  4. Hi Sumeet,

    I want to run Single test case from ALM test set, can you help me in achieving through OTA, I want to create one single vbs to do this.

    Thanks,
    Manju

    Like

  5. Hi,

    I am looking for a sample to download the Results tab files using VBcode. Could you please advise.

    Like

  7. Hi,
    Following error thrown while creating connection object:
    “Retrieving the COM class factory for component with CLSID {C5CBD7B2-490C-45F5-8C40-B8C3D108E6D7} failed due to the following error: 800703e6 Invalid access to memory location. (Exception from HRESULT: 0x800703E6).”

    Like

  8. Hi Sumeeth,
    can we add values while passing test case in test set like domain name,test type,Environment and can we click on pass all and end run and can be capture run id as well?

    Like

    • Hi Orel

      What are the steps to replicate the error ? Which programming language you are using ?
      What is the version of ALM you are using ?

      I don’t see error code 429 in the list of error codes available for OTA API. Can you send me the description for the error ?

      Thanks,
      Sumeet singh kushwah

      Like

  10. Hi Sumeet,

    The article is very informative, Thanks.

    Using the below code, I am not able to open the ALM. There is no error also. Could you please guide
    Dim QCConnection As Object;

    Set QCConnection= createObject (‘TDApiOle80.TDConnection’)

    Dim sUsername, sPassword

    Dim sProject, sDomain

    sUsername=sheet1.Range(A1).Value

    sPassword=sheet1.Range(B1).Value

    sProject=sheet1.Range(C1).Value

    sDomain=sheet1.Range(D1).Value

    QCConnection.InitConnectionEx sheet1.Range(E1).Value

    QCConnection.login sUsername,sPassword

    QCConnection.connect sDomain,sProject

    Like

  13. Pingback: Executing Queries using OTA API | Automation Insights·

  14. Pingback: Renaming test plan folder using OTA API | Automation Insights·

  15. Pingback: Deleting ALM test plan folder using OTA API | Automation Insights·

  16. Pingback: Find tests under a test plan folder using OTA API | Automation Insights·

  17. Pingback: Find folders under test plan folder in ALM using OTA API | Automation Insights·

  18. Hi Sumeet,
    I am getting the below error when I try my c# code to check connection with HP ALM.

    ‘System.__ComObject’ to interface type ‘TDAPIOLELib.TDConnection’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{5FFC8521-6228-433D-B6DE-0D24E9E1B2D2}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).’

    Like

  20. Hi Sumeet,

    I am looking for automation this process using Java. Could you shed some ideas on how to achieve my goal.

    Regards
    Thulasiram

    Like

  22. Hi ,
    Getting error : “Not connected to Domain Server” while creating a project through SA Client.dll in c#
    Kindly guide me in this .

    Like

  23. using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Configuration;
    using System.Web;
    using SACLIENTLib;

    namespace ServiceLibrary
    {
    public enum Resource
    {
    login
    }

    public class ALMService
    {
    SACLIENTLib.SAapi _saClinet = null;
    public string m_Username;
    public string m_Password;

    public ALMService(string username, string password)
    {
    m_Username = username;
    m_Password = password;
    }

    //Destructor for Class .
    ~ALMService()
    {
    m_Username = string.Empty;
    m_Password = string.Empty;
    }//END..

    public bool IsAuthenciateALM(
    Resource resource,
    string m_BaseUrl = null
    )
    {
    //string url = string.Format(“{0}{1}”, m_BaseUrl, resource.ToString());

    string url = string.Format(“{0}”, m_BaseUrl);

    try
    {
    _saClinet = new SAapi();

    _saClinet.Login(url, m_Username, m_Password);

    return true;
    }
    catch (Exception Ex)
    {
    _saClinet.Logout();
    return false;
    }
    }

    public string CreateProject(
    Resource resource,
    string domain ,
    string project,
    string m_BaseUrl = null
    )
    {
    string result = string.Empty;
    //string url = string.Format(“{0}{1}/”, m_BaseUrl, resource.ToString());

    //SA_MSSQL_DB_TYPE (value: 2)
    //SA_ORACLE_DB_TYPE (value: 3).

    int SA_ACTIVATE_NEW_PROJECT = 1;

    string url = string.Format(“{0}”, m_BaseUrl);

    try
    {
    _saClinet = new SAapi();

    result = _saClinet.CreateProject(“DEFAULT”, “NewProject123”,2, string.Empty, m_Username, m_Password, string.Empty, string.Empty, 0, 0, SA_ACTIVATE_NEW_PROJECT);
    }
    catch (Exception Ex)
    {
    result = Ex.Message.ToString();
    _saClinet.Logout();
    }

    return result;
    }
    }
    }

    Like

    • The Only thing I will check is your 4th argument, its supposed to be your server name and you have passed it as Empty. Have you tried passing your server name ? It should be in the below format.
      sServerName = “http://myserver/sabin”

      Thanks,
      Sumeet

      Like

  27. I am not able to connect to ALM. Getting Runtime error ‘429’. ActiveX component can’t create object.
    What I see different from the steps I mentioned is,
    In VBA reference, for me it’s showing C:\Users\xxxx\AppData\Local\HP\ALM-Client\12.50.0.0
    instead of
    C:\Program Files (x86)\Common Files\Mercury Interactive\TDAPIClient
    Is it a problem?

    Like

    • You can simply write a query to do it, you just need the folder ID which can also be found by another query.

      /////// Replace folder id in below query

      Select TEST_PLAN_PATH, TS_NAME, DS_STEP_NAME, DS_DESCRIPTION, DS_EXPECTED
      from TEST
      Inner Join
      (
      Select AL_ITEM_ID, AL_DESCRIPTION, LEVEL, SYS_CONNECT_BY_PATH(AL_DESCRIPTION, ‘\’) “TEST_PLAN_PATH”
      from ALL_LISTS
      START WITH AL_ITEM_ID = ‘24560’
      CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
      ) TESTFOLDER
      ON TESTFOLDER.AL_ITEM_ID = TEST.TS_SUBJECT
      Inner join DESSTEPS
      on DS_TEST_ID = TS_TEST_ID
      ORDER BY TEST_PLAN_PATH, TS_TEST_ID, DS_STEP_ORDER

      //// Find folder ID by folder name using the below query
      Select * from ALL_Lists where AL_DESCRIPTION Like ”

      or you can use the API for downloading the design steps, you can find the examples below

      https://github.com/sumeet-kushwah/ALM_OTA_Wrapper/blob/master/ALM_Wrapper/Test.cs

      //Check GetDesignStepList on the above page.

      Like

  29. Also. i have Subject>Folder. I used your query to get the test scripts and design steps.
    1. What is the “TEST_PLAN_PATH” ? like i asked before.
    2.How do I know i have all the scripts downloaded ? how do i verify the num of actual TC in ALM vs what i was able to get from the query.
    3.My folder structure is Subject>Folder>Sub folder1>subfolder2 and so on. Is there a way to extract all info along with the folder hierarchy?

    Like

    • 1) TEST_PLAN_PATH should be in the results and is the folder path for the test case starting the folder ID you provided to the query.
      2) You can use live analysis in the test plan to verify it.
      3) Your folder structure is in TEST_PLAN_PATH, it cant pull the names of parent folders but you can simply concatenate the string in the query to generate that or simply concatenate that in the excel sheet, parent folder will be same for all downloaded tests.

      Like

Leave a comment